MySQL 数据库表创建详解
一、核心语法结构
CREATE TABLE [IF NOT EXISTS] table_name (
column1 datatype [column_constraints],
column2 datatype [column_constraints],
...
[table_constraints]
)
[ENGINE = storage_engine]
[DEFAULT] CHARACTER SET charset_name
[COLLATE collation_name]
[COMMENT 'table_comment'];
参数说明：
IF NOT EXISTS：可选，避免表已存在时报错table_name：表名，遵循命名规范（建议小写+下划线）column_constraints：字段级约束（如NOT NULL、DEFAULT等）table_constraints：表级约束（如PRIMARY KEY、FOREIGN KEY等）storage_engine：指定存储引擎（如InnoDB、MyISAM）charset_name：字符集（推荐utf8mb4支持emoji）collation_name：排序规则（影响字符串比较）table_comment：表注释，强烈建议添加
二、关键组件解析
字段定义 (field)
column_name datatype [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] [UNIQUE] [PRIMARY KEY] [COMMENT 'string']
详细规则：
命名规则：
允许字符：字母(a-z)、数字(0-9)、美元符($)、下划线(_)长度限制：不超过64个字符避免使用：MySQL保留字（如SELECT、WHERE）命名风格：建议全小写+下划线（user_name）
完整示例：
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
username VARCHAR(30) NOT NULL UNIQUE COMMENT '用户名',
status TINYINT(1) DEFAULT 1 COMMENT '状态:1启用0禁用',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
数据类型 (datatype)
类别常用类型说明整数INT, TINYINT, BIGINT可选UNSIGNED（无符号整数），如TINYINT UNSIGNED范围0-255浮点数FLOAT, DOUBLE, DECIMALDECIMAL(10,2)固定精度，适合金额存储字符串VARCHAR(255), CHAR(1)VARCHAR按需分配空间，CHAR固定长度日期DATE, DATETIME, TIMESTAMPTIMESTAMP自动时区转换（1970-2038年），DATETIME支持更大范围大文本TEXT, LONGTEXTTEXT约64KB，LONGTEXT约4GB，适合存储JSON/HTML内容二进制BLOB, LONGBLOB通常只存储文件路径而非实际二进制数据
选型建议：
状态字段：TINYINT用户ID：INT UNSIGNED或BIGINT UNSIGNED用户名：VARCHAR(20-50)密码哈希：CHAR(60)（适合Bcrypt固定长度哈希值）金额：DECIMAL(10,2)文章内容：LONGTEXT
字符集与校对规则
优先级顺序：字段级 > 表级 > 数据库级 > 服务器级
推荐设置：
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
字符集对比：
utf8：基本多语言平面（3字节/字符）utf8mb4：完整Unicode（4字节/字符），支持emoji表情
常用校对规则：
utf8mb4_general_ci：简单排序规则utf8mb4_unicode_ci：准确Unicode排序（推荐）utf8mb4_bin：二进制比较（区分大小写）
存储引擎 (ENGINE)
引擎关键特性适用场景InnoDB事务支持、行级锁、外键约束交易系统、需要ACID特性的表（默认）MyISAM表级锁、全文索引、高读取性能数据仓库、日志表（MySQL 5.7前）MEMORY内存存储、极速访问、重启数据丢失临时会话表、高速缓存Archive高压缩比(约10:1)、只支持INSERT/SELECT历史归档数据
迁移引擎示例：
ALTER TABLE logs ENGINE=Archive;
三、完整创建示例
CREATE TABLE IF NOT EXISTS `users` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`username` VARCHAR(50) NOT NULL COMMENT '用户名',
`password` CHAR(60) NOT NULL COMMENT 'Bcrypt加密密码', -- 固定60字符长度
`email` VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
`phone` VARCHAR(20) NULL COMMENT '手机号',
`birthday` DATE NULL COMMENT '出生日期',
`gender` ENUM('M','F','O') NULL COMMENT '性别:M男,F女,O其他',
`balance` DECIMAL(10,2) UNSIGNED DEFAULT 0.00 COMMENT '账户余额',
`status` TINYINT(1) UNSIGNED DEFAULT 1 COMMENT '状态:1正常0冻结',
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_username` (`username`), -- 唯一约束
INDEX `idx_email` (`email`(20)), -- 前缀索引
INDEX `idx_phone` (`phone`), -- 普通索引
INDEX `idx_status` (`status`) -- 低基数索引
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci
COMMENT='平台用户信息表';
设计要点：
主键自增INT/BIGINT密码使用固定长度CHAR存储哈希值金额使用DECIMAL避免精度问题枚举字段使用ENUM限定值范围自动管理时间戳字段为高频查询字段创建索引
四、字段约束详解
约束类型语法示例作用说明主键约束PRIMARY KEY (id)唯一标识记录，自动创建聚集索引，InnoDB表必须包含主键唯一约束UNIQUE KEY (email)保证列值唯一性，允许NULL值非空约束NOT NULL插入时必须提供值，与DEFAULT配合使用默认值DEFAULT 0未显式指定值时自动填充自增长AUTO_INCREMENT整数列自动递增（通常用于主键），注意并发问题外键约束FOREIGN KEY (dept_id) REFERENCES departments(id)确保引用完整性，InnoDB支持检查约束CHECK (age > 0)MySQL 8.0+支持的自定义验证条件
外键高级用法：
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE -- 级联删除
ON UPDATE SET NULL -- 引用更新时置空
五、表操作命令
查看表结构
-- 基础结构
DESC users;
-- 完整建表语句（含所有选项）
SHOW CREATE TABLE users;
-- 查看表信息
SHOW TABLE STATUS LIKE 'users';
修改表结构
-- 添加字段
ALTER TABLE users
ADD COLUMN wechat VARCHAR(30) NULL COMMENT '微信号' AFTER phone,
ADD COLUMN last_login DATETIME NULL COMMENT '最后登录时间';
-- 修改字段
ALTER TABLE users
MODIFY COLUMN email VARCHAR(150) NOT NULL COMMENT '电子邮箱',
CHANGE COLUMN phone mobile VARCHAR(20) NULL COMMENT '手机号码';
-- 删除字段
ALTER TABLE users
DROP COLUMN obsolete_field;
-- 添加索引
ALTER TABLE users
ADD INDEX idx_wechat (wechat),
ADD FULLTEXT INDEX ft_idx_username (username);
删除表
-- 安全删除（表不存在时不报错）
DROP TABLE IF EXISTS temp_users;
-- 清空表数据
TRUNCATE TABLE user_logs; -- 比DELETE更快且重置自增值
六、最佳实践建议
命名规范
表名：
使用小写字母+下划线（snake_case）多对多关系表用relation1_relation2格式（如user_roles）
字段名：
避免使用数据类型作为名称（如text_value）布尔字段用is_前缀（is_active）
字段设计原则
数据类型优化：
IP地址：建议VARCHAR(45)（兼容IPv6）固定长度代码：CHAR(2)（如国家代码）JSON数据：MySQL 5.7+直接使用JSON类型
特殊场景处理：
-- 软删除设计
ADD COLUMN is_deleted TINYINT(1) DEFAULT 0 COMMENT '是否删除',
ADD COLUMN deleted_at TIMESTAMP NULL COMMENT '删除时间';
-- 树形结构
ADD COLUMN parent_id INT UNSIGNED NULL COMMENT '父节点ID',
ADD COLUMN tree_path VARCHAR(255) NULL COMMENT '路径:1,5,22';
索引优化
-- 联合索引（注意顺序）
CREATE INDEX idx_name_phone ON customers(last_name, first_name, phone);
-- 前缀索引（长文本字段）
CREATE INDEX idx_product_desc ON products(description(20));
-- 覆盖索引优化
ALTER TABLE orders ADD INDEX idx_cover_user (user_id, status, create_time);
字符集统一
-- 创建数据库时指定
CREATE DATABASE myapp
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
-- 修改已有表字符集
ALTER TABLE products CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
七、存储引擎选择指南
生产环境建议：
默认使用InnoDB引擎日志类表可考虑Archive引擎（如操作日志）避免使用MyISAM（崩溃后易损坏且修复慢）
关键注意事项：
每个表必须包含主键（推荐自增INT/BIGINT）时间字段使用TIMESTAMP自动管理（或DATETIME）为所有字段添加COMMENT注释避免使用ENUM类型（改用关联表或CHECK约束）大字段（如TEXT/BLOB）单独建表存储