接手过MySQL项目的人大多有过这种经历新项目上线半年后想给某张核心业务表加一个字段打开表结构一看十几个列名全是英文缩写没有一条注释类型还选得乱七八糟。问当初写的人对方自己也说不明白。建表、新增字段、变更字段、存储过程、设置注释——这些是日常开发里最基础的SQL操作但恰恰是这些“基础动作”最能拉开一个工程项目的长期质量差距。这篇想聊的就是这些东西。我会把建表时的字段选型、ALTER TABLE的高频场景、注释管理的实用套路、存储过程的正确写法以及生产环境执行DDL前必须检查的事项统统过一遍。内容不追求面面俱到的官方文档复述只讲我实际在项目里验证过、踩过坑之后沉淀下来的做法。适合刚接触MySQL的同学照着抄也适合已经写了一段SQL但没系统梳理过表结构设计的人做一次自查。1. 建表、改字段这类操作为什么值得专门梳理一遍1.1 大多数项目的表结构都是从“能跑就行”开始烂掉的我参与过的项目里只要没有明确的表结构规范三个月之后必然出现这些问题同一类业务数据在不同的表里用了不同的字段名和类型核心表膨胀到几千万行之后想加一个索引都要战战兢兢数据字典几乎没有新同事接手时只能对着代码反推字段含义。根子在于很多人把建表当成“把需求里的数据装进去”这么简单。字段名随意起类型凭感觉选注释想起来才写索引用到再加。前期的随意会在后续每一次需求变更里变成加倍的成本。比如当初把手机号字段设为VARCHAR(11)后来发现要存区号、分机号或者遇到加国家码的需求VARCHAR(11)直接不够用只能动表结构。表小还好表大了就是一次低峰期加班。所以第一篇我先把建表这件事讲透。它不只是CREATE TABLE这条语句而是一套影响后续所有变更的决策。1.2 一次失败的DDL比一次慢查询的代价高得多慢查询影响的是某一次请求的体验而一次失败的DDL很可能把整张表锁住导致线上业务大面积报错。MySQL的DDL不像程序员想像的那么“瞬发”一条简单的ALTER TABLE可能因为表数据量大而执行几十分钟如果这期间又赶上业务高峰后果不堪设想。更隐蔽的是元数据锁后面我会专门讲。但对于大部分场景DDL本身是可控的。前提是理解每一步操作背后会发生什么提前评估影响而不是写完SQL就敢往生产库上扔。这篇博客的主线其实就是帮助你把“扔SQL”变成“做变更”。2. 建表不是敲几个字段那么简单从引擎、字符集到默认值2.1 引擎、字符集、排序规则开局就要定对建表第一步不是写字段而是确定数据库层面的几个全局参数。存储引擎现在没什么好纠结的直接选InnoDB支持事务、支持行级锁、崩溃恢复能力强。MyISAM这种老引擎在只读场景下也许有性能优势但并发写入一上来就露馅我建议忘掉它。字符集方面从兼容性和扩展性角度无脑选utf8mb4。注意不要用utf8mb3或旧写法里的utf8它们在MySQL里其实是三字节编码存不了emoji遇到生僻字也会报错。utf8mb4才是真正的四字节UTF-8。排序规则在MySQL 8.0里默认是utf8mb4_0900_ai_ci在5.7里默认是utf8mb4_general_ci差别主要体现在校验和排序的规则上日常业务选默认值即可。除非你明确知道要做区分大小写的查询否则_ai_ci不区分重音、不区分大小写符合大多数业务直觉。我见过一个项目部分表是utf8mb4部分表是latin1联表查询时字段无法走索引、结果乱码查了半天才发现是字符集不一致。所以建库、建表、连接串三个层面的字符集必须统一这条规矩要刻在脑门上。2.2 字段类型与长度选错类型后续改到想哭字段类型选型的核心原则就一句话用能存下数据的最小类型但预留合理的扩展空间。整数类型从TINYINT1字节到BIGINT8字节能覆盖的范围完全不同。比如用户等级用TINYINT就够了订单金额的整数部分用INT可能不够达到20亿以上就得考虑BIGINT。类型字节数有符号范围常见用途TINYINT1-128 ~ 127状态、类型、等级SMALLINT2-32768 ~ 32767小范围计数INT4约±21亿常规主键、普通计数BIGINT8极大范围海量数据主键、流水号DECIMAL(M,D)变长精确小数金额、单价VARCHAR(n)字符数1/2最长65535字节名字、地址、备注DATETIME81000-9999年时间存储浮点数FLOAT和DOUBLE有精度问题金额类数据坚决不用要用DECIMAL。存手机号、身份证这类“看起来像数字”的字段也别用整数类型因为头部可能带0、可能包含号直接VARCHAR(20)起步最稳。字符串类型上很多新手纠结VARCHAR和TEXT。规则是能确定最大长度的用VARCHAR比如手机号、订单号长度不确定且内容可能很大的比如文章正文、JSON配置才考虑TEXT。VARCHAR在排序和索引上比TEXT友好得多TEXT字段不能有默认值使用上限制也多。时间字段DATETIME和TIMESTAMP都能用TIMESTAMP范围只有1970到2038年而且受时区影响DATETIME范围大、语义直观现在多数团队倾向于全部用DATETIME。2.3 默认值、非空约束与自增主键字段是否允许NULL决定了很多隐性行为。我的建议是业务字段尽量NOT NULL并给出合理的默认值。NULL在查询时要用IS NULL判断聚合函数会跳过NULL索引对NULL的处理也更麻烦还容易在应用层触发空指针。与其让“未填写”成为一种诡异的NULL状态不如显式用空字符串、0或者状态位来表达。时间字段的默认值MySQL 5.6.5之后DATETIME也支持DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP了。创建时间就写create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间,这样插入时自动填当前时间更新时自动刷新省掉应用层一堆重复代码。主键方面单机常规业务用BIGINT UNSIGNED AUTO_INCREMENT最省心。分库分表场景才需要仔细设计分布式ID方案但那是另一个话题。UNSIGNED这个修饰词很容易被漏掉它能让整数容量翻倍主键这种只增不减的字段加上它不会吃亏。2.4 一张规范建表的完整示例把上面这些合并起来一张用户表的建表SQL应该长这样CREATE TABLE user_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 用户ID, user_name VARCHAR(64) NOT NULL DEFAULT COMMENT 用户名, user_phone VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号, user_level TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 用户等级0普通 1白银 2黄金, amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 账户余额, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常 0禁用, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), KEY idx_user_phone (user_phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT用户信息表;注意几点表名和字段名统一用小写下划线风格避免大小写混用导致跨平台问题每条字段后面都跟了COMMENT建表时顺手敲一行注释的成本几乎为零但后期补注释的成本高得惊人索引命名用idx_前缀表注释用COMMENT用户信息表收尾。这就是一个我眼中及格线以上的建表语句。3. 字段新增与变更高频场景与锁表真相3.1 先搞清楚ALTER TABLE背后的三种算法很多人对ALTER TABLE的理解是“改一下表定义”但MySQL内部执行这条语句时可能在做完全不同的事情。核心要理解三种算法INSTANT只修改数据字典不触碰数据文件秒级完成。8.0.12之后在表末尾新增字段走的就是这条路。INPLACE在原有表空间上重建表期间允许并发的DML操作但依然需要额外的IO和空间。COPY把整张表拷贝到新文件期间对写入的限制很大表越大耗时越长。操作场景常见算法生产环境建议在末尾新增字段8.0可INSTANT直接加在末尾位置别贪心在中间新增字段通常INPLACE或COPY尽量避免表大会重排数据修改字段类型/长度COPY大表要极其谨慎评估耗时重命名字段INPLACE先查代码依赖修改默认值8.0可INSTANT相对安全修改字段注释视版本而定低峰期执行删除字段8.0部分版本可INSTANT先停应用代码引用还有一种没进表格但比算法更常见的坑叫元数据锁。任何一条ALTER在执行前都要获取表的元数据锁哪怕它是INSTANT也一样。如果表上有一个长事务在跑ALTER会卡在Waiting for table metadata lock状态表现就是“SQL发出去一直转圈没结果”后面所有针对这张表的查询都会被堵住。这个问题我后面在第六章单独讲。3.2 七个日常高频修改场景的SQL模板新增字段是最常见的需求。如果表很小怎么写都行如果表很大请把新字段加在末尾利用8.0的INSTANT能力避免数据重排ALTER TABLE user_info ADD COLUMN nick_name VARCHAR(64) NOT NULL DEFAULT COMMENT 昵称 AFTER user_name;AFTER可以指定新字段的位置。但我的建议是业务上不要依赖字段顺序查询时永远显式写列名新字段放哪个位置对代码没有任何影响所以大表就放最后。修改字段类型最常见的是“长度不够了”比如手机号从VARCHAR(20)扩到VARCHAR(30)ALTER TABLE user_info MODIFY COLUMN user_phone VARCHAR(30) NOT NULL DEFAULT COMMENT 手机号支持新版区号;MODIFY COLUMN必须把整列定义重新写一遍包括注释。很多人只写了类型没带注释结果一改原来的注释丢了这是特别常见的隐形事故。字段重命名用CHANGE它比MODIFY多一个“新字段名”参数ALTER TABLE user_info CHANGE COLUMN user_phone phone VARCHAR(20) NOT NULL DEFAULT COMMENT 手机号;删除字段直接用DROP COLUMN执行前务必确认没有应用代码、报表、存储过程在引用这个字段。我遇到过删了字段之后某个统计任务凌晨跑崩的情况当时这个字段只在一个很偏门的分支逻辑里被用到代码仓库里怎么搜都没搜到最后查日志才发现。删字段的教训就一条先搜全链路停掉所有引用再动手。修改默认值有专门的简写语法不必把整列重写一遍ALTER TABLE user_info ALTER COLUMN user_level SET DEFAULT 2;修改注释则还是要走MODIFYALTER TABLE user_info MODIFY COLUMN user_level TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 用户等级1普通 2白银 3黄金;重命名表用RENAME TABLERENAME TABLE user_info TO app_user_info;这条要特别注意改名之后所有视图、存储过程、外键、定时任务里的表名引用全部都要同步更新。我们项目里发生过改名后第二天才发现某个报表还在查旧表名的情况。3.3 大表改字段为什么慢怎么办表数据量超过千万行之后任何ALTER TABLE都不能拍脑袋执行。先说为什么会慢如果是COPY算法MySQL要把原有数据全部搬到新表期间还要校验约束、重建索引一个小时都算正常就算走INPLACE也需要额外的磁盘空间和持续IO。通用做法是先看表容量和行数评估风险等级。在从库或者备份环境上先执行一遍记录耗时。选业务最低峰期执行设置会话级lock_wait_timeout避免无限等锁。大表字段变更尽量用业界成熟的在线变更工具思路来做通过触发器或解析binlog的方式在原表对外提供服务的同时在后台一点一点把数据同步到新表结构全部完成后在秒级切换表名。小表反而容易让人放松警惕结果大表小心翼翼小表随手一改把正在跑的导入任务堵住了。无论表大小DDL前看一眼SHOW PROCESSLIST都不亏。4. 注释别只写在需求文档里表注释、字段注释与自动数据字典4.1 注释是给未来同事看的也是给工具看的很多团队把表结构说明写在项目的Word文档或Wiki里代码里的建表SQL反而干干净净。文档一旦没人维护半年后就全是过期信息。我的习惯是把注释直接写进数据库元数据因为information_schema里存着它们随时能查随时能导出不需要有人额外维护一份文档。MySQL的COMMENT关键字和SQL脚本里的--、#注释完全是两回事。--注释只存在于脚本文件里建完表就消失了COMMENT才会写进information_schema.COLUMNS和TABLES表。4.2 添加和更新注释的正确姿势建表时直接在字段定义后面跟COMMENT xxx表定义最后加COMMENT表说明这是最省事的方式。老表补注释也没有捷径就是用MODIFY COLUMN把整列定义重写一遍同时把注释带上ALTER TABLE user_info MODIFY COLUMN user_name VARCHAR(64) NOT NULL DEFAULT COMMENT 用户名用户注册时填写;表注释单独改ALTER TABLE user_info COMMENT 应用用户信息表包含认证与等级信息;注释内容也要有规矩。我见过COMMENT 名字这种等于没写的注释也见过把开发者的心情写进注释的。好的字段注释应该包含“字段是什么 枚举值含义 特殊规则”。比如状态字段写“状态1正常 0禁用”比写“状态标记”强十倍。4.3 用information_schema一键生成数据字典手工整理数据字典是反人性的直接查系统表自动生成才是正道。下面这条SQL可以把整个库的表名、表注释、字段名、类型、默认值、字段注释全部列出来SELECT c.TABLE_NAME AS 表名, t.TABLE_COMMENT AS 表注释, c.ORDINAL_POSITION AS 序号, c.COLUMN_NAME AS 字段名, c.COLUMN_TYPE AS 类型, c.IS_NULLABLE AS 是否可空, c.COLUMN_DEFAULT AS 默认值, c.COLUMN_COMMENT AS 字段注释 FROM information_schema.COLUMNS c LEFT JOIN information_schema.TABLES t ON c.TABLE_SCHEMA t.TABLE_SCHEMA AND c.TABLE_NAME t.TABLE_NAME WHERE c.TABLE_SCHEMA your_db ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION;把your_db换成实际库名在Navicat或MySQL命令行里执行后导出就是一份基本可用的数据字典。很多团队的“数据字典文档”其实就是定期跑这条SQL生成的维护成本几乎为零。4.4 定期体检找出注释缺失的表注释补没补全不能靠人肉检查。写一条“找没有注释的字段”的SQL纳入每周巡检脚本SELECT t.TABLE_NAME, t.TABLE_COMMENT, COUNT(c.COLUMN_NAME) AS total_cols, SUM(CASE WHEN c.COLUMN_COMMENT IS NULL OR c.COLUMN_COMMENT THEN 1 ELSE 0 END) AS missing_comment_cols FROM information_schema.TABLES t JOIN information_schema.COLUMNS c ON c.TABLE_SCHEMA t.TABLE_SCHEMA AND c.TABLE_NAME t.TABLE_NAME WHERE t.TABLE_SCHEMA your_db GROUP BY t.TABLE_NAME, t.TABLE_COMMENT HAVING missing_comment_cols 0 ORDER BY missing_comment_cols DESC;只要这条SQL能查出结果就说明有表需要补注释。把它挂在定时任务里每次大版本上线前跑一遍注释覆盖率缺失的表一目了然。这事看起来琐碎但对团队协作效率的提升是实打实的。5. 存储过程什么时候值得写怎么写才不埋雷5.1 别滥用但该用的时候确实好用存储过程在不少团队里名声不太好调试麻烦、版本管理困难、容易写出难以维护的巨型逻辑。这话对一半。把它们当万能工具用当然灾难但有几个场景它比应用层代码合适得多。第一类是批量数据处理比如按月归档历史数据、给全量用户重算积分、清理过期日志。这些逻辑放在应用层跑要么写一堆定时任务脚本要么直连数据库跑几千行SQL都不如在数据库里用一个存储过程闭环来得干净。第二类是强事务的多次写入操作比如“生成订单同时扣库存同时写流水”用存储过程包裹事务能保证要么全成功要么全回滚减少应用层分布式事务的复杂度。第三类是多个系统共用同一套数据处理逻辑时把逻辑收敛在数据库里避免每个语言各写一遍、规则还不一致。5.2 基本语法骨架与三个容易忽略的细节存储过程的骨架并不复杂但有几个地方特别容易踩坑。先看一个最简单的结构DELIMITER $$ CREATE PROCEDURE sp_demo(IN p_id BIGINT, OUT p_name VARCHAR(64)) BEGIN SELECT user_name INTO p_name FROM user_info WHERE id p_id; END$$ DELIMITER ;DELIMITER $$的作用是告诉MySQL客户端别把;当成整个存储过程的结束符否则一执行就报错。写完过程之后要记得把分隔符改回;很多人忘记这一步导致后续SQL全部执行异常。参数有三种类型IN是传入参数OUT是输出参数INOUT既能传又能出。日常90%的场景用IN就够了。内部变量用DECLARE声明注意变量名不要和字段名同名否则在SELECT INTO时会产生歧义。另外存储过程里不能直接使用RETURN返回值这是它跟函数最大的区别要返回值就靠OUT参数或者结果集。5.3 带事务和异常处理的归档存储过程我写一个生产里真正用得上的例子把订单表里90天前的数据分批搬运到历史表。这个例子涵盖了WHILE循环、事务、ROW_COUNT()判断和每批停顿基本是归档类存储过程的标准模板。DELIMITER $$ CREATE PROCEDURE sp_archive_orders(IN p_days INT, IN p_batch_size INT) BEGIN DECLARE v_threshold DATETIME DEFAULT DATE_SUB(NOW(), INTERVAL p_days DAY); DECLARE v_affected INT DEFAULT 1; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; WHILE v_affected 0 DO START TRANSACTION; INSERT INTO order_info_history (order_id, user_id, amount, status, create_time) SELECT order_id, user_id, amount, status, create_time FROM order_info WHERE create_time v_threshold LIMIT p_batch_size; DELETE oi FROM order_info oi JOIN ( SELECT order_id FROM order_info WHERE create_time v_threshold LIMIT p_batch_size ) tmp ON tmp.order_id oi.order_id; SET v_affected ROW_COUNT(); COMMIT; DO SLEEP(1); END WHILE; END$$ DELIMITER ;几个设计点说明一下LIMIT分批防止一次性锁太多行每批一个事务这批失败最多回滚一批不会影响历史已归档数据每一批之间SLEEP(1)给主从复制和磁盘IO留出喘息时间SQLEXCEPTION处理器负责回滚和重新抛出异常避免静默失败。注意一个问题这个过程的幂等性依赖归档表没有重复主键。如果某批事务提交成功但网络断开客户端没收到结果重跑时就会因为主键冲突报错。生产上要么在归档表上用INSERT IGNORE但要小心它会吞掉其他类型错误要么在归档前先删除目标批次的数据。我倾向用后者语义更干净。调用方式CALL sp_archive_orders(90, 1000);意思是归档90天前的数据每批处理1000条。5.4 调试、权限与维护经验存储过程的调试确实比应用层代码难受一些。我的经验是不要指望一步到位先写一个只读不写的版本把中间结果SELECT出来人工核对或者建一张临时日志表在过程中不断INSERT日志跑完再查。还有一点SHOW WARNINGS在MySQL里是排查SQL警告的利器过程执行完顺手执行一下比干瞪眼猜问题强。权限上给存储过程单独授权不要因为过程里写了增删改就把整个表的DML权限都敞开。最小权限原则在数据库里同样适用GRANT EXECUTE ON PROCEDURE your_db.sp_archive_orders TO data_ops%;维护侧最容易被忽视的问题是版本管理。存储过程定义要像代码一样纳入版本控制每次修改都留一个变更记录。我在项目里看到的反面教材是直接在数据库里改过程改了三版之后连自己都说不清线上跑的是哪一版。正规做法是把所有CREATE PROCEDURE存成.sql文件放进代码仓库数据库里的任何修改都同步回仓库。6. 生产环境执行DDL前的最后检查以及我踩过的坑6.1 上线前检查清单无论建表还是改字段我在生产环境执行之前的检查顺序是固定的先看表数据量级小于百万行基本可以放松千万级就要进入谨慎模式。看表上的长事务执行SELECT * FROM information_schema.INNODB_TRX;确认没有长时间未提交的事务。看当前连接数SHOW PROCESSLIST;注意有没有大批连接卡在等待。评估算法能用INSTANT的绝不用COPY这是大原则。先在从库或测试环境演练一遍记下真实耗时。准备好回滚SQL能不用但不能没有。最后一条很多人忽略。比如给表加字段出问题的回滚SQL就是ALTER TABLE xxx DROP COLUMN yyy这个SQL要提前准备好而不是等事故发生了现查。6.2 元数据锁等待与长事务最隐蔽的生产事故这是DDL事故里最阴险的一种。表现是ALTER发出去一直卡在Waiting for table metadata lock然后后面所有对该表的读写都在排队应用层超时DBA还没反应过来线上已经炸了。为什么会这样ALTER要拿元数据锁而一个几百毫秒的普通查询如果因为某种原因一直持锁不释放ALTER就只能等。最常见的原因是应用层的一个事务开了没提交、没回滚默默挂在那儿。ORM框架里自动开启事务的代码尤其容易埋这种雷。规避手段有两个一是执行DDL前查一下INNODB_TRX和PROCESSLIST二是给会话设置锁等待超时别傻等SET SESSION lock_wait_timeout 5; ALTER TABLE user_info ADD COLUMN nick_name VARCHAR(64) NOT NULL DEFAULT COMMENT 昵称;一旦超时MySQL会报ER_LOCK_WAIT_TIMEOUT错误这时至少你知道是锁的问题而不是SQL本身卡死。6.3 变更失败的回滚策略DDL不像普通的DML可以靠事务来回滚。执行到一半失败时可能表结构已经部分变更数据可能已经重建怎么处理全看变更前做了什么准备。我能给的最实用建议就是任何大表变更前确认备份是可恢复的。MySQL的备份通常用逻辑备份或物理备份变更前手动触发一次最新的备份至少保存一份变更前的表结构定义SHOW CREATE TABLE user_info;把结果存成.sql文件。这个文件就是结构回滚的最底牌万一变更乱套了至少能根据当时的建表语句重建现场。6.4 我自己的实际操作习惯最后分享几个只有动手多了才会形成的习惯。第一个习惯所有DDL脚本都写在代码仓库里跟版本发布一起走而不是直接在客户端敲。这样表结构变更记录天然可追溯同事也能在代码评审阶段看到这周要动哪些表。第二个习惯改字段之前先跑一遍所有引用该表的查询至少在代码仓库里搜一遍字段名。排查漏网之鱼比修复线上事故便宜太多了。第三个习惯执行完DDL后立刻用SHOW CREATE TABLE确认最终结构和预期一致。很多人改了字段注释没带上、默认值丢了等发现时已经又过了几个迭代。经验就是这么攒出来的。建表时的多一行注释、变更前的一次排查、归档时的一个事务都不是什么高深技巧但在关键时刻能帮你避开绝大多数数据库事故。