MySQL 数据库表创建-EW帮帮网

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

MySQL 数据库表创建