数据表设计决定了数据的正确性、查询效率和后续维护成本。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,
email VARCHAR(191) NOT NULL,
status TINYINT UNSIGNED NOT NULL DEFAULT 1,
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_users_username (username),
UNIQUE KEY uk_users_email (email)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci
COMMENT='用户表';
二、字段类型如何选择
2.1 整数类型
| 类型 | 字节 | 常见用途 |
|---|---|---|
| TINYINT | 1 | 状态、布尔值、小范围枚举 |
| SMALLINT | 2 | 较小的计数值 |
| INT | 4 | 普通编号、数量 |
| BIGINT | 8 | 大型业务主键、长期增长的流水号 |
UNSIGNED表示无符号,适合不可能为负数的 ID 和数量。不要用整数显示宽度判断存储范围,例如 INT(11)不会比 INT存储更多数据。
2.2 精确小数与浮点数
price DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
weight DOUBLE NULL
金额应使用 DECIMAL,不要使用 FLOAT 或 DOUBLE,因为浮点数存在精度误差。
2.3 字符串类型
CHAR(n):固定长度,适合长度稳定的编码。VARCHAR(n):可变长度,适合名称、标题和短文本。TEXT:适合较长正文,不建议设置普通默认值。JSON:保存结构化扩展属性,可使用 JSON 函数查询。BINARY/VARBINARY/BLOB:保存二进制数据。
VARCHAR长度表示字符数,但实际占用还受到字符集和行大小限制。大段正文通常使用 TEXT,图片和文件更适合保存到对象存储或文件系统,表中只保存地址和元数据。
2.4 日期与时间
DATE:只保存日期。TIME:保存时间或时间间隔。DATETIME:保存日期时间,受时区转换影响较少。TIMESTAMP:内部按 UTC 处理,读取时会根据会话时区转换,范围相对有限。
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
三、主键约束
3.1 单字段主键
CREATE TABLE departments (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(80) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
主键必须唯一且不能为 NULL。InnoDB 使用主键组织数据,因此主键应尽量短、稳定并且不会频繁修改。
3.2 联合主键
CREATE TABLE user_roles (
user_id BIGINT UNSIGNED NOT NULL,
role_id BIGINT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, role_id)
) ENGINE=InnoDB;
联合主键适合中间关系表。列顺序会影响索引的使用,应把最常作为查询前缀的列放在前面。
四、AUTO_INCREMENT 自增列
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
一个表只能有一个自增列,并且该列必须建立索引。自增值不保证连续,事务回滚、插入失败和并发执行都可能产生空洞,因此不能用它判断实际行数。
五、NOT NULL 与 DEFAULT
title VARCHAR(200) NOT NULL,
status TINYINT UNSIGNED NOT NULL DEFAULT 1,
remark VARCHAR(500) NULL DEFAULT NULL
能确定必须存在的数据应设置为 NOT NULL。默认值应表达明确的业务含义,不能为了避免报错而随意填入 0 或空字符串。
NULL表示未知或不存在,它与数字 0、空字符串不同。查询 NULL 必须使用:
WHERE deleted_at IS NULL
WHERE deleted_at IS NOT NULL
六、UNIQUE 唯一约束
CREATE TABLE products (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
sku VARCHAR(64) NOT NULL,
name VARCHAR(160) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_products_sku (sku)
) ENGINE=InnoDB;
一个表只能有一个主键,但可以有多个唯一索引。唯一索引中的 NULL 处理方式容易与业务预期不同,如果字段必须唯一且必须存在,应同时声明 NOT NULL。
七、CHECK 检查约束
MySQL 8.0.16 起会真正执行 CHECK 约束,可用于限制字段取值:
CREATE TABLE coupons (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
discount DECIMAL(5, 2) NOT NULL,
start_at DATETIME NOT NULL,
end_at DATETIME NOT NULL,
PRIMARY KEY (id),
CONSTRAINT chk_discount
CHECK (discount > 0 AND discount <= 100),
CONSTRAINT chk_coupon_time
CHECK (end_at > start_at)
) ENGINE=InnoDB;
复杂业务规则仍应由应用层处理,但数据库约束可以守住最基本的数据边界。
八、外键约束
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
order_no VARCHAR(40) NOT NULL,
amount DECIMAL(12, 2) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_orders_order_no (order_no),
KEY idx_orders_user_id (user_id),
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT
) ENGINE=InnoDB;
建立外键时需要注意:
- 父表和子表通常都使用 InnoDB。
- 关联列的数据类型、长度和符号属性必须一致。
- 被引用列必须有合适的索引。
- 要明确设计
RESTRICT、CASCADE或SET NULL。
ON DELETE CASCADE会自动删除子表数据,使用前必须评估误删影响。高并发、分库分表或跨服务系统也可能选择由应用维护关联关系。
九、普通索引与联合索引
CREATE TABLE articles (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
category_id BIGINT UNSIGNED NOT NULL,
title VARCHAR(200) NOT NULL,
status TINYINT UNSIGNED NOT NULL DEFAULT 1,
published_at DATETIME NULL,
PRIMARY KEY (id),
KEY idx_articles_category (category_id),
KEY idx_articles_status_time (status, published_at)
) ENGINE=InnoDB;
索引应根据真实查询设计,不是越多越好。每个索引都会占用空间,并增加 INSERT、UPDATE 和 DELETE 的维护成本。联合索引遵循最左前缀原则,列顺序需要结合等值条件、范围条件、排序和区分度分析。
十、字符集与排序规则
CREATE TABLE tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_tags_name (name)
) ENGINE=InnoDB
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;
MySQL 8 推荐使用 utf8mb4。排序规则决定字符串比较和排序方式,例如是否区分大小写、重音符号。有关联、比较或联合查询的字符串字段应尽量保持字符集和排序规则一致。
十一、存储引擎和表选项
MySQL 8 通常使用 InnoDB,它支持事务、行级锁、崩溃恢复和外键。
CREATE TABLE operation_logs (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
content JSON NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COMMENT='操作日志';
不要在没有明确依据时调整行格式、页大小等底层参数。表级 COMMENT 和字段注释可以提高可维护性,但不能代替正式的数据字典。
十二、生成列
生成列的值由表达式计算,可选择虚拟列或存储列:
CREATE TABLE order_items (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
price DECIMAL(12, 2) NOT NULL,
quantity INT UNSIGNED NOT NULL,
total_amount DECIMAL(14, 2)
GENERATED ALWAYS AS (price * quantity) STORED,
PRIMARY KEY (id)
) ENGINE=InnoDB;
生成列适合稳定、确定性的计算逻辑,也可以为 JSON 中的常用属性创建生成列并建立索引。
十三、复制表结构
13.1 只复制结构和索引
CREATE TABLE users_backup LIKE users;
13.2 根据查询结果创建表
CREATE TABLE active_users AS
SELECT id, username, email
FROM users
WHERE status = 1;
CREATE TABLE ... AS SELECT主要复制查询结果和推导出的列定义,不会完整复制原表的主键、索引、外键、默认值和自增属性。需要完整结构时应使用 LIKE,再单独插入数据。
十四、查看建表信息
-- 查看标准建表语句
SHOW CREATE TABLE users\G
-- 查看字段
SHOW FULL COLUMNS FROM users;
-- 查看索引
SHOW INDEX FROM users;
-- 查看表状态
SHOW TABLE STATUS LIKE 'users'\G
SHOW CREATE TABLE是检查实际表结构最可靠的方法之一,因为图形工具可能隐藏字符集、索引和约束细节。
十五、综合建表示例
CREATE TABLE blog_posts (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '文章ID',
author_id BIGINT UNSIGNED NOT NULL COMMENT '作者ID',
title VARCHAR(200) NOT NULL COMMENT '标题',
slug VARCHAR(191) NOT NULL COMMENT '访问标识',
summary VARCHAR(500) NULL COMMENT '摘要',
content LONGTEXT NOT NULL COMMENT '正文',
status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0草稿 1发布',
view_count BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '阅读量',
published_at DATETIME 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_blog_posts_slug (slug),
KEY idx_blog_posts_author (author_id),
KEY idx_blog_posts_status_time (status, published_at),
CONSTRAINT chk_blog_posts_status CHECK (status IN (0, 1)),
CONSTRAINT fk_blog_posts_author
FOREIGN KEY (author_id) REFERENCES users (id)
ON UPDATE RESTRICT ON DELETE RESTRICT
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci
COMMENT='博客文章表';
十六、常见错误与设计建议
- 金额使用 FLOAT,导致精度误差,应改用 DECIMAL。
- 所有字段都使用 VARCHAR,失去类型校验和高效比较能力。
- 主键类型过小,业务增长后达到上限。
- 字符串字段字符集或排序规则不一致,导致关联查询报错或索引失效。
- 只创建字段不设计索引,查询量增长后性能明显下降。
- 索引过多,导致写入性能下降和存储浪费。
- 滥用
ON DELETE CASCADE,误删时扩大影响范围。 - 把空字符串、0 和 NULL 混为一谈,造成业务语义混乱。
- 使用保留字作为表名或字段名,增加 SQL 编写和迁移成本。
十七、总结
MySQL 8 建表的核心不是记住语法,而是用数据库结构准确表达业务规则。合理的数据类型可以减少空间和错误,主键与索引决定查询效率,约束负责守住数据边界,字符集和存储引擎则影响兼容性与可靠性。
正式建表前,应结合实际查询、数据增长速度、事务要求和归档策略进行评审;建表后使用 SHOW CREATE TABLE 再次核对最终结构。