1. CREATE TABLE 基本功一份建表语句的完整解剖先说个真实场景。我在带新人写项目的时候经常发现一个通病很多人建表全靠可视化工具点鼠标比如用 Navicat 右键“新建表”点出来的确很快但本质上并不清楚背后执行的是什么 SQL。一旦到了需要写脚本、做数据迁移、在 CI 环境里自动初始化表结构的时候就完全抓瞎了。CREATE TABLE 是 SQL 里最基础的一条语句它的作用就是在数据库里创建一张新表。我说的“新表”可以理解为一个二维表格有行有列每一列有固定的数据类型每一行是一条具体的数据记录。你建表的时候做的本质上是声明这张表的骨架告诉数据库“我要存哪些字段、每个字段存什么类型、哪些字段不能为空、哪些字段是唯一标识”。最基本的语法长这样CREATE TABLE table_name ( column1 datatype [constraints], column2 datatype [constraints], ... );我来拆一个实际生产环境里的例子假设你要为电商系统建一张用户表CREATE TABLE users ( id INT NOT NULL, username VARCHAR(50) NOT NULL, email VARCHAR(100), created_at DATETIME DEFAULT CURRENT_TIMESTAMP );这条语句做了四件事声明表名叫users定义四个字段给id和username加上非空约束给created_at设置默认值。在数据库里执行之后你就拥有了一张空表随时可以往里写数据。但我要提醒你这是最简版。大量真实的建表场景远比这个复杂需要结合主键、自增、外键、唯一约束、索引、字符集、存储引擎等条件一起设计。接下来我把这些内容逐一展开并且会告诉你每一步为什么要这么做而不是光把语法给你背一遍。2. 字段类型怎么选选错类型会付出巨大的代价2.1 整数类型不好好看长度会埋雷市面上主流数据库比如 MySQL、SQL Server、PostgreSQL在整数类型上各有各的叫法但思路是一致的。拿 MySQL 举例TINYINT1 字节范围 -128 到 127适合存状态值比如 0/1/2。SMALLINT2 字节适合存年龄、枚举值这种小数字。INT4 字节日常用得最多比如 ID、数量、次数。BIGINT8 字节存雪花 ID、订单号这种超大的数字。很多人会犯一个错误不管什么数字都用INT。结果时间一长业务量上去了ID 逼近 21 亿上限整个表没法再写数据。到那时候再改表结构锁表锁半天业务断断续续运维焦头烂额。所以设计阶段就要想清楚将来这个字段可能涨到多大如果有一丁点不确定直接上BIGINT。再说 SQL Server这边的叫法稍微不一样用INT还是 32 位整数也有SMALLINT、TINYINT、BIGINT。写法和 MySQL 大差不差。如果你以后要在不同数据库之间迁移这个知识点尤其重要。2.2 小数类型为什么别用 FLOAT 存金额浮点数的问题在于精度。FLOAT、DOUBLE这类类型是二进制近似存储的也就是说你存进去的 0.1在数据库内部可能是一个二进制无法精确表示的数。做个简单例子SELECT 0.1 0.2;在很多数据库里结果不是 0.3而是 0.30000000000000004。这个用在科学计算没人管你但放在金额上就出大事了对账对不上财务直接投诉。所以涉及钱统一用定点数类型。MySQL 里是DECIMAL(10, 2)PostgreSQL 里叫NUMERIC(10, 2)SQL Server 里是DECIMAL(10, 2)。意思是一共 10 位有效数字其中小数占 2 位。2.3 字符串和日期类型长度字符还是字节要想清楚字符串类型常见的坑在VARCHAR的长度单位上。MySQL 5.0 之后VARCHAR(255)里的 255 默认指的是字符数不是字节数。也就是说你可以存 255 个汉字如果存英文也是 255 个。这一点和 Oracle 不一样Oracle 里的VARCHAR2(255)指的是字节数一个汉字算三个字节UTF-8 下所以 Oracle 建表时长度要往大了给。日期时间类型也得注意。MySQL 里最常用的就是DATETIME和TIMESTAMP。DATETIME范围从 1000 年到 9999 年与时区无关存什么显示什么。TIMESTAMP范围从 1970 年到 2038 年底层存的是 UTC 时间展示的时候会根据数据库时区转换。我个人的习惯是能用TIMESTAMP就用TIMESTAMP因为它在跨时区业务里更省心。但要注意如果你遇到的项目可能活到 2038 年之后你自己掂量一下。这里我插一句很多人在建表初期完全不考虑字符集。比如 MySQL 里写成CREATE TABLE users ( nickname VARCHAR(50) ) CHARACTER SET utf8mb4;为什么要强调utf8mb4因为老的utf8在 MySQL 里最多支持 3 字节的编码存不了 emoji 表情和一些特殊字符。你如果建表时没设置以后往表里插入 emoji直接报错或者存进去之后查出来是一堆问号非常难看。这个问题在真实业务里太常见了我现在看到新项目里有人用不带mb4的utf8当场就让他改掉。3. 约束机制数据质量的最后一道防线3.1 NOT NULL 与 DEFAULT别让空值泛滥我见过太多人建表时偷懒所有字段都不写NOT NULL让数据库允许空值。结果就是后续写WHERE条件时各种NULL值判断绕来绕去统计结果莫名其妙少几条或者聚合函数算出来的结果对不上。我的建议是业务上必须有值的字段一律加上NOT NULL。比如订单表的订单号、用户表的用户名这些字段如果没有值这条记录本身就没有存在意义。加了约束之后如果应用层忘了传值数据库会直接拒绝插入算是最后一道防线。DEFAULT也值得多写。比如CREATE TABLE orders ( order_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP );status默认 0表示新建订单created_at默认当前时间表示记录创建时刻。这比应用层代码DateTime.Now再传进去靠谱得多至少不会出现“应用服务器时间不一致”的幺蛾子。3.2 PRIMARY KEY 与 UNIQUE怎么选才不后悔主键是个老生常谈的话题但真正理解它价值的人不多。主键有两个核心特性一是非空二是唯一。一张表只能有一个主键它可以由一列构成也可以由多列联合构成。日常推荐的做法用自增整数作为主键简单高效。用 UUID 或者雪花 ID 作为主键适合分布式场景。用业务字段做主键比如身份证号但要非常谨慎因为业务字段可能会变。至于UNIQUE约束它和主键很像也是保证唯一性但一张表可以有多个唯一约束。比如用户表里username设了主键不一定email又设了唯一约束这样在插入数据时数据库会自动检查避免重复。很多人不在email上加唯一约束靠应用层先SELECT再INSERT结果并发场景下还是插入了重复邮箱这种问题本质上就是“能用数据库约束解决的事非要用代码去碰运气”。如果要建联合唯一约束比如“同一个用户不能对同一件商品重复评价”你可以这样写CREATE TABLE reviews ( user_id BIGINT NOT NULL, product_id BIGINT NOT NULL, content TEXT, UNIQUE KEY uk_user_product (user_id, product_id) );这样在数据库层面就强制了唯一性比应用层查两遍再插入要可靠得多。3.3 外键与索引要不要用要分场景外键约束是用来保证两张表之间的数据完整性的。比如订单表里的user_id引用用户表的id那就能保证订单表里不会出现不存在的用户 ID。这个功能看起来美好但在互联网大流量项目里外键经常被刻意禁用。原因很简单外键会造成写入时的额外检查开销而且在分布式分库分表场景下跨库的外键根本没法实现。很多公司的规范是“数据库只做存储约束逻辑放到应用层”所以建表时不写外键。不过我要说句公道话如果你做的是企业内部管理系统、后台系统、数据量不大、并发不高那用外键其实是省心的事情。数据完整性交给数据库比让开发在代码里维护一堆检查逻辑要靠谱。我自己的习惯是外键对业务逻辑有强约束作用的场景保留纯粹为了“引用关系”而存在的外键能去掉就去掉。再说索引。CREATE TABLE后面的字段定义里可以直接加索引也可以在建表之后用ALTER TABLE加比如CREATE TABLE users ( id BIGINT NOT NULL PRIMARY KEY, email VARCHAR(100) NOT NULL, INDEX idx_email (email) );建立索引的本质是拿空间换时间。查询经常用的字段不加索引的话全表扫描那叫一个慢。尤其数据量到了几十万上百万条没索引的查询能把你熬死。但索引也不是越多越好每多一个索引插入、更新、删除时都要同步维护写性能下降。所以索引要在真实查询模式出来之后再慢慢加前期建表只加主键索引和唯一索引就够了。4. 自增、序列与默认值这几个细节决定了开发体验4.1 自增主键在三大数据库里的写法很多刚入门的朋友会踩这个坑在 MySQL 里自增写法是AUTO_INCREMENT在 SQL Server 里是IDENTITY(1,1)在 PostgreSQL 里则是SERIAL或GENERATED AS IDENTITY。语法完全不同但理念一样。MySQL 写法CREATE TABLE article ( id INT NOT NULL AUTO_INCREMENT, title VARCHAR(100) NOT NULL, PRIMARY KEY (id) );SQL Server 写法CREATE TABLE article ( id INT NOT NULL IDENTITY(1,1), title NVARCHAR(100) NOT NULL, PRIMARY KEY (id) );这里的IDENTITY(1,1)意思是从 1 开始每次自增 1。PostgreSQL 写法CREATE TABLE article ( id SERIAL PRIMARY KEY, title VARCHAR(100) NOT NULL );选自增还是 UUID主要看你的系统是不是分布式部署。单库单表自增完全够性能好、索引紧凑。如果是分布式的自增会在多节点下冲突这时候改用雪花 ID 或者 UUID配合BIGINT类型最好。4.2 默认值的正确打开方式默认值的使用极大地简化了应用层代码。一个常见的错误是很多人在建表时让create_time字段允许为空等插入数据时再由代码传入时间。如果代码忘了传数据库里就是NULL以后你排序、统计都很痛苦。正确姿势CREATE TABLE orders ( id BIGINT NOT NULL AUTO_INCREMENT, amount DECIMAL(10, 2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) );注意最后那个updated_atMySQL 里支持ON UPDATE CURRENT_TIMESTAMP意思是这条记录每一次被更新时自动把该字段刷新成当前时间。这就省了代码里手动维护“修改时间”的功夫。很多新手不知道这个特性每次更新只更新业务字段忘了改updated_at结果时间永远停留在插入那一刻。SQL Server 里表达默认时间一般用CREATE TABLE orders ( id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, created_at DATETIME2 NOT NULL DEFAULT GETDATE() );GETDATE()就是 SQL Server 的当前时间函数。而DATETIME2是比DATETIME精度更高的类型能到微秒级别我建议优先用DATETIME2。5. CREATE TABLE AS一条语句建一张新表还有一种非常实用的建表方式叫CREATE TABLE AS SELECT简称 CTAS。它的用途是基于查询结果直接创建一张新表。比如你要把一张表里的部分数据抽出来单独放不想手动定义字段就可以CREATE TABLE user_backup AS SELECT * FROM users WHERE status 1;在 SQL Server 里对应的写法是SELECT * INTO user_backup FROM users WHERE status 1;PostgreSQL 和 MySQL 都支持CREATE TABLE AS。这个功能最常用的场景有三个一是快速做临时备份二是做报表汇总三是建一张表结构相同但数据经过过滤的新表。但我得给你提个醒CTAS 这种方式建出来的表通常不会自动复制源表的索引、主键、外键等约束。也就是说新表的结构很“裸”只有字段和数据类型。你要做数据清洗或者临时分析可以但如果是上生产环境的正式表还是得手工补上索引和约束。另外还要注意CTAS 在数据量很大的时候会一次性读全量数据并写入新表如果源表上亿行这一步会非常耗资源。生产环境里执行前一定要确认磁盘空间和数据库负载我亲历过同事一条CREATE TABLE AS SELECT把整个数据库磁盘写满的惨案最后数据库直接只读所有业务都停了。6. 临时表与会话生命周期临时表也是 CREATE TABLE 的一个重要分支。它的特点是只在当前会话或者当前事务里存在会话一结束表就被自动删掉。做复杂的数据处理时非常好用。MySQL 里临时表用法CREATE TEMPORARY TABLE temp_data ( id INT NOT NULL, value VARCHAR(20) );SQL Server 里临时表分两种一种是#开头的本地临时表一种是##开头的全局临时表CREATE TABLE #temp_data ( id INT NOT NULL, value VARCHAR(20) );临时表的价值在于你可以把一个复杂查询拆成多步中间结果放到临时表里然后再接着做下一步。尤其在存储过程、复杂报表里临时表能让 SQL 的可读性和维护性强很多。举个例子你要统计一个门店列表和每个门店的订单金额。先取出符合条件的门店放临时表然后 JOIN 订单表做聚合。这比一条大长 SQL 嵌套子查询要清楚得多也更容易排查问题。临时表还有一个常见用途去重后的结果暂存。没有临时表的时候一条一条处理很难受有临时表就直接SELECT DISTINCT出来放临时表后续 SQL 反复用。这个思路在处理“清洗数据”“去重查询”这种场景时特别顺手。不过要注意MySQL 里临时表是每个连接独立的不同会话看不到对方的临时表。如果你在应用层用连接池同一个连接上创建的临时表一定要在业务逻辑结束前用完否则连接回收到池里临时表还在下次这个连接被其他请求拿到时容易出莫名其妙的错。7. 建表时最容易踩的坑命名规范与常见错误7.1 表名和字段名的命名原则命名这件事看起来简单做起来乱套的项目一大把。我见过的烂表名有A1、test、t1、新建表。这些东西放到生产环境里就是灾难维护起来全靠猜。我建议的命名规则是表名用业务域 下划线 表语义比如order_info、user_account_log。字段名用下划线分隔单词比如order_status、created_at不要用orderStatus这种驼峰。不建议加前缀t_或者后缀_table例如t_user、user_table这种不会让表更好理解反而多个字符。字段名不要和数据库关键字冲突比如order、group、desc都不是好的字段名真要用时得加反引号括起来。在 SQL Server 和 MySQL 里字段名是区分大小写还是不分大小写取决于数据库的配置和操作系统。但你要记住一点在一个团队里形成统一风格比大小写本身重要得多。7.2 常见建表错误速查表错误类型错误示例正确做法后果类型太短用户 ID 用INT用BIGINT数据量上亿后溢出金额用浮点FLOAT存价格用DECIMAL(10,2)精度丢失对账不平不写非空约束核心字段不写NOT NULL按业务语义定义脏数据混入字符串长度含义混淆MySQL 与 Oracle 混淆字符/字节明确数据库类型中文超长报错表名使用中文订单表用英文order_info兼容性问题维护困难字段名带空格或关键字user name、orderuser_name、order_idSQL 频繁报语法错7.3 生产环境建表的常见问题第一个坑没有预先考虑数据量增长。创建表的时候拍脑袋定类型没有考虑几年后的规模导致表还没上线多久就开始做结构调整。调整表结构不是不能做但很多数据库在修改字段类型时要重建表几千万行记录的表重建一次就是一场灾难。第二个坑可视化工具建表后不留 SQL 脚本。用 Navicat 或者 SSMS 的图形界面建表建完就走人。等到要复制到测试环境、生产环境时只能手工重新建一遍漏掉一个字段就出问题。正确的做法是建完表后把 DDL 脚本导出保存到版本管理仓库里这就是你的表结构记录以后要追溯、要变更、要回滚都靠它。第三个坑字段注释缺失。生产环境的表动辄几十上百个字段哪个新人能记住每个字段的含义MySQL 里每个字段可以写COMMENT建表时一定写清楚这个字段是干嘛的、值代表什么含义。SQL Server 里用扩展属性也行总之要留注释。注释不是给别人看的是给三个月之后崩溃的你看的。来看一个带注释的完整建表语句CREATE TABLE user_account ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, user_id BIGINT NOT NULL COMMENT 用户ID关联 user_info.id, balance DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 当前余额, frozen_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 冻结金额提现处理中, 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_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户账户表;看到没这是一个可以直接上生产的建表语句。8. 不同数据库在建表语法上的差异对照面试的时候经常有人被问“MySQL、SQL Server、Oracle 建表有什么区别”。我干脆在这里给一张对照表省得你去翻文档项目MySQLSQL ServerOracle / PostgreSQL自增写法AUTO_INCREMENTIDENTITY(1,1)Oracle 用SEQUENCEPG 用SERIAL字符串类型VARCHAR(n)按字符VARCHAR(n)按字符OracleVARCHAR2(n)按字节PGVARCHAR(n)按字符是否区分大小写取决于操作系统不区分默认Oracle 默认大写PG 分大小写当前时间默认值CURRENT_TIMESTAMPGETDATE()CURRENT_TIMESTAMP分页写法LIMIT offset, countOFFSET ... FETCHOracle 用ROWNUMPG 用LIMIT建表后是否存在永久永久永久临时表后缀TEMPORARY#、##GLOBAL TEMPORARY这里面最坑的是 Oracle 的VARCHAR2长度单位是按字节算的。你要是把 MySQL 的经验直接搬到 Oracle建出来的表往里面存中文经常报“超出最大长度”。很多从 MySQL 转 Oracle 的团队都在这上面栽过跟头。另一个容易踩的差异是 SQL Server 的NVARCHAR和VARCHAR。NVARCHAR存的是 Unicode 字符能存中文和多种语言但它每字符占用 2 字节VARCHAR在默认排序规则下也能存中文但如果你需要存 emoji、藏文之类的特殊字符建议用NVARCHAR。总之SQL Server 里涉及到多语言文本我直接写NVARCHAR省心。PostgreSQL 是这几家里最“守规矩”的类型系统非常严格VARCHAR(50)就是 50 个字符不搞歧义。再加上SERIAL和GENERATED AS IDENTITY两种自增写法我个人非常推荐做新项目时优先考虑 PG。9. 结合常见场景多条查询语句里如何用表前面讲的都是单张表的创建。实际开发里建表往往不是孤立的表建好之后要应对各种查询场景比如热搜词里提到的“SQL 语句去重”“SQL 窗口函数”“慢 SQL 优化”这些都和表设计息息相关。举个例子你要去重统计每个用户的订单数量。如果没有合理的表结构约束去重会变得异常痛苦。如果建表时已经加了UNIQUE约束那数据源头就控制了重复问题查询时很多去重逻辑根本不需要写。如果你建的表一开始就没这个约束那只能靠SELECT DISTINCT或者GROUP BY去扛。数据量小还行数据量一大去重查询慢得离谱。这就是我在前面一直强调建表时就把约束设计好的原因。再比如窗口函数在 MySQL 8.0 和 SQL Server 里都可以用ROW_NUMBER() OVER (PARTITION BY ...)来做分组排序。但如果表结构设计不合理比如大量字段类型选择错误导致隐式转换窗口函数跑起来也会慢。所谓的“慢 SQL 优化”很多时候优化的重心不在 SQL 本身而是回到表结构本身回到字段类型、索引、约束这些基础设计上。所以我的一个核心观点是CREATE TABLE 不仅仅是“建一张表”这个动作它决定了你后面几十年写 SQL 的体验。10. 一个完整的实操案例从零搭建用户订单系统最后来一个完整的实战演示。假设你要为一个商城系统建两张核心表用户表和订单表。我完整走一遍建表流程你可以直接抄。第一步设计用户表。用户表需要存用户 ID、用户名、加密密码、昵称、邮箱、手机号、注册时间、状态。注意密码是加密后的哈希字符串长度我给VARCHAR(100)而不是 32 位因为哈希算法如 bcrypt 的输出长度可能超过 32 位。CREATE TABLE user_info ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 用户ID, username VARCHAR(50) NOT NULL COMMENT 登录用户名, password_hash VARCHAR(100) NOT NULL COMMENT 密码哈希值, nickname VARCHAR(50) DEFAULT COMMENT 昵称, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, phone VARCHAR(20) DEFAULT NULL COMMENT 手机号, 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), UNIQUE KEY uk_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户信息表;第二步设计订单表。订单关联用户订单表里有订单号、用户 ID、总金额、支付状态、收货地址快照、下单时间。CREATE TABLE order_info ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 订单主键, order_no VARCHAR(32) NOT NULL COMMENT 订单编号业务唯一, user_id BIGINT NOT NULL COMMENT 下单用户ID, total_amount DECIMAL(12,2) NOT NULL COMMENT 订单总金额, pay_status TINYINT NOT NULL DEFAULT 0 COMMENT 支付状态0-待支付 1-已支付 2-已退款, receiver_address VARCHAR(200) NOT NULL COMMENT 收货地址快照, remark VARCHAR(500) DEFAULT NULL COMMENT 买家备注, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 下单时间, paid_at DATETIME DEFAULT NULL COMMENT 支付时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_pay_status (pay_status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;注意order_no我设置了唯一约束因为订单号业务上必须唯一user_id加了普通索引因为按用户查订单是高频查询pay_status也加了索引方便后续按状态筛选。这里我没有加外键。并不是说外键不好而是在这个场景下订单和用户的关联通过user_id加索引已经足够支撑查询完整性靠应用层保证。如果你做的系统并发不高、团队规模小加上外键其实也可以这个取决于你的实际上下文。第三步验证表是否建成功。MySQL 里可以用SHOW CREATE TABLE order_info;执行后会展示完整的建表语句你可以核对有没有漏字段、漏约束。SQL Server 里对应的命令是EXEC sp_help order_info;这一步千万别跳过尤其是从图形界面建表之后用这个命令导出的 DDL 就是你日后维护和版本控制的依据。11. 最后关于建表这件事我踩过的坑想再叮嘱几句在我做过的项目里表结构设计得差有多痛我体验过太多次。最经典的一个项目订单表在早期设计时所有金额字段用了FLOAT到后期每天有几百万订单进来财务对账对不上整个技术团队花了两周排查加迁移数据。要是一开始就老老实实用DECIMAL(12,2)根本没有这些破事。还有一次同事建表时把created_at和updated_at这两个字段漏了后来业务要查“最近三个月有过修改的用户”发现完全没有办法追踪修改时间最后只能从日志里恢复数据痛苦不堪。从那时候起我给自己定了一个硬规矩任何业务表都至少要有created_at和updated_at两个时间字段除非明确知道不需要。建表这件事表面上只是一个 DDL 脚本实际上包含了你对业务的理解对数据类型的把控对未来增长规模的预判。宁可在建表时多花半小时想清楚也不要将来用十天半个月去改一张烂表。另外再说个小技巧如果你是团队里的技术负责人可以把建表规范写成一个文档规定类型选择、命名风格、必备字段、默认要求。新人入职时先看这个文档再让他动手建表整个团队的表结构风格统一后面的维护成本会低很多。