如果不设置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'