1. 写在前面的几句唠叨我估计点进这篇文章的兄弟多半是刚把 MySQL 装上、能连上服务、也会敲几条最简单的 SELECT 了。基础一里我们聊过怎么下载安装、怎么启动服务、怎么建库建表那期的评论里问得最多的就是“装好了然后呢”“怎么用起来更像干活的样子”。所以这一篇我打算换个思路不再像说明书一样罗列命令而是把日常开发里真正高频的 SQL 操作、调优思路、存储过程、锁和连接报错串起来讲一遍每一段都配上我实际踩过的坑和验证过的写法。这篇“MYSQL基础二”适合刚入门的朋友也适合写了不少 SQL 但没系统梳理过细节的同行。你不需要把内容全背下来只要跟着过一遍把几个容易出错的地方记下来再去翻自己的项目代码很多疑惑会自动解开。老规矩文中所有示例都用 MySQL 8.0 的默认语法来写如果你还在用 5.7大部分都能直接用个别差异我会单独点出来。2. 数据操作三件套INSERT、UPDATE、DELETE 的细节2.1 UPDATE 语法最容易踩的坑很多人写 UPDATE 的时候特别豪放上来就是UPDATE user SET age age 1 WHERE name 张三;这行没问题注意 SET 子句里可以用字段本身做计算。但更常见的问题是忘写 WHERE 条件。SQL 里 UPDATE 不带 WHERE默认更新全表而且 MySQL 默认情况下并不会像 Navicat 那样给你弹个确认框执行就执行了数据直接没。我在测试环境经常看到新人把整张表的 status 字段全部改掉然后慌慌张张来问怎么恢复。还有 UPDATE 的语法细节很多人分不清赋值和比较。MySQL 里 UPDATE 的 SET 用“”SELECT 的 WHERE 也用“”但语义完全不同。如果你想把字段更新为另一个字段的值写法是这样UPDATE orders SET total_amount price * quantity WHERE order_id 1001;这里 total_amount 会更新成 price 和 quantity 相乘的结果。如果你想一次性更新多条记录用 CASE WHEN 比多条 UPDATE 语句更高效UPDATE product SET status CASE WHEN stock 0 THEN sold_out WHEN stock 10 THEN low_stock ELSE normal END WHERE category_id 5;注意这条语句后面有 WHERE避免把所有分类的商品状态都改掉。实操心得执行 UPDATE 之前先 SELECT 一遍同样的 WHERE 条件看看会命中多少行。养成这个习惯能救你无数次。2.2 DELETE 与 TRUNCATE别用混DELETE 是逐行删除事务里可以回滚TRUNCATE 是直接把表重建不能回滚。日常清空表数据新手最容易用错。比如你要把测试表清理干净但还留着表结构用 TRUNCATE 快得多TRUNCATE TABLE temp_log;TRUNCATE 会把自增主键重置成 1DELETE 不会。如果你只是删几行必须用 DELETE 加条件DELETE FROM cart WHERE user_id 123 AND created_time 2024-01-01;另外 DELETE 在 InnoDB 引擎下执行时如果表数据量大会导致锁范围扩大甚至阻塞其他查询。我曾经在线上执行一条 DELETE 删 50 万行跑了快十分钟期间整张表都锁住了。后来改成每次删除 5000 行循环执行影响就小多了。DELETE FROM big_table WHERE id IN (SELECT id FROM big_table WHERE status 0 LIMIT 5000);这句其实有个 MySQL 的经典限制子查询里不能直接 LIMIT 同一个表会报错。正确姿势是先查询出主键列表再批量删SELECT id FROM big_table WHERE status 0 LIMIT 5000; -- 拿到 id 列表后 DELETE FROM big_table WHERE id IN (ids...);2.3 INSERT 的批量写法单条 INSERT 和批量 INSERT 的性能差距非常明显。一次插入 1000 条记录用单条循环插入可能要 5 秒用批量语句只需要几十毫秒。批量写法INSERT INTO student (name, class_id, score) VALUES (小王, 1, 90), (小李, 1, 85), (小张, 2, 76);这里有一个易错点如果某条记录的主键冲突整条 INSERT 会报错。可以用INSERT IGNORE忽略冲突或者ON DUPLICATE KEY UPDATE实现“存在就更新不存在就插入”INSERT INTO user (id, name, age) VALUES (1, 小明, 18) ON DUPLICATE KEY UPDATE name VALUES(name), age VALUES(age);注意 MySQL 8.0.20 之后VALUES()在 ON DUPLICATE KEY UPDATE 里已经标记为废弃推荐用别名语法INSERT INTO user (id, name, age) VALUES (1, 小明, 18) AS new ON DUPLICATE KEY UPDATE name new.name, age new.age;这个细节在面试里经常被问到也是网上很多老教程没更新的地方。3. SELECT 进阶排序、JOIN 与子查询3.1 ORDER BY 排序的隐藏规则排序看起来简单ORDER BY age DESC谁都会写但有几个点值得注意。第一个是字段选择如果 ORDER BY 的字段上没有索引MySQL 就需要把结果集全部加载到内存中做 filesort数据量一大性能就很差。比如一个 100 万行的表按created_time排序如果没有索引查询耗时可能从几十毫秒飙到几秒。第二个是排序的稳定性。MySQL 8.0 之前如果两条记录的排序字段值相同它们之间的相对顺序不保证。现在用了更稳定的排序算法但如果你想要一个可预测的顺序最好加上唯一字段作为次级排序条件SELECT * FROM student ORDER BY score DESC, id ASC;这样分数一样时按学号排结果稳定也方便分页。第三个是 NULL 值的排序位置。MySQL 默认升序时 NULL 排在最前面降序时 NULL 排在最后面。如果想强制控制可以用IS NULL表达式SELECT * FROM user ORDER BY (age IS NULL) ASC, age ASC;这句的意思是先把 age 为 NULL 的排到最后非 NULL 的再按从小到大排序。3.2 JOIN 到底是啥怎么用JOIN 是 MySQL 基础里的重点也是热词里出现频率很高的一个。很多人被 JOIN 的语法吓到其实它就一句话把两张表按某个条件横向拼在一起。假设有两个表订单表 orders 和用户表 users。你想查出每个订单对应的用户姓名最简单的 INNER JOINSELECT o.order_id, o.amount, u.name FROM orders o INNER JOIN users u ON o.user_id u.id;INNER JOIN 只返回两张表中都能匹配上的记录。如果某个订单的 user_id 在 users 表里不存在这条订单就不会显示。LEFT JOIN 返回左表全部记录右表没有匹配的字段会填 NULLSELECT o.order_id, o.amount, u.name FROM orders o LEFT JOIN users u ON o.user_id u.id;实际使用中最容易出的问题是 JOIN 条件写错导致数据翻倍。比如 orders 表的 user_id 在 users 表里不唯一users 表存在重复数据结果就会多出很多行。排查方法是先分别统计两边数据再用COUNT(DISTINCT join_key)验证。JOIN 还有一个隐性成本两张大表 JOIN 时如果没走索引MySQL 会用嵌套循环扫描那画面太美不敢看。所以 JOIN 的关联字段一定要建索引这个下面讲索引的时候再细说。3.3 子查询和更新子查询子查询就是套在另一个查询里的查询。常见的有SELECT name, (SELECT MAX(score) FROM exam WHERE exam.student_id student.id) AS max_score FROM student;这种相关子查询对每一条外层记录都会执行一次性能较差。能用 JOIN 解决的尽量用 JOIN 代替。更新的子查询更麻烦。MySQL 不允许同时 UPDATE 一个表又从同一个表里面 SELECT经典的错误就是之前提到的那种。如果想更新一个表数据来源是同一张表的聚合结果需要绕一下比如先放到临时表或者派生表UPDATE student s JOIN (SELECT class_id, AVG(score) AS avg_score FROM student GROUP BY class_id) t ON s.class_id t.class_id SET s.class_avg t.avg_score;这样写就避开“不能直接 select from same table”的限制也是实际业务里更新统计字段很常用的姿势。4. 索引让查询快的核心4.1 创建索引的语法与场景索引算是 MySQL 性能的基础。创建索引语法很简单CREATE INDEX idx_user_name ON user(name);也可以在建表时直接定义CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50), age INT, INDEX idx_age (age) );组合索引更常用。比如业务里经常按class_id和status筛选那就建一个组合索引CREATE INDEX idx_class_status ON student(class_id, status);组合索引遵循“最左前缀原则”如果查询条件只用 status 不用 class_id这个索引就派不上用场因为索引最左边的字段没被用到。实际开发中索引不是越多越好。每个索引都会拖慢插入、更新、删除的速度因为每次数据变化都要同步维护索引。我在项目里见过一张表建了十几个索引结果写操作慢得离谱后来砍到五六个查询速度几乎没变。4.2 索引失效的典型情况知道索引怎么建还不够还要知道什么时候索引会失效。最常见的几种函数包裹字段SELECT * FROM user WHERE YEAR(create_time) 2024;这种写法会让索引失效。正确写法是范围查询SELECT * FROM user WHERE create_time 2024-01-01 AND create_time 2025-01-01;隐式类型转换也会失效。比如 user 表的 phone 字段是 VARCHAR你用数字去查SELECT * FROM user WHERE phone 13800138000;MySQL 会把字符串字段转成数字再比较索引就废了。正确写法是写成字符串。还有前导模糊查询SELECT * FROM user WHERE name LIKE %张%;这种因为开头就必须扫描所以索引也用不上。如果业务确实需要可以试试全文索引或者搜索引擎。注意索引失效的判断不是绝对的优化器会根据统计信息和成本决定要不要走索引。我建议你实际用 EXPLAIN 验证不要凭经验拍脑袋。5. 存储过程与触发器把逻辑写进数据库5.1 存储过程的声明与调用存储过程就是存在数据库里的一组 SQL可以带参数、有流程控制适合封装复杂逻辑。声明一个最简单的存储过程DELIMITER // CREATE PROCEDURE get_student_count(IN class_id INT, OUT total INT) BEGIN SELECT COUNT(*) INTO total FROM student WHERE student.class_id class_id; END // DELIMITER ;这里有两个关键点。第一个是DELIMITER因为 MySQL 默认用分号作为语句结束符如果你不临时改掉结束符存储过程体里面的分号会被 mysql 客户端当作一条完整的语句直接发出去导致 CREATE PROCEDURE 语法报错。所以要先DELIMITER //告诉客户端看到//才算结束执行完再改回来。第二个是IN和OUT参数IN 是输入参数OUT 是输出参数。调用时这样写CALL get_student_count(1, cnt); SELECT cnt;变量的 cnt 是用户变量可以在会话里保持方便下一步使用。实际业务里我不太建议把太重量的逻辑塞进存储过程。原因是版本管理麻烦、调试麻烦、迁移到其他数据库更麻烦。但如果你工作在数据仓库、报表类场景存储过程还是很常见的。5.2 触发器和 DELIMITER 分隔符触发器是另一种数据库对象它会在某个表执行 INSERT、UPDATE、DELETE 时自动触发。创建一个简单触发器在往订单表插数据后自动更新统计表DELIMITER // CREATE TRIGGER trg_order_after_insert AFTER INSERT ON orders FOR EACH ROW BEGIN UPDATE daily_stat SET order_count order_count 1 WHERE stat_date CURDATE(); END // DELIMITER ;注意触发器里的FOR EACH ROW表示每一行受影响都会执行一次。如果一次插入 1000 行触发器就被执行 1000 次性能影响很大而且出错时容易掩盖问题。我的建议是触发器适合轻量级、强一致性的场景比如审计日志复杂的统计还是由应用层去做别把所有逻辑都压给数据库。这里再次碰到DELIMITER是因为触发器的 BEGIN...END 内部有多个 SQL 语句同样需要临时修改结束符。这是很多新手在命令行敲触发器报错的根源。6. 新手最容易碰到的连接与安装问题6.1 error 2002 与 socket 连接问题热词里有条很典型的报错ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock (2)这个报错表示客户端想通过 Unix socket 文件连接本地 MySQL但没找到/tmp/mysql.sock。通常有三个原因MySQL 服务没启动socket 文件路径不对配置文件里指定了别的路径。排查步骤确认服务状态systemctl status mysqld # 或者 service mysql status如果服务没启动先启动systemctl start mysqld如果服务已经启动还是报这个错检查 MySQL 配置文件[mysqld] socket/var/lib/mysql/mysql.sock然后客户端连接时指定 socket 路径mysql -u root -p --socket/var/lib/mysql/mysql.sock我自己遇到更多的情况是用mysql -uroot -p去连的时候操作系统找的默认 socket 路径和 MySQL 实际用的不是同一个尤其是在编译安装或 docker 部署的场景。所以如果你不想折腾 socket就直接走 TCP 协议连mysql -u root -p -h 127.0.0.1 -P 3306这样连会比较稳定也能顺便验证端口是否正常监听。6.2 初始密码和登录绕坑很多新人安装 MySQL 8.0 后不知道初始密码是什么。官方 RPM 或 Linux 仓库安装后会在日志里生成临时密码grep temporary password /var/log/mysqld.log或者 Debian/Ubuntu 的 apt 安装会提示你用默认密码登录然后强制修改。如果你实在找不到密码可以通过 skip-grant-tables 方式重置# 停止 MySQL systemctl stop mysqld # 在配置文件临时加一行 [mysqld] skip-grant-tables # 启动 systemctl start mysqld # 无密码登录 mysql -u root登录后执行FLUSH PRIVILEGES; ALTER USER rootlocalhost IDENTIFIED BY 你的新密码;改完后一定要把配置文件里的skip-grant-tables删掉再重启服务。这个参数如果忘了删数据库会处于无认证状态风险非常高相当于把大门敞开。实操心得在生产环境千万不要直接改 root 密码而是创建业务专用账号并最小授权。比如只给某个应用账号授某几个表的权限避免误操作和安全隐患。6.3 安装启动服务报错的排查Linux 下安装 MySQL 后启动服务报错是最常见的问题之一。常见错误比如mysqld.service - LSB: start and stop mysql Loaded: loaded (/etc/rc.d/init.d/mysqld)这个报错信息本身看不清问题根源需要看日志。MySQL 的日志位置通常在/var/log/mysqld.log /var/log/mysql/error.log打开日志后常见的几种原因目录权限不对datadir路径下的目录属主要改成 mysql 用户磁盘空间满配置文件里有非法参数端口 3306 被占用。比如端口被占用可以这样查ss -tlnp | grep 3306如果被占用了要么停掉冲突的进程要么修改 MySQL 的端口配置。我之前遇到过一次/var/lib/mysql目录属主是 rootMySQL 进程无法写入启动后立刻退出。解决方式chown -R mysql:mysql /var/lib/mysql这里记住改属主前要确保目录里数据文件是你想要的否则重置权限可以找回服务但数据文件本身如果损坏就得另想办法。7. 基础性能调优与锁的初步认识7.1 慢查询和 EXPLAINMySQL 性能调优不是一上来就改参数第一步是找到慢的 SQL。开启慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;这条设置让执行时间超过 1 秒的 SQL 记录到日志。然后在日志里看到慢 SQL 后用 EXPLAIN 查看执行计划EXPLAIN SELECT * FROM orders WHERE user_id 1001;重点看几个字段type如果是 ALL说明是全表扫描通常需要加索引如果看到 range 或 ref就是走索引的范围扫描或等值查询还算可以。key实际使用的索引名如果为 NULL说明没走索引。rows预估扫描的行数越小越好。Extra如果出现 Using filesort 或 Using temporary说明排序或分组使用了临时文件通常需要优化。比如上面那条查询如果 user_id 上有索引type 一般是 ref。如果没有索引type 就是 ALLrows 可能几百万那这条 SQL 就是性能瓶颈。调优三板斧先加合适的索引再改写 SQL 去掉隐式转换和函数包裹最后考虑修改 MySQL 参数。不要一上来就把innodb_buffer_pool_size调得很大很多时候问题根本不在缓存大小而是一条糟糕的 SQL 拖垮了全库。7.2 锁表是怎么发生的“锁表”这个词在运维群和热词里都经常出现。简单理解InnoDB 引擎默认使用行锁但在某些情况下会升级成表锁导致整个表不可写甚至不可读。最常见的锁表场景是事务没提交。比如你执行了一条 UPDATE没有 COMMIT然后又执行一条相关 UPDATE它就会等待前面事务释放行锁。如果前一个事务一直不提交后面所有语句都会阻塞看起来就像表被锁死了。排查当前锁等待可以用SELECT * FROM information_schema.innodb_trx; SELECT * FROM information_schema.innodb_lock_waits;看到长时间运行的事务后可以找到它的trx_mysql_thread_id然后 KILL 掉KILL 1234;另一个锁表场景是大批量 UPDATE 或 DELETE 没有走索引导致 InnoDB 需要扫描全表每一行都加锁效果接近表锁。这就是为什么前面强调 UPDATE、DELETE 的 WHERE 条件必须走索引一个看起来很小的条件可能隐藏着锁表风险。实操里还要注意一个事务里如果先锁了 A 行再锁 B 行另一个事务恰好先锁 B 再锁 A就会死锁。MySQL 会自动回滚其中一个事务但应用层最好通过一致的加锁顺序避免这种情况。8. 我的几点实操心得文章写到这主体内容差不多讲完了。最后再分享几个我这些年用 MySQL 总结出来的习惯不算什么高深理论但对新人挺有用。第一个习惯是写任何 UPDATE、DELETE 之前先 SELECT 一遍同样的条件确认影响行数。这个动作在命令行下只要三秒钟却能避免绝大多数事故。我见过太多人把条件里的写成或者干脆忘掉 WHERE一条语句干废整个表。第二个习惯是给表和字段起名字的时候不要用order、group、select这种保留字。如果实在要用必须用反引号包裹以后所有 SQL 都要多打一对反引号非常麻烦。我见过一个老系统里的表名叫order结果每次写查询都像在做转义练习。第三个习惯是学会看错误日志。很多同学遇到报错第一反应是复制错误信息去搜索这没错但最好先打开 MySQL 的 error log 看一眼有时候日志里已经把真正的原因写得明明白白比网上搜到的解答更准确。第四个习惯是测试环境尽量用 docker 部署 MySQL。这样你可以随时用不同版本测试兼容性比如热词里提到的MySQL 8.4 or later is required (found 8.0)这类版本差异问题用 docker 拉一个对应版本的镜像几秒钟就能验证。我一般在项目里会写一个 docker-compose 文件固定好 MySQL 版本、端口、数据目录换机器搭建环境只需要一条命令docker run -d --name mysql8 -p 3306:3306 -e MYSQL_ROOT_PASSWORD123456 mysql:8.0这样本地开发和测试非常省心当然生产环境还是建议交给专业的运维方式。MySQL 的基础内容其实还有很多比如窗口函数、分区表、主从复制、连接池配置等等这些留到后面的文章慢慢聊。我始终觉得基础知识就像房子的地基你不需要把每块砖都背下来但得知道哪块砖承重最大出了问题该往哪个方向查。希望这篇“基础二”能帮你把前面的路铺得稳一点下次遇到数据库报错至少不慌了。