MySQL 数据库表创建 - 详解

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, DECIMAL

DECIMAL(10,2)固定精度，适合金额存储

字符串

VARCHAR(255), CHAR(1)

VARCHAR按需分配空间，CHAR固定长度

日期

DATE, DATETIME, TIMESTAMP

TIMESTAMP自动时区转换（1970-2038年），DATETIME支持更大范围

大文本

TEXT, LONGTEXT

TEXT约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）单独建表存储