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