第一次带零基础学员写数据表时最常听到的一句话是建表不就是CREATE TABLE吗是也不全是。数据表才是MySQL里真正存放数据的地方。上一篇我们只是把数据库建出来等于准备了一间空仓库这一篇要做的是给仓库搭货架、划区域、定规则。我陪很多零基础的朋友走过这一步这篇打算把数据表的创建、查看、修改、删除聊透顺便把最容易踩坑的设计习惯也一起讲了。适合刚看完第一篇、能连上MySQL并执行简单语句的读者看完你至少能独立设计一张有主键、有约束、字段类型合理的表并且知道改动表结构时怎样最安全。说明本文所有例子都围绕一个虚构的school库和students表展开你可以把表名换成自己的业务名直接套用。1. 动手建表之前先想清楚这几件事1.1 字段类型选错后面全要返工很多人喜欢拿到需求就写CREATE TABLE然后在字段类型上凭感觉姓名就是VARCHAR年龄就是INT备注就TEXT。这样建出来的表能用但后患不小。字段类型不是随便选的它决定了三件事存储空间、可用的运算符、索引优化的空间。举个例子。年龄用INT没有问题但年龄不可能为负如果用了INT某天程序被误操作写入一个负数数据库并不会拦你。如果换成TINYINT UNSIGNED范围0到255既省了三个字节又天然挡住了非法负数这就是在类型层面建立约束的思路。类似的还有手机号别用BIGINT存。很多人直觉觉得手机号是数字就用INT或BIGINT但手机号前面可能带国际区号、可能有号而且你不需要对手机号做任何加减乘除这类字段应该用VARCHAR。金额更是重灾区FLOAT/DOUBLE在计算机里是近似存储算钱会出现0.1加0.2不等于0.3的情况业务上一分钱都不能差所以金额用DECIMAL(10,2)。日期字段也要讲究。存生日用DATE存创建时间用DATETIME或TIMESTAMP。有些朋友习惯把日期存成字符串比如2024-01-01 12:00:00这样确实能显示但你没法用DATE_ADD、DATEDIFF这些日期函数也没法让范围查询走索引排序还会按字典序排错。能用原生日期类型就别绕路。字符串长度这块我见过一种典型误区所有VARCHAR都写255。VARCHAR(255)在utf8mb4字符集下最坏情况占用255乘4字节也就是1020个字节更重要的是如果拿这个字段建索引会直接触及索引长度上限不是每个版本都让你建成功。长度够用就好姓名和邮箱写50到100足够VARCHAR(10)不一定就比VARCHAR(255)差因为它按实际内容长度存储平时只占真实空间。1.2 字符集和引擎没选对线上迟早背锅建表语句末尾常见的两段配置是ENGINEInnoDB DEFAULT CHARSETutf8mb4。这两行很多人是复制过来的没想过为什么。我聊下自己的理解。存储引擎方面MySQL 5.5以后默认就是InnoDB它支持事务、行级锁、外键约束和崩溃恢复。而MyISAM不支持事务表级锁意味着写操作会把整张表锁住并发一高就卡。除非你有非常明确的原因比如纯读的归档表、老式全文索引需求新业务一律InnoDB不用纠结。字符集方面utf8在MySQL里其实是个历史包袱它最多支持3字节存不了emoji和大部分生僻字utf8mb4才是完整的UTF-8支持4字节。很多聊天场景里用户昵称带个emoji如果表是utf8入库直接报Incorrect string value或者变成问号。所以统一用utf8mb4排序规则通常选utf8mb4_unicode_ci8.0版本也可以选utf8mb4_0900_ai_ci后者对中文排序更友好。字符集不只在建表语句里出现连接层也得一致这一点后面第6章会专门说。另外强烈建议把表注释和字段注释写全。很多人觉得COMMENT可写可不写但在一个维护了两三年的系统里注释才是后人最快的导航。某个字段是干什么的、状态值1和0分别代表什么都写进COMMENT里。我接手过几乎没有注释的库一百多个字段全靠猜那种滋味不想再体验第二次。2. 第一张表CREATE TABLE 从零建表2.1 建表语句的骨骼逐行拆给你看下面这个例子包含了我认为零基础应该掌握的全部常用元素主键、唯一键、普通索引、外键、默认值、注释、指定引擎和字符集。先建班级表因为学生表要引用它。USE school; CREATE TABLE class ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 班级ID, name VARCHAR(30) NOT NULL COMMENT 班级名称, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_class_name (name) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 班级表;逐行解释一下。id INT UNSIGNED NOT NULL AUTO_INCREMENT表示自增主键所有INSERT都可以不写这个字段数据库自动往下排UNSIGNED让id不能为负。name VARCHAR(30) NOT NULL表示班级名称必须有值。created_at用了DEFAULT CURRENT_TIMESTAMP插入时如果没传就自动取当前时间不用在应用层拼时间字符串。接着建学生表班级和学生构成一对多关系。CREATE TABLE students ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 学生ID, stu_no VARCHAR(20) NOT NULL COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender ENUM(M, F, U) NOT NULL DEFAULT U COMMENT 性别M男/F女/U未知, birth_date DATE DEFAULT NULL COMMENT 出生日期, phone VARCHAR(20) DEFAULT NULL COMMENT 手机号, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, class_id INT UNSIGNED DEFAULT NULL COMMENT 班级ID关联class.id, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1在读/0离校, 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_stu_no (stu_no), KEY idx_class_id (class_id), CONSTRAINT fk_students_class FOREIGN KEY (class_id) REFERENCES class (id) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 学生信息表;两个细节值得注意。第一个是updated_at的ON UPDATE CURRENT_TIMESTAMP以后只要这一行的其他字段被UPDATE它就会自动刷新时间省得每次手动维护更新时间。第二个是学号加了唯一键uk_stu_no学号天然不应该重复这个唯一约束是数据库层面的兜底比应用层的判断可靠得多。外键fk_students_class保证了students.class_id要么为NULL要么必须存在于class.id里。这条规则对数据完整性非常友好。但有个老生常谈的坑外键字段类型必须和主键表的字段类型完全一致这里class.id是INT UNSIGNEDstudents.class_id也必须是INT UNSIGNED少写一个UNSIGNED都会在建表时报错。这是零基础阶段经常卡住的第一道门槛。2.2 约束和索引让数据库替你把关上面那张学生表里出现了四类键主键、唯一键、普通键KEY、外键。简单区分一下主键确定唯一一行数据一张表只能有一个唯一键也要求不重复但一张表可以有多个而且允许NULL普通键KEY就是索引不限制重复目的是加快查询外键则维护两张表之间的关联关系。写约束有个先后顺序的坑。如果一个表有外键必须先创建被引用的父表class再创建子表students。反过来建会直接报错Failed to open the referenced table。同理DROP表时先删子表再删父表否则外键会拦住你的删除请求。主键用自增整数最省事但有两个优化习惯一是加UNSIGNED让正数容量翻倍二是不希望业务轻易猜出用户量的话用BIGINT UNSIGNED当前主流业务规模基本不会爆。有些系统喜欢用UUID做字符串主键不是不行但字符串主键在InnoDB里会显著增大索引体积随机字符串主键还容易导致页分裂影响插入性能。零基础阶段先记住一句话绝大多数表用自增整数主键。建表时顺手把索引规划好也很重要。很多人上线后才发现查询慢回头补索引结果表里已经有几百万行数据加一次索引锁半天。建表时把查询条件里的高频字段比如WHERE name ?里的name做成普通KEY不亏。但索引也不是越多越好每个索引都会拖慢写入速度、占用额外空间只给真正查得频繁的字段建。3. 动态调整ALTER TABLE 实战3.1 增删字段ALTER 的安全操作顺序业务是变化的表结构不可能永远不动。加字段是最高频的操作语法非常简单ALTER TABLE students ADD COLUMN address VARCHAR(200) NOT NULL DEFAULT COMMENT 家庭住址 AFTER phone;AFTER phone控制新字段在表中的展示位置也可以写FIRST放最前面。我个人的看法是位置不用太纠结新字段加到表尾就行因为SELECT *的列顺序只要ORM层有映射就不会受影响如果你确实想让结构整齐些用AFTER把它挪到合适位置。删字段用ALTER TABLE students DROP COLUMN birth_date;DROP COLUMN之后和这个字段相关的索引或约束也会一并删除。但你要知道这个操作不可逆除非有备份。所以我在线上很少直接DROP字段更多是先让字段废弃应用不再读写过一两个版本再统一清理。这样做的价值在于万一要回滚旧版本代码字段还在不会一瞬间满屏报错。还有一个表结构变更的老大难大表ALTER。如果students有两千万行一条ALTER TABLE会把整张表锁住期间所有读写都排队主从复制架构下还可能把从库延迟拖到几秒甚至几分钟。我在一个订单量很大的系统里就见过午休低峰期加字段从库延迟一下子飙到三分钟。稳妥的流程是选流量最低的窗口执行如果数据量实在太大交给在线改表工具处理比如gh-ost或pt-online-schema-change这类方案它们用临时表和Binlog同步的方式把对线上业务的影响降到最低。零基础阶段暂时用不上但要知道有这回事别将来发现生产库被一条ALTER锁死时一脸懵。ALTER TABLE可以一次写多个操作用逗号分隔。比如同时加字段、改字段、删字段ALTER TABLE students ADD COLUMN address VARCHAR(200) NOT NULL DEFAULT COMMENT 家庭住址, MODIFY COLUMN phone VARCHAR(30) NOT NULL DEFAULT COMMENT 联系电话, DROP COLUMN birth_date;一次提交比分开三次提交更快因为表结构锁只需要拿一次。不过零基础阶段我还是建议一步步来出错了也容易定位。3.2 改类型、改默认值、改表名改字段类型用MODIFY。比如手机号原来只留了20个字符现在要兼容国际号码扩到30ALTER TABLE students MODIFY COLUMN phone VARCHAR(30) NOT NULL DEFAULT COMMENT 联系电话;改类型有个必须警惕的场景不要轻易把长字段改短。原来VARCHAR(100)里已经存了80个字符的数据你一下改成VARCHAR(50)在严格模式下MySQL会拒绝执行在非严格模式下会默默截断成50字符静默丢数据。金额字段也一样DECIMAL(10,2)改成DECIMAL(8,2)数据超范围时同样会出问题。规则就一句话改类型尽量是兼容方向、等宽或更宽的方向要缩短长度先确认列里数据的最大长度。改字段名用CHANGE它会连名字带定义一起改ALTER TABLE students CHANGE COLUMN email email_address VARCHAR(100) DEFAULT NULL COMMENT 电子邮箱;注意CHANGE后面要完整写一遍新的字段定义不能只写新名字。忘记写类型是特别常见的低级错误执行后MySQL会报You have an error in your SQL syntax。改默认值还有一个轻量写法不需要重设整个字段类型ALTER TABLE students ALTER COLUMN status SET DEFAULT 0; ALTER TABLE students ALTER COLUMN status DROP DEFAULT;第一句把status默认值改成0第二句去掉默认值。这类语句只改元数据不扫描全表数据大表上执行也基本不阻塞业务适合高频使用。表名变更和库内移动也很常用ALTER TABLE students RENAME TO student_info;整体表改名我要额外啰嗦一句改完名之后所有应用配置、ORM映射、定时任务、报表SQL里涉及旧表名的地方会集体失效。我见过一次改名开发只改了主业务代码漏了一个半夜跑批的统计脚本第二天报表数据一片空白。所以改名之前先全局搜索一下表名的引用确认没有遗漏再动手。4. 看清表结构DESC 和 SHOW 配合使用4.1 DESC 输出里每列代表什么接手一张不熟悉的表第一件事就是看结构。可以先确认库里都有哪些表SHOW TABLES;然后看某张表的字段结构DESC students;结果大概长这样FieldTypeNullKeyDefaultExtraidbigint unsignedNOPRINULLauto_incrementstu_novarchar(20)NOUNINULLnamevarchar(50)NONULLgenderenum(M,F,U)NOUphonevarchar(20)YESNULLclass_idint unsignedYESMULNULLcreated_atdatetimeNOCURRENT_TIMESTAMPDEFAULT_GENERATED每一列信息都很值得读Null是YES就表示允许为空Key列里的PRI主键、UNI唯一键、MUL非唯一索引决定了查询能不能走索引Default是默认值Extra里的auto_increment说明是自增列。看一眼Key和Null这两个字段你基本就能判断写代码时该不该主动处理空值。DESC有个局限它看不到字段注释和字符集也看不到外键约束。所以它适合快速确认字段名和类型想知道完整建表语句要看下一个命令。4.2 SHOW CREATE TABLE最靠谱的结构存档完整视图用SHOW CREATE TABLE students\G输出里会包含一张表的完整定义包括所有字段、键、外键、字符集、注释。我第一次推荐这个命令时很多人不以为然后来都真香了。三个典型场景给表做备份前先把这条命令的结果保存成sql文件结构就永远丢不了上线前对比测试环境和生产环境的表结构差异直接跑这条输出做对比要复刻一张表把输出改个表名就变成全新的建表语句。如果你需要结构化的字段清单而不是用眼睛硬扫可以查MySQL自带的字典表SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA school AND TABLE_NAME students ORDER BY ORDINAL_POSITION;information_schema里记录了所有表的元数据。上面这条查询能拿到字段名、类型、长度、是否可空、默认值、注释非常适合导出Excel字段清单或者写自动化巡检脚本。我维护老系统时结构核对基本都靠这个视图脚本完成比人工翻大半年没动过的字段定义靠谱太多。5. 删除与清空DELETE / TRUNCATE / DROP 怎么选5.1 三兄弟的区别一张表说清很多零基础学员在这里反复搞混我直接上一个对比表。操作语句类型能否回滚是否重置自增能否加WHERE执行速度DELETEDML在事务内可以否可以逐行删慢TRUNCATEDDL一般不可是不可以极快DROPDDL一般不可表都没了不可以极快使用场景不同。只要数据是删掉部分行比如删除已经离校的学生就用DELETEDELETE FROM students WHERE status 0;这段SQL如果在一个事务里执行可以先SELECT看一眼影响行数再ROLLBACK撤销这是DELETE最大的安全感来源。但要注意DELETE之后自增id不会回退这是正常现象不是bug。想把一张表彻底清空、保留结构用TRUNCATE。它不像DELETE那样一行行删而是直接标记整张表的数据页为可重用速度飞快同时把自增计数重置回1。测试环境里清表TRUNCATE是最常用的选择。代价是它逐行恢复不了想反悔基本不可能。DROP是最后的大招把表和它的数据、索引、外键关系一次性全部删除。我之前见过有人想清空表手滑写了DROP TABLE students然后整个人愣在原地。这句SQL一旦执行结构就没有后悔药了。5.2 怕误删把这些安全习惯刻进肌肉记忆围绕删表我把这些年踩过的坑转化成几条习惯建议你写进自己的工作备忘里。第一高危语句执行前先备份结构。哪怕只是清个表也先跑一句mysqldump -h主机地址 -u你的账号 -p school students students_backup.sqlmysqldump导出的文件包含表结构和INSERT数据。如果整张表被DROP可以靠这个文件完整恢复。备份文件是文本格式你可以打开看一眼确认里面确实有内容再执行危险操作。第二写删除语句时先SELECT后DELETE。想删status 0的学生先执行SELECT COUNT(*) FROM students WHERE status 0;确认这个数量在预期范围内再把SELECT换成DELETE执行。这个习惯在前期尤其重要它能拦住大多数WHERE条件写错了导致的全表删除。第三DROP和TRUNCATE这类不可回滚操作执行前把SQL语句复制出来发给同事看一眼。不是不信任自己而是人在上线赶工和晚间疲劳状态下判断力会下降第二双眼睛真的能救命。我给自己定的纪律是涉及表结构变更的场景必须有审核没人审核就至少分开两次确认库名和表名。6. 常见问题与排查技巧实录6.1 保留字冲突的反引号处理建表报错里最常见的一类和保留字有关。我见过有人第一次建订单表写了CREATE TABLE order ( id INT NOT NULL, amount DECIMAL(10,2) );MySQL直接回一句语法错误因为order是排序关键字的保留字在很多版本里都不能直接当表名。解决办法是用反引号包起来CREATE TABLE order ( id INT NOT NULL, amount DECIMAL(10,2) );但我的建议是不要习惯性依赖反引号。尽量在命名阶段就避开order、desc、select、table、group这类词。字段用order_no、order_count既不会和保留字打架语义也更清楚。反引号是后手不是常规武器。6.2 中文乱码从字符集到连接层全链路排查字符串乱码几乎人人都会遇到。表已经是utf8mb4插入的中文还是变成问号问题往往不在建表语句而在连接层。MySQL客户端和服务端连接时如果两边字符集不一致你敲进去的中文到了服务端就已经被错误解码。排查看一组系统变量SHOW VARIABLES LIKE character_set%;重点看character_set_client、character_set_connection、character_set_results这三项。临时解决连接后立刻执行SET NAMES utf8mb4;这条语句会把三方都设置成utf8mb4新手阶段够用了。长远做法是改配置文件在[mysqld]段设置character_set_serverutf8mb4和collation_serverutf8mb4_unicode_ci在[client]段设置default-character-setutf8mb4。字符集问题有个朴素的排查顺序库、表、连接一层一层核对九成的乱码都出在连接这一层。6.3 伤人最深的五个表操作坏习惯最后分享五个我在日常维护里见得最多的坏习惯前三个和建表相关后两个和改表相关。坏习惯一所有字符串字段一律VARCHAR(255)。表面看很省事实际上会膨胀索引体积、影响内存占用还会让不合理的长度掩盖业务含义。坏习惯二日期时间都用字符串存。这会让MySQL的日期函数全部失效范围查询无法走索引排序还会出错。用原生DATE/DATETIME类型花不了多少学习成本。坏习惯三建表不设主键。没有主键的表在InnoDB里会生成一个隐藏主键数据复制、日志解析、故障修复都非常别扭更麻烦的是后续想补一个真正符合业务语义的主键往往要重建整张表。坏习惯四改表结构不通知团队。你半夜加了字段第二天凌晨跑批脚本因为SQL里用了SELECT *或者旧映射直接崩掉。上线前把表结构变更同步到文档或群里这不是流程负担是给所有人买保险。坏习惯五在线上直接ALTER大表。一两百万行也许没感觉几千万行的时候一次简单加字段可能锁表几十分钟。这个场景一定要有低峰期、备份、回滚方案的概念再往前走就要借助在线改表工具了。最后再分享一个我的个人体会。带过这么多零基础的朋友我发现大家真正卡住的往往不是SQL语法而是不敢动手怕把表删了怕把线上数据搞坏怕看不懂Field/Type/Null/Key。其实数据表这块你在本地测试库里随便折腾就行把DROP和TRUNCATE都试着跑一遍最多损失一点练习数据换来的是对这几个操作边界的熟悉感这笔账怎么算都划算。建议你拿一个通讯录或记账本的小项目把今天这篇里的建表、加字段、改类型、清空、备份整个流程亲手过一遍。走过一轮之后再面对多大的表也不会慌因为你已经知道它不过就是一张由字段、约束和索引拼起来的表格。