CREATE TABLE 是 MySQL 中创建数据表的核心命令。一个完整的建表语句不仅定义字段名称和类型,还应明确主键、唯一约束、默认值、索引、外键、字符集、排序规则和存储引擎。本文以 MySQL 8 为基础,系统梳理 CREATE TABLE 的常用语法和实际设计方法。
一、CREATE TABLE 基本语法
CREATE TABLE [IF NOT EXISTS] table_name (
column_name data_type [column_options],
...,
[table_constraints],
[indexes]
) [table_options];
一个最简单的用户表如下:
CREATE TABLE users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
二、创建前选择数据库
CREATE DATABASE IF NOT EXISTS shop
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;
USE shop;
SELECT DATABASE();
也可以使用完全限定名直接指定数据库:
CREATE TABLE shop.users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL
);
三、IF NOT EXISTS 的作用
CREATE TABLE IF NOT EXISTS users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
);
当表已经存在时,IF NOT EXISTS 可以避免语句直接报错,但它不会检查已有表的字段是否与当前定义一致。执行后仍应通过 SHOW WARNINGS 和 SHOW CREATE TABLE 确认结果。
四、常用字段类型
| 用途 | 常用类型 | 示例 |
|---|---|---|
| 整数 | TINYINT、INT、BIGINT | id BIGINT UNSIGNED |
| 精确小数 | DECIMAL | price DECIMAL(10,2) |
| 短字符串 | CHAR、VARCHAR | title VARCHAR(200) |
| 长文本 | TEXT、MEDIUMTEXT、LONGTEXT | content MEDIUMTEXT |
| 日期时间 | DATE、DATETIME、TIMESTAMP | created_at DATETIME |
| 二进制数据 | BINARY、VARBINARY、BLOB | file_hash BINARY(32) |
| 结构化文档 | JSON | settings JSON |
金额应使用 DECIMAL,而不是 FLOAT 或 DOUBLE;电话号码、身份证号等虽然由数字组成,但不参与数值运算,通常应该使用 VARCHAR。整数类型后面的显示宽度不代表存储范围,MySQL 8 中也不应再依赖该写法。
五、NULL 与 NOT NULL
nickname VARCHAR(50) DEFAULT NULL,
email VARCHAR(120) NOT NULL
NULL 表示值未知或不存在,与空字符串和数字 0 不相同。业务必填字段应明确使用 NOT NULL;确实允许缺失的字段才使用 NULL。不要为了减少 NULL 而给所有字段设置没有业务意义的空字符串或 0。
六、DEFAULT 默认值
status TINYINT UNSIGNED NOT NULL DEFAULT 1,
score DECIMAL(8,2) NOT NULL DEFAULT 0.00,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
默认值应符合业务含义。DEFAULT 只在 INSERT 没有提供该字段时生效;显式写入 NULL 是否成功,仍取决于字段是否允许 NULL。
七、主键与 AUTO_INCREMENT
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
一张表只能有一个主键。InnoDB 使用主键组织数据,主键应稳定、非空且尽量简洁。自增整数主键适合大多数单库业务表,但对外公开的资源标识可以另外设计随机编号或 UUID。
八、UNIQUE 唯一约束
email VARCHAR(120) NOT NULL,
UNIQUE KEY uk_users_email (email)
唯一索引用于保证字段或字段组合不重复。联合唯一约束示例:
UNIQUE KEY uk_tenant_username (tenant_id, username)
可空字段的唯一性行为需要特别注意:MySQL 唯一索引通常允许存在多个 NULL。如果业务要求“必须有值且唯一”,应同时使用 NOT NULL。
九、CHECK 检查约束
age TINYINT UNSIGNED DEFAULT NULL,
price DECIMAL(10,2) NOT NULL,
CONSTRAINT chk_users_age CHECK (age IS NULL OR age <= 150),
CONSTRAINT chk_price_nonnegative CHECK (price >= 0)
MySQL 8 可以强制执行 CHECK 约束。它适合表达简单、稳定且与单行数据相关的规则;复杂业务验证仍应由应用层处理。
十、创建普通索引
KEY idx_status (status),
KEY idx_category_created (category_id, created_at),
KEY idx_title_prefix (title(50))
联合索引字段顺序应根据实际 WHERE、JOIN、ORDER BY 和 GROUP BY 条件设计。不要为每个字段机械建立索引,因为 INSERT、UPDATE 和 DELETE 都需要同步维护索引。
十一、定义外键
CREATE TABLE departments (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE employees (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
department_id BIGINT UNSIGNED NOT NULL,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
KEY idx_department_id (department_id),
CONSTRAINT fk_employee_department
FOREIGN KEY (department_id)
REFERENCES departments (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
外键两侧字段类型必须兼容,包括整数的 UNSIGNED 属性;父表引用列需要索引,表的存储引擎也必须支持外键。删除规则应根据业务明确选择 RESTRICT、CASCADE、SET NULL 等策略,不能为了省事一律使用 CASCADE。
十二、生成列
CREATE TABLE order_items (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
quantity INT UNSIGNED NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
total_amount DECIMAL(12,2)
GENERATED ALWAYS AS (quantity * unit_price) STORED,
PRIMARY KEY (id)
) ENGINE=InnoDB;
生成列由表达式计算,可分为 VIRTUAL 和 STORED。它适合稳定的派生值,也能结合索引优化特定查询,但表达式需要满足 MySQL 的确定性要求。
十三、表级选项
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci
COMMENT='用户信息表'
AUTO_INCREMENT=1000
ENGINE=InnoDB:支持事务、行级锁、崩溃恢复和外键,是 MySQL 8 的默认选择。utf8mb4:完整支持 Unicode 和 Emoji。COLLATE:决定字符串比较和排序规则,需要结合大小写、重音和语言要求选择。COMMENT:记录表的业务用途。AUTO_INCREMENT:设置下一个自增起始值,不应将它当作业务编号规则。
十四、创建临时表
CREATE TEMPORARY TABLE temp_user_ids (
user_id BIGINT UNSIGNED NOT NULL PRIMARY KEY
);
临时表只在当前数据库连接中可见,连接关闭后自动删除。它适合存放会话内的中间结果,不适合跨请求共享数据。
十五、复制已有表结构
1. CREATE TABLE ... LIKE
CREATE TABLE users_archive LIKE users;
LIKE 会复制字段和索引等表结构,但不会复制原表数据。创建后仍应使用 SHOW CREATE TABLE 检查外键、触发器等对象是否需要单独处理。
2. CREATE TABLE ... AS SELECT
CREATE TABLE active_users AS
SELECT id, username, email
FROM users
WHERE status = 1;
该写法会根据查询结果创建字段并写入数据,但通常不会完整保留原表的主键、索引、默认值和约束。它适合生成临时分析表,不适合直接代替正式表结构迁移。
十六、完整电商订单表示例
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '内部主键',
order_no CHAR(24) NOT NULL COMMENT '对外订单号',
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '订单金额',
status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单状态',
remark VARCHAR(500) DEFAULT NULL COMMENT '订单备注',
paid_at DATETIME DEFAULT NULL COMMENT '支付时间',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_order_no (order_no),
KEY idx_user_created (user_id, created_at),
KEY idx_status_created (status, created_at),
CONSTRAINT chk_orders_amount CHECK (total_amount >= 0)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci
COMMENT='订单主表';
此示例把内部主键与业务订单号分开,使用 DECIMAL 保存金额,并根据用户订单列表和状态任务两个常见查询建立联合索引。
十七、查看创建结果
DESCRIBE orders;
SHOW FULL COLUMNS FROM orders;
SHOW INDEX FROM orders;
SHOW CREATE TABLE orders\G
SHOW CREATE TABLE 能展示 MySQL 最终保存的完整定义,建表后应优先使用它进行复核。
十八、常见建表错误
1. Table already exists
目标表已经存在。不要直接 DROP 正式表,应先确认当前数据库和表用途,再决定修改、改名或使用迁移脚本。
2. Invalid default value
默认值与字段类型或 SQL 模式不兼容。日期、JSON、TEXT 等字段应根据当前 MySQL 8 小版本的规则定义默认值。
3. Specified key was too long
索引字段过长,常见于 utf8mb4 长字符串联合索引。应根据查询需求缩短字段、调整联合索引或使用合理的前缀索引,不要随意更换为不完整字符集。
4. Cannot add foreign key constraint
检查父子字段类型、UNSIGNED、索引、引擎和排序规则是否兼容,并为约束使用清晰且不重复的名称。
5. Duplicate key name
同一张表中索引名称重复。建议采用 pk_、uk_、idx_、fk_ 等统一命名方式。
十九、建表设计建议
- 字段名使用统一的小写下划线命名,避免保留字和含义不清的缩写。
- 根据真实数据范围选择类型,不要所有数字都使用 BIGINT。
- 金额使用 DECIMAL,时间字段明确时区和业务语义。
- 必须填写的字段使用 NOT NULL,默认值应具有明确业务含义。
- 索引围绕真实查询设计,并检查重复、冗余和低区分度索引。
- 统一使用 InnoDB 和 utf8mb4,并明确排序规则。
- 结构变更通过版本化迁移脚本管理,不要只在生产环境手动执行。
- 上线前使用接近真实数据量的测试环境验证 SQL 和查询计划。
二十、总结
MySQL 8 的 CREATE TABLE 不只是把字段排列在一起,而是在数据库层表达数据类型、完整性约束和访问方式。规范的建表语句应包含合适的字段类型、稳定主键、必要约束、面向查询的索引,以及统一的存储引擎和字符集。创建完成后使用 SHOW CREATE TABLE 复核,并通过迁移脚本持续管理结构,能够显著降低后期维护成本。