- 1android studio 两个app之间的交互_andriod studio两个程序之间发消息
- 2深入了解PostgreSQL：高级查询和性能优化技巧_pg数据库集群主库查询效率高于从库查询效率
- 32023年最新人工智能入门指南（学习清单）_点头ai - 机器学习实战应用
- 4uniapp中的renderjs使用_uniapp renderjs
- 5Python应用实例（一）外星人入侵（七）_外星人入侵pythoh外星人撞击屏幕底部
- 6【unity】制作一个角色的初始状态（左右跳二段跳）【2D横板动作游戏】_c#怎么在unity中写一个二段跳的代码
- 7完美解决ERROR: Command errored out with exit status 1: command: ‘f:\program files\python\python36\pyt
- 8【python教程入门学习】Python新年炫酷烟花秀代码_烟火代码复制
- 9react native readMe笔记_ios开发rctimageloader
- 10得物面试（得物golang工程师一面）_得物长沙面试golang
MySQL的多种约束
赞
踩
事实上，写入表的数据只有数据类型上的约束，但是没有其他更为严格为约束（例如 48.2kg 中的 48.2 填在体重字段里是符合浮点类型的约束的，但是万一该数据填入到另外一个身高字段里呢？），就有可能导致用户产生错误输入。通过约束，就可以倒逼程序员做正确的输入，保证数据库中的数据是符合预期的、合法的（数据库处的数据检查几乎是检验数据合法的最后一条防线）。
1.空属性
NULL 表示“没有”（默认）和 NOT NULL 代表“不为空”，而 '' 或 "" 表示空字符串。在实际开发中，应该尽可能保证字段不为空，因为数据为空的不能参与运算。只要字段的属性设置为 not null 时，就会硬性要求用户的数据项在对应字段处的输入不为空且不能忽略（除非后续设置了默认值，这个后面提及），否则不允许插入该数据项。
那怎么添加呢？在创建表时，在对应不为空的字段使用 NOT NULL 即可，即：CREATE TABLE IF NOT EXISTS table_name(field_name TYPE NOT NULL);，这样就形成了一种“非空约束”。
2.默认值
某一个字段的数据在大部分情况下都是一个值，只有在少数情况下才会发生变动，因此就需要为字段设置一个默认值，一般字段的默认值为 NULL，可以使用 DESC table_name; 查询 Default 所设定的默认值。其设置方法和非空属性设置类似，即：CREATE TABLE IF NOT EXISTS table_name(field_name TYPE DEFAULT default_value);，并且 NO NULL 和 DEFAULT 可以叠加使用，其意义是：不允许数据为 NULL 值。但是，有一种情况是不允许叠加使用的，也就是 NOT NULL DEFAULT NULL。
注意：一般使用
DEFAULT就不会使用NOT NULL，因为设置了DEFAULT一般就不会为空。
3.列描述
列描述是类似注释一样的存在，没有其他实际含义，主要是给程序员和 DBA 数据库管理员做提示使用，使用方法和之前的语法差不多，也是加在字段后面即可，即：CREATE TABLE IF NOT EXISTS table_name(field_name TYPE COMMENT 'comment_text');，这只是一种软性约束，其说明实质就是一种注释（不过需要注意的是，使用 DESC 是看不到注释信息的，但是使用 SHOW CREATE 是可以的）。
4.zerofill
我们之前创建表格的时候，其实故意忽略了一个问题，请看下面操作：
# 创建表时的细节
mysql> create table if not exists my_table(number int unsigned not null);
Query OK, 0 rows affected (0.02 sec)
mysql> desc my_table;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| number | int(10) unsigned | NO | | NULL | |
+--------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> show create table my_table\G
*************************** 1. row ***************************
Table: my_table
Create Table: CREATE TABLE `my_table` (
`number` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
不是说 int 是 4 字节的数据么？这个 int(10) 的 10 又是什么意思？实际上如果 ZEROFILL 没有被设置，这个 10 是没有太大意义的。如果设置了 ZEROFILL，会根据这个数字来填充 0，直到 0 和数据位数达到该数字（也就是类似 C 语言使用 printf() 的数据补零修改宽度的操作），补零的目的只是为了显示上更加美观简洁一些，使表数据在显示上是等宽的。而我们也可以在 int() 里填上数字来规定等宽的长度，如果用户没有设定宽度，则会填上默认的宽度（不同类型的宽度可能不同）。
# 修改数据宽度
mysql> insert into my_table (number) values (5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into my_table (number) values (500);
Query OK, 1 row affected (0.00 sec)
mysql> select * from my_table;
+------------+
| number |
+------------+
| 0000000005 |
| 0000000500 |
+------------+
2 rows in set (0.00 sec)
mysql> alter table my_table modify number int(2) unsigned zerofill;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from my_table;
+--------+
| number |
+--------+
| 05 |
| 500 |
+--------+
2 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
注意：补零操作并不影响查找，也是一种软规定。
5.主键
PRIMARY KEY 用来约束字段里的数据为唯一的、不可重复、不为空的，一张表最多只有一个主键，主键所在的列通常是整数类型。使用方法和上述约束类似。主键约束使得某一项数据的某一类型的数据只能有一份。根据主键来搜索，绝对只能检索到一条记录。
而使用 ALTER TABLE table_name DROP PRIMARY KEY; 即可去除表内设置的主键。
而修改某一些为主键时，就需要保证该列没有重复的数据，再使用 ALTER TABLE table_name ADD PRIMARY KEY(field_name);。
还可以设置复合主键（多个列/字段组合为一个主键），只需要在定义好字段名称和字段类型后，使用 PRIMARY KEY(field_name_1, field_name_2, ...) 即可，然后使用 DESC 查看表的属性就会发现，设置为主键的相关字段在 key 出都会显示 PRI 的提示字样。
# 使用复合主键
mysql> create table my_table(
-> id int unsigned,
-> course_id int unsigned comment '课程编号',
-> score tinyint unsigned comment '课程得分',
-> primary key(id, course_id)
-> );
mysql> desc my_table;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| course_id | int(10) unsigned | NO | PRI | NULL | |
| score | tinyint(3) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into my_table values(001, 41, 90);
Query OK, 1 row affected (0.00 sec)
mysql> desc my_table;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| course_id | int(10) unsigned | NO | PRI | NULL | |
| score | tinyint(3) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into my_table values(001, 41, 90);
Query OK, 1 row affected (0.00 sec)
mysql> insert into my_table values(002, 41, 89);
Query OK, 1 row affected (0.00 sec)
mysql> select * from my_table;
+----+-----------+-------+
| id | course_id | score |
+----+-----------+-------+
| 1 | 41 | 90 |
| 2 | 41 | 89 |
+----+-----------+-------+
2 rows in set (0.00 sec)
mysql> insert into my_table values(001, 41, 78);
ERROR 1062 (23000): Duplicate entry '1-41' for key 'PRIMARY'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
可以看到，复合主键中的各个字段是允许重复的，但是整体不能重复，要统一看作一个主键。
6.自增长
AUTO_INCREMENT 实际上是主键的一种（一般会先设置为主键约束），一张表最多只有一个自增长约束，且对应字段必须是整型，使用方法和前面类似。
设定完后，插入一个记录就会自动加 1（从 1 开始自增，当然也可以自己插入初始值）。
另外，还可以使用 SELECT LAST_INSERT_ID() 来查询最后一个增长的最大数字。
同样，一个表一般只有一个自增长约束。
7.唯一键
和主键类似，唯一键约束 unique 可以保证一个字段的数据保持唯一性（不会出现重复的数据），但是和主键不同的是，一张表只能有一个主键，但可以有多个唯一键。
成功设置为唯一键时，使用 DESC table_name 则会在 key 处显示 UNI。
另外，唯一键的字段可以插入空值（但是主键不可以插入为 NULL），并且不作唯一性比较。
补充：主键和唯一键在很多地方时重叠的，但是一般主键是标识一个记录的唯一性，一般和业务无关（这样业务改变也无需变动主键），但是唯一键更多是为了保证在业务上不出现数据重复的问题。两者是互相补充的，不是互相独立、对立的。
8.外键
外键的语法为 [CONSTRAINT foreign_key_name] FOREIGN KEY (field_name) REFERENCES main_table(main_field_name); 有些时候，表与表之间是会产生关系/依赖的。因此外键和之前所有的键不太一样，是根据其他数据表来产生约束的。
# 研究使用外键的场景
mysql> create table student( s_id int unsigned primary key auto_increment,
s_name varchar(20) not null, s_telphone varchar(32) not null, c_id int );
Query OK, 0 rows affected (0.04 sec)
mysql> create table class( c_id int unsigned primary key auto_increment, c_
name varchar(20) not null );
Query OK, 0 rows affected (0.04 sec)
mysql> desc student;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| s_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| s_name | varchar(20) | NO | | NULL | |
| s_telphone | varchar(32) | NO | | NULL | |
| c_id | int(11) | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc class;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| c_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| c_name | varchar(20) | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into class (c_id, c_name) values (1, '101');
Query OK, 1 row affected (0.00 sec)
mysql> insert into class (c_name) values ('102');
Query OK, 1 row affected (0.00 sec)
mysql> insert into class (c_name) values ('103');
Query OK, 1 row affected (0.01 sec)
mysql> select * from class;
+------+--------+
| c_id | c_name |
+------+--------+
| 1 | 101 |
| 2 | 102 |
| 3 | 103 |
+------+--------+
3 rows in set (0.01 sec)
mysql> insert into student (s_id, s_name, s_telphone, c_id) values (1, 'lim
ou', '13455657787', 3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (s_name, s_telphone, c_id) values ('dimou', '135
55652387', 3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (s_name, s_telphone, c_id) values ('eimou', '13255232000', 2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (s_name, s_telphone, c_id) values ('wimou', '135
44232001', 1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+------+--------+-------------+------+
| s_id | s_name | s_telphone | c_id |
+------+--------+-------------+------+
| 1 | limou | 13455657787 | 3 |
| 2 | dimou | 13555652387 | 3 |
| 3 | eimou | 13255232000 | 2 |
| 4 | wimou | 13544232001 | 1 |
+------+--------+-------------+------+
4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
上述的学生信息录入的过程确实没毛病，但是程序员很有可能给一个学生插入一个不存在的 c_id（甚至是将原班级删除导致 c_id 不存在），为了防止这种情况下的产生，就诞生了外键。
# 使用外键
# 先删除原先的数据表
mysql> drop table student;
# 重新建立带有外键的数据表
mysql> create table student(s_id int unsigned primary key, s_name varchar(20) not null, s_telphone varchar(32) unique, c_id int unsigned, foreign key
(c_id) references class(c_id) );
Query OK, 0 rows affected (0.06 sec)
mysql> desc student;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| s_id | int(10) unsigned | NO | PRI | NULL | |
| s_name | varchar(20) | NO | | NULL | |
| s_telphone | varchar(32) | YES | UNI | NULL | |
| c_id | int(10) unsigned | YES | MUL | NULL | |
+------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from class;
+------+--------+
| c_id | c_name |
+------+--------+
| 1 | 101 |
| 2 | 102 |
| 3 | 103 |
+------+--------+
3 rows in set (0.00 sec)
# 尝试插入数据
mysql> insert into student values (1, 'limou', '13455657787', 3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values (2, 'dimou', '13555652387', 3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values (3, 'eimou', '13255232000', 2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values (4, 'wimou', '13544232001', 1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values (5, 'fimou', '13514875183', 4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`limou_database`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`c_id`))
# 尝试直接删除被子表引用为外键的父行
mysql> select * from student;
+------+--------+-------------+------+
| s_id | s_name | s_telphone | c_id |
+------+--------+-------------+------+
| 1 | limou | 13455657787 | 3 |
| 2 | dimou | 13555652387 | 3 |
| 3 | eimou | 13255232000 | 2 |
| 4 | wimou | 13544232001 | 1 |
| 6 | fimou | 13514875183 | 2 |
| 7 | fimou | 13514875123 | 3 |
+------+--------+-------------+------+
6 rows in set (0.00 sec)
mysql> select * from class;
+------+--------+
| c_id | c_name |
+------+--------+
| 1 | 101 |
| 2 | 102 |
| 3 | 103 |
+------+--------+
3 rows in set (0.00 sec)
mysql> delete from class where c_id=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`limou_database`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`c_id`)) # 很明显删除失败
# 去除子表对父表的引用，再尝试删除
mysql> delete from student where c_id=3;
Query OK, 3 rows affected (0.01 sec)
mysql> delete from class where c_id=3;
Query OK, 1 row affected (0.00 sec) # 很明显删除成功
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
可以看到，最后一次插入出现了错误，说明外键约束生效。
另外 [CONSTRAINT foreign_key_name] 实际上是给外键起一个别名，是可选做法，不设定则会有默认的名字。
