如果你已经完成了 MySQL 的连接登录也知道怎么用 CREATE DATABASE、DROP DATABASE 去操作数据库那这第二课咱们就该把重心移到数据表上了。毕竟一个库里如果只有库名、没有表业务照样跑不起来。数据表的操作无非三件事创建、查看、删除。看起来简单真正落手的时候你会发现选择字段类型、决定要不要加唯一约束、哪天突然要把某张表删掉每一个动作背后都有讲究。这篇文章我按实际干活时的顺序来讲先讲建表前要想清楚的字段设计再讲 CREATE TABLE 的执行细节然后讲四种查看表结构的方式最后讲 DROP TABLE 的正确姿势以及我踩过的一些坑。内容会尽量贴近真实操作不是只把命令罗列一遍。1. 建表前先想清楚字段设计类型、字符集与存储引擎在教学和带人过程中我见过太多人拿到 CREATE TABLE 语法就开始写结果建出来的表乱七八糟某个字段本来该存整数用了 VARCHAR金额字段用了 FLOAT月底对账对不上表建完了中文乱码。所以我想把建表之前那点“设计功夫”单独拎出来说。这部分一旦搞定后面的语法其实就是默写。1.1 字段类型的选取逻辑整数、小数、字符串和时间MySQL 的字段类型大体可以分成数值、字符串、日期时间、二进制几类。你不需要一次记完所有类型只需要掌握每个分类里的主力选项。先看整数。常见的有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT占用字节依次变大选型逻辑很简单你的值最大可能到多少就选能装下它且尽量小的类型。主键一般直接用 BIGINT别用 INT 去赌业务量。INT 上限是 21 亿多对很多业务听起来足够但一旦出现删除、导入合并、id 复用这类操作它会比你想象的更快逼近上限。我这几年的经验是凡是做主键的整数默认无符号 BIGINT 最稳妥省得以后做迁移时捶胸顿足。类型字节数有符号范围常见用途TINYINT1-128 ~ 127状态值、开关、性别SMALLINT2-32768 ~ 32767小范围计数器MEDIUMINT3-8388608 ~ 8388607中等计数器INT4-2147483648 ~ 2147483647一般业务数值BIGINT8-9223372036854775808 ~ 9223372036854775807主键、订单号小数值也要说清楚。金额这类精确计算必须用 DECIMAL比如 DECIMAL(10,2) 表示最长 10 位、小数点后 2 位。FLOAT 和 DOUBLE 是浮点数计算机的浮点表示天然有误差做加减乘除后可能会出现 0.1 0.2 不等于 0.3 这种结果。如果记账、金额字段用了 FLOAT那几乎等于给自己埋雷。聊天内容可以接受约等数钱不能接受。字符串类型主要区分 CHAR 和 VARCHAR。CHAR 是定长VARCHAR 是变长。定长意味着即使你只存一个字符也会占满声明的长度VARCHAR 则根据实际内容占用空间而且 8.0 版本之前每个 VARCHAR 还要额外用字节记录长度信息。所以性别、状态这类固定选项适合 CHAR(1)用户名、昵称这类长短不一的适合 VARCHAR(50) 之类。特别提一句不要无脑给所有字符串都写 VARCHAR(255)。长度越长单行数据可能越大索引也会更占空间后续数据量上来了全是成本。再往下是 TEXT适合存大段文本但它有两个常识性坑一是 TEXT 列不能设置默认值二是普通索引只能通过前缀长度建立。如果你只是想存一两行备注VARCHAR 完全够用没必要上升到大文本类型。日期时间主要记住 DATETIME 和 TIMESTAMP。DATETIME 范围大、存的是字面时间、跟时区无关适合业务上需要明确展示的时间TIMESTAMP 在写入和读取时会根据会话时区转换适合记录系统内部时间。很多建模规范里直接把创建时间字段写成DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP简洁且在插入时不用手动赋值建议照抄。1.2 字符集与排序规则为什么优先选 utf8mb4建表时如果不显式指定字符集表会继承数据库、服务器层面的默认值。很多老环境里的默认值还停留在 latin1 或旧版 utf8这里有个历史坑MySQL 的 utf8 最早只是 utf8mb3最多支持 3 个字节的字符。常用中文没问题但 emoji、部分生僻字是 4 字节一插入就直接乱码或者报错。所以我的习惯是在建表语句里明确写DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci排序规则比较常用的有utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_bin。以_ci结尾表示大小写不敏感case insensitive。general_ci速度略快但规则粗糙一些unicode_ci更准确绝大多数场景两者感知差别不大。入门阶段可以直接从utf8mb4_general_ci起步不要在排序规则上纠结太久重点是先把字符集锁定在utf8mb4。1.3 存储引擎InnoDB 是默认也应该是你的首选MySQL 有多种存储引擎入门阶段认准 InnoDB 就行不需要做太多横向对比。InnoDB 支持事务、行级锁、外键、崩溃恢复对真实业务来说这些几乎是必需品。MyISAM 是上一代引擎全表锁在并发写多的时候会明显卡顿也不支持事务一旦写了一半出问题数据的一致性很难保证。能力InnoDBMyISAM事务支持不支持行级锁支持仅表级锁外键支持不支持崩溃恢复支持较差在 MySQL 8.0 里默认引擎就是 InnoDB所以建表时不写 ENGINE 也能建出 InnoDB 表。不过显式写上ENGINEInnoDB仍然是个好习惯尤其当你要把建表语句复制到其他版本或迁移到异构环境时明确引擎能少掉很多不确定性。2. CREATE TABLE 实战从单字段语法到约束条件设计思路理清楚之后就可以实际建表了。这一章我会用一张非常典型的用户信息表作为例子把 CREATE TABLE 的每个部分都拆开讲。2.1 一条完整建表语句逐行拆解CREATE TABLE IF NOT EXISTS user_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, user_name VARCHAR(50) NOT NULL COMMENT 用户名, phone VARCHAR(20) DEFAULT NULL COMMENT 手机号, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, gender TINYINT NOT NULL DEFAULT 0 COMMENT 性别0未知 1男 2女, register_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 注册时间, PRIMARY KEY (id), UNIQUE KEY uk_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT用户基础信息表;这条语句看起来不长信息密度却不低。我建议刚开始学习的人把反引号用起来把表名和字段名包住避免字段名和保留字冲突。比如你真的建了一张叫 order 的表order 是排序关键字不包反引号就会出问题。IF NOT EXISTS的意思是如果表已经存在不报错、直接跳过。这对脚本重复执行非常友好否则同一段 SQL 跑两遍就报 Table already exists。日常练习和批量执行脚本时加上这个短语能省去很多麻烦。字段定义可以拆成几部分看。BIGINT UNSIGNED NOT NULL AUTO_INCREMENT表示无符号大整数、不允许为空、自增。自增列在插入数据时不用手动给值MySQL 会自动生成 1、2、3 这样的递增序列。UNSIGNED意味着不能为负数主键用无符号可以让正数范围翻倍。NOT NULL和DEFAULT是配合使用的。email VARCHAR(100) DEFAULT NULL的意思是邮箱允许为空没传值时默认就是 NULLgender TINYINT NOT NULL DEFAULT 0则是不允许为空没传值时自动填 0。我倾向于尽量减少可空字段尤其是业务核心字段空值会让后续所有统计和查询都多一层判断。COMMENT是注释写在字段后面用来描述这个字段的含义。强烈建议每个字段都写注释时间长了以后你自己也会忘记当初为什么这么设计。字段注释会直接显示在SHOW CREATE TABLE和部分可视化工具里是表结构文档的重要组成部分。主键和唯一约束写在字段列表之后。PRIMARY KEY (id)指定主键UNIQUE KEY uk_email (email)给 email 建了唯一索引索引名是 uk_email。唯一约束能保证 email 列的值不重复比如注册系统里一个邮箱只能被一个账号绑定。这里的细节是email 允许 NULL而 NULL 不参与唯一性判断所以多个 NULL 是可以共存的。2.2 五类常用约束的作用与适用场景建表时最常用的约束就这么几类我习惯把它们理解成“对数据的关卡”NOT NULL拒绝 NULL适合必须有值的列。DEFAULT给字段设定默认值插入时可以不传。UNIQUE KEY保证该列值不重复。PRIMARY KEY主键每张表最好都有一个主键。AUTO_INCREMENT自增通常配主键使用。FOREIGN KEY外键让数据库层面保证引用完整性。外键这里单独多说几句。外键会在删除、更新时增加一层引用检查性能上有损耗而且会让表之间的耦合变强。很多互联网团队为了性能和拆分方便刻意不在数据库层加外键而是由应用层逻辑来保证一致性。但入门阶段你不必急着跟这个风在练习项目或小系统里加上外键反而能帮你更直观地理解表之间的关联关系。等真到了高并发的业务场景再回来权衡也不迟。AUTO_INCREMENT有个容易踩的规则自增列必须被定义为某个索引的组成部分最常见的就是主键。如果只写id INT AUTO_INCREMENT却没有定义主键或唯一索引MySQL 会直接报错。这个设计是为了保证自增值能够被快速定位避免生成重复值。2.3 复制表结构LIKE 和 AS SELECT 到底选哪个除了从零建表另一种常见的“创建”场景是复制一份表。初学者经常把两种写法混在一起CREATE TABLE user_info_copy LIKE user_info;这条命令会完整复制原表的结构包括索引、约束、自增属性但不会复制数据所以新表是一张空表。CREATE TABLE user_info_copy AS SELECT * FROM user_info;后面这种写法会同时复制结构和数据但问题在于它不会把主键、索引、约束一起带过来。很多新手用 AS SELECT 做完备份表最后发现新表连主键都没有这就是结构缩水了。如果你只是想快速拿一份数据做测试AS SELECT 确实方便如果目标是得到一张和原表结构完全一致的新表一定优先用 LIKE然后再手动导数据。我自己的习惯是尽量用CREATE TABLE ... LIKE再用INSERT INTO ... SELECT把需要的数据搬过去这样结构不会丢数据也能按需挑选。3. 查看数据表四种命令的适用场景与输出解读建完表之后要做的第一件事不是急着插数据而是先查看表结构是否正确。MySQL 提供了好几种查看方式适用场景不一样这里拆开说。3.1 SHOW TABLES先看库里有几张表SHOW TABLES;这条命令会列出当前库下所有的表。如果表很多可以用模糊匹配SHOW TABLES LIKE user%;它适合新建完表之后快速判断“表到底建出来没有”。注意它受当前默认库影响如果你没有 USE 具体的库建议写成SHOW TABLES FROM 数据库名;这样就不会看错对象。3.2 DESC快速摸清字段概貌DESC user_info;DESC等价于DESCRIBE是平时最常用的看结构命令输出每一列的字段名、类型、是否允许 NULL、Key 类型、默认值、额外信息。执行结果类似这样FieldTypeNullKeyDefaultExtraidbigint unsignedNOPRINULLauto_incrementuser_namevarchar(50)NONULLphonevarchar(20)YESNULLemailvarchar(100)YESUNINULLgendertinyintNO0register_timedatetimeNOCURRENT_TIMESTAMP看这个输出会非常直观。PRI表示主键UNI表示唯一索引。我写业务 SQL 之前经常会先执行一次 DESC确认字段名没有拼错、类型是不是预期中的然后再写 SELECT。这一个动作大概只花两秒钟却能省掉很多低级错误。3.3 SHOW CREATE TABLE还原完整建表语句SHOW CREATE TABLE user_info\G\G是 MySQL 命令行客户端里的一个显示选项表示把输出竖排展示避免字段太多时横向折行。这条命令会返回 MySQL 还原出来的完整建表语句包括索引、约束、字符集、注释等全部信息。什么时候用到它最常见的是对比环境差异。测试库和正式库的表结构经常会出现细微差别分别执行SHOW CREATE TABLE把输出放一起对比差异一眼就能看出来。做表结构迁移、归档文档、排查默认值问题时它也比 DESC 更完整。需要提醒的是它返回的语句是 MySQL 根据当前结构重新生成的未必和你最初手写的一模一样。比如 8.0 版本会在 CHARSET 后面带上版本条件注释这并不影响使用别被吓到。3.4 SHOW TABLE STATUS表的隐含信息SHOW TABLE STATUS LIKE user_info\G这条命令适合看一张表的物理状态。输出内容非常长重点关注几个字段Engine当前存储引擎。Rows行数估算值。注意 InnoDB 的行数不是精确值是从索引采样出来的估算千万别拿它当精确 count 用。Data_length/Index_length数据与索引占用的字节数可以粗略判断表有多大。Collation排序规则。Create_time表的创建时间。比如你做性能排查想知道某张表是不是占了大量磁盘空间SHOW TABLE STATUS是最快的入口。入门阶段知道有这个命令即可后面学到索引和慢查询优化时会经常回来看它。4. 删除数据表DROP TABLE 的语法边界与不可逆风险删除是三个操作里风险最高的一个所以我愿意花多一些篇幅把话说透。DROP TABLE 的语法很简单简单反而容易让人放松警惕。4.1 DROP TABLE 基本语法与 IF EXISTSDROP TABLE 表名; DROP TABLE IF EXISTS 表名; DROP TABLE IF EXISTS 表1, 表2;一次删除多张表在脚本清理时很方便。有一个细节特别值得注意如果表列表里有一张表不存在而且你没加 IF EXISTSMySQL 会直接报错。但对已经执行成功的那部分删除不会回滚前面删掉的表已经没了。所以批量删表时要么先确认所有目标表都存在要么把 IF EXISTS 加上否则容易造成一场只删了一半的意外。DROP TABLE 是 DDL 语句执行后立即提交没有事务回滚的机会。这和 DELETE 删掉的行还能通过 ROLLBACK 救回来完全是两码事。4.2 DROP、TRUNCATE、DELETE 三者核心区别这三个操作经常被混在一起聊但它们的语义差别很大操作对象是否可回滚速度适用场景DROP TABLE整个表结构加数据不可回滚很快彻底不要这张表TRUNCATE TABLE表数据保留结构通常不可回滚很快清空整表并重置自增DELETE FROM行数据事务中可以回滚较慢按条件删除部分行例如DELETE FROM user_info WHERE id 100;删的是符合条件的某一行DELETE FROM user_info;删除全部行但如果有事务包着还能 ROLLBACK。TRUNCATE TABLE user_info;清空所有数据保留表结构同时把自增计数重置为 1大多数情况下不能回滚。DROP TABLE则是连结构带数据一起消失连清空的机会都不留。理解了这三者区别你会发现很多“删错表”事故其实都源于操作前没有想清楚我到底是要清空、删行还是彻底删除4.3 外键关联给删表带来的连锁反应假设有两张表父表order_info和子表order_item子表里有一个外键指向父表的主键。此时直接DROP TABLE order_info;MySQL 会报错提示存在外键约束不允许删除。处理方式有两种思路第一种是先删子表再删父表顺序正确就能正常执行第二种是如果子表还要保留就只删除子表上的外键约束再回头删父表。所以第 2 章里我提醒过外键不是越多越好。它会直接影响后续的删除和更新操作生产环境里很多团队宁可在应用层保证一致性也不愿意让数据库层的外键把运维操作困死。入门练习可以随便加外键理解表之间的关联生产级系统里加外键前先问问自己能不能承受它带来的运维成本。4.4 生产环境删除表的保守流程没有备份的情况下直接执行 DROP TABLE是最容易出事故的一步。我的建议很简单重要环境里永远不要直接 DROP。保守流程是这样先备份mysqldump -u用户名 -p 库名 表名 表名_备份日期.sql把旧表更名为备份表RENAME TABLE old_name TO old_name_bak_20250201;观察业务侧没有报错、确实不再依赖这张表最好能保持一两个版本周期。确认无误再真正删除带_bak后缀的表。很多团队发布流程中就是这么做的。核心目的就是给“删表”加一道后悔药。这个习惯入门的时候就可以培养一点都不亏等你某天真的面对一张几百 GB 的线上表时会庆幸自己一直执行这套流程。5. 建表/删表踩坑实录几个可以直接抄的教训最后这部分没有固定章法我就按实际踩过、见过、帮别人排查过的经历来写每一条都很具体建议直接抄进自己的笔记。5.1 建表选错了库然后到处找表我刚入行时发生过一次很典型的事连接上 MySQL 之后没有执行 USE 某个库直接执行 CREATE TABLE结果表建到了当前默认库里跟我以为的完全不是同一个。等到我用 SHOW TABLES 一看发现“表不见了”顿时吓出一身冷汗。教训有两点。第一连接数据库之后先执行SELECT DATABASE();确认当前所在库第二建表语句尽量使用“库名.表名”的完整写法比如CREATE TABLE my_db.user_info (...);。这样脚本复制到任何环境都不会串库。5.2 字符集没指定中文变成问号之前给某个内部小系统建临时表建表语句里没有写字符集而那个旧环境的服务器默认字符集是早期配置。插入中文后再查出来全是问号。我最初怀疑是客户端编码问题试了各种 SET NAMES完全没用。最后执行SHOW CREATE TABLE才发现表建好时字符集就落在 latin1。修复方式倒是有一条ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4;但数据已经变成问号的部分基本救不回来。正确做法是建表时就显式指定字符集客户端连接也统一 utf8mb4。这个坑的典型误区是只记得在客户端执行SET NAMES utf8mb4却忘了表本身用的还是旧字符集连接层和存储层不一致照样乱码。5.3 VARCHAR 长度乱写导致行大小超限InnoDB 单表行大小是有限制的大约是 65535 字节不含 BLOB/TEXT 的特殊处理。如果一张表里堆了一堆 VARCHAR(255)在 utf8mb4 字符集下一个 VARCHAR(255) 最多占 1020 字节几十个这样的字段加起来很容易直接触顶。我见过一个实际案例有位同学做练习时把 30 个字段全定义成 VARCHAR(255)CREATE TABLE 直接报错提示 Row size too large。他反复看语法也没发现问题直到把长度缩到 VARCHAR(100) 才通过。所以写字段长度时一定想清楚业务需求用户名 50 足够备注 200 很宽裕文章正文用 TEXT。不要一刀切全写 255。5.4 删除表前的最后一道防线最后说一个让我受益很久的习惯删除任何表之前先执行一次SHOW TABLES LIKE 目标表名;确认它确实存在而且确实是你想删的那一张。尤其是生产库里存在一堆相似表名时一个字母打错就可能删错对象。再有就是备份。哪怕一张表只有几百行DROP 之前用 mysqldump 导一份也花不了几秒。我曾在一个临时项目里删掉过一张感觉已经没用的表结果半小时后业务方反馈缺数据幸好那张表有备份才在一个小时内恢复回来。从那以后凡是我不是百分之百确定这辈子都用不上的表一律先改名成_bak观察几天再处理绝不立即删。最后再分享一个我坚持到现在的习惯每次建完表先执行 SHOW CREATE TABLE 看一遍把结果复制进项目里的 SQL 文件留档每次要删表先 SHOW TABLES LIKE 看一眼目标再决定要不要备份。这套动作消耗的时间不超过半分钟但已经帮我躲过好几次低级事故。数据表操作本身很简单真正拉开差距的就是这些看似多余的确认和习惯。