今天是学习MySQL的第二天我和它的相处模式终于从“下载安装”过渡到了“正式上手”。如果说DAY01的主题是装环境、配连接那DAY02的核心任务就一个——用SQL把一张真实的成绩表建出来、查明白。我把这一天的过程完整记录在这里每一步做了什么、为什么这么做、踩了什么坑、最后怎么解决的。不管你是Windows本机装的MySQL还是像我一样在Linux服务器上跑MySQL 8.0下面这些内容应该都能直接用上。不废话直接开始今天的复盘。1. 先复盘DAY01装好的环境DAY02到底要学什么1.1 为什么我把“背语法”换成了“先建一张成绩表”很多初学者DAY02容易陷入一个误区拿着官方文档从头啃数据类型、SQL语法啃了两天还是不会写业务查询。我今天换了个思路——不背语法直接做一个“学生课程成绩”的小项目。这个场景在网络上被归纳为“学生课程成绩信息实体表设计mysql”几乎是所有MySQL教程里最容易理解的案例学生表、课程表、成绩表三张表一设计关联查询、排序分组、索引、事务全都能覆盖到学起来不抽象。我当前的环境是云服务器上的Linux MySQL 8.0之前用命令行工具初始化的。如果你是用docker安装mysql起的容器或者Windows下装的MySQL结论大体一致只有服务启动方式和socket路径有差异后面排查问题时会单独说。1.2 我准备的连接工具命令行为主图形工具为辅今天的大多数操作我都是在mysql命令行里完成的这样能让自己真正理解SQL执行过程。遇到过卡住的地方再用图形工具辅助查看表结构。连接工具方面我用的官方MySQL WorkbenchNavicat for MySQL这类工具功能更丰富但没必要刻意追求什么破解版官方或者开源替代品足够用。这里有个小建议新手尽量先别依赖图形工具的“可视化建表”按钮否则容易忽略SQL本身。今天所有表的创建和查询都在命令行敲一遍敲着敲着手感就有了。2. 建库建表把“实体表设计”落到SQL上2.1 字符集和排序规则宁可一开始全用UTF8MB4建库第一步不是写CREATE TABLE而是先决定字符集。我建库的语句是这样的CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;为什么不用老式的utf8因为MySQL里的utf8实际最多存3字节像emoji这类4字节字符会存不进去中文场景下也会出现莫名其妙的乱码。utf8mb4才是真正的“完整UTF-8”。排序规则我选了utf8mb4_unicode_cici表示大小写不敏感适合绝大多数业务场景MySQL 8.0默认的utf8mb4_0900_ai_ci也可以两者在实际查询里差别不大。这个决定帮我避免了一个很典型的坑如果建库时没指定字符集或者用了latin1后面插入中文数据很容易看到一排问号。到时候你可能会以为是程序问题其实根源在数据库字符集。记住字符集要在最开始就统一越早越省事等表里有数据了再改字符集代价就大了。2.2 字段类型怎么选学生表、课程表、成绩表的实战设计我用到的三张表结构如下这也是我今天花了最多时间琢磨的地方。学生表CREATE TABLE student ( s_id INT UNSIGNED AUTO_INCREMENT COMMENT 学生ID主键自增, s_name VARCHAR(50) NOT NULL COMMENT 学生姓名, gender TINYINT DEFAULT 0 COMMENT 性别0未知1男2女, birth_date DATE DEFAULT NULL COMMENT 出生日期, phone VARCHAR(20) DEFAULT NULL COMMENT 手机号, PRIMARY KEY (s_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生表;课程表CREATE TABLE course ( c_id INT UNSIGNED AUTO_INCREMENT COMMENT 课程ID主键自增, c_name VARCHAR(100) NOT NULL COMMENT 课程名称, credit DECIMAL(3,1) DEFAULT 0 COMMENT 学分, PRIMARY KEY (c_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT课程表;成绩表CREATE TABLE score ( s_id INT UNSIGNED NOT NULL COMMENT 学生ID, c_id INT UNSIGNED NOT NULL COMMENT 课程ID, score DECIMAL(5,2) DEFAULT 0 COMMENT 成绩, PRIMARY KEY (s_id, c_id), CONSTRAINT fk_score_student FOREIGN KEY (s_id) REFERENCES student(s_id), CONSTRAINT fk_score_course FOREIGN KEY (c_id) REFERENCES course(c_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT成绩表;三点实战心得主键用INT UNSIGNED AUTO_INCREMENT比直接用学号字符串当主键更高效。联合主键放在成绩表这种关联表上天然防止同一学生同一课程重复录入。性别字段我用TINYINT而不是VARCHAR省空间也方便程序里做枚举映射出生日期用DATE手机号用VARCHAR(20)因为手机号可能存在前导零或者加号用INT会出问题。物理外键FOREIGN KEY可以建但实际高并发项目里很多团队会刻意不用物理外键改为应用层维护关联。原因很简单外键会在每次插入、更新时触发额外的完整性检查影响写入性能。学习阶段建上没毛病能帮你理解表关系生产环境里是否保留要结合团队规范来决定。2.3 一个坑字段默认值为什么设置成0却没反应今天踩的第一个坑就是“mysql设置默认值为0”不生效。我在学生表里给gender设了DEFAULT 0但插入一条没带gender的数据后查出来gender是NULL而不是0。排查后发现原因很基础问题出在插入语句本身。我插入时写的是INSERT INTO student (s_name, gender) VALUES (张三, NULL);当显式给gender传了NULLMySQL不会再用默认值最终存的当然就是NULL。DEFAULT只在“插入时这个字段完全没出现”时才生效。所以如果你想用默认值插入语句里就别带这个字段INSERT INTO student (s_name) VALUES (张三);还有一个相关注意事项MySQL的SQL模式里有个严格模式如果字段设置为NOT NULL且没有默认值插入时不传该字段会直接报错。反过来如果字段允许NULL即使你设置了DEFAULT 0程序里习惯性传入NULL最后仍会存成NULL。很多“默认值不生效”的问题本质上都是没分清“字段缺省”和“显式写NULL”。3. 第一次完整的数据操作增删改查与排序分组3.1 插入数据列名一定要写全建完表我往三张表里插了一批测试数据。插入语句看起来简单但有一个习惯今天直接养成了永远写全列名。INSERT INTO student (s_id, s_name, gender, birth_date, phone) VALUES (1, 张三, 1, 2005-06-15, 13800000001);不写列名的简写方式如下INSERT INTO student VALUES (1, 张三, 1, 2005-06-15, 13800000001);这种写法虽然能跑但太脆弱了。只要表结构一变比如插入一列这条SQL就全错位。列少的时候还好列一多你根本没心思去数第几个值对应哪个字段。我见过生产事故里最典型的案例就是有人删了一列后旧的简写INSERT语句把数据写错列了第二天跑数才发现。如果是批量插入多行VALUES和循环单条插入的性能差距非常大。MySQL支持一条语句插多行INSERT INTO score (s_id, c_id, score) VALUES (1, 1, 88.5), (1, 2, 92.0), (2, 1, 76.0);一条语句批量提交比循环执行三条INSERT效率高得多因为少了大量网络往返和事务提交开销。3.2 排序是最常用的查询手段ORDER BY的几种写法插入完数据我做的第一类查询练习就是“mysql排序”。排序是今天的高频关键词因为几乎所有报表都离不开它。最简单的单字段排序SELECT s_id, c_id, score FROM score ORDER BY score DESC;多字段排序SELECT s_id, c_id, score FROM score ORDER BY c_id ASC, score DESC;这个查询的意思很直观先按课程ID升序排同一门课内部再按成绩降序排。写多字段排序时顺序非常重要第一个字段是主排序键只有在它相同的情况下第二字段才会起作用。还有一点值得关注ORDER BY配合LIMIT是做“Top N”分析的标配。比如查成绩最高的前3条SELECT s_id, c_id, score FROM score ORDER BY score DESC LIMIT 3;工程里经常听到“查询很慢加了索引也没用”很多时候就是ORDER BY字段没建索引。排序字段如果能走索引MySQL可以直接按索引顺序扫不需要额外的filesort如果排序字段没索引数据量一大临时文件和排序耗时都上来了。这个知识点在“mysql性能调优”面试里几乎是必问的。最后提一个容易忽略的中文排序。正常UTF8MB4下的ORDER BY中文是按Unicode码点排序的并不是按拼音。如果要按拼音排姓名可以这样SELECT s_name FROM student ORDER BY CONVERT(s_name USING gbk);GBK编码的字符顺序恰好与拼音字典序一致所以转成GBK再排序就能得到“按拼音排序”的效果。3.3 分组统计用GROUP BY算平均分和人数DAY02最难的部分是分组统计在MySQL里对应“mysql排序”之外的另一大高频话题但实际是“分组聚合”。我拿成绩表做了几个练习。每门课的平均分SELECT c_id, AVG(score) AS avg_score, COUNT(*) AS cnt FROM score GROUP BY c_id;每个学生的总成绩SELECT s_id, SUM(score) AS total_score FROM score GROUP BY s_id;最容易搞错的点是WHERE和HAVING的区别。我的记忆方法很简单WHERE是在分组之前过滤行HAVING是在分组之后过滤组。比如只想看平均分超过80分的课程SELECT c_id, AVG(score) AS avg_score FROM score GROUP BY c_id HAVING AVG(score) 80;而如果只是想排除掉某个学生的成绩再统计就要用WHERESELECT c_id, AVG(score) AS avg_score FROM score WHERE s_id ! 1 GROUP BY c_id;我见过很多新手在GROUP BY语句里用HAVING去过滤普通字段比如HAVING c_id 1语法上没错但逻辑上绕远了。原则是能用WHERE的地方别用HAVING因为WHERE在分组前就过滤掉数据处理的数据量更小性能更好HAVING是在分组聚合后才过滤能用到它的场景只有“对聚合结果做条件判断”。还有个小细节SELECT后面出现的非聚合字段最好都出现在GROUP BY里。比如SELECT c_id, s_id, COUNT(*) FROM score GROUP BY c_id;这种写法MySQL 8.0在ONLY_FULL_GROUP_BY模式下会直接报错这是好事说明它在逼你写更严谨的SQL。4. 今天最重要的进阶索引、事务和锁4.1 创建索引哪些字段值得加索引DAY02我给自己加了一个进阶任务——给成绩表创建索引。原因很直接后面我要做大量“按课程查成绩”的练习而score表的主键是(s_id, c_id)这意味着按c_id单独查询时没法有效利用主键索引。创建索引的命令CREATE INDEX idx_cid ON score(c_id);也可以建联合索引。如果查询经常同时按学生和课程筛选一个联合索引更合适CREATE INDEX idx_sid_cid ON score(s_id, c_id);索引的原理我给自己总结了一句人话索引就像书的目录没有索引时你得一页页翻全表扫描有了索引数据库可以直接通过目录跳到目标位置。但今天我想重点说的是“不要盲目加索引”。索引不是越多越好每个索引都会占用磁盘空间而且每次INSERT、UPDATE、DELETE时MySQL都要同步维护索引写操作越多索引带来的负担越重。经验上只有满足下面几个条件之一的字段才值得建索引经常出现在WHERE条件里的字段经常做为ORDER BY排序依据的字段经常出现在GROUP BY或JOIN关联字段判断一个查询是否真的用上了索引可以使用EXPLAINEXPLAIN SELECT * FROM score WHERE c_id 1;看输出里的type列如果出现的是ref或const说明走了索引如果出现ALL说明是全表扫描。rows列可以预估扫描行数数字越小越好。这个习惯我从DAY02就开始培养后面做任何慢查询排查都靠它。创建索引时长度也要注意。如果给一个很长的VARCHAR字段加索引最好加前缀索引。比如手机号VARCHAR(20)可以只对前11位建索引没必要索引整个字段能省不少空间。4.2 事务处理自动提交、COMMIT与ROLLBACK“mysql事务处理”是今天第二个进阶主题。我一开始以为事务是特别玄的东西亲手试过一次之后发现它其实就是保证“一组SQL操作要么全部成功要么全部失败”。MySQL默认是自动提交模式即每执行一条写SQL都立刻提交。打开自动提交状态可以用SHOW VARIABLES LIKE autocommit;大多数情况下值是ON。但在需要保证数据一致性的场景里比如转账、下单必须手动开启事务START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;如果第二条SQL执行失败或者数据不对可以ROLLBACK回滚START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; -- 发现异常 ROLLBACK;两条UPDATE就像一个整体要么都生效要么都撤销。这就是事务的原子性。我今天专门做了一个小实验开启事务后执行UPDATE在另一个会话里查数据发现查到的还是旧值直到COMMIT之后第二个会话才能看到新值。这就是事务隔离性的直观感受。一个值得一提的点MySQL默认引擎是InnoDB它支持事务但MyISAM引擎完全不支持事务而且没有行级锁。现在很多老项目还在用MyISAM一旦涉及并发写入特别容易出问题。判断一张表是什么引擎SHOW TABLE STATUS WHERE Name score;如果是MyISAM需要改成InnoDBALTER TABLE score ENGINE InnoDB;4.3 锁表是怎么一回事一次实测记录“mysql锁表”是搜索热词也是面试常客。今天我实际模拟了一次理解立刻立体了。开两个mysql会话第一个会话执行START TRANSACTION; UPDATE student SET s_name 张三改 WHERE s_id 1;此时事务未提交。第二个会话执行同一条UPDATEUPDATE student SET s_name 李四 WHERE s_id 1;现象第二条UPDATE一直卡住不报错也不返回直到第一个会话执行COMMIT第二条才瞬间执行完成并返回。这个“卡住”就是锁等待。InnoDB默认对UPDATE走的是行级锁两个会话同时改同一行后者必须等前者的锁释放。这个实验让我理解了两件事。第一事务一定要短尽量不要在事务里做耗时操作比如调用远程接口、sleep否则并发一大后面的请求全堵在那里数据库连接池很快耗尽。第二UPDATE和DELETE尽量按主键或索引字段来定位行如果WHERE条件没走索引InnoDB会锁住更多的行极端情况下可能升级成锁表影响整张表的写入。补一个进阶概念InnoDB在可重复读隔离级别下还会产生间隙锁锁定的是一个范围而不只是具体行。所以“明明只更新了几条数据却把整个范围的插入都挡住了”这种问题并不罕见。等学到更深的锁机制时这会是一个非常重要的话题。5. 连接故障排查实录error 2002与客户端连不上5.1 error 2002的常见原因与排查步骤今天下午我重启了一次服务器再连MySQL时直接报错这个错误在搜索热词里出现频率极高ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock看到这个报错第一反应应该是MySQL服务进程没起来或者客户端与服务端的socket路径不一致。我当时的排查顺序是这样的第一步确认进程是否在跑ps -ef | grep mysqld如果没有任何mysqld进程说明服务没启动直接去启动服务。Linux上一般用systemctl管理systemctl start mysqld第二步如果进程在跑但还是连不上查socket路径配置cat /etc/my.cnf看里面是否有socket配置项比如socket/tmp/mysql.sock或者socket/var/lib/mysql/mysql.sock。如果配置文件和客户端默认找的路径不一致就会出现报错里那个路径连不上的情况。解决办法是连接时显式指定socketmysql -uroot -p -S /var/lib/mysql/mysql.sock第三步如果是用docker安装mysql起的容器情况又不一样了。容器里一般用docker exec -it my-mysql mysql -uroot -p进容器执行mysql命令或者在宿主机上用-p参数映射3306端口再通过TCP方式连接mysql -h127.0.0.1 -P3306 -uroot -p注意使用TCP方式时如果报的还是“Cant connect through socket”那是因为mysql命令在没有-h参数时默认走socket连接不是连不上TCP而是没走对协议。5.2 Workbench和Navicat连接失败的三个检查点命令行能连上但MySQL Workbench和Navicat这类图形工具连不上这个问题我过去遇到过无数次今天也顺手查了一遍。归纳下来就三个检查点第一服务器是否允许外部连接。MySQL默认的bind-address是127.0.0.1表示只允许本机连接。如果要从远程连需要把bind-address改成0.0.0.0并重启MySQL[mysqld] bind-address 0.0.0.0有些发行版甚至默认开启了skip-networking这会直接禁用TCP连接只有本地socket能连这个选项在/etc/my.cnf里能看到有就要去掉。第二防火墙端口是否放行。Linux上检查3306端口是否在监听ss -tlnp | grep 3306如果远端ping不通3306先看云服务商的安全组规则再看服务器本地防火墙。很多“连不上”的问题根本不是MySQL配置问题而是安全组忘了放行端口。第三数据库用户的host范围。MySQL用户有“用户名主机”的概念如果创建用户时写的是rootlocalhost那它只能从本机登录要从远程登录需要有一个root%或者专门的用户。查看现有用户SELECT user, host FROM mysql.user;5.3 连接串和驱动的坑SSL参数与驱动版本还有一个隐蔽的坑是“mysql ssl连接错误”。MySQL 8.0默认开启了某些SSL相关行为很多老客户端或者特定连接串参数匹配不上就会出现类似“SSL connection error”或者证书验证失败的报错。临时排查方式是在连接命令里关闭SSL验证mysql -uroot -p -h192.168.1.10 --ssl-modeDISABLED如果用JDBC连接设置连接串参数jdbc:mysql://192.168.1.10:3306/school?useSSLfalseallowPublicKeyRetrievaltrueallowPublicKeyRetrievaltrue这个参数也很关键MySQL 8.0用caching_sha2_password做默认密码认证时首次连接可能需要获取公钥很多客户端由于没开这个参数导致连不上去。另外8.0默认插件是caching_sha2_password旧的Navicat或低版本驱动不一定兼容。工程里的兼容做法是给用户改回mysql_native_passwordALTER USER root% IDENTIFIED WITH mysql_native_password BY your_password;但这属于兼容方案新项目优先升级客户端驱动而不是降低数据库的认证方式毕竟caching_sha2_password本身更安全。6. DAY02收尾留下的笔记和下一步计划6.1 我给自己整理的“DAY02速查卡”这一天结束前我把高频操作整理成了一小块速查卡方便接下来几天快速回顾。这也是“mysql数据库命令大全”这类笔记的初版。用途命令查看所有库SHOW DATABASES;切换库USE school;查看表结构DESC student;建索引CREATE INDEX idx_cid ON score(c_id);查索引SHOW INDEX FROM score;慢查询分析EXPLAIN SELECT ...;当前事务隔离级别SELECT transaction_isolation;查表引擎SHOW TABLE STATUS WHERE Namescore;查看线程/锁状态SHOW PROCESSLIST;这张表不看内容光看这些话就能看出DAY02已经从“会建表”走到了“会排查问题”。数据库的学习就是这样命令是永远背不完的但核心的几十条高频命令在日常开发里反复出现多敲几遍就成肌肉记忆了。6.2 下一步学什么从视图、存储过程到主从复制今天结束时我给自己列了一个后续路线。DAY03可以学视图和触发器理解“虚拟表”和自动化逻辑的适用场景再往后可以到“mysql存储过程”但现阶段不急着写因为存储过程在业务代码里用得越来越少很多团队明确不用重点还是把表设计和查询优化打牢。等到单机MySQL的性能和稳定性碰到瓶颈再去碰“mysql主从复制”和读写分离到时候会牵扯到binlog、GTID、延迟监控等一系列概念。如果以后做中间件可能还会研究数据库连接池的配置、连接数的估算。有朋友已经在准备“mysql面试题”里面大量内容是锁机制、索引优化、事务隔离级别今天学到的这些都算是给面试铺了一部分路但离深还有距离得继续往下学。今天过完我最想说的一句话是别急着把Workbench、Navicat的按钮都玩一遍命令行里敲懂一条SELECT比图形界面点十次都有用。建表时多看几眼字段类型查询时多想一下能不能走索引报错时先看“服务在不在、端口通不通、账号权限对不对”这三个习惯DAY02养成真不亏。