悠悠楠杉
网站页面
MySQL创建表语句详解：从语法到实战案例
07/10
一、MySQL创建表基础语法解析
创建表是数据库设计的核心操作，标准的MySQL创建表语句包含以下要素：
sql
CREATE TABLE [IF NOT EXISTS] 表名 (
列名1 数据类型 [约束条件],
列名2 数据类型 [约束条件],
...
[PRIMARY KEY (列名)],
[FOREIGN KEY (列名) REFERENCES 目标表(列名)],
[INDEX 索引名 (列名)]
) [ENGINE=存储引擎] [DEFAULT CHARSET=字符集];
这个结构中，方括号内为可选内容，但实际开发中我们通常会指定存储引擎和字符集。现代MySQL默认使用InnoDB引擎，推荐统一使用UTF-8MB4字符集支持emoji等特殊字符。
二、5个典型创建表示例
示例1：基础用户表
sql
CREATE TABLE `users` (
`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL COMMENT '登录账号',
`password` CHAR(60) NOT NULL COMMENT 'BCrypt加密密码',
`email` VARCHAR(100) UNIQUE,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`is_active` TINYINT(1) DEFAULT 1,
PRIMARY KEY (`user_id`),
INDEX `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
设计要点：
- 使用无符号INT自增主键
- 密码字段采用固定长度CHAR类型（适合哈希值存储）
- 为username建立索引提升查询效率
- 使用utf8mb4字符集确保兼容性
示例2：带外键的商品订单表
sql
CREATE TABLE `orders` (
`order_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT UNSIGNED NOT NULL,
`order_no` VARCHAR(32) NOT NULL COMMENT '业务订单号',
`total_amount` DECIMAL(10,2) NOT NULL,
`status` ENUM('pending','paid','shipped','completed') DEFAULT 'pending',
`payment_time` DATETIME NULL,
PRIMARY KEY (`order_id`),
UNIQUE KEY `uk_order_no` (`order_no`),
CONSTRAINT `fk_user_order` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB;
外键约束细节：
- ON DELETE CASCADE表示用户删除时同步删除其订单
- ON UPDATE RESTRICT禁止直接修改用户ID
示例3：含JSON字段的产品表（MySQL 5.7+）
sql
CREATE TABLE `products` (
`product_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`price` DECIMAL(10,2) NOT NULL,
`specs` JSON NOT NULL COMMENT '商品规格JSON',
`stock` INT DEFAULT 0,
`tags` JSON DEFAULT NULL COMMENT '商品标签数组',
PRIMARY KEY (`product_id`),
FULLTEXT INDEX `ft_name` (`name`) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
JSON字段优势：
- 灵活存储非结构化数据
- 支持JSON路径查询如：WHERE specs->"$.cpu" = 'i7'
示例4：分区表示例（按日期范围）
sql
CREATE TABLE `access_logs` (
`log_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT UNSIGNED,
`access_time` DATETIME NOT NULL,
`ip_address` VARCHAR(45) NOT NULL,
`action` VARCHAR(20) NOT NULL,
PRIMARY KEY (`log_id`, `access_time`)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(access_time)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
示例5：临时表与内存表
sql
-- 会话级临时表
CREATE TEMPORARY TABLE temp_cart (
item_id INT NOT NULL,
quantity INT DEFAULT 1,
session_id VARCHAR(40) NOT NULL
);
-- 内存表（注意数据持久性问题）
CREATE TABLE cache_data (
cache_key VARCHAR(255) NOT NULL,
cache_value LONGTEXT NOT NULL,
expire_time DATETIME NOT NULL,
PRIMARY KEY (cache_key)
) ENGINE=MEMORY;
三、表设计最佳实践
命名规范：
- 表名使用小写复数形式（如users/products）
- 字段名使用snake_case命名法
- 避免使用MySQL保留字
字段选择原则：
- 自增ID适合做代理主键，业务主键需另设唯一索引
- 金额类数据必须使用DECIMAL
- TEXT/BLOB类型尽量避免（影响查询性能）
索引策略：
- 每张表索引不宜超过5个
- 联合索引遵循最左匹配原则
- 频繁更新的字段不适合建索引
字符集选择：
- 统一使用utf8mb4字符集
- 排序规则根据业务需求选择：
- utf8mb4generalci（不区分大小写）
- utf8mb4_bin（二进制精准匹配）
四、常见问题解决方案
问题1：如何修改已存在的表结构？
使用ALTER TABLE语句：sql
-- 添加新字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
-- 修改字段类型
ALTER TABLE products MODIFY COLUMN price DECIMAL(12,2);
-- 删除索引
ALTER TABLE orders DROP INDEX ukorderno;
问题2：大表结构变更如何避免锁表？
MySQL 8.0+可以使用INSTANT算法：
sql
ALTER TABLE access_logs ADD COLUMN user_agent VARCHAR(200), ALGORITHM=INSTANT;
对于更早版本，可以考虑使用pt-online-schema-change工具。
问题3：如何查看表结构信息？
sql
-- 基础结构
DESC users;
-- 完整DDL语句（含所有约束）
SHOW CREATE TABLE orders;
通过掌握这些表创建技术，你已经能够应对90%的日常数据库设计需求。实际开发中建议配合数据库建模工具（如MySQL Workbench）进行可视化设计，再导出SQL脚本。
