刘硕的技术查阅手册

Python 全栈开发学习笔记

0%

MySQL 表的完整性约束

发表于 更新于 分类于
为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS 自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

概述

为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS 自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：

  • UNSIGNED：无符号，搭配数值型数据使用
  • ZEROFILL：使用 0 填充
  • NOT NULL：非空约束，指定某列不能为空
  • UNIQUE：唯一约束，指定某列或者几列组合不能重复
  • PRIMARY KEY (PK)：主键，指定该列的值可以唯一地标识该列记录
  • AUTO_INCREMENT：标识该字段的值自动增长（整数类型，而且为主键）
  • FOREIGN KEY (FK)：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性

unsigned 无符号

unsigned 主要是针对数值型数据进行的约束。经过 unsigned01约束后，将只能表示整数，但能表示数值的范围会翻倍。

创建一个 test_unsigned 数据表，表中的两个字段的类型分别为普通 int 整数和无符号的 int 整数：

1
2
3
4
5
6
7
8
9
10
11
mysql> create table test_unsigned (id1 int,id2 int unsigned);
Query OK, 0 rows affected (0.04 sec)

mysql> desc test_unsigned;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id1   | int(11)          | YES  |     | NULL    |       |
| id2   | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

给普通 int 插入负数，给无符号 int 插入正数时，是完全正常的：

1
2
3
4
5
6
7
8
9
10
mysql> insert into test_unsigned value (-1,1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_unsigned;
+------+------+
| id1  | id2  |
+------+------+
|   -1 |    1 |
+------+------+
1 row in set (0.00 sec)

但是如果给无符号的 int 插入负数，将会报错：

1
2
mysql> insert into test_unsigned value (1,-1);
ERROR 1264 (22003): Out of range value for column 'id2' at row 1

如果给无符号的 int 插入一个超过 2 147 483 647 的数字，是可以的：

1
2
3
4
5
6
7
8
9
10
11
mysql> insert into test_unsigned value (2147483647,2147483648);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_unsigned;
+------------+------------+
| id1        | id2        |
+------------+------------+
|         -1 |          1 |
| 2147483647 | 2147483648 |
+------------+------------+
2 rows in set (0.00 sec)

但是给普通的 int 插入一条超过 2 147 483 647 的数字就会报错：

1
2
mysql> insert into test_unsigned value (2147483648,2147483647);
ERROR 1264 (22003): Out of range value for column 'id1' at row 1

not null 非空约束

not null 用来约束字段中的数据是否可空，null 表示空，而非字符串

  • not null：不可空
  • null：可空

创建一个数据表，约束字段为 not null：

1
2
3
4
5
6
7
8
9
10
mysql> create table test_null (id int not null);
Query OK, 0 rows affected (0.03 sec)

mysql> desc test_null;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

我们看到 Null 列显示为 NO，也就是不能为空。

如果我们插入一条空数据，会报错，插入普通整数则是可以的：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> insert into test_null value (null);
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into test_null value (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_null;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

如果向非空字段中插入空值成功了，可以配置严格模式来解决这个问题。

设置严格模式：

  • 不支持对 not null 字段插入 null 值
  • 不支持对自增长字段插入值
  • 不支持 text 字段有默认值

直接在mysql中生效（重启失效）:

1
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

配置文件添加（永久失效）：

1
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

default 设置默认值

如果某一列中经常有重复的内容（比如有的时候性别可能很单一），就需要我们频繁地插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值。

创建数据表，其中一个字段只有非空约束，另一个字段在非空的基础上设置默认值：

1
2
3
4
5
6
7
8
9
10
11
mysql> create table test_default (id1 int not null, id2 int not null default 666);
Query OK, 0 rows affected (0.03 sec)

mysql> desc test_default;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | NO   |     | NULL    |       |
| id2   | int(11) | NO   |     | 666     |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

只给 id1 字段插入数据，id2 字段会自动被插入默认的数据：

1
2
3
4
5
6
7
8
9
10
mysql> insert into test_default(id1) value (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_default;
+-----+-----+
| id1 | id2 |
+-----+-----+
|   1 | 666 |
+-----+-----+
1 row in set (0.00 sec)

如果也给 id2 传入数据，那么会以传入的值为准，默认数据会被覆盖：

1
2
3
4
5
6
7
8
9
10
11
mysql> insert into test_default value (222,333);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_default;
+-----+-----+
| id1 | id2 |
+-----+-----+
|   1 | 666 |
| 222 | 333 |
+-----+-----+
2 rows in set (0.00 sec)

unique 唯一约束

唯一约束，指定某列或者几列组合不能重复。

唯一约束有两种方式可以实现。

第一种方式是像前面的几个约束一样，直接在定义的字段后面加上 unique 实现：

1
2
3
4
5
6
7
8
9
10
mysql> create table test_unique1 (id int unique);
Query OK, 0 rows affected (0.04 sec)

mysql> desc test_unique1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

第二种方式是当字段定义完成后，再声明字段唯一：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> create table test_unique2 (
    -> id int,
    -> unique(id)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc test_unique2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

当第一次插入一个数据时，是正常的，但是如果再次插入相同的值，则会报错：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> insert into test_unique1 value (666);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_unique1;
+------+
| id   |
+------+
|  666 |
+------+
1 row in set (0.00 sec)

mysql> insert into test_unique1 value (666);
ERROR 1062 (23000): Duplicate entry '666' for key 'id'

非空唯一

非空唯一就是联合使用 not null 和 unique 约束。非空唯一的字段既不可以为空，也不能重复：

1
2
3
4
5
6
7
8
9
10
mysql> create table test_nnu (id int not null unique);
Query OK, 0 rows affected (0.03 sec)

mysql> desc test_nnu;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

我们看到，Key 成了 PRI，也就是主键。主键是一种非空唯一的字段。如果不设置主键，第一个非空唯一字段会默认成为主键。

联合唯一

联合唯一是两个字段的值不可以一起重复，但是单个字段是可以重复的：

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
mysql> create table test_union_unique (
    -> id int,
    -> name char(20),
    -> host char(15) not null,
    -> port int not null,
    -> unique(host,port) # 联合唯一
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc test_union_unique;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
| host  | char(15) | NO   | PRI | NULL    |       |
| port  | int(11)  | NO   | PRI | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> insert into test_union_unique values
    -> (1,'nginx','192.168.0.10',80),
    -> (2,'haproxy','192.168.0.20',80),
    -> (3,'mysql','192.168.0.10',3306)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test_union_unique;
+------+---------+--------------+------+
| id   | name    | host         | port |
+------+---------+--------------+------+
|    1 | nginx   | 192.168.0.10 |   80 |
|    3 | mysql   | 192.168.0.10 | 3306 |
|    2 | haproxy | 192.168.0.20 |   80 |
+------+---------+--------------+------+
3 rows in set (0.00 sec)

mysql> insert into test_union_unique value (4,'nginx2','192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

PRIMARY KEY

主键为了确保该字段中的所有值都是非空且唯一的。换言之，主键用来独一无二地确认一个表格中的每一行数据。
主键可以包含一个字段或多个字段。当主键包含多个字段时，称为组合键（Composite Key），也称作联合主键。
主键可以在建置新表格时设定（运用 CREATE TABLE 语句），也可以通过改变现有的表格架构的方式设定（运用 ALTER TABLE）。
主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。

单字段主键

使用一个字段作为主键，称为但字段主键。

方法一：not null + unique

如果不手动设置，MySQL 默认会将第一个非空且唯一的字段设置为主键