MySQL CREATE TABLE

一、CREATE TABLE 是什么？

CREATE TABLE 用于 定义表结构，一次性确定：

表名
字段类型
是否可空
默认值
主键 / 唯一键 / 普通索引
外键
存储引擎
字符集 / 排序规则

📌 建表是否合理，决定了数据库 80% 的性能上限

二、基础语法结构（标准模板）

CREATE TABLE table_name (
    column_name data_type [column_constraint],
    ...
    table_constraint
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

三、字段定义详解（最重要）

1️⃣ 字段基本属性

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',

常用关键字：

关键字	含义
NOT NULL	不允许 NULL
NULL	允许 NULL
DEFAULT	默认值
AUTO_INCREMENT	自增
COMMENT	字段注释

2️⃣ 数据类型选型

🔢 整数类型

类型	范围	备注
TINYINT	-128~127	状态位
INT	~21亿	常用
BIGINT	更大	ID / 雪花

⚠️ INT(11) 的 11 不是长度限制（仅显示宽度，MySQL 8 已废弃）

🔤 字符串类型

类型	特点
CHAR	定长
VARCHAR	变长（推荐）
TEXT	大文本（不建议频繁查询）

📌 建议：

普通字符串：VARCHAR
大文本：TEXT（单独表）

🕒 时间类型（强烈建议）

类型	说明
DATE	日期
DATETIME	日期时间（推荐）
TIMESTAMP	带时区、2038 问题

📌 业务时间字段优先 DATETIME

💰 精度类型

类型	用途
DECIMAL(10,2)	金额
FLOAT / DOUBLE	非精确

3️⃣ UNSIGNED 的重要性

id BIGINT UNSIGNED

可用范围翻倍
不允许负数
推荐用于 ID / 计数器

四、约束（Constraints）

1️⃣ 主键（PRIMARY KEY）

PRIMARY KEY (id)

规则：

唯一
非空
InnoDB 聚簇索引

📌 建议使用 BIGINT UNSIGNED 自增 / 雪花 ID

2️⃣ 唯一约束（UNIQUE）

UNIQUE KEY uk_email (email)

自动创建唯一索引
允许多个 NULL（InnoDB）

3️⃣ 外键（不推荐高并发使用）

CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES user(id)

📌 生产建议：

逻辑外键（代码控制）
避免物理外键带来的锁和性能问题

4️⃣ CHECK（MySQL 8 开始支持）

CHECK (status IN (0,1))

五、索引定义（建表即优化）

1️⃣ 普通索引

KEY idx_name (name)

2️⃣ 联合索引（非常重要）

KEY idx_a_b (a, b)

遵循 最左前缀原则

3️⃣ 覆盖索引优化

KEY idx_name_age (name, age)

六、表级选项（ENGINE / CHARSET）

ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;

🔧 存储引擎

引擎	说明
InnoDB	默认，事务 / 行锁
MyISAM	不支持事务（不推荐）

🔤 字符集选型

utf8mb4：支持 Emoji（强烈推荐）
不要用 utf8（不完整）

七、完整标准建表示例（企业模板）

CREATE TABLE user (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    username VARCHAR(64) NOT NULL COMMENT '用户名',
    email VARCHAR(128) NOT NULL COMMENT '邮箱',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态',
    balance DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '余额',
    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_email (email),
    KEY idx_username_status (username, status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COMMENT='用户表';

八、CREATE TABLE 常见坑

❌ 1. 没有主键

InnoDB 会创建隐藏主键
性能差
主从复制问题多

❌ 2. TEXT 字段建索引

占空间
慢
必须前缀索引

❌ 3. 默认 NULL 滥用

NULL 判断复杂
索引效率低

❌ 4. 没建索引就上线

UPDATE / DELETE 锁表
慢查询频发

❌ 5. 字符集混乱

排序不一致
索引失效
联表异常

九、建表最佳实践

表必有主键
主键递增（减少页分裂）
字段尽量 NOT NULL + DEFAULT
金额用 DECIMAL
状态用 TINYINT
时间统一 DATETIME
索引建在 WHERE / JOIN / ORDER BY 上
禁用物理外键

十、其它

Q：为什么 InnoDB 必须有主键？

A：主键是聚簇索引，决定数据物理顺序。

Q：VARCHAR 最大多长？

A：理论 65535 字节（受字符集影响）

Q：AUTO_INCREMENT 一定连续吗？

A：不保证（回滚/并发）

Q：为什么不推荐外键？

A：性能差、锁多、扩展性差

🔚 总结

CREATE TABLE 是数据库设计的起点，索引和字段类型的选择，直接决定系统的上限。