mysql 约束条件 unique key 唯一的键

 

 

如果不设置unique

 

会出现两条相同的记录

mysql> create table department1(id int,name varchar(16));
Query OK, 0 rows affected (0.01 sec)


mysql> insert into department1 values(1 ,'mike'),(2,'mike');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

 

 

mysql> desc department1 ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

 

============设置唯一约束 UNIQUE===============


不能插入相同记录

方式一：

mysql> create table department1(id int,name varchar(16) unique);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into department1 values(1 ,'mike'),(2,'mike');
ERROR 1062 (23000): Duplicate entry 'mike' for key 'name'

 

方式二：

mysql> create table department1(id int,name varchar(16),unique(id),unique(name));
Query OK, 0 rows affected (0.02 sec)

mysql> desc department1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(16) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

这两种方法都叫单列唯一 针对一个字段设置唯一性


还有一种 联合唯一
几个字段合到一起不重复就可以


联合唯一
unique(ip,port)
desc 看到有 MUL 就是联合唯一

mysql> create table services(id int,ip char(16),port int,unique(id),unique(ip,port));
Query OK, 0 rows affected (0.01 sec)

mysql> desc services;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  | UNI | NULL    |       |
| ip    | char(16) | YES  | MUL | NULL    |       |
| port  | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 

验证 插入记录

 

mysql> insert into services values 
    -> (1,'192.168.10.11',80),
    -> (2,'192.168.10.11',81),
    -> (3,'192.168.10.10',80);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from services;
+------+---------------+------+
| id   | ip            | port |
+------+---------------+------+
|    1 | 192.168.10.11 |   80 |
|    2 | 192.168.10.11 |   81 |
|    3 | 192.168.10.10 |   80 |
+------+---------------+------+
3 rows in set (0.00 sec)

 

再插入一条原本有的记录 报错了

mysql> insert into services values(4,'192.168.10.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.10.10-80' for key 'ip'