1. 建库前的规划先想清楚再动手1.1 版本选择与存储引擎的基本功MySQL 学了这么久真正自己动手建库的时候才发现建库这个操作远不止敲一行CREATE DATABASE那么简单。很多人包括我自己早期就在这一步踩了不少坑——明明语句执行成功了跑起来却各种乱码、连接不上、权限报错回头一看都是建库前没想清楚。这篇笔记记录的就是我从零开始搞数据库的完整过程版本怎么选、字符集怎么定、库怎么建、表怎么建、各类 SQL 怎么跑以及遇到问题怎么排查。适合刚学完基础语法准备上手实操的同学也适合那些已经写了不少 SQL 但始终没系统梳理过建库环节的开发者。先聊版本。MySQL 现在主流的是 5.7 和 8.0 两个大版本。如果你是从官网下载新装直接上 8.0 就行了它的默认字符集已经改成了 utf8mb4排序规则也换了性能优化比 5.7 强不少。但如果你的项目要兼容老环境、跟别人共用服务器那就先查一下现有实例的版本和生产环境用的版本尽量保持一致。举个例子5.7 里的utf8mb4_general_ci和 8.0 里的utf8mb4_0900_ai_ci排序规则差别很大如果程序里硬编码了对排序结果的期望跨版本迁移时会莫名其妙出问题。我吃过这个亏5.7 排序出来的结果和 8.0 不完全一致排查了半天才发现是排序规则差异。存储引擎方面InnoDB 是绝对的主流选择——支持事务、支持行级锁、崩溃恢复能力强。MyISAM 这个老引擎虽然查询快但表级锁并发差、不支持事务除非你确定自己的场景完全不需要这些特性否则别选。我记得早期用 MySQL 的人特别喜欢 MyISAM因为它的全文索引好用但现在 InnoDB 也支持全文索引了MyISAM 真的没有继续用的理由了。1.2 字符集与排序规则的决策字符集这个坑应该是所有 MySQL 新手都会经历的。我建第一个库的时候直接用了默认的latin1结果中文存进去全是问号折腾了一晚上终于明白是怎么回事。现在的原则很简单字符集只要不是 utf8mb4就要多想三秒钟。utf8mb4 是 UTF-8 的超集能存 4 字节的字符比如 emoji、生僻字这些全都不在话下。用 utf8 的话一个汉字三个字节没问题但 emoji 存进去就直接报错或者变成乱码。排序规则也顺手说一下。utf8mb4 下面有两套常见选择utf8mb4_general_ci和utf8mb4_unicode_ci8.0 里是utf8mb4_0900_ai_ci。general_ci 速度略快但排序精确度不如 unicode_ci。对于绝大多数业务场景这两者的差异你根本感知不到随便选一个坚持统一就好。真正重要的问题不是选哪套而是整个数据库链路的字符集要一致——库、表、连接、客户端全都要统一。连接层那边如果用了SET NAMES utf8mb4或者连接串指定了 characterEncoding服务端这边也要对得上否则你库和表都是 utf8mb4写入的数据还是会乱。建库时的字符集设定语句大概是这样的CREATE DATABASE IF NOT EXISTS my_app DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这一步直接把字符集钉死之后就再也不用来回改。改已存在的库的字符集很麻烦虽然可以执行ALTER DATABASE ... CHARACTER SET ...但历史表不会被自动迁移你得逐个表去改痛苦得很。所以我的习惯是宁可建库时多敲几个字符也不要事后补作业。2. 创建数据库与数据表的完整实操2.1 建库语句的细节与验证先给出一套我常用的建库模板适配 MySQL 8.0CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;IF NOT EXISTS这个关键字我每次必带。它的意义在于脚本重复执行不会报错。尤其你写部署脚本的时候谁也没办法保证整个流程不重复跑两遍。我当时写初始化脚本第一次没加第二次执行直接弹ERROR 1007 (HY000): Cant create database shop; database exists当场尴尬。加上之后重复执行就是静默跳过安全很多。然后是反引号的问题。对于库名、表名、字段名我的建议是能用小写下划线的组合就绝对不用特殊字符比如user_order、order_detail。但如果你接手老项目发现表名里有大写字母或空格那 SQL 里就得用反引号包起来否则语法报错。写代码时的习惯是从代码层生成 SQL 时全部加上反引号这样任何合法的表名都不会出问题手写探索性的 SQL 时就不加保持简洁。建完库之后验证一下SHOW CREATE DATABASE shop\G这条命令会把数据库的创建语句原样打印出来你就能确认字符集和排序规则是否真的生效了。注意SHOW后面跟的是CREATE DATABASE不是CREATE TABLE别搞混了。同时可以用SHOW DATABASES LIKE shop确认库是否存在。建库之后我做的第一件事不是立刻建表而是查看一下物理目录确认数据文件真的落盘了。用SHOW VARIABLES LIKE datadir;查到数据目录然后ls一下能看到一个名为shop的目录。这样做的好处是你能感知到 MySQL 的物理存储结构到底是什么样的后面做备份、做迁移的时候会更有感觉。纯逻辑层面的操作做多了容易忽略数据其实是落在磁盘上的文件这回事。2.2 建表设计与字段选择的经验建完库紧接着建表。这是整个过程中最容易暴露设计问题的环节。我见过太多一上来就CREATE TABLE users (id int, name varchar(255))的示例代码但放到真实项目里这种设计跑起来的性能惨不忍睹。实际建表时字段类型的选择稍微多花点时间后面能省很多事。整数类型的选择遵循一个逻辑范围够用就选最小的。比如用户表的id用INT UNSIGNED能存 40 多亿一般业务足够。但如果你做的是消息表、日志表这种一天可能上百万条数据的表从第一天就用BIGINT更省心。另一个例子是status字段0 表示待处理、1 表示已处理、2 表示失败这种用TINYINT就够了用INT就是浪费字节索引还变宽了扫描速度也受影响。字符串类型的选择也是经典问题。VARCHAR存可变长度字符串CHAR是定长。身份证号、手机号这种长度固定的业务字段用CHAR存储性能略好但大部分字段像用户名、备注、地址长度完全不可控肯定选VARCHAR。再有一个关键点VARCHAR的长度单位是字符不是字节所以VARCHAR(255)在 utf8mb4 下最多存 255 个字符不是 255 字节别被这个坑到。文本类型就TEXT和BLOB之间选择。实际业务中文档、详情大部分用TEXT就够了。像文章内容这种大文本可以考虑拆到独立的表里避免主表行变得过大影响 InnoDB 的行存储效率。这是我做过一次博客系统后的教训一开始把正文直接塞进主表后来文章多了查询明显变慢拆表后主表瘦身性能立刻回来了。时间字段我的建议是业务时间用DATETIME因为可读性好TIMESTAMP虽然有 2038 年问题和时区转换机制但占用空间小适合程序内部传递时间戳。如果你的业务要跨越多个时区统一用TIMESTAMP配合时区配置反而更方便但大多数单体应用根本不需要这么复杂DATETIME从头用到尾即可。一个规范的建表语句长这样CREATE TABLE IF NOT EXISTS user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(32) NOT NULL COMMENT 用户名, phone CHAR(11) DEFAULT COMMENT 手机号, gender TINYINT NOT NULL DEFAULT 0 COMMENT 性别 0未知 1男 2女, status TINYINT NOT NULL DEFAULT 0 COMMENT 账号状态 0正常 1禁用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户表;注意两点第一COMMENT一定要写这是给未来的自己和同事看的等三个月后你再回来看这个表没有注释的字段真的想不起来是干嘛的第二updated_at的ON UPDATE CURRENT_TIMESTAMP非常实用每次更新行数据时它会自动刷成当前时间省了一堆手动赋值。这两个习惯是我在一线干活之后才逐渐养成的属于账面上不会写清楚、但真实项目里非常好用的细节。3. 各类 SQL 的运行与踩坑实录3.1 DML 操作——写入、修改、删除的姿势库有了表有了接下来就是跑各类 SQL。经常听到一句话叫“MySQL 的 SQL 就是把增删改查写好”但当业务复杂了之后看似简单的增删改查里全是学问。插入语句要搞清楚两件事字段列表和值的对应关系。显式列出字段是一个值得坚持的习惯INSERT INTO user (username, phone, gender) VALUES (张三, 13800138000, 1);不写字段列表直接VALUES (1, 张三, ...)是很危险的做法。一旦表结构发生变化插进去的数据全错位而且错位后的数据根本看不出来是错的只有业务跑起来才会暴露。批量插入时一条 INSERT 后跟多个 VALUES 会大幅提升效率比循环单条插入的性能好了不少这就是“减少 SQL 往返”的核心思路。修改数据时最容易犯的错是忘写WHERE。前几年有一次我在测试环境做数据订正跑了一次UPDATE user SET status 1直接把全表状态都给改了幸好是测试库。从那以后我给自己定了个规矩任何 UPDATE 和 DELETE 语句写 WHERE 条件之前先数清楚影响行数。MySQL 客户端执行完会显示Rows matched / Changed / Warnings三组数字看一眼再回车心里才踏实。正式库操作我甚至会在事务里先 SELECT 出来 count 一遍确认数据量对了再执行 UPDATE。DELETE 操作还要考虑是否带LIMIT。不带 LIMIT 的大范围 DELETE在数据量大的表上会一直锁行锁间隙长时间不释放可能导致核心业务阻塞。对于清理历史数据这种操作我的做法是分批处理每批几百条或者几千条加上条件循环清理宁可慢一点也不要一次把生产库锁死。还有一点DELETE 不会重置自增 ID如果你DELETE完后想id从 1 开始那要考虑ALTER TABLE ... AUTO_INCREMENT 1或者干脆用TRUNCATE TABLE但 TRUNCATE 是清空全表不可按条件删使用前务必三思。3.2 DQL 查询——排序、去重、分组的关键细节查询是 SQL 里占比最高的操作也是最能看出功底的环节。先说说排序。ORDER BY后面自然就是字段名和方向ASC / DESC但有个细节是如果你排序的字段没有索引MySQL 就不得不使用文件排序filesort数据量一大性能直线下降。我给订单表做过一次优化原本ORDER BY created_at DESC要 1.2 秒加了一个普通索引后降到 12 毫秒这个差距就是索引和没有索引的真实写照。排序还有个容易被忽略的点——多字段排序时顺序是有意义的ORDER BY status ASC, created_at DESC会先按 status 排完再按时间排亲测前端列表想要“未处理的在前、新的在前”用这个写法就对了。去重这个关键字DISTINCT很常用但用起来有讲究。比如SELECT DISTINCT user_id FROM order WHERE status 1;这是查出所有下单过的用户 ID没问题。但DISTINCT的语义是“整行去重”如果 SELECT 的字段里有多个列那只有这些列组合完全一样才会被去重。很多人以为DISTINCT列名是按某一列去重这是误解。另外DISTINCT写多了之后逻辑会显得笨重遇到“按某字段分组取最新一条”这种场景DISTINCT是做不到的实际工作中更常用GROUP BY配合聚合函数或者窗口函数来解决。比如我需要拿到用户最近一次下单时间SELECT user_id, MAX(created_at) FROM order GROUP BY user_id;分组之后HAVING比WHERE更符合“对聚合结果过滤”的语义。在 WHERE 里写聚合条件是语法错误在 HAVING 里过滤非聚合条件虽然语法上允许但逻辑上性能并不好。我自己一般这样区分WHERE 先过滤原始行HAVING 再过滤分组结果。这行逻辑想清楚GROUP BY 的很多怪问题都迎刃而解。再说一个热搜词里出现频率很高的需求——“SQL语句去重”。除了DISTINCT之外实际业务中更常见的是“按某个字段去重但保留完整行”特别是日志表或者流水表。最早的版本用嵌套子查询和 GROUP BY 实现后来 MySQL 8.0 支持了窗口函数写法变得更优雅了SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM operation_log ) t WHERE t.rn 1;这种写法是按user_id分组组内按时间倒序编号最后取每组的第一条就得到了“每个用户最新的操作日志”。相比原来的 GROUP BY 子查询方案窗口函数的逻辑直观太多我建议有条件的同学直接升级 MySQL 8.0 用起来。3.3 事务处理与存储过程事务是 InnoDB 的看家本领也是业务数据一致性的基石。我最早写代码的时候每次插入一条订单再更新一次库存各写一条 SQL然后完事。结果有一次程序执行到一半报错了订单插进去了库存没减整个数据就对不上了。后来学了事务终于明白这类问题应该这样解决START TRANSACTION; INSERT INTO order (user_id, product_id, amount) VALUES (18, 2001, 2); UPDATE product SET stock stock - 2 WHERE id 2001 AND stock 2; COMMIT;事务的四个特性 ACID概括下来就是要么全成功要么全失败。上面的库存更新里加了AND stock 2这是防止超卖的关键条件。开启事务后万一执行到一半发现库存不足可以ROLLBACK把刚才的操作全部撤销数据回滚到事务开始之前。这个能力平时用不到但真正出问题的时候它能救你一命。事务还牵涉隔离级别的问题默认的REPEATABLE READ可重复读对大多数业务够用不用一上来就研究各种隔离级别差异等真碰上脏读、幻读再说。存储过程这东西有的人爱得深沉有的人避之不及。我的观点是存储过程适合封装复杂且相对固定的数据逻辑比如批量对账、月末统计、数据归档等任务但如果你天天在上面做业务逻辑那说明代码分层可能出了问题。存储过程里比较实用的场景是循环处理大量数据比如我写过一个小存储过程把历史日志按月份迁移到归档表一条 SQL 触发整个流程省去了在代码里写循环的麻烦。不过存储过程的坑也很明显不好调试、不好做版本管理、出错信息不够透明。我的建议是可以用但控制好边界真正的业务逻辑尽量留在应用程序里。一个简单的存储过程示例DELIMITER // CREATE PROCEDURE proc_archive_logs(IN days INT) BEGIN INSERT INTO operation_log_archive SELECT * FROM operation_log WHERE created_at DATE_SUB(NOW(), INTERVAL days DAY); DELETE FROM operation_log WHERE created_at DATE_SUB(NOW(), INTERVAL days DAY); END // DELIMITER ;执行CALL proc_archive_logs(30);就会把 30 天前的日志搬到归档表。注意DELIMITER的使用非常关键因为默认的语句分隔符是分号而存储过程体内部也有分号不切换分隔符的话 MySQL 会错误地提前结束整个语句。这个细节是新手写存储过程时最常踩的坑我看到过好几个人在这里卡了半天。3.4 字段默认值与常见约束设置热搜词里有“mysql设置默认值为0”这个话题虽然看起来小实际项目里用到的频率非常高。一个场景是给商品表的销量、库存或者用户表的积分字段设置默认值 0ALTER TABLE product MODIFY COLUMN sale_count INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 销量;这样就保证新插入的商品如果不指定sale_count它会自动取默认值 0而不是 NULL。为什么这一点很关键因为在程序里处理 NULL 和 0 完全不一样NULL 参与运算的结果经常是 NULL比如price * quantity如果 quantity 是 NULL结果就是 NULL前端展示就出现空白。而 0 参与运算则正常得多。很多线上数据异常追根溯源就是字段允许了 NULL计算时没做处理。所以我建表的偏好是数值型字段尽量 NOT NULL DEFAULT 0字符串字段尽量 NOT NULL DEFAULT 时间字段用 DEFAULT CURRENT_TIMESTAMP。保持这个风格后业务代码里 null 判断的数量少了一大半。如果想要在已有表上做修改MySQL 8.0 的ALTER TABLE ... ALTER COLUMN ... SET DEFAULT和MODIFY COLUMN都能用注意区分ALTER COLUMN只改默认值MODIFY COLUMN需要重写整个字段定义。重写字段定义时如果漏写了原有属性很可能把字段类型悄悄改掉这个操作危险程度不低最好先在测试库验证一遍。4. 连接、权限与运维排查笔记4.1 客户端连接与权限配置细节数据库建好了SQL 也会写了接下来的问题是我该怎么连上去命令行、Navicat 或者其他客户端工具本质上连接的链路是一回事无非 TCP、账号、密码、端口。端口默认是 3306修改过要记得去防火墙放行。我在用 Navicat 连接 MySQL 时遇到过各种奇怪的问题最常见的一类错误是所谓的 “mysql ssl连接错误”。现在的 MySQL 8.0 默认开启 SSL 连接很多客户端默认也尝试用 SSL 去连但证书配置不全就会握手失败。排查的顺序是先用命令行本地连接测试排除服务端问题如果命令行能连而 Navicat 报 SSL 错误那基本就是 SSL 参数不匹配。这时可以把连接配置里的 SSL 项改为“禁用”或“如果可用”这个问题经常就迎刃而解了。注意这里只是客户端连接方式的调整并不是什么高深操作不用纠结。权限相关的错误也排在前几名。ERROR 1045 (28000): Access denied for user几乎每个人都会撞到一次。这条错误出现时第一时间确认的是账号是否存在、密码是否正确、该账号是否有从当前 IP 登录的权限。MySQL 的用户权限模型实际上是“用户 来源主机”的组合testlocalhost和test%是两个不同的账号这一点极其容易混淆。给开发同事授权时我习惯这样写CREATE USER app_user% IDENTIFIED BY StrongPss123; GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO app_user%;之后刷新权限FLUSH PRIVILEGES;。这里有个细节MySQL 8.0 的用户密码插件默认是caching_sha2_password某些老版本的客户端驱动对这个插件支持不好连接时会报认证失败。解决办法要么是升级驱动要么是创建用户时显式指定IDENTIFIED WITH mysql_native_password BY 密码但后者是过渡方案长远看还是升级客户端驱动更稳妥。4.2 常见报错速查整理我把实操中遇到的高频错误整理成一张速查表方便遇到问题时对照排查错误信息常见原因处理思路ERROR 1045 Access denied账号/密码不对或来源主机无权限先确认账号密码再看 host 匹配最后看权限表ERROR 1007 Cant create database数据库已存在建库语句加IF NOT EXISTSERROR 1064 SQL 语法错误比如遗漏符号、表名包含特殊字符没加反引号查看语法提示位置确认关键字和引号ERROR 1054 Unknown column字段不存在或拼写错误DESC table;查看表结构核对字段名ERROR 1171 字符集排序规则不支持排序规则与字符集不匹配统一使用 utf8mb4 对应排序规则ERROR 1205 Lock wait timeout exceeded行锁等待超时检查是否有长事务未提交杀掉阻塞线程ERROR 1366 Incorrect string value字符集不一致导致乱码检查库/表/连接三层字符集ERROR 1264 Out of range value数值超出字段范围放大字段类型或者检查插入值是否有异常ERROR 1418 存储过程权限不足建存储过程需要权限为账号授予CREATE ROUTINE权限ERROR 1819 密码策略不满足密码太简单不符合 validate_password 策略按提示调整密码复杂度或调低策略级别ERROR 3024 for sha2 authentication客户端驱动不支持默认密码插件升级驱动或改用 mysql_native_password表格里最后一行的 authentication 问题我直到做了 8.0 部署才真正体会。之前在公司做项目迁移时服务器上的caching_sha2_password让一个用老版本 JDBC 驱动的服务在启动时反复报认证失败。排查了很久最终是通过升级驱动解决。如果你比较着急临时把用户密码插件改回去也能顶上但记得这只是临时方案。4.3 慢 SQL 分析与基础优化技巧热搜词里“慢sql优化”出现频率很高这个技能对于一个数据库用户来说比想象中重要。MySQL 开启慢查询日志非常简单SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;之后执行超过 1 秒的 SQL 都会被记录下来。分析工具方面其实不用一上来就用专业平台直接看日志也行配合EXPLAIN足够定位大部分问题。有次我查一个报表接口的慢 SQL日志显示某条查询耗时 4 秒EXPLAIN 一看扫描行数 80 万key 显示 NULL——没走索引。加完索引再查耗时 50 毫秒。整个过程用不了一刻钟收益却极其明显。这就是慢 SQL 优化的核心套路先定位慢的 SQL再看 EXPLAIN 的执行计划然后针对性加索引或改写 SQL。索引也不是加得越多越好每个索引都会拖慢写入速度只给高频查询的 WHERE、JOIN、ORDER BY 字段加索引就够了。所谓优化不是把所有能加的都加上而是把钱花在刀刃上。EXPLAIN输出里重点看三个字段type访问类型、key实际使用的索引、rows预估扫描行数。type从高到低有 system const eq_ref ref range index ALL看到 ALL 就基本说明全表扫描了这是一个非常直观的警示信号。5. 学习复盘与我的几个习惯写到最后分享几个我实际干活这几年沉淀下来的小习惯。建库的习惯是每个环境保持相同的字符集和排序规则。开发库、测试库、生产库只要这三者的字符集不一致早晚会出一次数据问题而且问题特别隐蔽——本地正常线上乱码排查起来极度耗费时间。统一用 utf8mb4 后这种问题直接绝迹。跑 SQL 的习惯是在事务里做有风险的写操作并且养成操作前先备份、操作后验证结果的习惯。比如 UPDATE 之后我会立刻写一条 SELECT 确认关键数据确实变成预期值而不是改完就跑。不是每次都需要但大操作和关键库的小操作都要保持这个意识。学习 SQL 的习惯是别背语法背思路。SQL 语法是有限的工作里遇到不会的语法随手查一下手册或者看别人写的案例就懂了。真正值钱的是思路——比如去重用窗口函数、防止库存超卖时用条件更新、加索引前先用 EXPLAIN 看执行计划。语法可以现查思路需要反复沉淀。这篇笔记就是我用一条条实操中的选择沉淀出来的希望对刚进入 MySQL 世界的同学有点帮助。