上一篇我们从零开始装好了MySQL搞定登录建了几个库和表算是把地基打完了。这篇《MySQL基础二》继续沿着这条线走目标非常明确让你把手头的SQL操作练熟——增删改查、条件过滤、排序分页、分组统计、多表关联最后再带一下事务和索引的入门认知。读完这篇你写出来的SQL不再只是能跑通而是在项目里敢用、能解释清楚为什么这么写。文章适合两类人一类是刚跟着系列第一篇把环境弄好、准备正式上手SQL的初学者另一类是零零散散写过一些SQL但总觉得没有体系、老在一些小细节上翻车的同学。为了让例子好懂后面统一切入两个场景一张users用户表一张orders订单表。所有语法都在这两张表上演示你可以在自己的环境里照着敲一遍效果比光看要扎实得多。1. 先把手头这几张表摸清楚客户端、字段类型和建表约定1.1 连接数据库的常见姿势命令行、图形客户端与连接串动手写SQL之前先说说手头工具这件事。我在带新人时发现一个现象很多人什么工具会一点但每一种都只停留在能连上的程度一旦出问题就不知道怎么排查。最基础的是命令行。装完MySQL之后在终端里执行mysql -u root -p输入密码之后就进入MySQL的交互界面了。日常调试、查看系统状态、处理应急问题命令行永远是兜底的那一个因为任何图形客户端底层都是通过同样的协议跟MySQL通信命令行没问题而客户端连不上问题基本出在客户端配置。图形客户端方面我用得比较多的是DBeaver和Navicat。DBeaver开源免费Navicat功能成熟但需要授权这里不展开谈工具优劣只说要留意一件事连接时填的host、port、用户名、密码必须和MySQL实际配置一致。很多新手连不上数据库不是密码错了而是MySQL没有监听预期端口或者用户只允许本机登录。可以用一条命令验证服务状态sudo systemctl status mysql或者直接看MySQL的端口监听netstat -tlnp | grep 3306项目代码里连MySQL走的是连接串Java里大概长这样jdbc:mysql://127.0.0.1:3306/test_db?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/Shanghai这一段后面项目篇再细细拆现在你只需要知道字符集参数characterEncodingutf8很关键少了它中文读写大概率出乱码。useSSLfalse在本地开发环境也建议加上否则不少客户端会因为SSL握手出奇奇怪怪的报错这次热搜词里就有一个mysql ssl连接错误多半和本地连接没配SSL参数有关。1.2 字段类型选错是后面所有麻烦的根源设计表结构这件事很多人觉得不就是有几个字段、什么类型吗但实际项目里字段类型选错带来的麻烦是连锁的。举个例子存金额。新手容易用FLOAT或者DOUBLE理由是小数嘛。做开发和电商的人看到这里应该会心一笑——FLOAT是浮点数底层是二进制近似存储日常好像没问题但算到分、做对账的时候0.10.2不等于0.3这种经典问题就来了。金额字段正规做法是DECIMAL比如DECIMAL(10,2)表示总长10位、小数2位精确存储不会出现浮点误差。再比如时间。MySQL里有DATETIME和TIMESTAMP两种TIMESTAMP有2038年上限问题而且跟时区挂钩DATETIME的存储范围宽得多实际开发里更省心。我在建表时的默认习惯是业务时间字段基本都用DATETIME并且加DEFAULT CURRENT_TIMESTAMP来记录创建时间。字符串类型是另一个重灾区。CHAR是定长VARCHAR是变长。手机号、身份证这类长度固定的用CHAR问题不大但大多数业务字段比如昵称、地址用VARCHAR更合理因为变长类型按实际长度存储省空间。千万别把VARCHAR(255)当成万能默认字符串越长索引占用的空间越大查询性能越受影响。字符集上建议从一开始就用utf8mb4因为MySQL里的utf8其实不是完整的UTF-8它最多只能存3个字节遇到emoji这类4字节字符就报错。utf8mb4才是完整的UTF-8实现。建一个用户表的完整语句如下CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nickname VARCHAR(50) NOT NULL COMMENT 昵称, phone VARCHAR(20) NOT NULL COMMENT 手机号, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, UNIQUE KEY uk_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;对应的订单表CREATE TABLE orders ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL COMMENT 下单用户ID, amount DECIMAL(10,2) NOT NULL COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态 0待支付 1已支付, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;为什么id用INT UNSIGNED因为大部分业务表的记录量到不了BIGINT的量级INT足够加UNSIGNED把负数区域也让给正数上限翻倍。订单表的user_id加上普通索引是因为后面做关联查询会频繁按用户查订单这个索引会起到决定性作用——这个点在本文第5、6部分会真正用上。ENGINEInnoDB也需要养成习惯。InnoDB支持事务和行级锁而老旧的MyISAM不支持事务表锁粒度大并发写场景容易出问题。从MySQL 5.5之后InnoDB就是默认存储引擎我自己建表时哪怕简单到只有一个字段也一定会写清楚ENGINEInnoDB避免误用其他引擎。2. 增删改的日常INSERT、UPDATE、DELETE的写法与翻车现场2.1 INSERT单行插入、批量插入和不存在才插入增删改查里INSERT是写入操作逻辑最简单但正因为简单很多人忽视它的细节。单行插入的常规写法INSERT INTO users (nickname, phone) VALUES (张三, 13800001111);插入之后可以用SELECT LAST_INSERT_ID();查看刚才这行记录的自增ID这在后面做关联操作时非常有用比如新建用户之后马上给他插入一条订单记录。批量插入是日常开发里非常实用的写法一次多行效率远高于逐条循环插入INSERT INTO users (nickname, phone) VALUES (李四, 13800002222), (王五, 13800003333), (赵六, 13800004444);批量插入的一大价值在减少客户端和MySQL之间的网络往返次数。一次SQL包装100行比100次单条插入快得多这在初始化测试数据、批量导入Excel数据时尤其明显。还有一类场景是有则更新无则插入比如同步外部数据。如果phone上加了唯一索引常规的INSERT在遇到重复时会直接报错而用ON DUPLICATE KEY UPDATE可以优雅地转为更新INSERT INTO users (nickname, phone) VALUES (张三, 13800001111) ON DUPLICATE KEY UPDATE nickname VALUES(nickname);这条语句的意思是如果因为唯一键冲突插入失败就改成更新nickname字段。实际项目里做数据同步、ETL、接口幂等返回时经常用到。不过注意一个坑它和普通SELECT不同返回的影响行数逻辑比较绕匹配更新也可能返回2刚开始接数据同步需求时容易被这个数字搞懵。2.2 UPDATE和DELETE没有WHERE条件就是全表事故说句不太好听的工作里见过太多因为漏写WHERE导致的生产事故轻则回滚数据重则全体用户收到错误状态。UPDATE和DELETE的标准姿势是先SELECT再UPDATE/DELETE最后看一眼影响行数。更新订单状态的例子UPDATE orders SET status 1 WHERE id 1001;如果忘了WHEREUPDATE orders SET status 1;上面这条会把整张表的订单状态全部改成已支付非常危险。所以我的习惯是执行这类语句前先在事务里做或者先跑一遍等价的SELECTSELECT id, user_id, status FROM orders WHERE id 1001;确认这就是你要动的那一行再执行UPDATE。这也是为什么强烈建议所有业务表都带主键——按主键定位永远最精确。DELETE的坑类似。DELETE FROM后面不跟WHERE那就是清空表。而DELETE FROM orders;和TRUNCATE TABLE orders;看起来都像清空表但行为差异很大操作是否走事务是否可回滚自增ID是否重置执行效率DELETE FROM orders逐行删走事务可回滚不重置相对慢TRUNCATE TABLE orders直接删表重建不可回滚重置非常快这个差异对应到业务场景就是测试环境清空数据用TRUNCATE没问题生产环境万一误删想回滚只有走事务的DELETE还有一线生机。这也是为什么我在本文第6部分会专门强调事务习惯。另外很多业务系统做删除其实并不是真DELETE而是打一个逻辑删除标记。比如给用户表加deleted_at DATETIME DEFAULT NULL删除时执行UPDATE users SET deleted_at NOW() WHERE id 1001;查询时统一过滤WHERE deleted_at IS NULL。这个习惯在真实项目里非常常见好处是数据可追溯、误操作可恢复。代价是每条查询都要多带一个条件容易漏所以一般会配合视图或者ORM的全局筛选来兜底。3. SELECT查询三板斧条件过滤、排序和分页3.1 WHERE别让NULL判断毁掉你的查询SELECT是SQL使用频率最高的操作也是面试和日常开发中最容易暴露问题的地方。先从最常见的WHERE条件说起。假设我要查所有已支付订单SELECT id, user_id, amount FROM orders WHERE status 1;这里status 1是等于条件。WHERE里能用的逻辑除了等于还有大于、小于、不等于、范围BETWEEN、集合IN、模糊匹配LIKE。多个条件组合时AND的优先级高于OR这是个容易踩坑的点。比如SELECT id FROM orders WHERE status 1 OR status 2 AND amount 100;这条SQL的实际执行逻辑是status 1 OR (status 2 AND amount 100)而不是你以为的状态1或2且金额大于100。想要后者必须加括号SELECT id FROM orders WHERE (status 1 OR status 2) AND amount 100;NULL判断是另一个重灾区。初学者十有八九栽在这里想查手机号还没填写的用户写了WHERE phone NULL。结果返回0行。原因很简单NULL代表未定义和任何值比较包括和NULL本身比较结果都是未知而不是真。判断NULL要专门用IS NULL或者IS NOT NULLSELECT id, nickname FROM users WHERE phone IS NULL;3.2 ORDER BY排序别小看排序字段的选择排序看起来简单ORDER BY后面跟上字段名和方向就行但有几个细节值得注意。最基本的是单字段排序SELECT id, user_id, amount, created_at FROM orders ORDER BY created_at DESC;多字段排序时从左到右依次做主排序和次排序。比如先按状态升序再按下单时间倒序SELECT id, user_id, amount, status FROM orders ORDER BY status ASC, created_at DESC;排序方向用ASC升序、DESC倒序默认是升序。实际业务里订单列表、消息列表基本都是created_at DESC也就是最新的在最上面。还有一个容易忽略的问题排序字段是否走索引直接影响性能。如果表里数据量大而ORDER BY的字段没有索引MySQL就得把结果先全部查出来再在内存或磁盘里做一次排序执行计划里会看到Using filesort。这个我在第6部分展开这里先记住一个原则高频率排序的字段建表时就应该加上索引。NULL值的排序位置也需要心里有数。MySQL里默认ASC时NULL排在最前DESC时NULL排在最后。如果业务上想让NULL排到末尾可以这样写SELECT id, nickname, phone FROM users ORDER BY (phone IS NULL), phone ASC;3.3 LIMIT分页offset越大越慢这是个大坑分页是Web后台最常见不过的需求。基本写法SELECT id, user_id, amount FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 20;这里的含义是跳过20条记录取10条也就是第三页的数据。LIMIT 10 OFFSET 20和LIMIT 20, 10是等价写法注意顺序是limit(个数), offset(偏移量)。分页最大的坑是深分页。当用户翻到几百页OFFSET变成几万甚至几十万时MySQL会先把前面的所有行都读取一遍再往后找查询会明显变慢。优化方案是在列表页用游标分页代替传统的offset分页——这种分页的核心思想是记录上一页最后一条数据的主键下一页直接从这个主键之后取SELECT id, user_id, amount FROM orders WHERE id 上一页最后一条id ORDER BY id DESC LIMIT 10;没有OFFSET每个下一页都是直接走主键索引定位性能非常稳定。后台管理系统如果不要求跳页用这种游标分页体验会好很多。别忘了LIMIT还可以和聚合配合使用比如取订单金额最高的TOP5SELECT id, user_id, amount FROM orders ORDER BY amount DESC LIMIT 5;4. 聚合与分组从找出数据到算清账目4.1 聚合函数COUNT、SUM、AVG、MAX、MIN的坑与用法光有增删改查你只能回答有哪些数据聚合函数能让你回答数据说明了什么。这是从操作迈向分析的第一步。统计订单总数和总金额是最典型的场景SELECT COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE status 1;COUNT(*)统计行数SUM求和AVG求平均MAX和MIN分别取最大值和最小值。一个常用的场景是查最大单笔金额、最小单笔金额和平均客单价SELECT MAX(amount) AS max_order, MIN(amount) AS min_order, AVG(amount) AS avg_order FROM orders;这里要特别留意COUNT的细节。COUNT(*)是统计所有行包括值为NULL的行COUNT(字段)是统计该字段不为NULL的行。如果一个字段经常有空值这两种写法统计出来的数字可能不一样。比如SELECT COUNT(*) FROM users; -- 总用户数 SELECT COUNT(phone) FROM users; -- 填写了手机号的用户数日常写统计报表时一定要想清楚自己到底要的是哪个口径。COUNT(1)和COUNT(*)在MySQL里的执行效果基本相同不必纠结直接写COUNT(*)最直观。4.2 GROUP BY HAVING分组统计的完整姿势分组统计是理解SQL数据组织方式的一道坎。举例我想知道每个用户下了多少单、总消费多少SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_spent FROM orders GROUP BY user_id;这条SQL先把所有订单按user_id分成一组再对每组做聚合。它回答的问题是每个用户的数据。GROUP BY配合HAVING还可以做分组后的过滤比如只保留消费总额大于1000的用户SELECT user_id, SUM(amount) AS total_spent FROM orders GROUP BY user_id HAVING total_spent 1000;HAVING和WHERE的分工是很多初学者的困惑点。WHERE在分组之前过滤HAVING在分组之后过滤。举例来说如果你想统计已支付订单中每个用户的消费总额必须先WHERE掉未支付的订单再分组再HAVINGSELECT user_id, SUM(amount) AS total_spent FROM orders WHERE status 1 GROUP BY user_id HAVING total_spent 1000;记住一个执行顺序口诀FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT。这也是MySQL实际执行SQL的顺序按这个顺序理解SQLWHERE里能不能用别名、HAVING里能不能用普通列这些问题就全通了。GROUP BY还有个数限制问题需要注意。MySQL在默认的only_full_group_by模式下SELECT后面只能出现在GROUP BY里出现过的列或者包在聚合函数里的列不能随意挑选一个非分组列。也就是说下面这条在严格模式下直接报错SELECT user_id, amount FROM orders GROUP BY user_id;因为amount既不在GROUP BY分组里也没有被聚合函数包裹MySQL不知道该返回哪一条的amount。我的建议是不要想着关闭这个模式去绕过报错而是应该调整SQL思路如果想知道每个用户最近一笔订单金额应该换子查询或者窗口函数来解决而不是开兼容模式容忍不严谨的SQL。去重统计也是一个高频小场景比如统计有过订单的用户数SELECT COUNT(DISTINCT user_id) FROM orders;DISTINCT会对user_id去重后再计数。注意它和纯COUNT(*)的区别这行不写DISTINCT的话一个用户有5笔订单就会被算成5次。5. JOIN多表查询能画出关系图就写得出关联SQL5.1 为什么需要拆表关系型数据库的取舍前面我们用users和orders两张表存数据但现实中有人会问为什么不把所有信息塞进一张大表查起来多方便这就要回到关系型数据库设计的基本逻辑——减少冗余、避免更新异常。假设订单表里直接存用户昵称、用户手机号那用户改了昵称所有历史订单里的昵称都要跟着改不改就数据不一致。拆成两张表订单表只存user_id用户信息统一维护在users表里需要的时候通过关联查询组合起来。这个拆的过程在数据库设计里叫规范化。拆表之后查询自然免不了把两张表重新拼回去这就是JOIN存在的意义。users和orders是一对多的关系一个用户可以有多个订单一个订单属于一个用户。画关系图时orders表通过user_id指向users表的id这就是外键的语义——虽然现代开发里很多人不建物理外键约束但逻辑上的关联关系是明确的。5.2 JOIN的三种常见形态INNER JOIN、LEFT JOIN、RIGHT JOIN最常见的关联查询是查订单信息同时把该订单所属用户的昵称带出来SELECT o.id, o.amount, o.status, u.nickname FROM orders o INNER JOIN users u ON o.user_id u.id;INNER JOIN返回的是两张表都能匹配上的记录有订单且有用户的才出现。如果一个订单的user_id在users表里不存在这条订单就会被过滤掉。LEFT JOIN则是左表为主右表匹配得上就带上匹配不上就补NULL。典型场景是查所有用户的订单情况包括从没下过单的用户SELECT u.id, u.nickname, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.nickname;对于没有订单的用户o.id全是NULL所以COUNT(o.id)结果是0。注意这里COUNT(o.id)不能写成COUNT(*)因为COUNT(*)把左连接产生的NULL行也算进去了会得到1这是LEFT JOIN里最经典的坑。RIGHT JOIN在MySQL里存在但大部分场景都可以用调整左右表顺序的方式替换成LEFT JOIN实际项目里用得少我建议你只需要知道它存在日常优先用LEFT JOIN。JOIN关联时的条件写在ON里过滤条件写在WHERE里。两者的区别一旦用错结果就不对。看这个例子SELECT u.id, u.nickname, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.status 1;如果把o.status 1从ON挪到WHERESELECT u.id, u.nickname, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.status 1;这两条SQL结果完全不同。第一条中o.status 1是Join的匹配条件没下过单的用户依然会返回o.amount为NULL第二条中WHERE是在连表完成之后过滤会把所有没有已支付订单的用户整行过滤掉。理解这个差异LEFT JOIN才算是真正掌握了。另外强烈建议写JOIN时不要省略ON条件也不要故意写CROSS JOIN。省略ON会产生笛卡尔积也就是两张表所有行两两组合数据量立刻爆炸。比如一张表1000行、另一张表2000行不带ON的JOIN会返回200万行这是慢查询的经典来源。6. 事务、索引与EXPLAINSQL能跑通只是第一步6.1 事务为什么你写的SQL需要ACIDMySQL基础系列如果没有事务就像学了开车不会挂挡。事务解决的是一组操作要么全部成功要么全部失败的问题。举个经典场景转账A账户扣钱B账户加钱两步操作必须同时成功或同时失败不能出现扣了钱但对方没收到的情况。在SQL里用事务包住这两步START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;如果第二条UPDATE执行失败直接ROLLBACK整个事务里的所有操作都会被撤销ROLLBACK;为什么要单独强调这个因为InnoDB默认是autocommit1也就是每条SQL自动提交。初学者在校验数据时经常遇到明明UPDATE了结果一闪就没了或者DELETE了想后悔的尴尬——答案就在这里在事务里执行COMMIT之前其他会话看不到你的改动你也能随时回滚。事务的四个特性是ACID原子性Atomicity、一致性Consistency)、隔离性Isolation、持久性Durability。日常开发中和你直接相关的是隔离性MySQL默认隔离级别是REPEATABLE READ也就是同一个事务里多次查询结果一致。不同隔离级别影响并发下的读写行为这个属于进阶内容这篇先做到会用事务包住关键业务操作就已经比大多数初学者走得远了。6.2 索引为什么加了索引查询快得像开了挂索引是MySQL性能的核心也是基础篇和程序员日常最明显的分水岭。前面建orders表时我给user_id加了一个普通索引KEY idx_user_id (user_id)这个索引就是为了让下面这类查询快SELECT * FROM orders WHERE user_id 1;没有索引时MySQL要全表扫描一行一行找user_id等于1的记录。有索引之后MySQL通过B树结构快速定位类似查字典先看偏旁部首再翻页而不是从第一页翻到最后一页。数据量小的时候两者没区别数据量大到十万百万行时快慢天壤之别。索引大体分几类主键索引PRIMARY KEY、唯一索引UNIQUE、普通索引KEY/INDEX、还有组合索引。主键索引和唯一索引除了加速查询外还有约束作用普通索引只是单纯加速。我给orders.user_id加普通索引理由是这张表会频繁按用户查订单属于高频查询路径。但索引不是越多越好。每次INSERT、UPDATE数据库都要同步维护索引结构写操作会因此变慢而且索引也占磁盘空间。所以建索引的保守策略是先在慢查询、高频查询确定之后再针对性补索引而不是建表时一把梭。索引失效的情况也要心里有数。比如对索引列做函数操作会让索引失效SELECT * FROM users WHERE DATE(created_at) 2024-01-01;上面这种写法索引在created_at上也会失效因为函数破坏了索引的连续性。同样字段类型不匹配导致的隐式转换、LIKE以%开头的模糊匹配都救不了索引。后面你有机会可以试着在EXPLAIN里对比看看。6.3 EXPLAIN执行计划优化SQL的第一把手术刀光说不练假把式。写SQL时养成一个习惯用EXPLAIN看执行计划是成本最低的优化手段。用法简单在SQL前面加EXPLAINEXPLAIN SELECT id, amount FROM orders WHERE user_id 1;输出的结果里重点看几个字段type表示访问类型从好到差大致是const、ref、range、ALLkey表示实际用到的索引rows是估算的扫描行数Extra里如果出现Using filesort说明排序没用上索引出现了额外排序。如果你发现typeALL也就是全表扫描而且rows非常大那这条SQL大概率有优化空间。常见的优化方式就是加索引。比如一个经常按status过滤的订单表可以加ALTER TABLE orders ADD INDEX idx_status (status);再跑一次EXPLAIN看到type变成了refrows大幅下降就说明索引生效了。这个过程非常直观也是我个人的习惯——任何一条线上查询写得再顺手上线前至少EXPLAIN看一眼。EXPLAIN的作用是告诉你MySQL打算怎么执行SQL而不是实际执行数据。有了这个工具前面提到的深分页、JOIN性能、索引失效问题就都有了一个统一的检查和验证出口。这一篇最后落到这里你其实已经从会写SQL摸到了理解SQL的门槛。我自己带新人的经验是SQL基础学得好不好不在于背了多少语法而在于遇到一条未预期的慢查询、一个查不到数据的怪现象时知不知道从哪里下手。这篇提供的过滤、排序、分页、聚合、JOIN、事务、索引和EXPLAIN正是你形成这套排查能力的骨架。把这些概念用自己的话讲一遍、在本地环境跑一遍下一篇我们就能放心地往存储过程、视图、更复杂的优化方向走了。 MySQL基础（二）：从增删改查到事务索引，SQL实战全掌握
上一篇我们从零开始装好了MySQL搞定登录建了几个库和表算是把地基打完了。这篇《MySQL基础二》继续沿着这条线走目标非常明确让你把手头的SQL操作练熟——增删改查、条件过滤、排序分页、分组统计、多表关联最后再带一下事务和索引的入门认知。读完这篇你写出来的SQL不再只是能跑通而是在项目里敢用、能解释清楚为什么这么写。文章适合两类人一类是刚跟着系列第一篇把环境弄好、准备正式上手SQL的初学者另一类是零零散散写过一些SQL但总觉得没有体系、老在一些小细节上翻车的同学。为了让例子好懂后面统一切入两个场景一张users用户表一张orders订单表。所有语法都在这两张表上演示你可以在自己的环境里照着敲一遍效果比光看要扎实得多。1. 先把手头这几张表摸清楚客户端、字段类型和建表约定1.1 连接数据库的常见姿势命令行、图形客户端与连接串动手写SQL之前先说说手头工具这件事。我在带新人时发现一个现象很多人什么工具会一点但每一种都只停留在能连上的程度一旦出问题就不知道怎么排查。最基础的是命令行。装完MySQL之后在终端里执行mysql -u root -p输入密码之后就进入MySQL的交互界面了。日常调试、查看系统状态、处理应急问题命令行永远是兜底的那一个因为任何图形客户端底层都是通过同样的协议跟MySQL通信命令行没问题而客户端连不上问题基本出在客户端配置。图形客户端方面我用得比较多的是DBeaver和Navicat。DBeaver开源免费Navicat功能成熟但需要授权这里不展开谈工具优劣只说要留意一件事连接时填的host、port、用户名、密码必须和MySQL实际配置一致。很多新手连不上数据库不是密码错了而是MySQL没有监听预期端口或者用户只允许本机登录。可以用一条命令验证服务状态sudo systemctl status mysql或者直接看MySQL的端口监听netstat -tlnp | grep 3306项目代码里连MySQL走的是连接串Java里大概长这样jdbc:mysql://127.0.0.1:3306/test_db?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/Shanghai这一段后面项目篇再细细拆现在你只需要知道字符集参数characterEncodingutf8很关键少了它中文读写大概率出乱码。useSSLfalse在本地开发环境也建议加上否则不少客户端会因为SSL握手出奇奇怪怪的报错这次热搜词里就有一个mysql ssl连接错误多半和本地连接没配SSL参数有关。1.2 字段类型选错是后面所有麻烦的根源设计表结构这件事很多人觉得不就是有几个字段、什么类型吗但实际项目里字段类型选错带来的麻烦是连锁的。举个例子存金额。新手容易用FLOAT或者DOUBLE理由是小数嘛。做开发和电商的人看到这里应该会心一笑——FLOAT是浮点数底层是二进制近似存储日常好像没问题但算到分、做对账的时候0.10.2不等于0.3这种经典问题就来了。金额字段正规做法是DECIMAL比如DECIMAL(10,2)表示总长10位、小数2位精确存储不会出现浮点误差。再比如时间。MySQL里有DATETIME和TIMESTAMP两种TIMESTAMP有2038年上限问题而且跟时区挂钩DATETIME的存储范围宽得多实际开发里更省心。我在建表时的默认习惯是业务时间字段基本都用DATETIME并且加DEFAULT CURRENT_TIMESTAMP来记录创建时间。字符串类型是另一个重灾区。CHAR是定长VARCHAR是变长。手机号、身份证这类长度固定的用CHAR问题不大但大多数业务字段比如昵称、地址用VARCHAR更合理因为变长类型按实际长度存储省空间。千万别把VARCHAR(255)当成万能默认字符串越长索引占用的空间越大查询性能越受影响。字符集上建议从一开始就用utf8mb4因为MySQL里的utf8其实不是完整的UTF-8它最多只能存3个字节遇到emoji这类4字节字符就报错。utf8mb4才是完整的UTF-8实现。建一个用户表的完整语句如下CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nickname VARCHAR(50) NOT NULL COMMENT 昵称, phone VARCHAR(20) NOT NULL COMMENT 手机号, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, UNIQUE KEY uk_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;对应的订单表CREATE TABLE orders ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL COMMENT 下单用户ID, amount DECIMAL(10,2) NOT NULL COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态 0待支付 1已支付, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;为什么id用INT UNSIGNED因为大部分业务表的记录量到不了BIGINT的量级INT足够加UNSIGNED把负数区域也让给正数上限翻倍。订单表的user_id加上普通索引是因为后面做关联查询会频繁按用户查订单这个索引会起到决定性作用——这个点在本文第5、6部分会真正用上。ENGINEInnoDB也需要养成习惯。InnoDB支持事务和行级锁而老旧的MyISAM不支持事务表锁粒度大并发写场景容易出问题。从MySQL 5.5之后InnoDB就是默认存储引擎我自己建表时哪怕简单到只有一个字段也一定会写清楚ENGINEInnoDB避免误用其他引擎。2. 增删改的日常INSERT、UPDATE、DELETE的写法与翻车现场2.1 INSERT单行插入、批量插入和不存在才插入增删改查里INSERT是写入操作逻辑最简单但正因为简单很多人忽视它的细节。单行插入的常规写法INSERT INTO users (nickname, phone) VALUES (张三, 13800001111);插入之后可以用SELECT LAST_INSERT_ID();查看刚才这行记录的自增ID这在后面做关联操作时非常有用比如新建用户之后马上给他插入一条订单记录。批量插入是日常开发里非常实用的写法一次多行效率远高于逐条循环插入INSERT INTO users (nickname, phone) VALUES (李四, 13800002222), (王五, 13800003333), (赵六, 13800004444);批量插入的一大价值在减少客户端和MySQL之间的网络往返次数。一次SQL包装100行比100次单条插入快得多这在初始化测试数据、批量导入Excel数据时尤其明显。还有一类场景是有则更新无则插入比如同步外部数据。如果phone上加了唯一索引常规的INSERT在遇到重复时会直接报错而用ON DUPLICATE KEY UPDATE可以优雅地转为更新INSERT INTO users (nickname, phone) VALUES (张三, 13800001111) ON DUPLICATE KEY UPDATE nickname VALUES(nickname);这条语句的意思是如果因为唯一键冲突插入失败就改成更新nickname字段。实际项目里做数据同步、ETL、接口幂等返回时经常用到。不过注意一个坑它和普通SELECT不同返回的影响行数逻辑比较绕匹配更新也可能返回2刚开始接数据同步需求时容易被这个数字搞懵。2.2 UPDATE和DELETE没有WHERE条件就是全表事故说句不太好听的工作里见过太多因为漏写WHERE导致的生产事故轻则回滚数据重则全体用户收到错误状态。UPDATE和DELETE的标准姿势是先SELECT再UPDATE/DELETE最后看一眼影响行数。更新订单状态的例子UPDATE orders SET status 1 WHERE id 1001;如果忘了WHEREUPDATE orders SET status 1;上面这条会把整张表的订单状态全部改成已支付非常危险。所以我的习惯是执行这类语句前先在事务里做或者先跑一遍等价的SELECTSELECT id, user_id, status FROM orders WHERE id 1001;确认这就是你要动的那一行再执行UPDATE。这也是为什么强烈建议所有业务表都带主键——按主键定位永远最精确。DELETE的坑类似。DELETE FROM后面不跟WHERE那就是清空表。而DELETE FROM orders;和TRUNCATE TABLE orders;看起来都像清空表但行为差异很大操作是否走事务是否可回滚自增ID是否重置执行效率DELETE FROM orders逐行删走事务可回滚不重置相对慢TRUNCATE TABLE orders直接删表重建不可回滚重置非常快这个差异对应到业务场景就是测试环境清空数据用TRUNCATE没问题生产环境万一误删想回滚只有走事务的DELETE还有一线生机。这也是为什么我在本文第6部分会专门强调事务习惯。另外很多业务系统做删除其实并不是真DELETE而是打一个逻辑删除标记。比如给用户表加deleted_at DATETIME DEFAULT NULL删除时执行UPDATE users SET deleted_at NOW() WHERE id 1001;查询时统一过滤WHERE deleted_at IS NULL。这个习惯在真实项目里非常常见好处是数据可追溯、误操作可恢复。代价是每条查询都要多带一个条件容易漏所以一般会配合视图或者ORM的全局筛选来兜底。3. SELECT查询三板斧条件过滤、排序和分页3.1 WHERE别让NULL判断毁掉你的查询SELECT是SQL使用频率最高的操作也是面试和日常开发中最容易暴露问题的地方。先从最常见的WHERE条件说起。假设我要查所有已支付订单SELECT id, user_id, amount FROM orders WHERE status 1;这里status 1是等于条件。WHERE里能用的逻辑除了等于还有大于、小于、不等于、范围BETWEEN、集合IN、模糊匹配LIKE。多个条件组合时AND的优先级高于OR这是个容易踩坑的点。比如SELECT id FROM orders WHERE status 1 OR status 2 AND amount 100;这条SQL的实际执行逻辑是status 1 OR (status 2 AND amount 100)而不是你以为的状态1或2且金额大于100。想要后者必须加括号SELECT id FROM orders WHERE (status 1 OR status 2) AND amount 100;NULL判断是另一个重灾区。初学者十有八九栽在这里想查手机号还没填写的用户写了WHERE phone NULL。结果返回0行。原因很简单NULL代表未定义和任何值比较包括和NULL本身比较结果都是未知而不是真。判断NULL要专门用IS NULL或者IS NOT NULLSELECT id, nickname FROM users WHERE phone IS NULL;3.2 ORDER BY排序别小看排序字段的选择排序看起来简单ORDER BY后面跟上字段名和方向就行但有几个细节值得注意。最基本的是单字段排序SELECT id, user_id, amount, created_at FROM orders ORDER BY created_at DESC;多字段排序时从左到右依次做主排序和次排序。比如先按状态升序再按下单时间倒序SELECT id, user_id, amount, status FROM orders ORDER BY status ASC, created_at DESC;排序方向用ASC升序、DESC倒序默认是升序。实际业务里订单列表、消息列表基本都是created_at DESC也就是最新的在最上面。还有一个容易忽略的问题排序字段是否走索引直接影响性能。如果表里数据量大而ORDER BY的字段没有索引MySQL就得把结果先全部查出来再在内存或磁盘里做一次排序执行计划里会看到Using filesort。这个我在第6部分展开这里先记住一个原则高频率排序的字段建表时就应该加上索引。NULL值的排序位置也需要心里有数。MySQL里默认ASC时NULL排在最前DESC时NULL排在最后。如果业务上想让NULL排到末尾可以这样写SELECT id, nickname, phone FROM users ORDER BY (phone IS NULL), phone ASC;3.3 LIMIT分页offset越大越慢这是个大坑分页是Web后台最常见不过的需求。基本写法SELECT id, user_id, amount FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 20;这里的含义是跳过20条记录取10条也就是第三页的数据。LIMIT 10 OFFSET 20和LIMIT 20, 10是等价写法注意顺序是limit(个数), offset(偏移量)。分页最大的坑是深分页。当用户翻到几百页OFFSET变成几万甚至几十万时MySQL会先把前面的所有行都读取一遍再往后找查询会明显变慢。优化方案是在列表页用游标分页代替传统的offset分页——这种分页的核心思想是记录上一页最后一条数据的主键下一页直接从这个主键之后取SELECT id, user_id, amount FROM orders WHERE id 上一页最后一条id ORDER BY id DESC LIMIT 10;没有OFFSET每个下一页都是直接走主键索引定位性能非常稳定。后台管理系统如果不要求跳页用这种游标分页体验会好很多。别忘了LIMIT还可以和聚合配合使用比如取订单金额最高的TOP5SELECT id, user_id, amount FROM orders ORDER BY amount DESC LIMIT 5;4. 聚合与分组从找出数据到算清账目4.1 聚合函数COUNT、SUM、AVG、MAX、MIN的坑与用法光有增删改查你只能回答有哪些数据聚合函数能让你回答数据说明了什么。这是从操作迈向分析的第一步。统计订单总数和总金额是最典型的场景SELECT COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE status 1;COUNT(*)统计行数SUM求和AVG求平均MAX和MIN分别取最大值和最小值。一个常用的场景是查最大单笔金额、最小单笔金额和平均客单价SELECT MAX(amount) AS max_order, MIN(amount) AS min_order, AVG(amount) AS avg_order FROM orders;这里要特别留意COUNT的细节。COUNT(*)是统计所有行包括值为NULL的行COUNT(字段)是统计该字段不为NULL的行。如果一个字段经常有空值这两种写法统计出来的数字可能不一样。比如SELECT COUNT(*) FROM users; -- 总用户数 SELECT COUNT(phone) FROM users; -- 填写了手机号的用户数日常写统计报表时一定要想清楚自己到底要的是哪个口径。COUNT(1)和COUNT(*)在MySQL里的执行效果基本相同不必纠结直接写COUNT(*)最直观。4.2 GROUP BY HAVING分组统计的完整姿势分组统计是理解SQL数据组织方式的一道坎。举例我想知道每个用户下了多少单、总消费多少SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_spent FROM orders GROUP BY user_id;这条SQL先把所有订单按user_id分成一组再对每组做聚合。它回答的问题是每个用户的数据。GROUP BY配合HAVING还可以做分组后的过滤比如只保留消费总额大于1000的用户SELECT user_id, SUM(amount) AS total_spent FROM orders GROUP BY user_id HAVING total_spent 1000;HAVING和WHERE的分工是很多初学者的困惑点。WHERE在分组之前过滤HAVING在分组之后过滤。举例来说如果你想统计已支付订单中每个用户的消费总额必须先WHERE掉未支付的订单再分组再HAVINGSELECT user_id, SUM(amount) AS total_spent FROM orders WHERE status 1 GROUP BY user_id HAVING total_spent 1000;记住一个执行顺序口诀FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT。这也是MySQL实际执行SQL的顺序按这个顺序理解SQLWHERE里能不能用别名、HAVING里能不能用普通列这些问题就全通了。GROUP BY还有个数限制问题需要注意。MySQL在默认的only_full_group_by模式下SELECT后面只能出现在GROUP BY里出现过的列或者包在聚合函数里的列不能随意挑选一个非分组列。也就是说下面这条在严格模式下直接报错SELECT user_id, amount FROM orders GROUP BY user_id;因为amount既不在GROUP BY分组里也没有被聚合函数包裹MySQL不知道该返回哪一条的amount。我的建议是不要想着关闭这个模式去绕过报错而是应该调整SQL思路如果想知道每个用户最近一笔订单金额应该换子查询或者窗口函数来解决而不是开兼容模式容忍不严谨的SQL。去重统计也是一个高频小场景比如统计有过订单的用户数SELECT COUNT(DISTINCT user_id) FROM orders;DISTINCT会对user_id去重后再计数。注意它和纯COUNT(*)的区别这行不写DISTINCT的话一个用户有5笔订单就会被算成5次。5. JOIN多表查询能画出关系图就写得出关联SQL5.1 为什么需要拆表关系型数据库的取舍前面我们用users和orders两张表存数据但现实中有人会问为什么不把所有信息塞进一张大表查起来多方便这就要回到关系型数据库设计的基本逻辑——减少冗余、避免更新异常。假设订单表里直接存用户昵称、用户手机号那用户改了昵称所有历史订单里的昵称都要跟着改不改就数据不一致。拆成两张表订单表只存user_id用户信息统一维护在users表里需要的时候通过关联查询组合起来。这个拆的过程在数据库设计里叫规范化。拆表之后查询自然免不了把两张表重新拼回去这就是JOIN存在的意义。users和orders是一对多的关系一个用户可以有多个订单一个订单属于一个用户。画关系图时orders表通过user_id指向users表的id这就是外键的语义——虽然现代开发里很多人不建物理外键约束但逻辑上的关联关系是明确的。5.2 JOIN的三种常见形态INNER JOIN、LEFT JOIN、RIGHT JOIN最常见的关联查询是查订单信息同时把该订单所属用户的昵称带出来SELECT o.id, o.amount, o.status, u.nickname FROM orders o INNER JOIN users u ON o.user_id u.id;INNER JOIN返回的是两张表都能匹配上的记录有订单且有用户的才出现。如果一个订单的user_id在users表里不存在这条订单就会被过滤掉。LEFT JOIN则是左表为主右表匹配得上就带上匹配不上就补NULL。典型场景是查所有用户的订单情况包括从没下过单的用户SELECT u.id, u.nickname, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.nickname;对于没有订单的用户o.id全是NULL所以COUNT(o.id)结果是0。注意这里COUNT(o.id)不能写成COUNT(*)因为COUNT(*)把左连接产生的NULL行也算进去了会得到1这是LEFT JOIN里最经典的坑。RIGHT JOIN在MySQL里存在但大部分场景都可以用调整左右表顺序的方式替换成LEFT JOIN实际项目里用得少我建议你只需要知道它存在日常优先用LEFT JOIN。JOIN关联时的条件写在ON里过滤条件写在WHERE里。两者的区别一旦用错结果就不对。看这个例子SELECT u.id, u.nickname, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.status 1;如果把o.status 1从ON挪到WHERESELECT u.id, u.nickname, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.status 1;这两条SQL结果完全不同。第一条中o.status 1是Join的匹配条件没下过单的用户依然会返回o.amount为NULL第二条中WHERE是在连表完成之后过滤会把所有没有已支付订单的用户整行过滤掉。理解这个差异LEFT JOIN才算是真正掌握了。另外强烈建议写JOIN时不要省略ON条件也不要故意写CROSS JOIN。省略ON会产生笛卡尔积也就是两张表所有行两两组合数据量立刻爆炸。比如一张表1000行、另一张表2000行不带ON的JOIN会返回200万行这是慢查询的经典来源。6. 事务、索引与EXPLAINSQL能跑通只是第一步6.1 事务为什么你写的SQL需要ACIDMySQL基础系列如果没有事务就像学了开车不会挂挡。事务解决的是一组操作要么全部成功要么全部失败的问题。举个经典场景转账A账户扣钱B账户加钱两步操作必须同时成功或同时失败不能出现扣了钱但对方没收到的情况。在SQL里用事务包住这两步START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;如果第二条UPDATE执行失败直接ROLLBACK整个事务里的所有操作都会被撤销ROLLBACK;为什么要单独强调这个因为InnoDB默认是autocommit1也就是每条SQL自动提交。初学者在校验数据时经常遇到明明UPDATE了结果一闪就没了或者DELETE了想后悔的尴尬——答案就在这里在事务里执行COMMIT之前其他会话看不到你的改动你也能随时回滚。事务的四个特性是ACID原子性Atomicity、一致性Consistency)、隔离性Isolation、持久性Durability。日常开发中和你直接相关的是隔离性MySQL默认隔离级别是REPEATABLE READ也就是同一个事务里多次查询结果一致。不同隔离级别影响并发下的读写行为这个属于进阶内容这篇先做到会用事务包住关键业务操作就已经比大多数初学者走得远了。6.2 索引为什么加了索引查询快得像开了挂索引是MySQL性能的核心也是基础篇和程序员日常最明显的分水岭。前面建orders表时我给user_id加了一个普通索引KEY idx_user_id (user_id)这个索引就是为了让下面这类查询快SELECT * FROM orders WHERE user_id 1;没有索引时MySQL要全表扫描一行一行找user_id等于1的记录。有索引之后MySQL通过B树结构快速定位类似查字典先看偏旁部首再翻页而不是从第一页翻到最后一页。数据量小的时候两者没区别数据量大到十万百万行时快慢天壤之别。索引大体分几类主键索引PRIMARY KEY、唯一索引UNIQUE、普通索引KEY/INDEX、还有组合索引。主键索引和唯一索引除了加速查询外还有约束作用普通索引只是单纯加速。我给orders.user_id加普通索引理由是这张表会频繁按用户查订单属于高频查询路径。但索引不是越多越好。每次INSERT、UPDATE数据库都要同步维护索引结构写操作会因此变慢而且索引也占磁盘空间。所以建索引的保守策略是先在慢查询、高频查询确定之后再针对性补索引而不是建表时一把梭。索引失效的情况也要心里有数。比如对索引列做函数操作会让索引失效SELECT * FROM users WHERE DATE(created_at) 2024-01-01;上面这种写法索引在created_at上也会失效因为函数破坏了索引的连续性。同样字段类型不匹配导致的隐式转换、LIKE以%开头的模糊匹配都救不了索引。后面你有机会可以试着在EXPLAIN里对比看看。6.3 EXPLAIN执行计划优化SQL的第一把手术刀光说不练假把式。写SQL时养成一个习惯用EXPLAIN看执行计划是成本最低的优化手段。用法简单在SQL前面加EXPLAINEXPLAIN SELECT id, amount FROM orders WHERE user_id 1;输出的结果里重点看几个字段type表示访问类型从好到差大致是const、ref、range、ALLkey表示实际用到的索引rows是估算的扫描行数Extra里如果出现Using filesort说明排序没用上索引出现了额外排序。如果你发现typeALL也就是全表扫描而且rows非常大那这条SQL大概率有优化空间。常见的优化方式就是加索引。比如一个经常按status过滤的订单表可以加ALTER TABLE orders ADD INDEX idx_status (status);再跑一次EXPLAIN看到type变成了refrows大幅下降就说明索引生效了。这个过程非常直观也是我个人的习惯——任何一条线上查询写得再顺手上线前至少EXPLAIN看一眼。EXPLAIN的作用是告诉你MySQL打算怎么执行SQL而不是实际执行数据。有了这个工具前面提到的深分页、JOIN性能、索引失效问题就都有了一个统一的检查和验证出口。这一篇最后落到这里你其实已经从会写SQL摸到了理解SQL的门槛。我自己带新人的经验是SQL基础学得好不好不在于背了多少语法而在于遇到一条未预期的慢查询、一个查不到数据的怪现象时知不知道从哪里下手。这篇提供的过滤、排序、分页、聚合、JOIN、事务、索引和EXPLAIN正是你形成这套排查能力的骨架。把这些概念用自己的话讲一遍、在本地环境跑一遍下一篇我们就能放心地往存储过程、视图、更复杂的优化方向走了。