必知！MySQL 数据库表的三大核心 —— 数据、类型与约束规则

一、表的操作

在进行具体表操作之前，我们需要先选定具体的数据库

mysql> use learn1;
Database changed

如果我们不记得我们选的是哪个数据库了，也可以进行查看，如下：

select database();

1. 创建表

语法：

CREATE TABLE table_name (
	field1 datatype,
	field2 datatype,
	field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

📚 说明：

field：表示列名
datatype：表示列的类型
character set：字符集，如果没有指定字符集，则以所在数据库的字符集为准
collate：校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准

2. 查看表

desc 表名;

在这里插入图片描述

3. 修改表

在项目实际开发中，经常修改某个表的结构，比如字段名字，字段大小，字段类型，表的字符集类型，表的存储引擎等等。

我们还有需求，添加字段，删除字段。这时我们就需要修改表。不会存在查找！

ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...);
ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column datatype]...);
ALTER TABLE tablename DROP (column);

4. 删除表

drop table t1;

说明：

像create，drop，show tables 都是在做表操作
但是select并不是操作表结构，操作的是表的内容。

前面学过SQL分类有：DDL、DML、DCL。

像我们目前学到的库的操和表的操作，属于那种类型的SQL呢？DDL 数据定义语言

5. 案例

① 创建表

create table if not exists user1(
 id int,
 name varchar(20) comment '用户名',
 password varchar(20) comment '用户密码',
 birthday date comment '用户生日'
)character set utf8 collate utf8_general_ci engine MyIsam;

# 两种存储引擎，可以用 空格 或者 = 来设置

create table if not exists user2(
 id int,
 name varchar(20) comment '用户名',
 password varchar(20) comment '用户密码',
 birthday date comment '用户生日'
)character set utf8 collate utf8_general_ci engine=InnoDB;

这里 if not exists 表示不存在再创建
当然后面为了方便的话，会省略一些东西

说明：

不同的存储引擎，创建表的文件不一样。

users 表存储引擎是 MyISAM ，在数据目中有三个不同的文件，分别是：
users.frm：表结构
users.MYD：表数据
users.MYI：表索引

而当存储引擎是 InnoDB ，在数据目中有两个不同的文件

Users.frm：表结构

Users.ibd：表数据&表索引

② 插入| 删除 | 查看表数据

③ 查看表信息

MySQL会记录下来用户的所有操作痕迹的，包括建表、删表等行为都会被记录下来。
这里查看创建表语句的时候，那个设置编码字段不见了，是因为服务器内部进行语法优化

④ 修改表信息

Ⅰ、修改表名

如下：

mysql> show tables;
+------------------+
| Tables_in_learn1 |
+------------------+
| u1               |
| user1            |
+------------------+
2 rows in set (0.00 sec)

mysql> alter table user1 rename to user;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_learn1 |
+------------------+
| u1               |
| user             |
+------------------+
2 rows in set (0.01 sec)

注意：这里的 to 可以省去

Ⅱ、新增一列

add 后面跟的是你想新增那一列，列的类型是什么，描述是什么
after 表示你想指定新增到那一列的后面

注意：插入新字段后，对原来表中的数据没有影响，之前的数据仍然还在

Ⅲ、修改列中某一字段

modify 后面跟你要改谁，后面在跟新的属性。
我们发现name字段大小确实已经变成60了，也没有影响该字段原有数据。

但是更为重要的是，如果刚才改字段属性就是只改了字段大小，你会发现以前 name 后面的 comment 没有了。

说明并不是定向你要改那个字段就给你改那个字段，它是把新的属性直接覆盖把原来创建name字段，属性等全部覆盖掉。

换句话说如果未来你想改某一列，你肯定要把这一列曾经的所有属性全部复制下来，在代码中对要改的字段修改，在重新提交一下。

Ⅳ、删除列名

alter table user drop password;

**注意：**删除之后，删除字段及其对应的列数据都没了

V、修改列名

将 name 列修改为 xingming

alter table user change name xingming varchar(60) DEFAULT NULL; # 新字段需要完整定义

说明：

change 后面跟着旧列名，再跟新列名和属性。
列名称要改不仅仅需要提供新列名称，这个列的相关属性也需要。相当于把这一列重新设置。
因为修改是覆盖实现的

二、数据类型

1. 数据类型分类

数据类型分类，在MySQL中，每种数据类型都有其特定的用途，类似于我们在学习C/C++等语言时遇到的情形。

以下是一些MySQL中常见的数据类型：

2. 数值类型

数值类型可以分为以下几类：位类型、布尔类型、整数类型、浮点数类型。

以下主要以整型为例进行说明：

整数类型包括 tinyint 、 smallint 等，它们根据名称不同，所占的字节数也不同，这些都是MySQL预先定义好的。
默认情况下，如果我们只写 tinyint 、 smallint 等，它们是有符号类型的。其取值范围与C/C++语言中的对应整数范围相同。
如果后面加上 unsigned，则表示是无符号类型。

2.1 tiny 类型

以 tinyint 类型为例，其他整数类型的使用方法与此类似。示例语句如下：

create table if not exists t1(num tinyint);

下面查看表的时候，看到 tinyint 后面有一个数字4，这个含义我们将在讨论约束时详细说明，现在先不管

mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| num   | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

🍡 插入数据时， tinyint 默认是有符号的，其取值范围是-128~127。超出这个范围的数据将无法插入
🍡 在MySQL中，整型可以指定为有符号或无符号，默认是有符号的。可以通过 UNSIGNED 关键字指定字段为无符号类型。

注意：MySQL Server 8.0.17 在不使用 ZEROFILL 修饰符时弃用了 TINYINT.SMALLINT、MEDIUMINT、INT和 BIGINT 数据类型的显示宽度，并且MySQL Server 8.0.19 已从 SHOW 的结果中删除了这些数据类型的显示宽度

所以 tinyint 后没有 4 也是正常的

创建无符号 tinyint 类型的表：

create table t2(num tinyint unsigned);

这样就创建了一个num字段为无符号类型的表。当插入超出取值范围的数据时，MySQL会拦截，不允许插入。

数据越界测试如下：

mysql> insert into t1 values(-129);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t1 values(-128);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values(128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

mysql> insert into t2 values(128);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(-129);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

虽然这里用的是无符号，范围是： 0 - 255
但是需要注意的是：尽量不使用 unsigned ，对于 int 类型可能存放不下的数据， int unsigned 同样可能存放不下。
不如在设计时将 int 类型提升为 bigint 类型。

⭕ 约束

如果我们向 mysql 特定的类型中插入不合法的数据，MySQL一般都是直接拦截我们，不让我们做越界的操作!
MySQL必须保证插入数据的完整性，一旦截断，那在MySQL中有些是成功插入的有些是截断后插入的，那作为用户来讲，他还能信任MySQL中插入的数据吗？
反过来，如果我们已经有数据被成功插入到mysql中，一定是插入的时候合法的！

所以 mysql 中，一般而言，数据类型本身也是一种：约束

约束 —> 倒逼程序员尽可能进行正确插入。所以约束，约束的是使用者。另外如果你不是一个很好的使用者， mysql 也能保证数据插入的合法性。
这样的话就能保证数据库中的数据是可预期，完整的。
以 tinyint 为例，它是有符号的，所以可预期的是未来插入的值范围一定在-128~127的。并且数据是完整的没有发生过截断或者 隐式类型转化

思考：

我们还可以发现一个细节，mysql表中建立 属性列，【列名称在前, 类型在后】如 num tinyint

如果反过来就是C/C++那一套形式。

2.2 bit 类型

语法： bit [ (M) ]：位字段类型。M表示值比特位的位数，范围从1到64。如果M被忽略，默认为1，示例如下：

create table t3(id int, online bit(1));

使用一个比特位来表示用户是否在线。由于只有一个比特位，只能插入0或1。

查询位类型数据时，通常按照 ASCLL码值 显示。例如，插入的 0 和 1 在 ASCLL码 中是 不可显示 的，因此查询时可能看不到内容。可以使用 hex 函数以 16进制形式 显示。

2.3 浮点数类型

2.3.1 float

语法： float [ (m, d) ] [ unsigned ]：M指定显示长度，d指定小数位数，占用空间4个字节。创建表示例如下：

create table t4(id int, salary float(4,2));

float(4,2)表示的范围是-99.99 ~ 99.99。
插入数据时，如果精度不够会补0
如果精度超过会四舍五入，但超过总位数范围的数据也无法插入。

当然我们还可以创建无符号float类型的表，如下：

create table t5(id int, salary float(4,2) unsigned);

无符号float(4,2)的取值范围是0 ~ 99.99，插入负数将失败。

浮点数在存储时可能会有精度损失。

2.3.2 decimal

语法：decimal(m, d) [unsigned]：定点数m指定长度，d表示小数点的位数

decimal类型的使用与float类似，但它可以有效地避免精度损失。
float的默认精度大约是7位，而decimal可以精确到65位整数和30位小数。如果d被省略，默认为0。如果m被省略，默认是10。
对于精度要求高的场景，应使用decimal类型。

【案例】：

create table t6 (id int, salary float(10,8), salary2 decimal(10,8));

mysql> insert into t8 values(100,23.12345612, 23.12345612);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
+------+-------------+-------------+
| id   | salary      | salary2     |
+------+-------------+-------------+
| 100  | 23.12345695 | 23.12345612 |
+------+-------------+-------------+

可以对比发现虽然float精度设为8但是实际上存的时候已经和插入数据不一样了。
float 类型往往在精度过大或者整体数字过大时会自作聪明帮我们做一些优化策略。
但是 decimal 不会，它能够完完全全让数据怎么存就怎么取。

decimal 的精度更准确，因此我们如果希望某个数据表示高精度，选择 decimal

3. 字符串类型

3.1 char

语法： char(L)：固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255。

示例： name char(2)，表示最多存两个字符。

插入数据时，如果超过定义的长度，则不允许插入。

mysql> create table if not exists t7(id int, name char(2));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t7 values(1, 'ab');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t7 (id, name) values(1, 'abc');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into t7 (id, name) values(1, '张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t7 (id, name) values(1, '张三四');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> select * from t7;
+------+--------+
| id   | name   |
+------+--------+
|    1 | ab     |
|    1 | 张三   |
+------+--------+
2 rows in set (0.00 sec)

这里为啥插入中文汉字也和插入字符规则一样呢？

MySQL中的字符和C/C++中的字符概念是不一样的，以前语言上的字符，一个字符对应一个字节，

而在MySQL中的字符真的代表一种符号，要么是1234、要么abcd、要么就是中文汉字，一个汉字就是一个字符。

char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个，最多只能是255，超过不让你插，连表都不让你建！

🧫 总结：

char 后面括号里面填的就是固定长度字符串的上限，一旦定义好之后该给你多少空间就给你申请好
你用多少是你的事，我给你多少由L决定。
另外如果插入字符超过L那就不给你插入

如下：

mysql> create table t(address char(256));
ERROR 1074 (42000): Column length too big for column 'address' (max = 255); use BLOB or TEXT instead

3.2 varchar

语法： varchar(L) ：可变长度字符串，L表示字符长度，最大长度65535个字节。

示例： varchar(6)，表示最多可以插入6个字符。

mysql> create table t8(id int, name varchar(6));

与char的区别： varchar 是变长字符串，实际使用空间根据字符串长度动态分配，而 char 是固定长度。

关于 varchar(len) ， len 的值与表的编码密切相关。

utf8 VS UTF-8【细节】

mysql> create table t9 (name varchar(21845));
ERROR 1074 (42000): Column length too big for column 'name' (max = 16383); use BLOB or TEXT instead

当我们插入varchar 过长时，会提示 max = 16383，当然有些朋友的 MySQL 是提示的 21844

utf8

MySQL的 utf8 不是真正的 UTF-8 编码

在 utf8 编码中， varchar(n) 的参数n最大值为 21844

MySQL在存储字符类型的时候，认为 utf8 编码， 单个字符是三个字节【21845 x 3 = 65535】

换句话说varchar保存最大长度是65535个字节，但字符数是21845！
总结： varchar 类型 根据实际字符个数动态分配空间， 最大字节数 为65535，但需要预留 1-3 个字节用于记录实际字符长度。所以我们上面说的是 21844

证明：

实际我们算出来是21844，这里是21845主要原因是因为它也到那三个字节计数数据的也带上来了。所以是21845，但实际只有21844，这里21844成功，是因为MySQL这一行都给你了。

UTF-8

相比于上面 utf8 只能存 3 个字节，utf8m64 可以存 4 个字节，方便存一些emoji符号、一些较复杂的文字、繁体字【都是 4 字节】

我之前还在想，为啥我设置的表是 utf8 字节，但是最后却变成了 utf8mb64，如下：

mysql> create table t(id int) character set utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show create table t \G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

ERROR:
No query specified

然后我一想到，MySQL 会做编码指令优化，可能是把这个创建语句的指令给优化了，相当于 MySQL 的“utf8mb4”才是真正的“UTF-8”。
MySQL官网在 2010 年重新发布了 “utf8mb4” 来支持真正的 UTF-8

现在 MySQL 8.0 之后，默认的数据库字符集已经变成了 utf8mb4

在这里Mark一下：所有在使用“utf8”的 MySQL 和 MariaDB 用户都应该改用“utf8mb4”，永远都不要再使用“utf8”。

总结：

varchar 有自己长度上限，在上限范围内用多少给多少

做法就是在申请的众多字节中有1~4个字节用来表示有效字符长度，通过这种方式来确定实际字符是多少来实现变长。

varchar 最大字节数65535，但65535一定要包含 1- 4 个记录数据长度的字段。在 UTF8 保存在大字符个数是 16383

如果这个表很干净一行内没用其他字段。 varchar 能到 16383，但是有其他字段那这个值就会变小。

3.3 char VS vachar

共同点：都能保存字符串，都有上限。
区别： char 是定长的， varchar 是变长的。 char 一次分配固定空间， varchar 根据实际使用分配空间。

实际存储	char(4)	varchar(4)	char 占用字节	varchar 占用字节
abcd	abcd	abcd	4*3=12	4*3+1=13
A	A	A	4*3=12	1*3+1=4
Abcde	x	×	数据超过长度	数据超过长度

如何选择：

如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。

定长 VS 变长

定长的磁盘空间比较浪费，但是效率高。
变长的磁盘空间比较节省，但是效率低。
定长的意义是，直接开辟好对应的空间
变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

4. 日期和时间类型

常用的日期类型：

date：日期 ‘yyyy-mm-dd’，占用三字节。
datetime ：日期时间 ‘yyyy-mm-dd HH:ii:ss’，表示范围从1000到9999，占用八字节。–手动设置
timestamp：时间戳，从1970年开始的 ‘yyyy-mm-dd HH:ii:ss’ 格式，占用四字节。–自动更新

【案例】

mysql> create table t10(t1 date, t2 datetime, t3 timestamp DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.03 sec)

mysql> desc t10;
+-------+-----------+------+-----+-------------------+-------------------+
| Field | Type      | Null | Key | Default           | Extra             |
+-------+-----------+------+-----+-------------------+-------------------+
| t1    | date      | YES  |     | NULL              |                   |
| t2    | datetime  | YES  |     | NULL              |                   |
| t3    | timestamp | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------+-----------+------+-----+-------------------+-------------------+
3 rows in set (0.00 sec)

mysql> insert into t10(t1,t2) values('1997-7-1','2008-8-8 12:1:1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t10;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 1997-07-01 | 2008-08-08 12:01:01 | 2025-02-07 14:00:58 |
+------------+---------------------+---------------------+

注意： timestamp 会自动更新，适用于记录数据的最后修改时间。

要结合具体的场景选择时间:

timestamp 时间戳有什么用呢？
- 比如说你在博客上面给别人评论的时候，用到的就是这。只要对评论更改或者插入，这个时间戳就会被更新到最新时间。
datetime 就是要存储一个固定时间，如记录你入职的时间。

5. enum 和 set

语法：

enum：枚举，“单选”类型； enum('选项1','选项2','选项3',...)
- 该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；
- 而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,…最多65535个；
- 当我们添加枚举值时，也可以添加对应的数字编号。
set：集合，“多选”类型； set('选项值1','选项值2','选项值3', ...).
- 该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；
- 而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，…最多64个。

说明：不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读

📚 【案例】：

有一个调查表votes，需要调查人的喜好，比如（登山，游泳，篮球，武术）中去选择(可以多选)，（男，女）[单选]

create table votes(
 username varchar(30),
 hobby set('clime','swim','draw'), # 注意：使用数字标识每个爱好的时候，想想Linux权限，采用比特位位置来个set中的爱好对应起来
 gender enum('男','女')); # 注意：使用数字标识的时候，就是正常的数组下标


mysql> desc votes;
+----------+----------------------------+------+-----+---------+-------+
| Field    | Type                       | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+---------+-------+
| username | varchar(30)                | YES  |     | NULL    |       |
| hobby    | set('clime','swim','draw') | YES  |     | NULL    |       |
| gender   | enum('男','女')            | YES  |     | NULL    |       |
+----------+----------------------------+------+-----+---------+-------+

enum 枚举类型给我提供约束，换句话说插入时只能插入枚举的类型，不允许插入除该枚举类型外其他任何字符。
当 enum 枚举类型在插入的时候，可以直接写这个枚举限定的常量，也可以写对应常量的下标

如下：这个数字下标从1开始，分别代表第一个枚举值,第二个枚举值等。有几个就只能到几，超过不行

mysql> insert into votes values('Tom', 'clime', '1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into votes values('Tim', 'draw', '2');
Query OK, 1 row affected (0.01 sec)

mysql> select * from votes;
+----------+-------+--------+
| username | hobby | gender |
+----------+-------+--------+
| Tom      | clime | 男     |
| Tim      | draw  | 女     |
+----------+-------+--------+
2 rows in set (0.00 sec)

数据库SET类型插入规则笔记

① 插入规则

set 以数字形式插入绝对不是下标！

目前这里有 3 个爱好，在集合中我们把它想象成 3 个比特位 000，这里我们从右到左表示从低比特位到高比特位。
插入 1 的时候 000 -> 001，这个比特位的位置代表是代码这个爱好，这个比特位的内容：为1 代表有代码这个爱好，为0 就代表没有。这就是我们刚才插入1的时候，显示的是代码的爱好
插入 3 的时候 00000 -> 00011，表示有前两个爱好

如下：

mysql> insert into votes values('R', '3', '1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from votes;
+----------+------------+--------+
| username | hobby      | gender |
+----------+------------+--------+
| Tom      | clime      | 男     |
| Tim      | draw       | 女     |
| R        | clime,swim | 男     |
+----------+------------+--------+

② SET类型的含义

因此，当我们在向一个 set 集合中插入的时候，这个数字代表的是位图。

集合中有几个类型就有几个比特位，比特位从低向高依次代表set类型中从左向右。
比特位的位置代表是那个类型，比特位为 0 为 1 就代表是否是有这个类型。

③ ENUM 与 SET 的区别

enum 和 set 在插入时提供特定的选项， enum 为单选， set 为多选。
enum 和 set 的查找可以通过常量或位图进行筛选。
enum 中插入数字代表的是下标， set 中插入数字代表的是位图。

集合查询使用find_ in_ set函数

在数据库查询中，对于集合类型的字段，可以使用find_in_set函数来进行查询。此函数用于确定一个子串是否存在于一个由逗号分隔的字符串列表中。

find_in_set(sub, str_list)：

  如果sub在str_list中，则返回子串的位置下标；
  如果不在，则返回0；
  str_list是由逗号分隔的字符串。

SELECT 语句可以执行表达式，同样，函数也可以执行表达式

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

mysql> select find_in_set('b','a,b,c');
+--------------------------+
| find_in_set('b','a,b,c') |
+--------------------------+
|                        2 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('ab','a,b,c');
+---------------------------+
| find_in_set('ab','a,b,c') |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

返回值解释

当子串存在于集合中时，返回的下标是从1开始的；
子串不存在于集合中时，返回0；
因此，非0值表示真（即存在），0表示假（即不存在）。

功能说明

find_in_set 只能用来检查一个元素是否在集合中。
如果存在，则返回对应的下标；
查找过程是判断元素是否在集合中，而非直接判断相等

应用实例

例如，在查找用户的爱好时，如果是 ‘client’ 或者 ‘swim’ 和 ‘client’，则可以用 find_in_set 来进行严格匹配：

mysql> select * from votes;
+----------+------------+--------+
| username | hobby      | gender |
+----------+------------+--------+
| Tom      | clime      | 男     |
| Tim      | draw       | 女     |
| R        | clime,swim | 男     |
+----------+------------+--------+

mysql> select * from votes where hobby = 'clime';
+----------+-------+--------+
| username | hobby | gender |
+----------+-------+--------+
| Tom      | clime | 男     |
+----------+-------+--------+

mysql> select * from votes where find_in_set('clime', hobby);
+----------+------------+--------+
| username | hobby      | gender |
+----------+------------+--------+
| Tom      | clime      | 男     |
| R        | clime,swim | 男     |
+----------+------------+--------+

mysql> select * from votes where find_in_set('clime, swim', hobby);
Empty set (0.00 sec)

mysql> select * from votes where find_in_set('clime', hobby) and find_in_set('swim', hobby);
+----------+------------+--------+
| username | hobby      | gender |
+----------+------------+--------+
| R        | clime,swim | 男     |
+----------+------------+--------+

三、表的约束

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如：有一个字段是email，要求是唯一的。

表的约束很多，这里主要介绍如下几个：null/not null、default、comment、zerofill、primary key、auto_increment、unique key

约束的最终目标：保证数据的完整性和可预期性

之前把列名称和类型都了解了一下，但是在实际查表得时候它们后面是什么东西呢？今天就来说一说~

3.1 空属性（null/not null）

null 表示列可以为空， not null 表示列不能为空。

通过 not null，可以设置某列数据在插入时必须填入具体值，否则会报错。例如注册账号时的某些必填信息。

【案例】：

create table myclass(
 class_name varchar(20) not null, -- 班级名不为空
 other varchar(20));

在此示例中， class_name 列设置了 not null；而 other 列默认允许为空。

【查询表】结果如下：

mysql> desc myclass;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO   |     | NULL    |       |
| other      | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table myclass \G;
*************************** 1. row ***************************
       Table: myclass
Create Table: CREATE TABLE `myclass` (
  `class_name` varchar(20) NOT NULL,
  `other` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

我们发现 other 这一列我们是只写了一个 varchar ，没有指定 not null ，默认是 null 的，然后面加了一个 default null
这里表示你想插就插，不插这一类就给默认值 null

【数据插入】测试：

mysql> insert into myclass values('1班');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> insert into myclass (class_name) values('1班');
Query OK, 1 row affected (0.01 sec)

mysql> insert into myclass (other) values(102);
ERROR 1364 (HY000): Field 'class_name' doesn't have a default value

mysql> insert into myclass values(NULL, NULL);
ERROR 1048 (23000): Column 'class_name' cannot be null

mysql> select * from myclass;
+------------+-------+
| class_name | other |
+------------+-------+
| 1班        | NULL  |
+------------+-------+
1 row in set (0.00 sec)

**注意：**我们对具体某列进行插入时，需要声明该列名字来进行匹配，否则不然就会上面第一行的错误

某列设置了 not null

必须要插具体值，不插因为后面没有默认值就报错，而且插入null也报错

设置默认为 null ，可以不插用的是后面带的默认值。

3.2 默认值（default）

default：当插入数据时，如果未指定该列的值，将使用默认值。

通过默认值，可以简化数据插入操作，提高数据一致性。

【案例】：

create table if not exists student(
     name varchar(20) not null,
     age tinyint unsigned default 18,
     gender varchar(10) default '男'
);

【操作如下】：

mysql> desc student;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| name   | varchar(20)      | NO   |     | NULL    |       |
| age    | tinyint unsigned | YES  |     | 18      |       |
| gender | varchar(10)      | YES  |     | 男      |       |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into student values('RM', '20', '女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student (name) values('Mike');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student (name, age) values('Mike', NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+------+------+--------+
| name | age  | gender |
+------+------+--------+
| RM   |   20 | 女     |
| Mike |   18 | 男     |
| Mike | NULL | 男     |
+------+------+--------+
3 rows in set (0.00 sec)

注意： default 和 not null 并不冲突，而是互相补充的。

当用户指明这一列要插的时候，受 null 和 not null 约束，要么插 null ，要么插合法数据。
- 用户指明这一列要插，not null来约束。
当用户忽略这一列的时候，如果设置了默认值使用默认值，如果没有就直接报错。
- 用户忽略这一列要插，default来约束。

如果建表的时候，不给某一列添加任何约束，我们会发现MySQL会对sql语句优化，默认会带上 defalut null。所以不插入的时候在表示会显示 null

3.3 列描述（comment）

comment：用于给列添加注释说明，便于程序员和数据库管理员理解字段用途。

该属性不会对数据插入产生约束效果。

create table if not exists t5(
     name varchar(20) not null comment '用户的用户名',
     age tinyint unsigned default 18 comment '用户的年龄'
);

在表中并不会说因为不符合不让你插入。就相当于C/C++里的注释一样。

3.4 zerofill

zerofill：在数字前补零，使显示字符长度符合指定的位数。

数据库存储的数值不变，仅用于展示效果。

【案例】

mysql> create table if not exists t6(a int unsigned, b int unsigned zerofill);

mysql> show create table t6 \G;
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `a` int unsigned NOT NULL,
  `b` int(10) unsigned zerofill NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

上面出现了 int(10)，这个 10 究竟有什么用，如下：

mysql> insert into t6 values(111, 222);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t6;
+-----+------------+
| a   | b          |
+-----+------------+
| 111 | 0000000222 |
+-----+------------+
1 row in set (0.00 sec)

我们会发现， zerofill 还是挺形象的，当我们插入 222，前面填满 0.

我们再来按照 16进制显示，验证一下：值不会改变， zerofill 只是一种格式化输出

mysql> select a, hex(b) from t6;
+-----+--------+
| a   | hex(b) |
+-----+--------+
| 111 | DE     |
+-----+--------+
1 row in set (0.00 sec)

如果以后你想显示出的是001，002就可以设置 zerofill

3.5 主键（primary key）

primary key：用于标识表中的 唯一记录，不允许重复或为空。

表中最多只能有一个主键列
主键可以通过复合主键的方式使用多列联合唯一标识。

创建主键有两种方法：

创建表的时候就把主键设置好
表建好之和但没有主键，可以追加主键

【案例 1】：创建表的时候就把主键设置好

mysql> create table t7(id int primary key, name varchar(20));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t7 values(1, 'amy');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t7 values(1, 'anna');
ERROR 1062 (23000): Duplicate entry '1' for key 't7.PRIMARY'
mysql> select * from t7;
+----+------+
| id | name |
+----+------+
|  1 | amy  |
+----+------+

主键约束 对于程序员来讲，未来想往这个表里面插对应插入的数据主键列不能冲突，一旦冲突不让你插入，所以倒逼程序员插的时候尽量不要出现 主键冲突

其次站在 mysql 视角凡是插入这个表里面的数据主键一定是不冲突的。这样的好处是根据主键绝对能拿出来确定的一条记录！–唯一性

有了主键可以有 针对性的对数据进行增删查改

【案例 2】：表建好之和但没有主键，可以追加主键

基于上面的表，先把原始表中的主键先去掉，然后再添加主键，操作如下：

mysql> alter table t7 drop primary key; -- 删除主键
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table t7 add primary key(id); -- 添加主键
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

【复合主键】

虽然一张表中最多只能有一个主键，但是并不意味着一个表中的主键只能添加给一列！

也就是说一个主键可以被添加到一列，或者多列上，而一个主键被添加到多列上的数据我们就叫做 复合主键
在创建表的时候，在所有字段之后，使用 primary key(主键字段列表) 来创建主键，如果有多个字段作为主键，可以使用 复合主键

【案例】下面创建表我们让两列合起来充当一个主键

mysql> create table t8(id int, name varchar(20), primary key(id,name));
Query OK, 0 rows affected (0.03 sec)

mysql> desc t8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

这里虽然可以看到 id 和 name 都是主键，但是一张表只能有一个主键，我们该怎么理解这个情况？

有两个 PRI，但并不证明有两个主键！而是这两个都是主键，两个都是主键如何理解呢？它们 两个合起来才是一个主键！

mysql> insert into t8 values(123, 'zs');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t8 values(156, 'zs');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t8 values(123, 'zs');
ERROR 1062 (23000): Duplicate entry '123-zs' for key 't8.PRIMARY'

但是我们不允许，同一个同学有同一个 id，这就会出现主键约束了。它是把 id 和 name 作为一个整体的。

换言之，可以选择一列作为主键，也可以选择多列作为主键
但是多个合起来做一个主键，都不一样可以插，有一个不一样可以插，只有多个同时和历史数据一样才会出现主键冲突。这就是 复合主键
复合主键理解：将多列看成一个整体，全部同时冲突，才会约束

3.6 自增长（auto_increment）

auto_increment：字段自动增长，从当前最大值加 1，通常配合主键使用，确保值唯一。

自增长的特点：

任何一个字段要做自增长，前提：本身是一个索引（key一栏有值）
自增长字段必须是整数
一张表最多只能有一个自增长

【案例】：在此示例中， id 列自增长，无需显式插入，系统自动为其赋值。

create table t10(id int primary key auto_increment, name varchar(20));

mysql> insert into t10 (name) values('a');
mysql> insert into t10 (name) values('b');

mysql> select * from t10;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

插入时，我们可以指定插入其他列，id这一列就不管了。可以看到虽然我并没有告诉id要插什么，但是id是自动帮我们插入的，并且是增长的。

和别人不冲突并且连续的，这就是自增长主键。
当我们指定id要插入的时候，也能插进行。然后再插入id相同值的时候，确实能够履行主键的职责发生主键冲突（主键和自增长搭配使用）

自增主键的插入机制

默认行为：自增主键在插入时若未设置任何默认值，则默认从1开始插入。
手动设置起始值：如果手动插入一个新的起始值，且该值大于历史值，则自增主键将从新的起始值开始进行插入

mysql> insert into t10 (id, name) values(1000, 'c');
mysql> insert into t10 (name) values('d');

mysql> select * from t10;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
| 1000 | c    |
| 1001 | d    |
+------+------+
4 rows in set (0.00 sec)

设置 AUTO_INCREMENT 的原理

表内外约束：创建表时，除了在表内设置 auto_increment 约束外，还可以在表外设置 auto_increment 的值，这代表下一次插入的起始值。

create table t11(id int primary key auto_increment, name varchar(20))auto_increment=100;

mysql> insert into t11 (name) values('a');
mysql> insert into t11 (name) values('b');

mysql> select *  from t11;
+-----+------+
| id  | name |
+-----+------+
| 100 | a    |
| 101 | b    |
+-----+------+

插入时更新起始值：当插入一个值时（如1000），系统会自动更新表外的 auto_increment 值，使其成为下次插入的起始值。

mysql> show create table t10 \G;
*************************** 1. row ***************************
       Table: t10
Create Table: CREATE TABLE `t10` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

获取上次插入的 AUTO_INCREMENT 值

单条插入：可以使用 last_insert_id() 函数来获取最后一次插入的 AUTO_INCREMENT 值。
批量插入：获取的是批量插入中的第一个 AUTO_INCREMENT 值。

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|             1001 |
+------------------+
1 row in set (0.00 sec)

还记得上面说自增长前提是个索引，我们现在来简单了解一下索引是啥？

索引定义：

物理存储结构：在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构。
组成：索引是某个表中一列或若干列值的集合，以及相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引使用：

快速定位：索引提供了指向存储在表的指定列中的数据值的指针，并根据指定的排序顺序对这些指针排序。
提高查询效率：数据库使用索引以找到特定值，然后顺指针找到包含该值的行，从而使得对应于表的SQL语句执行得更快。

3.7 唯一键（Unique Key）

定义： 一张表中有往往有很多字段需要 唯一性 ，数据不能重复，但是一张表中只能有一个主键，而此时 唯一键 就可以解决表中有多个字段需要唯一性约束的问题。

区别：

主键：标识唯一性，主要用于唯一标识记录。
唯一键：更多地用于业务逻辑上的唯一性约束，允许字段为空，并且多个空值不会影响唯一性比

示例场景：在员工管理系统中，身份证号码可以作为主键，确保员工的唯一标识；员工工号可以设置为唯一键，确保工号在公司业务上不会重复。

mysql> create table student (
    -> id char(10) unique comment '学号，不能重复，但可以为空',
    -> name varchar(10)
    -> );

mysql> insert into student(id, name) values('01', 'aaa');
mysql> insert into student(id, name) values('01', 'bbb'); -- 触发唯一约束错误
ERROR 1062 (23000): Duplicate entry '01' for key 'id'

mysql> insert into student(id, name) values(null, 'bbb'); -- 允许为空

主键 vs 唯一键

唯一键和主键不冲突，可以理解为对主键的补充设置

只能有一个主键，但可以有多个唯一键

建议：一般而言，建议将主键设计成和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整

3.8 外键（foreign Key）

外键：

从表和主表的关联关系
产生外键约束
为什么需要外键约束？

外键用于 确保表间数据的一致性，例如：防止插入一个不存在班级的学生或删除一个还有学生的班级。

定义：外键约束用于建立主表和从表之间的关联关系，主要定义在从表上，主表必须包含主键或唯一键。

外键用于定义主表和从表之间的关系：

外键约束主要定义在从表上
主表则必须是有主键约束或 unique 约束
当定义外键后，要求外键列数据必须在主表的主键列存在或为 NULL

在从表中，设置外键约束：

foreign key (字段名) references 主表(列)

【案例】：

mysql> create table stu(
    -> id int primary key,
    -> name varchar(30) not null comment '学生名',
    -> class_id int,
    -> foreign key (class_id) references class(id));

-- 插入班级数据
mysql> insert into class values(10,'1班'), (20, '2班');

-- 插入学生数据
mysql> insert into stu values(1,'1班',10), (2, '2班',20);

-- 插入无效数据
mysql> insert into stu values(30,'1班',3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learn2`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

如果两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题

解决方案就是通过外键完成的

建立外键的本质其实就是把相关性交给MYSQL去审核了，提前告诉 MySQL 表之间的约束关系

，但可以有多个唯一键

建议：一般而言，建议将主键设计成和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整

3.8 外键（foreign Key）

外键：

从表和主表的关联关系
产生外键约束
为什么需要外键约束？

外键用于 确保表间数据的一致性，例如：防止插入一个不存在班级的学生或删除一个还有学生的班级。

定义：外键约束用于建立主表和从表之间的关联关系，主要定义在从表上，主表必须包含主键或唯一键。

外键用于定义主表和从表之间的关系：

外键约束主要定义在从表上
主表则必须是有主键约束或 unique 约束
当定义外键后，要求外键列数据必须在主表的主键列存在或为 NULL

在从表中，设置外键约束：

foreign key (字段名) references 主表(列)

【案例】：

mysql> create table stu(
    -> id int primary key,
    -> name varchar(30) not null comment '学生名',
    -> class_id int,
    -> foreign key (class_id) references class(id));

-- 插入班级数据
mysql> insert into class values(10,'1班'), (20, '2班');

-- 插入学生数据
mysql> insert into stu values(1,'1班',10), (2, '2班',20);

-- 插入无效数据
mysql> insert into stu values(30,'1班',3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learn2`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

如果两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题

解决方案就是通过外键完成的

建立外键的本质其实就是把相关性交给MYSQL去审核了，提前告诉 MySQL 表之间的约束关系

那么当用户插入不符合业务逻辑的数据的时候， MySQL 不允许你插入

原文阅读

文章来源： https://study.disign.me/article/202508/2.mysql-table-trilogy.md

发布时间: 2025-02-17

作者： 技术书栈编辑