【MySQL】sql字段约束

在MySQL中，我们需要存储的数据在特定的场景中需要不同的约束。当新插入的数据违背了该字段的约束字段，MySQL会直接禁止插入。

• 数据类型也是一种约束，但数据类型这个约束太过单一；
• 比如我需要存储的是一个序号，那就不可能会有负数，这时候就要用无符号来对整形进行约束；
• 如果我要存储的是一个日期，且这个日期每个字段都必须要有，那就需要用NOT NULL不为空来进行约束；
• 如果我要存储的是一个用户ID，在整个用户系统中这个值肯定是唯一的，就可以使用UNIQUE来约束唯一性

本文主要介绍下面几种约束的类型，这依旧是MySQL中ddl类型的语句操作。

null/not null
default
comment
zerofill
primary key
auto_increment
unique key

顺带一提，在MySQL中，每一列的值可以称之为列或者字段；一般不称之为键值。

而表中的每一行被称作一条记录

1.空属性

1.1 说明

空属性包含两个值，NULL/NOT NULL，分别对应为空和不为空

在数据库中，如果我们在插入一行时没有指定某一列的值，那么数据库就会把这个值设置为NULL；

这里要注意区分NULL和空字符串，空字符串并不是NULL！

但实际使用数据的时候，假设这是一个整形的数据，我们需要取出来后对其进行运算。此时NULL取出来的结果就不是一个数字，没有办法进行运算。所以在很多时候，我们都会把一个字段的约束设置为NOT NULL并添加上一个默认值（比如0或者空字符串）

MariaDB [hello]> select null;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.000 sec)MariaDB [hello]> select not null;
+----------+
| not null |
+----------+
|     NULL |
+----------+
1 row in set (0.001 sec)

由下可见空属性是没办法参与运算的，不管如何运算其结果都是NULL

在Python中，NULL直接对应的就是None，当你尝试用None和int类型进行运算的时候，Python就会报错了。CPP中也是如此。

MariaDB [hello]> select 1+null;
+--------+
| 1+null |
+--------+
|   NULL |
+--------+
1 row in set (0.001 sec)

1.2 案例

假设我们有一个楼层中的班级和教室对应的表，其中包含班级编号和教室编号这两个字段

• 如果班级编号为空，那就不知道在这间教室上课的是那个班级
• 如果教室编号为空，那就不知道某个班级到底是在哪里上课

由实际场景可见，这两个字段都不可以为空，所以在建表的时候就需要考虑到这一点

create table if not exists myclass(class_name varchar(30) not null,class_room varchar(30) not null
)default charset=utf8;

创建了表之后，当我们尝试将一个NULL的字段插入，会出现如下的提示，标识某一列不能为空

MariaDB [hello]> insert into myclass values ('510',NULL);
ERROR 1048 (23000): Column 'class_room' cannot be null

而空字符串是可以被插入的，这里又一次说明了我们认为的空和NULL并不相同，空字符串不是NULL

MariaDB [hello]> insert into myclass values ('510','');
Query OK, 1 row affected (0.005 sec)MariaDB [hello]> select * from myclass;
+------------+------------+
| class_name | class_room |
+------------+------------+
| 510        |            |
+------------+------------+
1 row in set (0.000 sec)

2.默认值default

当我们注册某些网站的时候，一些信息不填，就会被系统设置为默认值。

比如你不选择年龄的时候，系统可能就会显示你为0岁；其他用户看到你的个人主页上显示的0岁，就知道你并没有填写自己的真实年龄。（而前端开发的时候也可以将0认作没有填写，显示成”隐藏年龄“）

再比如我们的网站上有一个用户积分的数值，当用户注册的时候，积分肯定是0（暂时不考虑新人送积分什么的操作），这时候就可以把积分那一列的默认值设置成0，在插入的时候就可以不显式插入这列的数据；

在MySQL中，某一列设置了默认值后。在insert时候如果没有指定这一列的数据，那就会采用默认值。

create table if not exists web_user(name varchar(30) not null default '默认用户名',age tinyint not null default 0,gender char(2) not null default '男' 
);

创建完毕这个表，当我们查看表结构的时候，就能看到是否为空，以及默认直的相关属性

MariaDB [hello]> desc web_user;
+--------+-------------+------+-----+-----------------+-------+
| Field  | Type        | Null | Key | Default         | Extra |
+--------+-------------+------+-----+-----------------+-------+
| name   | varchar(30) | NO   |     | 默认用户名      |       |
| age    | tinyint(4)  | NO   |     | 0               |       |
| gender | char(2)     | NO   |     | 男              |       |
+--------+-------------+------+-----+-----------------+-------+
3 rows in set (0.004 sec)

由于这个表里面的3个字段我们都设置了初始值，你甚至可以直接啥都不指定地插入一个数据；下方可以看到，所有列都被设置成了该列的初始值。

MariaDB [hello]> insert into web_user values ();
Query OK, 1 row affected (0.001 sec)MariaDB [hello]> select * from web_user;
+-----------------+-----+--------+
| name            | age | gender |
+-----------------+-----+--------+
| 默认用户名      |   0 | 男     |
+-----------------+-----+--------+
1 row in set (0.000 sec)

当我们想不设置某一列的值的时候，默认值就能帮上忙。

这里先给一个错误的演示，我们只在values里面设置了两个值，目的是让新插入的这个用户的性别采用默认值。但MySQL报错了，报错的信息提示是value的个数和列的数量不一致

MariaDB [hello]> insert into web_user values ('李华',16);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

这是因为我们在插入的时候，没有显示的告诉MySQL我们这两个值到底是哪两列的值。李华是给name列还是给gender列？MySQL没有办法自主决定！所以干脆拒绝插入。

所以，当我们想让某一列使用缺省值的时候，就需要告诉MySQL，我们当前指定的values到底是哪几列的数据

insert into web_user (name,age) values ('李华',16);

这样才能插入成功

MariaDB [hello]> insert into web_user (name,age) values ('李华',16);
Query OK, 1 row affected (0.005 sec)MariaDB [hello]> select * from web_user;
+-----------------+-----+--------+
| name            | age | gender |
+-----------------+-----+--------+
| 默认用户名      |   0 | 男     |
| 李华            |  16 | 男     |
+-----------------+-----+--------+
2 rows in set (0.001 sec)

因为这里做了对列名的显示指定，所以顺序并不一定需要依照表中列名的顺序，比如下方我们反过来也是可以插入的。但并不建议这么做，在插入的时候的列名顺序应该和表中列顺序保持一致！

MariaDB [hello]> insert into web_user (age,name) values (18,'小李');
Query OK, 1 row affected (0.001 sec)MariaDB [hello]> select * from web_user;
+-----------------+-----+--------+
| name            | age | gender |
+-----------------+-----+--------+
| 默认用户名      |   0 | 男     |
| 李华            |  16 | 男     |
| 小李            |  18 | 男     |
+-----------------+-----+--------+
3 rows in set (0.001 sec)

如果想让age列采用初始值，那就是如下的插入；

MariaDB [hello]> insert into web_user (name,gender) values ('菲菲公主','女');
Query OK, 1 row affected (0.005 sec)

2.1 默认值和NULL

需要注意的是，默认值和NOT NULL并不是必须一起使用的

• 当我们设置了默认值，但是没有设置NOT NULL，我们可以显式地插入NULL
• 默认值也可以设置成NULL

create table if not exists test_user(name varchar(30) not null default '默认用户名',age tinyint not null default 0,gender char(2) default null
);

使用如上sql创建表，数据库没有报错，即代表我们的语法是被支持的。因为性别并不需要参与运算，所以我们可以认为当性别列为空的时候，就是未选择性别的选项。不过，也可以通过空字符串作为默认值来解决这一问题，相比之下用空字符串更好，因为这样能保证这个字段的值始终是个字符串，而不需要对null进行特殊处理

default null 和 not null不能一起使用，这是肯定的！

3.列描述comment

需要注意，在sqlite中是不支持comment的，不同的数据库对sql字段的支持会有些许的差距，请根据你使用的数据库为准。本文所述基于MySQL和MariaDB。

所谓的列描述，就是对这列到底是干嘛的一个说明信息，相当于代码的注释。其本身没有任何含义；

列注释的主要作用，就是让所有使用这个数据库，使用这张表的人都能理解这个字段的作用。其中还可以添加额外的注释说明，来让程序员统一在不同模块的上传代码中进行额外的处理。

比如我们将第二点中出现过的用户表改成如下形式，每个字段都添加上注释

create table if not exists web_user(name varchar(30) not null default '默认用户名' comment '用户名',age tinyint not null default 0 comment '用户年龄',gender char(2) not null default '男' comment '用户性别' 
);

当我们使用这个sql创建了这个表后，如果想查询字段的注释，可以用如下命令查看创建表时使用的命令（其中就包含了表的注释）

SHOW CREATE TABLE web_user;

显示如下

MariaDB [hello]> SHOW CREATE TABLE web_user;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                               |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| web_user | CREATE TABLE `web_user` (`name` varchar(30) NOT NULL DEFAULT '默认用户名' COMMENT '用户名',`age` tinyint(4) NOT NULL DEFAULT 0 COMMENT '用户年龄',`gender` char(2) NOT NULL DEFAULT '男' COMMENT '用户性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

也可以用如下命令来展示所有列和列的属性，其中包括注释

SHOW FULL COLUMNS FROM web_user;

MariaDB [hello]> SHOW FULL COLUMNS FROM web_user;
+--------+-------------+-----------------+------+-----+-----------------+-------+---------------------------------+--------------+
| Field  | Type        | Collation       | Null | Key | Default         | Extra | Privileges                      | Comment      |
+--------+-------------+-----------------+------+-----+-----------------+-------+---------------------------------+--------------+
| name   | varchar(30) | utf8_general_ci | NO   |     | 默认用户名      |       | select,insert,update,references | 用户名       |
| age    | tinyint(4)  | NULL            | NO   |     | 0               |       | select,insert,update,references | 用户年龄     |
| gender | char(2)     | utf8_general_ci | NO   |     | 男              |       | select,insert,update,references | 用户性别     |
+--------+-------------+-----------------+------+-----+-----------------+-------+---------------------------------+--------------+
3 rows in set (0.002 sec)

需要注意，desc命令显示的结果中是不包含列注释的

MariaDB [hello]> desc web_user;
+--------+-------------+------+-----+-----------------+-------+
| Field  | Type        | Null | Key | Default         | Extra |
+--------+-------------+------+-----+-----------------+-------+
| name   | varchar(30) | NO   |     | 默认用户名      |       |
| age    | tinyint(4)  | NO   |     | 0               |       |
| gender | char(2)     | NO   |     | 男              |       |
+--------+-------------+------+-----+-----------------+-------+
3 rows in set (0.001 sec)

4.zerofill

4.1 测试结果

先来用如下命令创建一个表

create table if not exists test_int(a int not null,b int unsigned not null
);

创建完成后，我们查看创建这个表时使用的语句，会发现在int之后多了一个括号，里面跟了一个数字。

我们知道在char和varchar里面，这个括号是用来限制字符串字符长度的，那么在整形这里的括号是干嘛的呢？

MariaDB [hello]> show create table test_int;
+----------+------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                           |
+----------+------------------------------------------------------------------------------------------------------------------------+
| test_int | CREATE TABLE `test_int` (`a` int(11) NOT NULL,`b` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

先往这个表内插入一个数据，并查询显示出来

MariaDB [hello]> insert into test_int values (3,1);
Query OK, 1 row affected (0.005 sec)MariaDB [hello]> select * from test_int;
+---+---+
| a | b |
+---+---+
| 3 | 1 |
+---+---+
1 row in set (0.000 sec)

如果我们把a列的属性进行修改为如下的字段类型

alter table test_int change a a int(5) unsigned zerofill;

再去查看创建表的命令，此时结果如下，a列已经被修改成了信的属性

MariaDB [hello]> alter table test_int change a a int(5) unsigned zerofill;
Query OK, 1 row affected (0.005 sec)               
Records: 1  Duplicates: 0  Warnings: 0MariaDB [hello]> show create table test_int;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------+
| test_int | CREATE TABLE `test_int` (`a` int(5) unsigned zerofill DEFAULT NULL,`b` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

那么这个括号里面的数字，和zerofill有什么作用呢？

再次查询此表，会发现刚刚插入的数据，a列的3变成了00003！

MariaDB [hello]> select * from test_int;
+-------+---+
| a     | b |
+-------+---+
| 00003 | 1 |
+-------+---+
1 row in set (0.000 sec)

4.2 前补0

此时这个属性的作用就很明确了，其用于给数字进行前补0；而5就是规定的前补0的长度，而不是数字的长度；当数字的位数小于5位，就会触发前补0；

可以看到，即便表结构中出现了int(5)，我们依旧可以往这个表里面插入长度大于5位的数字

MariaDB [hello]> insert into test_int values (12345678,1);
Query OK, 1 row affected (0.005 sec)

所以这个括号并不是用来限制int的长度的，而是当一个数字小于5位的时候，会给这个数字前补0

MariaDB [hello]> insert into test_int values (18,3);
Query OK, 1 row affected (0.005 sec)MariaDB [hello]> select * from test_int;
+----------+---+
| a        | b |
+----------+---+
|    00003 | 1 |
| 12345678 | 1 |
|    00018 | 3 |
+----------+---+
3 rows in set (0.001 sec)

如果再把int(5) zerofill改成更长的数值，前补0的长度就会变化

alter table test_int change a a int(7) unsigned zerofill;

MariaDB [hello]> alter table test_int change a a int(7) unsigned zerofill;
Query OK, 0 rows affected (0.007 sec)
Records: 0  Duplicates: 0  Warnings: 0MariaDB [hello]> select * from test_int;
+----------+---+
| a        | b |
+----------+---+
|  0000003 | 1 |
| 12345678 | 1 |
|  0000018 | 3 |
+----------+---+
3 rows in set (0.001 sec)

需要注意，int(n)的属性只有和zerofill一起使用，才会触发前补0的操作。这便能解释为何最初创建的表里面是int(11)，但数字并没有被前补0；

而这里的前补0，只是一个在MySQL内部显示的优化，实际上存储的依旧是数字本身；比如我们在上表中查询3，是可以直接查出来的。

MariaDB [hello]> select * from test_int where a = 3;
+---------+---+
| a       | b |
+---------+---+
| 0000003 | 1 |
+---------+---+
1 row in set (0.001 sec)

比如我要存储的数字都是5位的，设置了前补0，查询整表时看到的格式化输出的结果会比没有前补0的结果看上去舒服很多。

4.3 为什么int是11，无符号是10？

在前面查询默认的创建表i语句的时候，会发现MySQL系统默认给int了11位，无符号int是10位

这是因为10位的长度已经能标识int范围内的所有值了，而有符号整数多了一位，是用来显示正负号的。

5.主键primary key

主键是用于约束字段里面的数据，不能重复，不能为空；一张表只有一个主键（或者没有），一般都是用整形作为主键。

主键是用于确定表中每一条记录的唯一性的，其告知了使用者，要想往这个表中插入数据，就必须保证主键的值不冲突。

5.1 主键的设计类型

以一个用户系统为例

• 我们可以把用户名设置为主键，当用户选择了一个已经存在的用户名时，拒绝此用户名并告知用户；
• 我们可以使用另外一个无关的数字作为主键，比如QQ中就使用了QQ号来标识用户唯一性，并不要求用户的用户名不能相相同（现在绝大部分聊天软件都使用了这种方式，比如QQ和KOOK，微信虽然没有QQ那样的唯一标识，但后台肯定也是有主键作为唯一性标识的）、
• 我们可以将多列组成复合主键

5.2 删除和添加主键

在创建表的时候，可以用两种方式来指定主键

-- 方法1，在字段后指明
create table test_pri_1(id int unsigned not null primary key,name varchar(30) not null
);
-- 方法2，在表的最后指明
create table test_pri_2(id int unsigned not null,name varchar(30) not null,primary key(id)
);

二者都能执行成功。

另外，主键本身就是不能为空的，所以我们定义主键列的时候可以不用写not null

MariaDB [hello]> create table test_pri_1(-> id int unsigned not null primary key,->     name varchar(30) not null-> );
Query OK, 0 rows affected (0.017 sec)MariaDB [hello]> create table test_pri_2(-> id int unsigned not null,->     name varchar(30) not null,->     primary key(id)-> );
Query OK, 0 rows affected (0.019 sec)

而且表结构相同，在id列的Key处可以看到PRI，就是Primary的缩写，代表id列是主键

MariaDB [hello]> desc test_pri_1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(30)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.001 sec)MariaDB [hello]> desc test_pri_2;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(30)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.001 sec)

如果是一个已经存在的表，我们也可以往里面追加主键或者删除主键

alter table 表名 drop primary key;
-- 用于删除主键列的主键属性，因为主键列只能有一个
-- 注意，这个语句不会删除该列
alter table 表名 add primary key(id);
-- 给id列加上主键属性（但是ID列里面不能有重复值）

测试一下，可以看到id列的PRI属性没有了

MariaDB [hello]> alter table test_pri_1 drop primary key;
Query OK, 0 rows affected (0.010 sec)              
Records: 0  Duplicates: 0  Warnings: 0MariaDB [hello]> desc test_pri_1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   |     | NULL    |       |
| name  | varchar(30)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.002 sec)

当我们往表里面插入数据的时候，如果想往主键列插入一个相同的记录，MySQL会拒绝插入

MariaDB [hello]> insert into test_pri_2  values (1,'李华');
Query OK, 1 row affected (0.006 sec)MariaDB [hello]> insert into test_pri_2  values (1,'李明');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

5.3 复合主键

一张表只有一个主键，但是主键可以不止一列；

以我自己写的活跃度统计机器人为例，机器人处在不同服务器中，会收到不同的服务器ID，和不同用户的操作；为了记录不同服务器的不同用户的活跃度情况，在用户统计表中，需要同时有服务器ID和用户ID；此时就会出现一个用户加入了两个服务器，而这两个服务器都使用了我这个机器人的情况。反馈到表中，就是一个用户ID出现了两次，但对应的服务器ID不同；

在这种情形下，肯定是不能把用户ID或服务器ID单独设置成主键的。我们就可以把用户ID和服务器ID统一设置成符合主键；

设置了复合主键后，我们可以出现相同的服务器ID，和相同的用户ID。但只能是某个服务器的某个用户，不能存在两条服务器ID和用户ID都相同的记录。这便是复合主键的作用！

create table user(guild_id int unsigned comment '服务器ID',user_id int unsigned not null comment '用户ID',score tinyint unsigned not null default 0 comment '用户积分',primary key(guild_id, user_id) -- guild_id + user_id 为复合主键
);

此时查看表结构，会发现服务器id和用户id的两个键值，在Key里面都有PRI属性，即他们都是主键；

而且，即便我们的guild_id没有指定not null，其的NULL属性依旧是NO。因为主键是不允许为NULL的！

MariaDB [hello]> create table user(-> guild_id int unsigned comment '服务器ID',-> user_id int unsigned not null comment '用户ID',-> score tinyint unsigned not null default 0 comment '用户积分',-> primary key(guild_id, user_id) -- guild_id + user_id 为复合主键-> );
Query OK, 0 rows affected (0.011 sec)MariaDB [hello]> desc user;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| guild_id | int(10) unsigned    | NO   | PRI | NULL    |       |
| user_id  | int(10) unsigned    | NO   | PRI | NULL    |       |
| score    | tinyint(3) unsigned | NO   |     | 0       |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.003 sec)

当我们插入时，服务器id和用户id可以在各自列中重复。

MariaDB [hello]> insert into user values (1,1,0);
Query OK, 1 row affected (0.008 sec)MariaDB [hello]> insert into user values (1,2,0);
Query OK, 1 row affected (0.008 sec)MariaDB [hello]> insert into user values (2,1,0);
Query OK, 1 row affected (0.001 sec)

但如果你想在已经有服务器id为1，用户id为1的记录的基础上再插入一条这样的记录，那就会报错拒绝插入

MariaDB [hello]> select * from user;
+----------+---------+-------+
| guild_id | user_id | score |
+----------+---------+-------+
|        1 |       1 |     0 |
|        1 |       2 |     0 |
|        2 |       1 |     0 |
+----------+---------+-------+
3 rows in set (0.000 sec)MariaDB [hello]> insert into user values (1,1,10);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'

6.自增auto_increment

自增，人如其名，就是MySQL会自动帮我们往这个列添加数据。比如一个序号，新增一条记录就会将序号加一；自增属性只能添加给整形！

自增的列必须是主键！

6.1 使用

自增的使用办法是在创建表的键值后添加这个约束

create table test_pri_3(id int unsigned auto_increment,name varchar(30) not null,primary key(id)
);

查看表结构，可以看到id列是主键，非空，且具有自增属性

MariaDB [hello]> desc test_pri_3;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.003 sec)

由于自增的列必须是主键，所以我们不能将其和主键分开来使用；

如果在设置自增的时候没有将这列同时设置为主键，那么创建表的时候就会报错

MariaDB [hello]> create table test_pri_4(-> id int unsigned auto_increment,->     name varchar(30) not null-> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

设置了自增之后，我们同样可以显示的指定该列的值；也可以不指定，直接插入其他列的数据。MySQL会自动帮我们把当前记录+1。

MariaDB [hello]> insert into test_pri_3 values (1,'李华');
Query OK, 1 row affected (0.007 sec)MariaDB [hello]> select * from test_pri_3;
+----+--------+
| id | name   |
+----+--------+
|  1 | 李华   |
+----+--------+
1 row in set (0.001 sec)MariaDB [hello]> insert into test_pri_3 (name) values ('小明');
Query OK, 1 row affected (0.001 sec)MariaDB [hello]> select * from test_pri_3;
+----+--------+
| id | name   |
+----+--------+
|  1 | 李华   |
|  2 | 小明   |
+----+--------+
2 rows in set (0.000 sec)

多插入几条数据，可以看到id列都成功自增了

MariaDB [hello]> insert into test_pri_3 (name) values ('小明3');
Query OK, 1 row affected (0.005 sec)MariaDB [hello]> insert into test_pri_3 (name) values ('小明5');
Query OK, 1 row affected (0.005 sec)MariaDB [hello]> select * from test_pri_3;
+----+---------+
| id | name    |
+----+---------+
|  1 | 李华    |
|  2 | 小明    |
|  3 | 小明3   |
|  4 | 小明5   |
+----+---------+
4 rows in set (0.000 sec)

6.2 自增是怎么判断当前所处序号位置的？

自增的长度是按最大的那个数字开始自增的？还是说有其他处理流程？

先尝试往表里面主动插入一个1000为id的键值，然后再不指定id的情况下再插入两行记录

MariaDB [hello]> insert into test_pri_3 values (1000,'test');
Query OK, 1 row affected (0.006 sec)MariaDB [hello]> insert into test_pri_3 (name) values ('test1');
Query OK, 1 row affected (0.005 sec)MariaDB [hello]> insert into test_pri_3 (name) values ('test2');
Query OK, 1 row affected (0.001 sec)

查询列表，会发现在这1000之后的的记录，全都是从1000开始增加的。

MariaDB [hello]> select * from test_pri_3;
+------+---------+
| id   | name    |
+------+---------+
|    1 | 李华    |
|    2 | 小明    |
|    3 | 小明3   |
|    4 | 小明5   |
| 1000 | test    |
| 1001 | test1   |
| 1002 | test2   |
+------+---------+
7 rows in set (0.000 sec)

莫非是依照最大的id来进行自增的吗？我们再来试试。

先把最大id的记录删除，再插入一个新数据

MariaDB [hello]> delete from test_pri_3 where id = 1002;
Query OK, 1 row affected (0.007 sec)MariaDB [hello]> insert into test_pri_3 (name) values ('test3');
Query OK, 1 row affected (0.006 sec)

再次查询，会发现自增的id是1003。可见其内部记录过一个id的最大值，是按内部一个额外的记录来进行自增的，而不是判断表中id列的最大值！

MariaDB [hello]> select * from test_pri_3;
+------+---------+
| id   | name    |
+------+---------+
|    1 | 李华    |
|    2 | 小明    |
|    3 | 小明3   |
|    4 | 小明5   |
| 1000 | test    |
| 1001 | test1   |
| 1003 | test3   |
+------+---------+
7 rows in set (0.001 sec)

那这个额外的记录在哪里呢？

show create table test_pri_3;

使用如上命令查看创建表的sql语句，你会发现紧跟在表之后的，就有一个自增的字段AUTO_INCREMENT=1004

MariaDB [hello]> show create table test_pri_3;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                              |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_pri_3 | CREATE TABLE `test_pri_3` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(30) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

这就是MySQL中对自增字段当前值的定义，这里存放的就是下一个插入的记录，其id的自增值。每次插入一个信的记录，这里的自增值就会对应变换为下一个记录应该是多少的数值；

举个例子，如果我们想让一个系统的ID从10001开始自增，那么我们就可以在创建了表之后，直接往表里面插入一个id为10000的记录。在这之后创建的其他记录，id就会从10001开始自增了！

6.3 索引

讲到这里，顺带一提MySQL中索引的概念

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

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。 数据库使用索引以找到特定值，然后顺指针找到包含该值的行。

这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

索引本质上就是一个用空间换时间的套路。在当前多并发的业务中，执行速度远比占用内存、磁盘多少来的更重要！

7.唯一键unique

唯一键对字段的约束，那就是这一列的数据都不能出现相同的。

看起来和主键有点相似，但实际上其是独立于主键之外的一种唯一性的约束。和主键的区别在于：唯一键可以为NULL

要知道，一个表里面的主键只能设置一个。复合主键在某些时候并不能满足我们的需求。于是MySQL就在主键之外，额外提供了唯一键的约束，让我们可以给其他列设置唯一性。

至于为什么要这么做？就好比一个免责声明：我这列的数据设置了唯一，那么就不可能接受两个相同的记录（比如用户表中两个人却有相同手机号，是不应该的）如果你的业务中出现了拒绝插入的报错，那么就应该去看业务处理代码中是哪里有BUG，而不应该怪罪MySQL没有维护唯一性或者拒绝记录的插入。

7.1 单独唯一键

假设我有一个平台，类似于qq一样使用了一个qq号作为用户的主键；但为了实名认证，我们又要求一个身份证只能注册一个账户。此时就无法用复合主键来解决这个问题，因为复合主键是允许其中某一列有重复的；而我们需要的是用户账户编号和用户身份证号都不能重复！

同时，在用户的联系方式中，两个用户的电话号码、微信号也不应该出现相同，如果要添加电话号码的键值，也可以将其设置为unique！

此时就可以将用户编号作为主键，用户身分证号设置unique作为唯一键；

create table test_unique_1(no int unsigned not null primary key,name varchar(30) not null,id_card varchar(30) not null unique 
);

查看表结构，唯一键的列，Key的约束是UNI，即unique的缩写

MariaDB [hello]> desc test_unique_1;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| no      | int(10) unsigned | NO   | PRI | NULL    |       |
| name    | varchar(30)      | NO   |     | NULL    |       |
| id_card | varchar(30)      | NO   | UNI | NULL    |       |
+---------+------------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

当我们往这个表中的主键列或者id_card列插入相同记录时，MySQL都会拒绝插入

MariaDB [hello]> insert into test_unique_1 values (1,'李华',123456);
Query OK, 1 row affected (0.006 sec)MariaDB [hello]> insert into test_unique_1 values (1,'李华',1234567);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'MariaDB [hello]> insert into test_unique_1 values (3,'小明',123456);
ERROR 1062 (23000): Duplicate entry '123456' for key 'id_card'

7.2 复合唯一键

唯一键也能设置多列，效果和复合主键相同；这里不再说明

create table user(user_no int unsigned primary key auto_increment comment '用户编号 主键',guild_id int unsigned comment '服务器ID',user_id int unsigned not null comment '用户ID',score tinyint unsigned not null default 0 comment '用户积分',unique(guild_id, user_id) -- guild_id + user_id 为复合唯一键
);

表中对guild_id和user_id的约束就变成了MUL，如下图所示；

其中能观察到，guild_id因为没有设置not null，其NULL一栏为YES，代表这列可以为NULL（唯一键可以为NULL，主键不能）

而MUL则代表目前允许多行在此列具有相同的值，但guild_id和user_id都相同的两行是不允许存在的

MariaDB [hello]> desc user;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| user_no  | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| guild_id | int(10) unsigned    | YES  | MUL | NULL    |                |
| user_id  | int(10) unsigned    | NO   |     | NULL    |                |
| score    | tinyint(3) unsigned | NO   |     | 0       |                |
+----------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

如下，最后的插入和第一次的插入中的guild_id和user_id相同，于是就出现了拒绝插入的报错

MariaDB [hello]> insert into user values (1,1,1,20);
Query OK, 1 row affected (0.006 sec)MariaDB [hello]> insert into user values (2,1,2,21);
Query OK, 1 row affected (0.005 sec)MariaDB [hello]> insert into user values (3,2,3,22);
Query OK, 1 row affected (0.006 sec)MariaDB [hello]> insert into user values (4,1,1,23);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'guild_id'

7.3 在MySQL中MUL、PRI和UNI是什么？

从Mysql 5.7官网文档可知：

• 如果键是PRI，则列是主键或多列主键中的列之一；
• 如果键是UNI，则该列是唯一索引的第一列(唯一索引允许多个空值，但可以通过检查NULL字段来判断该列是否允许空)；
• 如果键为MUL，则该列是非唯一索引的第一列，其中允许在列中多次出现给定值；

8.外键

8.1 说明

外键是用来定义两张表中某些字段的关系，并来约束记录的；

基础语法如下，在创建表的时候使用。设置外键的表是从表！

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

比如下图中，学生表中每个学生的班级编号都对应了班级表中班级的id，此时我们就可以将班级表的id设置为学生表中class_id的外键；

虽然我们可以将class表中的数据直接插入到学生表里面，但是这样并不合理。如果我们针对一个班级，或者针对一个学生的字段有非常多的话，将这两张表合起来是非常不方便的。因为同一个班级会有很多学生，合并表之后，就相当于同一个班级的学生，他们的班级列的信息全是相等的，这就存在了无意义的资源占用（冗余）。

相比之下，分表了之后，再采用外键的方式来绑定两个字段，是更好的选择！

在上图的情况中，myclass是主表，stu是一个从表；

-- 主表 班级
create table myclass (id int primary key comment '班级号',name varchar(30) not null comment '班级名'
);
-- 从表 学生
create table stu (id int primary key,name varchar(30) not null comment '学生名',class_id int,foreign key (class_id) references myclass(id) -- 设置从表外键约束
);

8.2 测试

创建好表之后，先来看看学生表i的属性，可以看到class_id表的key是一个MUL，和前面设置复合唯一键的时候相同。

MariaDB [hello]> desc stu;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(30) | NO   |     | NULL    |       |
| class_id | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

当我们尝试往学生表里面插入一个数据的时候，会报错

MariaDB [hello]> insert into stu values (1,'李华',2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`hello`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `myclass` (`id`))

这是因为你设置的这个记录中，学生的班级编号2压根在班级表里面不存在。一个不存在的班级怎么可以有学生呢？所以自然就拒绝了你的插入。

所以，要想插入学生，我们需要保证这个学生的记录所在班级，是存在于班级表里面的！这样就实现了学生和班级N对1的绑定。

MariaDB [hello]> insert into myclass values (1,'少华班');
Query OK, 1 row affected (0.002 sec)MariaDB [hello]> insert into stu values (1,'李华',1);
Query OK, 1 row affected (0.005 sec)MariaDB [hello]> select * from myclass;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 少华班    |
+----+-----------+
1 row in set (0.001 sec)MariaDB [hello]> select * from stu;
+----+--------+----------+
| id | name   | class_id |
+----+--------+----------+
|  1 | 李华   |        1 |
+----+--------+----------+
1 row in set (0.000 sec)

这里我又多插入了几个数据

MariaDB [hello]> select * from stu;
+----+-----------+----------+
| id | name      | class_id |
+----+-----------+----------+
|  1 | 李华      |        1 |
|  2 | 小明      |        1 |
|  3 | 小流      |        1 |
|  4 | 小流2     |        2 |
|  5 | 猪猪侠    |        2 |
|  6 | 苗条俊    |        2 |
+----+-----------+----------+
6 rows in set (0.000 sec)MariaDB [hello]> select * from myclass;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 少华班    |
|  2 | 你好班    |
+----+-----------+
2 rows in set (0.000 sec)

此时还有另外一个问题：如果这个班级有学生，我们可以把这个班级删掉吗？

考虑看来，肯定是不行的：既然没有这个班级，你不能插入对应的学生。那么这个班级有学生的时候，你也不应该把班级删除。二者是相互的逻辑；在MySQL里面也是如此，当一个班级有对应的学生的时候，是不允许删除的。

MariaDB [hello]> delete from myclass where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`hello`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `myclass` (`id`))

只有当这个班级没有学生了，才能从班级表中被删除！

MariaDB [hello]> delete from stu where class_id = 2;
Query OK, 3 rows affected (0.005 sec)MariaDB [hello]> delete from myclass where id = 2;
Query OK, 1 row affected (0.006 sec)MariaDB [hello]> 

更新班级id同样是不允许的，因为在学生表中有学生绑定了这个班级。MySQL并不能做到帮我们直接更新所有学生的班级号。

MariaDB [hello]> update  myclass set id = 3 where name = '你好班'; 
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`hello`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `myclass` (`id`))

8.3 外键约束

在上面的情况中，我们完全可以建立两个没有外键关系的独立表，在代码层进行两者关系的维护。

但是这样，这两个表的操作依旧是独立的，MySQL是不知道这两个表之间有毛线关联的；此时你就可以往不存在的班级里面插学生，把还有学生的班级删掉，最终就乱了套了；

• 自己维护：两个表的信息有关联
• 加上外键：MySQL的约束

两者合一，才是外键的完全体！

所以我们才需要在MySQL中，将这两个表之间定义外键的约束，让MySQL协助我们维护两张表中的数据关系。

注意：虽然此时MySQL会约束我们的操作，但实际上的业务流程还是需要程序袁在代码中处理。比如不要往MySQL中插入班级不存在的学生（MySQL只会拒绝插入，并不能帮你把班级给修正）

The end

基础的约束操作就是这些了，有其他会用到的，日后再新增！