MySQL 数据库表创建过程_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;