刚接手一个老项目的时候我最先干的事不是看业务代码而是登录MySQL把information_schema里的表翻了一遍。数据库里的表结构基本就是业务的骨架骨架歪了后面再怎么写都是将就。这些年建库建表、改表结构、迁数据踩了不少坑也总结了一些自己的套路这篇就把MySQL里数据库和表操作这块讲透从语法细节到避坑经验一次说清楚。这篇文章适合刚入门MySQL、正在准备面试、或者工作中要经常跟库表打交道的同学。我会把每个操作的“为什么”也一并讲掉而不只是告诉你命令怎么写。毕竟命令查文档就有但背后的设计取舍和实操陷阱才是真正值钱的部分。1. 建库建表前的全局视角1.1 字符集与排序规则别再凭感觉选了建库第一步不是敲CREATE DATABASE而是先想清楚字符集和排序规则。很多老库出现乱码、排序结果不对、索引长度超限追溯到底都是字符集没选好。MySQL里字符集character set控制的是存什么字符排序规则collation控制的是怎么比较和排序。这俩是绑在一起配置的。现在MySQL 8.0的默认字符集是utf8mb4但很多从5.7甚至更早版本迁移过来的库用的还是utf8或者更老的latin1。重点说下utf8mb4。它是真正的完整UTF-8支持emoji和四字节生僻字。utf8mb3通常直接写成utf8最多支持三字节遇到emoji就会报错或者存成问号。我之前有个客户一个用户昵称带了个emoji注册接口直接抛异常查了半天就是字段用了utf8。所以新库新表无脑选utf8mb4就行唯一要确认的就是排序规则。排序规则有两种常见选择utf8mb4_general_ci老默认和utf8mb4_unicode_ci。前者比较逻辑简单、速度略快后者基于Unicode标准、对多语言排序更准确。这个差别在中文场景下基本感知不到但我在实际项目中统一用utf8mb4_unicode_ci理由只有一个符合标准将来不别扭。另外一个容易被忽略的点是某些排序规则对大小写不敏感ci就是case-insensitive如果你的业务要求区分大小写就得选bin结尾的排序规则比如utf8mb4_bin。需要特别注意的是字符集有四级继承机制服务器级、数据库级、表级、列级。你改了数据库的字符集已存在的表不会跟着变。最坑的就是这种改了但没生效的情况。我的建议是在建库的时候就显式指定建表时也显式指定不要依赖继承。-- 建库时指定 CREATE DATABASE myapp DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;1.2 存储引擎别吵了默认InnoDB就是标准答案存储引擎之争大概十年前还挺热闹MyISAM靠着查询快、支持全文本索引在小网站时代有过一段辉煌。但现在如果你的表还在用MyISAM我会认真建议你考虑迁移。原因不复杂MyISAM不支持事务、不支持外键、崩溃恢复能力差最关键的是它只有表级锁写操作一多整个表都会被锁住。InnoDB提供行级锁、支持事务ACID、支持崩溃恢复还有MVCC并发控制这些在现代业务里都是刚需。MySQL 5.5.5之后InnoDB已经是默认存储引擎8.0更是把数据字典也统一了。如果人问我什么场景选MyISAM我会说只读的历史数据归档表、对完整性要求不高的分析场景可以考虑。但绝大多数业务表InnoDB就是标准答案不需要纠结。建表时可以直接指定引擎CREATE TABLE t_demo (...) ENGINEInnoDB;顺便说一句查看一个库下面每张表的引擎类型和行数可以这样SELECT table_name, engine, table_rows, table_collation FROM information_schema.tables WHERE table_schema myapp;这个查询我在排查问题的时候用得特别频繁。哪张表锁了、哪张表是MyISAM的漏网之鱼、表行数预估准不准一眼就能看出来。1.3 命名规范一个约定能省掉三成沟通成本命名规范这东西不写没人管你写了真的能少吵架。我见过一张表叫userinfo另一张叫order_detail_2020还有叫T_Log的混着大小写和下划线查的时候还得猜时间久了根本记不住。这里分享一套我一直在用的规范参考价值大于标准价值你可以按团队习惯调整库名、表名、字段名统一小写单词用下划线分割例如user_contact。表名用业务模块前缀比如用户模块user_、订单模块order_、日志模块log_。避免使用MySQL保留字。order、group、desc、key这些词是保留字如果非要写得用反引号包起来但我不建议这么干看着难受不说后续维护还容易踩坑。我见过有人建了张order表每次SQL里都得写order引号一漏就报语法错误。字段名用名词不用动词表示状态加status表示时间加time或者created_at/updated_at。索引名有规律主键PRIMARY唯一索引uk_字段名普通索引idx_字段名联合索引把字段名都拼上比如idx_user_status。这套规范最大的价值是你看到一张陌生表通过表名能猜到它属于哪个模块通过索引名能猜到它的查询场景。数据库这种东西是团队协作资产不是某个人的私货命名清晰比炫技重要得多。2. 库操作的完整拆解2.1 创建数据库的完整语法与参数细节CREATE DATABASE的完整语法看起来很简单实际用起来有几个关键参数值得展开讲CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET 字符集] [COLLATE 排序规则];IF NOT EXISTS这个修饰符在我看来是业务脚本里必加的选项。举个例子你写一个自动化部署脚本每次发布环境都要创建数据库如果不加这个判断数据库一存在脚本就报错部署直接中断。加了之后重复执行脚本就是幂等的稳得很。但注意这玩意儿不校验字符集和排序规则如果数据库已经存在但字符集不对它不会帮你修正。创建数据库之后我建议立刻确认一下实际的字符集用SHOW CREATE DATABASE命令SHOW CREATE DATABASE myapp;你可能会发现有些库显示的字符集和你预期的不一样。比如我遇到过MySQL实例级的character_set_server配置是latin1建库时没指定字符集库就继承了latin1后面表也全是latin1代码里还得做转码。这种问题一查一个准。2.2 查看和修改数据库信息从哪里来怎么改不掉坑查看数据库列表用的是SHOW DATABASES但很多人在这一步就停了。你还可以查得更细-- 查看当前连接所在库 SELECT DATABASE(); -- 查看库的创建语句、字符集、排序规则 SHOW CREATE DATABASE myapp; -- 查看库下所有表的信息 USE myapp; SHOW TABLES;修改库的字符集和排序规则是一个看起来无害、实际暗藏风险的操作ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这个命令只会修改数据库级别的默认值对库内已经存在的表不会产生任何影响别指望一句ALTER DATABASE就把所有表字符集改了。如果确实要批量改表得单独对每张表执行ALTER TABLE。另一个坑是如果某张表已经有一列用了varchar(255)而charset从utf8变成utf8mb4之后该列的最大字节数从765变成1020在旧版MySQL的索引长度限制767字节下这列作为索引前缀就会报错。这个我在5.7和8.0的环境里都遇到过utf8mb4的字段做索引时长度要仔细算尤其联合索引一个字符最多占4字节自己心里要有数。2.3 删除数据库先想三秒再说DROP DATABASE的语法没什么可讲的DROP DATABASE db_name;一行就完了但它也是最危险的操作之一。MySQL不会像某些图形化工具那样弹一个你确定吗的确认框你敲回车数据立刻没而且不是进回收站那种没是物理层面的没。所以我的习惯是生产环境里从不直接用DROP DATABASE最多在本地开发环境用。如果非要删我会先做这些动作用mysqldump或者物理备份工具把库完整备份一份备份文件要验证能恢复才叫备份。确认没有其他服务还在连接这个库不然删完别人业务直接报错线上事故就从这里来。先执行SHOW TABLES确认这个库确实是你要删的那个而不是名字相近的另一个。我见过有人要删myapp_test结果手滑删了myapp的事一个字母的差距毁掉整个环境。# 备份整个库到压缩文件 mysqldump -u root -p --single-transaction --routines --events myapp | gzip myapp_$(date %F).sql.gz保险的做法还有一个在删库前用RENAME TABLE把表先挪到一个待删除的库里观察几天确实没问题再删。这招我管它叫软删除虽然SQL层面没有事务性的删除保护但至少给了自己一个后悔的机会。3. 表结构的精细设计3.1 数据类型选择的实战经验数据类型选错了后果往往要等数据量上来才暴露。我在实际项目里见过不少短期方便长期返工的案例这里把常见字段类型的选择逻辑梳理一遍。整数类型TINYINT、SMALLINT、INT、BIGINT按取值范围选但不能只看存得下就完事还要考虑存储空间和索引性能。比如年龄字段用TINYINT UNSIGNED就够了0~255用INT纯属浪费3个字节。状态字段status用TINYINT就是标配。浮点数和定点数FLOAT和DOUBLE是浮点型计算时有精度损失DECIMAL(p, s)是定点数专门用于精确计算。涉及金额、税率这种一分钱都不能差的字段一定用DECIMAL。比如价格DECIMAL(10, 2)可以表示最大99999999.99。有些人图省事用DOUBLE存金额等到做对账的时候发现差了几分钱数据怎么都核对不上我已经见过太多次了。字符类型CHAR和VARCHAR的核心差别是CHAR是定长、VARCHAR是变长。CHAR适合长度固定的场景比如MD5、手机号固定11位但要注意国际区号。VARCHAR需要指定长度比如VARCHAR(64)这个长度指的是字符数而不是字节数在utf8mb4下最多能存64个字符最多256字节。TEXT类型适合大文本但它有坑不能有默认值而且会在内存中产生临时表影响性能。能不用就不用。日期时间类型DATETIME和TIMESTAMP是最常用的。DATETIME范围是1000年到9999年TIMESTAMP只能到2038年32位限制但实际上8.0的TIMESTAMP已经是64位范围到2106年了。两者的一个实用差异是TIMESTAMP会受时区设置影响DATETIME不会。如果你的系统面向多时区用户这个要仔细考虑。我一般两种都用过最终选择是内部系统用DATETIME跨时区的全球化业务用TIMESTAMP配合把连接时区设为UTC。还有个容易忽略的选择是JSON类型。MySQL 8.0里JSON是原生支持的类型可以做语法校验、用JSON函数查询。但我的建议是JSON适合存结构性不固定的配置类数据不适合做强查询字段。如果你需要根据JSON里的某个值做WHERE筛选还是应该拆成独立字段。3.2 约束与索引表结构里看不见的骨干表结构设计里索引是核心中的核心。但先说约束因为约束决定数据的正确性。主键约束一张表必须有主键这是底线。我从不用业务字段当主键一律用自增BIGINT UNSIGNED或者应用层生成的雪花ID。原因很简单业务字段当主键一旦业务规则调整主键就要变牵一发动全身。自增主键还有个好处是InnoDB的聚簇索引组织有序插入性能好。唯一约束业务上要求不能重复的字段比如用户编号加唯一索引。加了之后不仅查询快数据库层面还能兜住重复数据。我最怕的是代码里判断过不会重复这种说法并发场景下没有唯一约束脏数据迟早会来。非空和默认值字段尽量不要允许NULL。NULL在MySQL里很尴尬COUNT会忽略它WHERE判断要用IS NULL索引处理NULL也比NOT NULL更麻烦。业务没有特殊要求字段都设NOT NULL给一个合理的默认值。比如状态字段默认1时间字段默认CURRENT_TIMESTAMP。索引这块很多人只知道加了索引查询快但不知道索引也有代价写操作会变慢、磁盘占用变多、维护成本变高。所以索引要按需建不要每个字段都建索引。哪些字段值得加索引高频出现在WHERE、JOIN、ORDER BY里的字段。比如用户表的phone字段登录要按手机号查那phone必加索引。联合索引要注意最左前缀原则(a, b, c)联合索引可以命中a、ab、abc三种查询但你只按b或者c查的时候索引用不上。这就是为什么很多慢查询排查后发现索引建了但SQL没走索引大概率就是联合索引顺序搞反了。还有一种技巧叫覆盖索引关联到热搜里的辅助索引如何避免回表。InnoDB的辅助索引非聚簇索引叶子节点存的是主键值查询时如果索引里包含了你需要的所有字段就不用再去主键索引回表查一次。举个例子-- 假设有索引 idx_status(status) SELECT id, status FROM user_contact WHERE status 1;id是主键InnoDB的辅助索引里天然带有主键值这个查询就能直接在辅助索引上完成不回表。但如果你查username这个字段不在辅助索引里就需要回表。联合索引(status, username)就能覆盖住这个查询。这种设计对高频查询的性能提升是肉眼可见的。3.3 三范式与反范式不做教条的奴隶大学数据库课必讲三范式但工作中你会发现完全遵循第三范式的表结构做出来根本没法用。范式是帮你思考的工具不是必须遵守的法律。三范式的核心思想主要有三点第一范式要求字段原子性也就是每个字段只存一个值第二范式要求消除部分依赖也就是非主键字段必须完全依赖于主键第三范式要求消除传递依赖也就是非主键字段不能依赖于其他非主键字段。在订单表设计里如果把客户姓名直接冗余在订单表里这在第三范式看来是违法的因为姓名应该通过客户ID关联到客户表。但这种冗余在实际业务中很常见因为订单展示时几乎总需要显示客户姓名每次都要JOIN客户表查询性能就会受影响。我的经验是强一致性的核心业务数据比如资金流水、订单主表严格遵循范式读多写少、展示型的字段比如用户名冗余到订单表、商品名称冗余到订单详情表大胆反范式。所谓设计平衡就是你别一上来就无脑地全冗余也别一提反范式就觉得它不正规。关键是你要知道每条冗余数据需要在哪里维护它的一致性想清楚了就不怕。4. 表操作的实操指南4.1 建表语句的完整示例与逐行讲解理论讲了一堆这里给一个完整建表语句我把它当作模板来用你直接抄作业然后按需调整CREATE TABLE IF NOT EXISTS user_contact ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, user_no VARCHAR(32) NOT NULL COMMENT 用户编号业务唯一标识, username VARCHAR(64) NOT NULL COMMENT 用户名, phone VARCHAR(20) NOT NULL COMMENT 手机号, email VARCHAR(128) DEFAULT NULL COMMENT 邮箱允许为空, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1-正常0-禁用2-锁定, login_count INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 累计登录次数, last_login_time DATETIME DEFAULT NULL COMMENT 最近登录时间, 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_user_no (user_no), UNIQUE KEY uk_phone (phone), KEY idx_username (username), KEY idx_status_login_time (status, last_login_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户联系方式表;逐行讲几个关键点id列用BIGINT UNSIGNED NOT NULL AUTO_INCREMENT。为什么用UNSIGNED把表容量上限翻一倍。INT UNSIGNED最大到42亿多BIGINT基本可以认为是无穷大。user_no用VARCHAR(32)。这里有个细节我见过有人把user_no设计成INT自增结果用户量一大、系统一拆分全局唯一性保证不了最后也只能改成字符串。phone用VARCHAR(20)坚决不用BIGINT。因为手机号可能要处理86前缀还可能保留前导0用数值类型存电话号码是非常典型的错误设计。created_at和updated_at这两个字段几乎每张表都会有。DEFAULT CURRENT_TIMESTAMP是建行时自动带当前时间ON UPDATE CURRENT_TIMESTAMP是行更新时自动更新时间戳。这个特性实在太方便省去了代码里手动维护时间的操作。联合索引idx_status_login_time (status, last_login_time)对应的是查某个状态下的用户按最近登录时间排序这个高频业务场景。在设计索引时你要先列出业务的重点查询模式再决定建哪些索引而不是建完表再去补。4.2 修改表结构ALTER TABLE的完整实战生产环境里表结构不是一次建好就永远不变的业务迭代必然要加字段、改字段、删字段。ALTER TABLE看着简单坑主要集中在大表操作上。先看几个最常用的修改操作-- 新增字段 ALTER TABLE user_contact ADD COLUMN nickname VARCHAR(64) NOT NULL DEFAULT COMMENT 昵称 AFTER username; -- 修改字段类型和属性 ALTER TABLE user_contact MODIFY COLUMN phone VARCHAR(32) NOT NULL COMMENT 联系电话; -- 重命名字段 ALTER TABLE user_contact CHANGE COLUMN login_count login_num INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 登录次数; -- 删除字段 ALTER TABLE user_contact DROP COLUMN email; -- 添加索引 ALTER TABLE user_contact ADD KEY idx_email (email); -- 删除索引 ALTER TABLE user_contact DROP INDEX idx_email; -- 修改表名 ALTER TABLE user_contact RENAME TO user_contact_info;这几个命令的细节ADD COLUMN ... AFTER username指定新列加在哪列后面。如果不指定默认加在表的最后。很多人不知道MySQL可以指定位置但如果你在列顺序有要求的场景下这可以让表结构更符合阅读习惯。MODIFY COLUMN会把列的所有属性全部重写一遍如果原列有COMMENT修改时忘了带原注释会丢。我踩过这个坑只是改个默认值结果注释没了。所以MODIFY COLUMN一定要写完整属性。CHANGE COLUMN后面先写旧列名再写新列名是重命名改动属性的组合操作但如果你只想改名属性也得完整带上否则同样会丢注释。生产环境执行ALTER TABLE前务必在测试环境先跑一遍。尤其大表一个ALTER下去是全表重建old版MySQL或者在线DDL8.0对很多操作已优化但依然会对IO产生压力严重时把主库拖垮。千万级别的表加索引我在夜间低峰期执行都要盯进度。如果你的表已经到千万行甚至几千万行做DDL就要考虑专业工具了。pt-online-schema-changePercona Toolkit和github/gh-ost是业界用得最多的在线表结构变更方案原理大致是先创建一个目标结构的新表然后通过触发器或者模拟binlog的方式把旧表的增量变更同步到新表最后切换主备表名。这样业务几乎无感知。这种方案我建议你在表非常大、又不能停业务的场景下引入而不是等出了问题再研究。4.3 大表操作与迁移的注意事项热搜词里出现了几千万行大表正好多说两句。表数据量上来以后你平时觉得没什么的操作都会变得异常痛苦最常见的三个场景加索引、改字段、迁移数据。加索引这事儿极端考验时机。在小表上加索引毫秒级别搞定千万行上建索引可能要跑好几分钟甚至更久。这不只是时间长的问题InnoDB在构建索引期间会占用大量IO和内存很可能影响线上读写。所以我的做法是在业务低峰期执行或者用pt-online-schema-change工具在线执行避免对业务造成明显影响。改字段同理。很多人一上线就收到修改字段类型的需求结果ALTER了半个小时都没结束连接超时、复制延迟、主从不一致全部冒出来。有个经验是在MySQL 8.0里ALTER TABLE ... ALGORITHMINSTANT可以支持一些瞬时完成的操作比如增加一个在行尾的字段。但8.0的INSTANT也不是万能的不支持所有类型和位置。需要自己在实际操作前用EXPLAIN或者查看官方文档确认。大表迁移最典型的是加字段数据回填。比如给一个千万行的订单表加一个order_tag字段建完列之后要按业务规则把值刷上去。这种操作不能一条UPDATE直接干会锁表锁到怀疑人生。我的做法是先加字段允许为空然后分批UPDATE每批处理几千行批与批之间sleep几秒这样既不阻塞业务又能把数据在可控时间内补完。-- 分批回填示例每次处理5000条 UPDATE order_main SET order_tag high_value WHERE order_tag IS NULL AND amount 10000 LIMIT 5000;这种LIMIT配合条件循环的写法跑几次就能把数据刷完。注意LIMIT在UPDATE里不总是直观生效某些版本对UPDATE...LIMIT的行为有差异我一般用子查询找到主键ID区间再更新更可控。5. 常见问题与排查技巧实录5.1 高频问题速查表下面这个表是我在实际群里、论坛里、自己项目里收集的高频问题整理成速查形式每一条都是被真实踩过的坑问题现象根本原因解决方案中文乱码、emoji变成问号表字符集不是utf8mb4建表建库显式指定utf8mb4连接串追加characterEncodingutf8表名在Linux下面找不到表名大小写写错Linux下MySQL默认区分大小写设置lower_case_table_names1后重启COUNT(*)很慢大表非InnoDB或统计信息失真用EXPLAIN看执行计划考虑分区表或汇总表加了索引查询还慢SQL没走索引比如%关键词%、函数包裹字段改写SQL避免前置通配符避免对索引列做函数运算并发修改同一行导致死锁InnoDB加锁顺序不一致事务里对记录的访问顺序保持一致减少长事务删数据后磁盘空间没释放表碎片留下大量空闲页OPTIMIZE TABLE但大表会锁表要在低峰期做分页越翻越慢OFFSET太大扫描了大量无用行用WHERE id 上次最大ID LIMIT n代替深分页连接数打满应用没走连接池或泄漏连接检查连接池配置和异常分支的连接释放大ALTER导致复制延迟主库执行DDL期间产生大量binlog流量用pt-online-schema-change或gh-ost在线变更这里我想特别提一下第一条乱码问题。以前我遇到过一种很隐蔽的情况表是utf8mb4Java代码也是UTF-8但通过JDBC连接时连接串没加characterEncodingutf8于是驱动按默认编码老版本可能是ISO-8859-1去转换数据照样乱码。这种问题从建库到中间件到应用代码任何一环出问题都会导致乱码排查时要一级一级看。5.2 独家心法库表变更好习惯清单最后分享一下我这些年操作库表沉淀下来的一些肌肉记忆也是踩过几次坑之后的产物第一永远先备份再动手。这里的动手指的是DROP、大ALTER、批量UPDATE这类有风险的操作。哪怕是在灰度环境的库也先mysqldump一下因为你不确定这个库会不会被别人复用。备份成本很低几秒钟的事恢复的成本可能是几小时。第二测试环境先跑一遍不要直接上线操作。我见过团队在测试库执行ALTER很快到了生产库同样语句挂了原因是测试库数据量只有生产库的1%。所以要在测试库造一份接近生产量级的数据至少是千万行级别才能测出DDL真实耗时。第三执行计划先行。写完一条SQL不要急着跑先EXPLAIN看它走没走索引、扫描行数是多少。EXPLAIN输出里的type列从好到差大致是const、eq_ref、ref、range、index、ALL如果出现ALL全表扫描那这条SQL大概率要把慢查询拉满。第四观察监控。DDL、大批量UPDATE执行期间盯一下CPU、IO、锁等待三个指标。有个技巧是并发执行前用SHOW PROCESSLIST看看当前有没有长事务如果有先跟业务确认能不能等它结束不然你的DDL可能会被锁等待堵死。再说一个很容易被忽略但实际很实用的细节建表时给每张表加一句COMMENT给每个字段也加COMMENT。很多人不写注释过三个月回来查表一脸懵连字段代表什么意思都要反编译代码看。这个习惯一旦养成后面维护成本能低很多。还有一个心得是关于数据库和表的操作很多人觉得这是DBA的事应用开发不需要太关心。但我的体会是后端开发至少要把建表、加索引、改字段这些基本功练扎实因为等出了问题再找DBA沟通成本比你自己直接上手高好几倍。尤其现在很多公司DBA资源极度紧张开发同学能把表结构设计和DDL操作做好在整个交付链条上都会顺畅很多。最后再说一个小技巧也算是一个习惯任何新项目建库建表之前先花半小时把表结构和索引设计草图画出来过一遍最核心的三四个查询场景看看它们的WHERE和ORDER BY能不能被现有索引覆盖。这一步看起来简单但真的能规避掉不少上线之后才暴露的性能问题。等你把线上几千万行的表折腾过一遍就会明白前期设计多花的时间都是在给未来的自己省钱。