MySQL建库建表笔记

MySQL 建库建表笔记

目录

• 一、建库：先把库跑起来
• 二、字符集：utf8 还是 utf8mb4
• 三、命名规范
• 四、数据类型速查
• 五、建表
• 六、修改表和删除表
• 七、复制表
• 八、约束
• 九、存储引擎
• 十、完整建表语句模板
• 总结

---

一、建库：先把库跑起来

最常用的几条命令，先背下来：

-- 新建数据库
create database 数据库名;

-- 设为当前数据库
use 数据库名;

-- 查看当前使用的数据库（没有选中库时返回 NULL）
select database();

-- 显示所有数据库
show databases;

-- 删除数据库
drop database 数据库名;

几个我踩过的坑

1. database 是关键字，直接当库名会报错。

mysql> create database database;
ERROR 1064 (42000): You have an error in your SQL syntax; ...

用反引号包起来就没事了：

mysql> create database `database`;
Query OK, 1 row affected (0.01 sec)

反引号 ` 的作用就是避免和 SQL 语法冲突，顺便让代码更规范。
我的习惯是：保留字和特殊字符必须加，普通表名列名不用加，但为了统一风格我还是会加上。
另外别拿关键字当库名表名实在不优雅。

2. database 没有 s，但 show databases 有 s。

• show databases —— 复数，看所有库
• show create database xxx —— 单数，看建库语句

这两个最容易写混。

3. 库已存在会直接报错。

mysql> create database testdb;
ERROR 1007 (HY000): Can't create database 'testdb'; database exists

所以我写建库脚本时习惯带上 if not exists：create database if not exists 库名;

4. use 后面可以不加分号。

mysql> use testdb;
Database changed

5. 查建库语句用 \G 纵向显示，不然长语句会折行看不清楚。

mysql> show create database testdb\G
*************************** 1. row ***************************
       Database: testdb
Create Database: CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

这行可以拆成三段看：

片段	含义
CREATE DATABASE \testdb``	创建名为 testdb 的库
/*!40100 ... */	注释形式的字符集设置：utf8mb4 + utf8mb4_0900_ai_ci（ai 不区分重音、ci 不区分大小写）
/*!80016 DEFAULT ENCRYPTION='N' */	数据库未加密

系统自带的 4 个库

库名	存什么
information_schema	数据字典，库表列的元信息都在这（数据字典表）
performance_schema	服务器性能参数
mysql	用户权限、账号信息
sys	5.7 新增，把前两个库以视图方式组合起来，方便人看

这四个别删。

---

二、字符集：utf8 还是 utf8mb4

MySQL 里字符集是两个概念，容易混：

• Character Set（字符集）：字符的编码规则，决定字符怎么存、占几个字节、支持哪些字符
• Collation（字符序）：排序和比较的规则集合，比如 A>B>a>b 还是 a>b>A>B

为什么必须用 utf8mb4

MySQL 5.7 默认字符集是 latin1，8.0 是 utf8mb4。

关键是 MySQL 里的 utf8 其实只是 utf8mb3 的别名，一个字符最多占 3 字节，只能编码到 Unicode 的 0xffff，也就是基本多文种平面（BMP）。

这就意味着：Emoji 表情、很多生僻字、任何新增的 Unicode 字符，MySQL 的 utf8 都存不进去，一存就报错。

utf8mb4 里的 mb4 = most bytes 4，是真正的 4 字节 UTF-8，完整支持 Unicode，而且是 utf8mb3 的超集，不用做额外转换。

我的结论：8.0 之后，库表和程序里一律 utf8mb4，别再写 utf8 了。

选用规则

• 面向国外业务、多种语言 → utf8mb4
• 只支持中文、不涉及国外业务 → 可以考虑 GBK 换性能（但 GBK 不是国际通用字符集）

常用查看命令

show character set;                    -- 所有支持的字符集
show character set like 'utf%';
show collation;                        -- 所有字符序
show variables like 'character_set%';  -- 当前字符集设置

字符序的命名规则是「字符集名 + 国家名（或 general）+ 后缀」：

后缀	含义
ci	大小写不敏感
cs	大小写敏感
ai	口音不敏感
as	口音敏感
bin	二进制比较
ks	假名敏感

utf8mb4_0900_ai_ci 就是：utf8mb4 字符集 + Unicode 9.0 + 不区分口音 + 不区分大小写。

character_set% 的变量里，我重点关注两个：character_set_database（库默认字符集）和 character_set_server（服务器默认字符集）。

---

三、命名规范

通用规则

• 用有意义的英文词，中间用下划线 _ 分隔
• 只能由字母、数字、#、_、$ 组成，且必须以英文字母开头
• 不要用 MySQL 保留字
• 统一小写
• 同一类对象里不能重名

生产环境约定

数据库：一般不超过 30 个字符，格式是「项目名 + 库含义简写」，比如 IM 项目的工作流库叫 im_flow，且用小写。

表：

类型	命名规则	示例
常规表	t_ + 模块 + 表	t_user_eduinfo
临时表	temp_ + 模块 + 表 + 日期	temp_user_eduinfo_20240520
备份表	bak_ + 模块 + 表 + 日期	bak_user_eduinfo_20231219

同模块的表尽量用相同前缀，表名尽量控制在 30 字符以内。

---

四、数据类型速查

整型

类型	字节	带符号范围	无符号范围
tinyint	1	-128 ~ 127	0 ~ 255
smallint	2	-32768 ~ 32767	0 ~ 65535
mediumint	3	-8388608 ~ 8388607	0 ~ 16777215
int	4	-2147483648 ~ 2147483647	0 ~ 4294967295
bigint	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551616

选型心得：一定要给每列挑最合适的大小。
比如一个只有 0/1 两种值的枚举字段，用 TINYINT 就够，非要上 BIGINT 是纯浪费。100 万行数据就白白多占 700 万字节（约 6.7 MB），表一多就很可观。

另外 int(10) 里的 10 不是存储宽度，只是显示宽度，不够补 0，超了直接显示全部数字。
int 永远是 4 字节。MySQL 8.0.17 之后这个写法已经不推荐了，直接写 int 就行。

浮点与定点

类型	字节	说明
float	4	单精度浮点
double	8	双精度浮点
decimal	-	定点型，金额用它

可以写 float(M,D)、double(M,D) 来限制宽度和精度，不指定就按实际精度处理。

文本类型

类型	说明
char(n)	定长，0~255，不够用空格补，尾部空格会被去掉
varchar(n)	变长，最多 255，超过自动转 TEXT
tinytext / text / mediumtext / longtext	大文本，text 存 65535 字符
enum	单选
set	多选，最多 64 个列表项

char 适合长度固定的内容（手机号、身份证号），varchar 适合长度不固定的内容。

blob 和 text 都是存大数据的，区别是：blob 二进制形式存储，没有排序规则和字符集；text 字符形式存储，有排序规则和字符集。文章内容、评论这类用 text。

日期时间类型

类型	字节	格式
date	3	yyyy-MM-dd
time	3	HH:mm:ss
year	1	yyyy
datetime	8	yyyy-MM-dd HH:mm:ss
timestamp	4	yyyy-MM-dd HH:mm:ss，可当时间戳

---

五、建表

语法

create table [if not exists] 表名 (
    列名1 列类型1 [auto_increment] [default 默认值] [列约束] [comment '注释'],
    列名2 列类型2 [auto_increment] [default 默认值] [列约束] [comment '注释'],
    ...,
    [表约束]
) [engine=存储引擎] [default charset=字符集];

三个必须记住的点

1. 表属于数据库，建表前必须先 use 到对应库，否则报 No database selected
2. 表名不区分大小写，不能是 SQL 关键字（DROP、ALTER、INSERT 等）
3. 必须指定每一列的名称和类型，多列用逗号隔开；表已存在会报错，用 if not exists 兜底

完整示例

mysql> create database mydb2_stuinfo;
mysql> use mydb2_stuinfo;
mysql> create table student1(
    ->   id   int unsigned,
    ->   name varchar(30),
    ->   sex  char(2),
    ->   age  int unsigned,
    ->   score float,
    ->   addr varchar(50)
    -> );
Query OK, 0 rows affected (0.02 sec)

查看表

show tables;                            -- 当前库的所有表
show tables from mydb3_employee;        -- 看别的库的表
show tables like 'stu%';                -- 通配符匹配

desc student1;                          -- = describe student1
show columns from student1;             -- 同上

show create table student1\G            -- 完整建表语句（\G 纵向显示）

desc 的字段含义：

字段	含义
Field	字段名
Type	字段类型
Null	是否允许为空
Key	索引类型（PRI 主键 / UNI 唯一 / MUL 普通索引）
Default	默认值
Extra	额外信息，auto_increment 就在这里

show create table 的输出结尾会带存储引擎和字符集，比如：

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

顺便提一句，所有库表列的元信息都在系统库 information_schema 里，需要的时候可以查它。

删除表

drop table 表名;
drop table if exists 表名;   -- 推荐写法，先判断存在再删

---

六、修改表和删除表

-- 改表名（两种写法都行）
alter table student2 rename student3;
rename table student3 to teacher1;

-- 新增列
alter table 表名 add 列名 列类型;                    -- 加到最后
alter table 表名 add 列名 列类型 after 某列;        -- 加到指定列后面
alter table 表名 add 列名 列类型 first;              -- 加到第一列

-- 删除列
alter table 表名 drop 列名;

-- 修改列名
alter table 表名 change 旧列名 新列名 列类型;

-- 修改列类型
alter table 表名 modify 列名 新类型;

-- 修改列位置
alter table 表名 modify 列名 列类型 after 某列;
alter table 表名 modify 列名 列类型 first;

注意：没有 before 关键字，只有 after 和 first。
另外 modify 和 change 都会重写整列定义，原来的约束如果没写出来就丢了，动手前先 desc 看一眼。

---

七、复制表

-- 只复制表结构（字段、类型、索引、约束）
create table 新表名 like 源表;

-- 复制表结构 + 数据
create table 新表名 select * from 源表;

-- 只复制数据
insert into 新表名 select * from 源表;

做备份或者搭测试表的时候，这三条基本够用了。

---

八、约束

为什么需要约束

数据类型只能约束「是不是这个类型」，但约束不了「这个值合不合理」。比如身份证号字段，光是 varchar 类型显然不够，还得要求它唯一。

约束 = 表上强制执行的数据校验规则，本质是 MySQL 通过限制用户操作来维护数据安全和完整性。

四种完整性：

完整性	含义	例子
实体完整性	不能有两条完全相同、无法区分的记录	主键唯一
域完整性	值必须在合理范围内	年龄 0~120、性别 男/女
引用完整性	引用的数据必须存在	员工所在部门要在部门表里能找到
用户自定义完整性	业务规则	用户名唯一、密码不能为空

补充一点：约束不只出现在数据库层，业务代码里同样可以做校验，两边配合效果最好。

列级约束 vs 表级约束

-- 列级：定义列的同时写约束
create table 表名 (列名 类型 约束);

-- 表级：所有列定义完再写
create table 表名 (列信息, constraint 约束名 约束类型(列名));

约束名推荐格式：表名_列名_约束类型，比如 t_student_id_pk。

表级约束不能直接修改，只能先删掉再加新的。

1. 非空约束 NOT NULL

alter table 表名 modify 列名 类型 not null;
alter table 表名 modify 列名 类型 null;    -- 取消非空

实际开发尽量别留空值，空数据没法参与运算。
记牢：空字符串 '' 不等于 NULL，0 也不等于 NULL，所有类型的值都可以是 NULL。

2. 默认值约束 DEFAULT

alter table 表名 modify 列名 类型 default '值';

插入时不显式给这个字段，就用默认值。

坑：如果某字段同时设了 not null 和 default，那 not null 就没意义了 —— 不传值走默认值，not null 永远不会触发。

3. 列描述 COMMENT

alter table 表名 modify 列名 类型 comment '说明文字';

字段多了以后，注释就是救命的东西。

4. 主键约束 PRIMARY KEY

create table t1 (
    id   int unsigned primary key comment '学号',
    name varchar(20) not null,
    sex  char(2) default '男'
);

alter table 表名 add primary key (列名);    -- 表级主键
alter table 表名 drop primary key;          -- 删除

特点：

• 主键字段不能为空、不能重复
• 一张表最多一个主键
• 通常用整型
• 创建主键时会自动建立主键索引（按主键查效率更高）；删掉主键约束，索引也跟着没了
• 不要随意修改主键的值，它是记录的唯一标识，改了容易破坏数据完整性

联合主键：

create table t2 (
    id       int,
    hostname varchar(10),
    ip       varchar(20),
    port     int unsigned,
    primary key (ip, port)
);

5. 主键自增长 AUTO_INCREMENT

alter table 表名 modify 列名 类型 auto_increment;

规则：

• 必须是整数类型
• 必须建在有索引的列上（desc 里 Key 那栏要有值）
• 一张表最多一个自增列
• 默认从 1 开始递增
• 可以手动插入值，只要不重复，之后新数据从最大值继续递增

6. 唯一性约束 UNIQUE

alter table 表名 add constraint 约束名 unique (列名);
alter table 表名 drop index 约束名;

和主键的区别，我这么理解：

• 主键：标识唯一性，是「查数据用」的钥匙
• 唯一键：保证业务字段不重复，是「防脏数据用」的

两者不是对立关系，是互补关系。一张表可以有一个主键 + 多个唯一键。

比如学生表：身份证号当主键，学号不能重复 —— 学号就设成唯一键。唯一键允许为 NULL（多个 NULL 不算重复）。

坑：唯一键冲突时，自增会出现跳号（比如 id 跳到 5 去了），这是正常现象，不是 bug。

7. 外键约束 FOREIGN KEY

-- 语法
foreign key (从表的字段) references 主表(主表的字段)

-- 建表时加
create table 子表 (
    ...,
    class_id int,
    foreign key (class_id) references 班级表(id)
);

-- 建表后加
alter table 从表 add constraint 约束名 foreign key (从表字段) references 主表(主表字段);
alter table 从表 drop foreign key 约束名;

几个概念：

• 主表（父表）：被引用的那张表
• 从表（子表）：引用别人的那张表
• 外键约束定义在从表上，主表必须有主键或唯一键约束

要求：

1. 主表必须已存在，或者就是当前正在建的表（自参照表）
2. 外键列的个数要和主表主键的列数一致（联合主键时）
3. 外键列的数据类型必须和主表主键对应列完全相同
4. 外键列可以是 NULL，表示还没关联上（比如学生还没分班）

作用就是给表之间加了一层校验：往从表插数据时会检查这个值在主表存不存在；删主表数据时也会挡住还有子表引用的行。

8. 检查约束 CHECK（8.0.16+）

check (salary > 0 and salary < 10000)

alter table 表名 add constraint 约束名 check (条件);
alter table 表名 drop check 约束名;

报错长这样：

ERROR 3819 (HY000): Check constraint 't5_chk_1' is violated.

删除约束速查表

约束	级别	删除方式
NOT NULL	仅列级	alter table 表名 modify 列名 类型
DEFAULT	仅列级	alter table 表名 modify 列名 类型
AUTO_INCREMENT（不算约束，是列属性）	仅列属性	alter table 表名 modify 列名 类型
PRIMARY KEY	列级 / 表级	alter table 表名 drop primary key
UNIQUE	列级 / 表级	alter table 表名 drop index 约束名
FOREIGN KEY	列级 / 表级	alter table 表名 drop foreign key 约束名
CHECK	列级 / 表级	alter table 表名 drop check 约束名

---

九、存储引擎

存储引擎是 MySQL 底层的软件组织方式，MySQL 的核心特性之一就是插件式存储引擎 —— 用户可以根据应用需要选择怎么存、怎么建索引、要不要事务。

引擎	特点	适用场景
MyISAM	5.5 之前的默认引擎，插入查询快，不支持事务、不支持外键	以读写为主、很少更新、事务一致性要求不高
InnoDB	8.0 默认引擎，支持 ACID 事务、行级锁、支持外键	事务完整性要求高、并发一致性要求高
MEMORY	数据全在内存，效率极高，但重启数据就没了，占用内存和数据量成正比	临时数据
Archive	插入极快，查询支持差	存大量归档、历史记录
CSV	纯文本文件，每行一条，不支持索引	导出、导入中间态
BlackHole	写进去的数据直接消失	记录 binlog 做复制中继
FEDERATED	把不同 MySQL 服务器逻辑上组成一个库	分布式应用

对比几个关键维度：

功能	MyISAM	MEMORY	InnoDB
支持事务	否	否	是
支持外键	否	否	是
支持行级锁	否	否	是
存储限制	256 TB	RAM	64 TB
空间使用率	低	N/A	高

查看命令：

show engines\G;                            -- 支持哪些引擎
show variables like '%storage_engine%';     -- 当前默认引擎

建表时指定：

create table 表名 (...) engine=InnoDB;

不指定的话就走 my.cnf 里的 default-storage-engine=InnoDB。

---

十、完整建表语句模板

把这张表存下来，建表时照着抄：

create table 表名 (
    列名1  列类型  [auto_increment] [default 默认值] [列约束] [comment '注释'],
    列名2  列类型  [auto_increment] [default 默认值] [列约束] [comment '注释'],
    ...,
    [表约束]
) engine=InnoDB default charset=utf8mb4;

各部分说明：

部分	含义
列类型	该列数据的存储类型
auto_increment	自动增长，只能用在数值类型的列
default	该列的默认值
列约束	对列的一些限制（not null、primary key、unique、check、comment）
表约束	主键、外键等跨列的约束
engine	存储引擎
charset	表的字符集

我实际写的一个例子：

create table pro_tb(
    pro_num   int unique,                  -- 产品编号：唯一键
    pro_name  varchar(30) not null,        -- 产品名：非空
    pro_price float(5,2) not null,         -- 单价：非空
    pro_spe   varchar(8) default '件'      -- 规格：默认值
) engine=InnoDB default charset=utf8mb4;

---

总结

这章内容不少，但拎出来也就几条主线，整理成我自己的 checklist：

1. 建库建表前先 use，不然直接报 No database selected
2. 字符集统一 utf8mb4，因为 MySQL 的 utf8 存不了 Emoji
3. 关键字和特殊字符用反引号包起来，database 就是个现成的例子
4. 数据类型按需选，能用 tinyint 就别上 bigint，别用 float 存金额
5. 主键 + 唯一键 + 非空 是每张表的基本盘
6. 外键先想清楚再上，它会连带影响插入和删除的行为
7. 命名跟着生产规范走，t_ / temp_ / bak_ 三种前缀先记住
8. 建表语句模板存一份，比每次现想强

。

---

环境：MySQL 8.0.37（CentOS/RHEL）