技术 · 思考 · 实践 · 成长

首页/MySQL8专栏/正文

MySQL 8 CREATE TABLE 完整语法:字段、约束、索引与表选项

MySQL8专栏537 次阅读

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、BIGINTid BIGINT UNSIGNED
精确小数DECIMALprice DECIMAL(10,2)
短字符串CHAR、VARCHARtitle VARCHAR(200)
长文本TEXT、MEDIUMTEXT、LONGTEXTcontent MEDIUMTEXT
日期时间DATE、DATETIME、TIMESTAMPcreated_at DATETIME
二进制数据BINARY、VARBINARY、BLOBfile_hash BINARY(32)
结构化文档JSONsettings 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 复核,并通过迁移脚本持续管理结构,能够显著降低后期维护成本。

再长的路,一步步也能走完,再短的路,不迈开双脚也无法到达

评论交流

0 条评论 · 支持多级回复
验证码
暂无评论,来说说你的想法吧

相关文章

继续阅读