探讨 | MySQL约束

约束

一些关键字对字段能有约束性，分别是以下关键字。

1. 默认值default：

为某字段设置一个默认值，可以为null，如果不插入数据，数值就是设置的默认值。

2. 唯一键unique：

默认创建索引，不能填入相同的值，但默认可以为空，所以存在多个null。not null加unique的约束设置会成为主键PRI。

mysql> create table t1(id int not null unique, name char(15) default "钱财");
Query OK, 0 rows affected (0.01 sec)

mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(15) | YES  |     | 钱财    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.07 sec)

3. 主键primary key（not null + unique）：

默认创建索引，唯一且不为空。只能有一个字段设置为主键，一般标记id为主键。主键primary key和not null加unique的组合共存，此时primary key才是主键PRI，而not null加unique组合是UNI键。

mysql> create table t2(id int not null unique, name char(15) primary key default"彩彩");
Query OK, 0 rows affected (0.00 sec)

mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | UNI | NULL    |       |
| name  | char(15) | NO   | PRI | 彩彩    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into t2(id) values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+----+--------+
| id | name   |
+----+--------+
|  2 | 彩彩   |
+----+--------+
1 row in set (0.00 sec)

mysql> insert into t2(id) values(3);
ERROR 1062 (23000): Duplicate entry '彩彩' for key 'PRIMARY'

4. auto_increment自增：

需要为Key键的字段。默认为非空，能实现字段数据的自增，插入数据时即使填null，也会自动填入递增数字。

mysql> create table t3(id int unique auto_increment, name char(11) default "张飞");
Query OK, 0 rows affected (0.01 sec)

mysql> desc t3;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(11) | YES  |     | 张飞    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into t3 values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
+----+--------+
1 row in set (0.00 sec)

mysql> insert into t3 values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 张飞   |
+----+--------+
2 rows in set (0.00 sec)

mysql> insert into t3 values(5, "关羽");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 张飞   |
|  5 | 关羽   |
+----+--------+
3 rows in set (0.00 sec)

mysql> insert into t3(id, name) values(null,"关羽");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 张飞   |
|  5 | 关羽   |
|  6 | 关羽   |
+----+--------+
4 rows in set (0.00 sec)

5. zerofill零填充

位数不够时，拿0进行填充。

mysql> create table t5(id int(5) zerofill);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t5;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id    | int(5) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t5 values(33);
Query OK, 1 row affected (0.00 sec)

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