上海工商管理局官网_青州网站优化_宁德市政府_武汉百度推广电话

前言

上篇文章说过，约束本质是为了保证插入数据的有效性和正确性，如果不符合约束会进行报错，而数据类型本身就是一种约束，但这种约束比较单一，在多数情况下需要很多约束来一起对数据进行限制，进而达到约束的最终目的，反过来站在mysql视角，有了约束的保障，意味着里面保存的数据都是可预期和完整的

1. 空属性

两个值：null（默认的）和not null(不为空)

数据库默认字段基本都是字段允许为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算

NULL vs ’ ’
NULL表示什么都没有，而’'表示有，但内容是空字符串

创建class表并对其中几列设置非空属性：

mysql> create table class (->class_name varchar(10) not null, ->class_room varchar(10) not null, ->other varchar(10)->);
Query OK, 0 rows affected (0.09 sec)mysql> desc class;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(10) | NO   |     | NULL    |       |
| class_room | varchar(10) | NO   |     | NULL    |       |
| other      | varchar(10) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

第三列Null表示表中的class_name与class_room列在插入数据时不允许插入空值，而other则允许：

# 全列插入每列都要插入，且不允许为空的列不能插入空值，否则需要插入空值或其它具体值
mysql> insert into class values ('高三1', '106', null);
Query OK, 1 row affected (0.02 sec)mysql> insert into class (class_name, class_room)  values ('高三2', '206');
Query OK, 1 row affected (0.01 sec)mysql> select * from class;
+------------+------------+-------+
| class_name | class_room | other |
+------------+------------+-------+
| 高三1      | 106        | NULL  |
| 高三2      | 206        | NULL  |
+------------+------------+-------+

非法插入：

# 设置了not null的列不允许插入空值
mysql> insert into class values ('高三3', null, null);
ERROR 1048 (23000): Column 'class_room' cannot be null

~~就想插入一个空值怎么办？插入’NULL’字符串~~

2. 默认值

最开始建表时可以给每一列设置一个缺省值，不设置的话这个值为NULL值，在插入数据时如果用户提供了数据，那就用用户的，没提供的话就填充为最开始所设置的缺省值，要么是null要么是用户在建表时设置的值

mysql> desc class;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(10) | NO   |     | NULL    |       |
| class_room | varchar(10) | NO   |     | NULL    |       |
| other      | varchar(10) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

第五列default表示，表中每列的缺省值是什么，默认为NULL

mysql> insert into class (class_name)  values ('高三2');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

需要注意的是，如果在插入数据时没有插入某列，且该列设置为了not null且也没有给其设置默认值的话，会直接报错，除非给当前列设置默认值或者允许当前列插入空值

给class_room列设置默认值（或者去掉not null）:

mysql> alter table class modify class_room varchar(20) not null default '000';
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 这样插入就没问题了，使用的默认值000
mysql> insert into class (class_name)  values ('高三2');
Query OK, 1 row affected (0.01 sec)mysql> desc class;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO   |     | NULL    |       |
| class_room | varchar(20) | NO   |     | 000     |       |
| other      | varchar(10) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> select * from class;
+------------+------------+-------+
| class_name | class_room | other |
+------------+------------+-------+
| 高三1      | 106        | NULL  |
| 高三2      | 206        | NULL  |
| 高三2      | 000        | NULL  |
+------------+------------+-------+

2.1 not null 与 default

二者并不冲突而是相互补充的

not null约束的是当前列的值必须是非null的，如果插入的数据是null直接报错
default约束的是如果在插入一行记录且有的列没有提供数据时，看其是否设置了默认值，设置了就使用默认值，否则插入null，如果当前列还有not null约束的话会报错

3. 列描述

comment字段，或者说注释，是对当前列进行补充说明的，没什么含义也没什么约束，使用show create table xxx;可以查看到当时创建表时每列的说明字段，如果带了comment字段的话

用来给程序员和数据库管理人员看的

mysql> create table stu(-> name varchar(5) not null comment '这个是用来描述用户名的',-> age tinyint not null comment '这个是用来描述用户年龄的'-> );
Query OK, 0 rows affected (0.10 sec)mysql> show create table stu \G;
*************************** 1. row ***************************Table: stu
Create Table: CREATE TABLE `stu` (`name` varchar(5) NOT NULL COMMENT '这个是用来描述用户名的',`age` tinyint NOT NULL COMMENT '这个是用来描述用户年龄的'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)ERROR: 
No query specified

4. zerofill

mysql> desc t6;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| n1    | int                       | YES  |     | NULL    |       |
| n2    | int unsigned              | YES  |     | NULL    |       |
| n3    | int(10) unsigned zerofill | YES  |     | NULL    |       |     |
+-------+---------------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)mysql> show create table t6 \G;
*************************** 1. row ***************************Table: t6
Create Table: CREATE TABLE `t6` (`n1` int DEFAULT NULL,`n2` int unsigned DEFAULT NULL,`n3` int(10) unsigned zerofill DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)ERROR: 
No query specified

在创建表时若定义数值类型且后面设置了zerofill，mysql会自动在数值类型后加上一个小括号并设置一个值，这里的设置的值实际上是一个位宽也就是显示宽度，若插入的数值的位数小于当前值的话会在前面填充0进行补齐达到定义的显示宽度，如果位数大于这个值则无事发生

这个值对于有符号int来说是10，无符号则是11，因为要保存符号位，int是四个字节，无符号最大表示四十二亿多，也就是10位数字，刚好能保存下

mysql> insert into t6 values(1,2,3);
Query OK, 1 row affected (0.01 sec)mysql> select * from t6;
+------+------+------------+
| n1   | n2   | n3         |
+------+------+------------+
|    1 |    2 | 0000000003 |
+------+------+------------+
1 row in set (0.01 sec)mysql> alter table t6 modify n3 int(3) zerofill;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 2mysql> insert into t6 values(1,2,333);
Query OK, 1 row affected (0.01 sec)mysql> select * from t6;
+------+------+------+
| n1   | n2   | n3   |
+------+------+------+
|    1 |    2 |  003 |
|    1 |    2 |  333 |
+------+------+------+
2 rows in set (0.00 sec)

最后要注意的是zerofill并不会影响数据的实际存储，仅仅只会影响后续的格式化显示

5. 主键

primary key即主键，是用来约束当前字段或者属性列中的数据在整个列中具有唯一性，不能重复，不能为空，一张表中最多只能有一个主键，主键列通常是整数类型

主键列通常使用整数类型，但这并不是强制性的，选择哪种数据类型取决于具体的应用场景和需求

5.1 创建主键

创建一个带主键的表，并查看创建细节：

mysql> create table stu(-> id int unsigned primary key comment 'id不能为空',-> name varchar(10)-> );
Query OK, 0 rows affected (0.08 sec)
# 在类型后带上primary key表示设置当前列为主键mysql> desc stu;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   | PRI | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# Key表示主键，带pri的那一列为主键，也就是idmysql> show create table stu \G;
*************************** 1. row ***************************Table: stu
Create Table: CREATE TABLE `stu` (`id` int unsigned NOT NULL COMMENT 'id不能为空',`name` varchar(10) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
# 可以看到指令是被修改优化了的
# 修改后主键列自动加了not null且设置主键的方式发生了变化
# 这种设置主键的方式主要用在复合主键上

主键列插入相同的数据会报错不让插入：

mysql> insert into stu values (1, '蜘蛛侠');
Query OK, 1 row affected (0.04 sec)mysql> insert into stu values (1, '夜魔侠');
ERROR 1062 (23000): Duplicate entry '1' for key 'stu.PRIMARY'

根据主键的性质，在表中查找某个主键值所得到的记录一定是存在且唯一的，所以就可以对其进行更新或修改：

mysql> select * from stu where id=1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 蜘蛛侠    |
+----+-----------+
1 row in set (0.00 sec)mysql> update stu set name='钢铁侠' where id=1;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from stu;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 钢铁侠    |
|  2 | 夜魔侠    |
+----+-----------+
2 rows in set (0.00 sec)

5.2 删除主键

由于主键是唯一的，所以直接删除就好，语法如下：

mysql> alter table stu drop primary key;
Query OK, 2 rows affected (0.24 sec)
Records: 2  Duplicates: 0  Warnings: 0
# 删除后，Key列中就没有显示主键列了
mysql> desc stu;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   |     | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

主键被删除就意味着没有主键约束了，在合法的前提下可以插入相同的数据

5.3 添加主键

如果没有在建表时设置主键，也可以通过下述方式添加某一列为主键：

mysql> alter table stu add primary key(id);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 这样id就又变成了主键列
mysql> desc stu;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   | PRI | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

需要注意的时，再把某列设置为主键时，前提要保证当前表中该列的数据都要具有唯一性，不能出现重复，否则会添加主键失败：

mysql> select * from stu;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 钢铁侠    |
|  2 | 夜魔侠    |
|  2 | 蜘蛛侠    |
+----+-----------+
3 rows in set (0.00 sec)mysql> alter table stu add primary key(id);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'primary key(id)' at line 1

2重复了，解决方案就是删除重复的记录：

mysql> delete from stu where name='蜘蛛侠';
Query OK, 1 row affected (0.04 sec)
# 这样便设置成功了
mysql> alter table stu add primary key(id);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

但其实主要问题在于应该删谁呢？删谁都不合理，所以设置主键合理的做法是要么在建表时就设置主键，要么在还没有使用这张表之前添加一个主键，而不是使用到一半才添加主键，这样大概率会因为数据冲突而导致添加失败

5.4 复合主键

主键列不一定是一列，也可能是多列，多列的话叫做复合主键，总体也要保证唯一性

创建复合主键表：

mysql> create table course(-> id int unsigned,-> course_id int unsigned,-> score tinyint unsigned,-> primary key(id, course_id)-> );
Query OK, 0 rows affected (0.09 sec)mysql> desc course;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id        | int unsigned     | NO   | PRI | NULL    |       |
| course_id | int unsigned     | NO   | PRI | NULL    |       |
| score     | tinyint unsigned | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+

复合主键由id和course_id列共同组成，它会把插入的这几列数据合成一个组合值来看待，这个组合值不与表中的其它数据产生冲突就不会报错，换句话说就是只要新插入的组合值不完全与表中的其它记录冲突就不会报错，其中几列冲突也不影响，只要组合值唯一即可

mysql> insert into course values (1, 10, 99);
Query OK, 1 row affected (0.01 sec)mysql> insert into course values (2, 10, 99);
Query OK, 1 row affected (0.01 sec)mysql> insert into course values (1, 11, 99);
Query OK, 1 row affected (0.00 sec)
# 组合值冲突
mysql> insert into course values (1, 10, 99);
ERROR 1062 (23000): Duplicate entry '1-10' for key 'course.PRIMARY'
mysql> select * from course;
+----+-----------+-------+
| id | course_id | score |
+----+-----------+-------+
|  1 |        10 |    99 |
|  1 |        11 |    99 |
|  2 |        10 |    99 |
+----+-----------+-------+

6. 自增长

auto_increment字段，在插入数据时如果这列不给值的话，mysql会自动为该列生成一个唯一值，这个值是当前字段列中最大的一个值然后+1得到的，如果表是空的，那么auto_increment的值通常从该列定义的起始值（默认为1）开始，因此该字段通常是搭配主键一起使用，也就是作为主键的一列设置自增长属性，作为一个逻辑主键

实际上，在mysql中如果一列不设置为主键而只设置自增长会报错

示例：

mysql> create table tab(-> id int unsigned primary key auto_increment,-> name varchar(10) not null-> );
Query OK, 0 rows affected (0.08 sec)mysql> desc tab;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)  | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

插入数据：

mysql> insert into tab (name) values('a');
Query OK, 1 row affected (0.01 sec)mysql> insert into tab (name) values('b');
Query OK, 1 row affected (0.01 sec)mysql> insert into tab values(100, 'c');
Query OK, 1 row affected (0.00 sec)mysql> insert into tab (name) values('d');
Query OK, 1 row affected (0.01 sec)mysql> select * from tab;
+-----+------+
| id  | name |
+-----+------+
|   1 | a    |
|   2 | b    |
| 100 | c    |
| 101 | d    |
+-----+------+
4 rows in set (0.00 sec)

可以发现的是，如果自增长列不给值，系统默认给1，然后依次累加，如果给了指定的值，将使用给定的，不过要满足唯一，下次如果不给同样选择默认选择当前列最大值+1

mysql在内部维护了一个计数器来跟踪下一个auto_increment值，这个计数器在每次插入新行时自动更新。这意味着，即使手动删除了表中的某些行，auto_increment的值也不会回退，它只会继续增加

通过last_insert_id函数获取最后一次插入的自增长值：

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

特点如下：

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

简单介绍索引：
索引和主键关联性很强，在关系数据库中，索引是一种单独的存储结构，是对数据库表中一列或多列的值进行排序的结构，它允许数据库系统快速访问表中的特定信息

索引的作用就类似于图书的目录，就是用来维护内容和具体页码之间的映射关系，可以根据目录中的页码快速找到所需的内容

因为需要维护这种内容和具体位置的映射关系，这就意味着索引需要开辟额外的空间来保存它们，所以索引本质是拿空间换时间的技术策略

7. 唯一键

一张表中往往有很多属性是要具有唯一性的，也就是数据不能重复，但又不想把这些属性列设置为复合主键，这时另一种解决方案就是把它们设置为唯一键

唯一键的作用与主键类似，都是保证列中的值具有唯一性，区别在于唯一键是可以插入null的，而主键不允许为空，这是语法上的区别

如果在创建时给唯一键也带上not null约束，那么也不允许插入null了

在实际业务上的区别是，主键主要约束的侧重点在于保证数据唯一性，对于选择哪一列没什么要求，有时候，出于性能考虑，选择的列可能与业务数据没有直接关联，如使用自增ID作为主键

而唯一键约束的侧重点在于业务列相关的数据要保证唯一性，因此唯一键主要是选择与业务关联性很强的列进行设置，以避免数据冗余和不一致，这是二者在业务上的区别

因此在选择主键时建议选择与业务无关的列作为主键，这样如果业务列需要调整也不会导致主键被过多的影响

示例：

mysql> create table tab(-> id int unique key comment '这一列为唯一键',-> name varchar(10)-> );
Query OK, 0 rows affected (0.11 sec)
# key可以省略，只写uniquemysql> desc tab;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | UNI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> insert into tab values(1, '浩克');
Query OK, 1 row affected (0.01 sec)
# 键值冲突会报错
mysql> insert into tab values(1, '美队');
ERROR 1062 (23000): Duplicate entry '1' for key 'tab.id'
# 允许插入空
mysql> insert into tab values(null, '美队');
Query OK, 1 row affected (0.01 sec)mysql> select * from tab;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 浩克   |
| NULL | 美队   |
+------+--------+
2 rows in set (0.00 sec)

8. 外键

上面介绍的约束都是在一张表上的约束，但在实际业务中表与表之间多数情况下也是存在关联关系的

具体来说就是a表的某列中的数据对应b表的某列数据，也就是列的属性是相同的，然后拿着a表中该列的数据去查b表时结果会得到一条完整的记录，这里称a表叫做从表，而b表为主表，其中从表中的这一列叫做外键（列），而主表中的这一列必须要是主键或者唯一键

可以把两张表合并简化关系，但势必会造成大量的数据冗余，所以为了降低数据冗余，选择通过让多表之间建立起来关联，也会有冗余，但相较于之前的做法冗余度大大降低

当某列被设置为了外键列后就意味着后续往该列中插入的数据要遵守外键约束，该约束是两个表相关列中的数据要保证一致性和参照完整性，也就是说它要求从表的外键列中的每个值都必须在主表的主键或唯一键列中存在，当插入不合法的数据时mysql会报错

示例如下，先创建主表：

mysql> create table class(-> num int unsigned primary key,-> name varchar(10) not null-> );
Query OK, 0 rows affected (0.07 sec)mysql> desc class;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| num   | int unsigned | NO   | PRI | NULL    |       |
| name  | varchar(10)  | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

后创建从表：

mysql> create table stu( -> id int unsigned primary key, -> name varchar(10), -> class_num int unsigned, -> foreign key(class_num) references class(num) -> );
Query OK, 0 rows affected (0.08 sec)
# 外键是定义在从表中的，然后引用主表中的某一列且是主键或者唯一键
mysql> desc stu;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int unsigned | NO   | PRI | NULL    |       |
| name      | varchar(10)  | YES  |     | NULL    |       |
| class_num | int unsigned | YES  | MUL | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

往主表中插入两个数据：

mysql> insert into class values(1, '计算机科学1班'), (2, '软件工程2班');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from class;
+-----+---------------------+
| num | name                |
+-----+---------------------+
|   1 | 计算机科学1班       |
|   2 | 软件工程2班         |
+-----+---------------------+
2 rows in set (0.00 sec)

此时的外键约束就限定了后续往从表中的外键列插入数据时，数据范围必须是其引用的主表列中出现的，这里是1和2，如果插入其它数字就报错：

mysql> insert into stu values(001, '鹰眼', 1);
Query OK, 1 row affected (0.04 sec)mysql> insert into stu values(002, '黑寡妇', 2);
Query OK, 1 row affected (0.01 sec)
# 外键列数据越界报错
mysql> insert into stu values(003, '战争机器', 3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_num`) REFERENCES `class` (`num`))mysql> select * from stu;
+----+-----------+-----------+
| id | name      | class_num |
+----+-----------+-----------+
|  1 | 鹰眼      |         1 |
|  2 | 黑寡妇    |         2 |
+----+-----------+-----------+
2 rows in set (0.00 sec)

同样的如果从表的外键列中还保存着主表对应列中的数据，此时想删除主表中的某条记录也会报错，因为违反了两表之间的参照完整性：

mysql> delect from class where num=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delect from class where num=1' at line 1
# 因为stu表还有外键列引用主表中的这条记录，所以删不掉

反过来说，只要是在表中的数据一定是满足外键约束的