MySql中表的约束

本篇中将会介绍关于 MySql 数据库中的表的约束，关于表的约束其实约束的是表中的数据类型，因为有的数据类型很单一，需要我们添加一些额外的约束，才能更好的保证数据的合法性，从业务逻辑角度保证数据的正确性，比如QQ号，要求是唯一的。

文章目录

- - 空属性NULL
  - 默认值
  - 列描述
  - zerofill
  - 主键
  - 自增长
  - 唯一键
  - 外键

空属性NULL

对于空属性来说，包含两个值：null(通常默认是这个)，not null

数据库默认字段基本都是默认为null，但是在实际的开发中，需要我们尽可能的保证字段不为空，因为数据为空没办法保证参数运算。

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

如上所示，当我们查找null时，出现的是null，查找null+1时，出现的还是null，null不会参与到运算中。

如下，创建一个表中字段不为null的表，其中的属性也显示Null值为no，插入的时候也需要插入完整数据才可以：

mysql> create table class (-> class_name varchar(20) not null,-> class_room varchar(20) not null-> );
Query OK, 0 rows affected (0.02 sec)mysql> desc class;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO   |     | NULL    |       |
| class_room | varchar(20) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> insert into class(class_name) values('class1');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

默认值

默认值：当我们向表中插入某些个值的时候，没有指定哪列属性为什么值的时候，那么将会在该列中插入列默认的值，如下：

mysql> create table person (-> name varchar(20) not null,-> age tinyint unsigned default 0,-> sex char(2) default '男'-> );
Query OK, 0 rows affected (0.03 sec)mysql> desc person;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | 0       |       |
| sex   | char(2)             | YES  |     | 男      |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> insert into person(name) values('张三');
Query OK, 1 row affected (0.00 sec)mysql> select * from person;
+--------+------+------+
| name   | age  | sex  |
+--------+------+------+
| 张三   |    0 | 男    |
+--------+------+------+
1 row in set (0.00 sec)

如上我们创建的表中，age和sex就设置了对应的默认值，但同时我们还会发现其Null是为YES的，但是这并不妨碍直接插入的时候优先选择默认值

列描述

列描述：comment，没有实际的含义，只是用来描述字段，会根据创建语句保存起来，便于之后查询表属性的时候区别列对应的含义。如下：

mysql> create table person (-> name varchar(20) not null comment '姓名',-> age tinyint unsigned default 0 comment '年龄',-> sex char(2) default '男' comment '性别'-> );
Query OK, 0 rows affected (0.02 sec)mysql> desc person-> ;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)         | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | 0       |       |
| sex   | char(2)             | YES  |     | 男      |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> show create table person\G;
*************************** 1. row ***************************Table: person
Create Table: CREATE TABLE `person` (`name` varchar(20) NOT NULL COMMENT '姓名',`age` tinyint(3) unsigned DEFAULT '0' COMMENT '年龄',`sex` char(2) DEFAULT '男' COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

如上所示，使用desc查看表格的时候并不能看到注释信息，通过show可以查看。

zerofill

我们在定义数字类型的时候（以int为例），通过show查看到关于int还有一个括号，如下：

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

如上所示，int(10)，这个10表示什么含义呢？这个10和zerofill属性有关，如下所示，将表中a的类型加上zerofill约束：

mysql> alter table t1 change a a int(5) unsigned zerofill;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> insert into t1 values(1, 2);
Query OK, 1 row affected (0.01 sec)mysql> select * from t1;
+-------+------+
| a     | b    |
+-------+------+
| 00001 |    2 |
+-------+------+
1 row in set (0.00 sec)

如上所示，当我们将a的属性加上zerofill约束之后，显示出的值就在前面多出了四个0，这是因为zerofill的作用就是设定值的宽度，当宽度小于设定宽度的时候，自动填充0。但其实zerofill只是格式化输出了对应的变量，其本质还是int类型，当我们使用十六进制输出a值的时候，就不会显示出多余的0了，如下：

mysql> select a, hex(a) from t1;
+-------+--------+
| a     | hex(a) |
+-------+--------+
| 00001 | 1      |
+-------+--------+
1 row in set (0.00 sec)

主键

主键：primary key用来唯一的约束该字段里面的数据，一张表中最多只能有一个主键并且主键中的值不能重复。（主键所在列通常是整数类型）

创建表的时候直接在该字段上指定主键，如下：

mysql> create table t2 (-> id int unsigned primary key comment '学号不能为null',-> name varchar(20) not null-> );
Query OK, 0 rows affected (0.01 sec)mysql> desc t2;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

主键约束：主键对应字段中不能重复，一旦重复就会操作失败，如下：

mysql> insert t2 values (1, '张三');
Query OK, 1 row affected (0.00 sec)mysql> insert t2 values (1, '李四');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

删除主键与向表中追加主键，语法如下：

alter table table_name drop primary key;
alter table table_name add primary key(字段列表);

mysql> alter table t2 drop primary key;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0mysql> alter table t2 add primary key(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

复合主键：创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用重复主键，如下：

mysql> create table t3 (-> id int unsigned,-> course char(10) comment '课程代码',-> score tinyint unsigned default 10 comment '成绩',-> primary key(id ,course)-> );
Query OK, 0 rows affected (0.02 sec)mysql> desc t3;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     | int(10) unsigned    | NO   | PRI | NULL    |       |
| course | char(10)            | NO   | PRI | NULL    |       |
| score  | tinyint(3) unsigned | YES  |     | 10      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> insert into t3 (id, course) values (1, '123');
Query OK, 1 row affected (0.00 sec)mysql> insert into t3 (id, course) values (1, '123');
ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY'

自增长

auto_increment：当对应的字段不给值的时候，会自动的被系统触发，系统会从当前字段中已经有的最大值进行加一操作，得到一个新的不同的值。（通常和主键搭配使用，作为逻辑主键）

mysql> create table t4 (-> id int unsigned primary key auto_increment,-> name varchar(10) not null default ''-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert into t4 (name) values ('a');
Query OK, 1 row affected (0.00 sec)mysql> insert into t4 (name) values ('b');
Query OK, 1 row affected (0.00 sec)mysql> select * from t4;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

在插入之后获取上次插入的auto_increment的值（若是批量插入后获取的是第一个值），如下：

mysql> select last_insert_id() from t4;
+------------------+
| last_insert_id() |
+------------------+
|                2 |
|                2 |
+------------------+
2 rows in set (0.00 sec)

索引：在关系数据库中，索引是一种单独的、物理的对数据库表中一列或者多列的值进行排序的一种存储结构，他是表中某一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相对于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据指定的排序顺序对这些指针排序。数据库使用索引找到特定的值，然后顺着指针找到包含在该值的行。这样可以使得对应于表的SQL语句执行得更快，可以快速访问数据库表中的特定信息。

唯一键

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

唯一键的本质和主键差不多，唯一键允许为空，而且允许多个为空，所以唯一键对于空值不做唯一键约束。

唯一键和主键的区别：主键更多是标识唯一性的，而唯一键更多是保证在业务上，不要和别的信息出现重复。（比如我们使用的身份证可以是作为唯一标识我们的主键，但是我们在学校中也有对应的学号用来唯一的标识我们，学校并不会直接使用身份证来唯一的标识我们）

mysql> create table student (-> id char(10) unique comment '学号，不能重复但可以为null',-> name varchar(20)-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert into student (id, name) values ('01', 'zhangsan');
Query OK, 1 row affected (0.00 sec)mysql> insert into student (id, name) values ('01', 'lisi');
ERROR 1062 (23000): Duplicate entry '01' for key 'id'
mysql> insert into student (id, name) values (null, 'lisi');
Query OK, 1 row affected (0.00 sec)mysql> select * from student;
+------+----------+
| id   | name     |
+------+----------+
| 01   | zhangsan |
| NULL | lisi     |
+------+----------+
2 rows in set (0.00 sec)

外键

外键用于定义主表和从表之间的关系：外键约束组合要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键之后，要求外键列数据必须在主表的主键列存在或者为null。

语法：

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

如下所示：
在这里插入图片描述
如上图所示，假若我们将班级表中的数据都设计在每个学生表的后面，那么就会出现数据冗余，所以我们只需要设计成让 stu->class_id class->id，这样形成关联的关系，这就是外键约束。

对上图进行设计：

// 主表
mysql> create table class ( id int primary key, name varchar(30) not null comment '班级名' );
Query OK, 0 rows affected (0.02 sec)// 从表
mysql> create table stu (-> id int primary key,-> name varchar(30) not null comment '学生名',-> class_id int,-> foreign key (class_id) references class(id)-> );
Query OK, 0 rows affected (0.02 sec)

插入数据：

mysql> insert into class values (10, '数学分析'), (20, '计算机网络');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> insert into stu values (100, 'zhangsan', 10), (101, 'lisi', 20);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> insert into stu values (102, 'wangwu', 30);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))mysql> insert into stu values (102, 'wangwu', null);
Query OK, 1 row affected (0.00 sec)

如上所示，想要正常的插入数据，具有外键约束的变量只能插入合法的值或者null值，若插入的数据不为null且外键约束中没有这个值，那么就会插入失败。

外键约束的存在更加严格的让我们遵循数据之间的关联性，只要是存在关联的数据，就必须遵守外键约束，这样可以很好的防止出现异常数据。（比如当我们在学校对学生数据进行管理，设立外键约束就不会出现学生所属班级是不存在的班级的情况）