Mysql数据唯一索引与唯一约束案例总结
唯一约束的说明
唯一约束是约束(CONSTRAINT)里的一种，常见的还有主键、外检、默认值、是否为空、检查等。唯一约束即限制某个或某些字段具有唯一性（不能重复）。
唯一索引的说明
唯一索引是只允许索引中的每个值对应记录的一行，这就像身份标识一样，每个人都只有一个。
代码验证
创建表时指定唯一约束
CREATE TABLE tb_unique(id int,name varchar(20) UNIQUE,addr varchar(30),age int)
注：1 当某个字段被指定为UNIQUE时会自动产生唯一索引。
2 在某个字段定义为唯一时还可以通过修改表时对该字段再次定义为UNIQUE，不过这显然没太大意义。再SHOW INDEX FROM tablename时可查看到多个关于该字段UNIQUE的重复定义。
创建表时指定复合多字段唯一约束
CREATE TABLE tb_unique2(id int,name varchar(20),addr varchar(30),age int,PRIMARY key(id),UNIQUE KEY com_nameadrr (name,addr),KEY age (age))
注：1 这里的KEY age(age)意为普通索引。可通过查看索引命令SHOW INDEX FROM tablename命令查看，详细的见查看索引部分。
通过数据字典查看表的约束
select * FROM information_schema.TABLE_CONSTRAINTSWHERE TABLE_NAME='tb_unique' AND CONSTRAINT_TYPE='UNIQUE'/* 结果CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPEdef shenl name shenl tb_unique UNIQUE*/
通过修改表创建唯一约束
ALTER TABLE tb_unique ADD CONSTRAINT cons_uniquename UNIQUE(addr);select * FROM information_schema.TABLE_CONSTRAINTSWHERE TABLE_NAME='tb_unique' AND CONSTRAINT_TYPE='UNIQUE'/* 结果CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPEdef shenl name shenl tb_unique UNIQUEdef shenl cons_uniqueaddr shenl tb_unique UNIQUE*/
创建表时指定唯一索引
CREATE TABLE tb_uniqueidx(id int,name varchar(20) UNIQUE,addr varchar(30),age int,UNIQUE INDEX idx_name(name(20) ASC))-- 通过show create table查看表创建脚本。show create table tb_uniqueidx;/* 结果Table Create Tabletb_uniqueidx CREATE TABLE `tb_uniqueidx` (`id` int(11) DEFAULT NULL,`name` varchar(20) DEFAULT NULL,`addr` varchar(30) DEFAULT NULL,`age` int(11) DEFAULT NULL,UNIQUE KEY `name` (`name`),UNIQUE KEY `idx_name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3*/-- 约束里查看查看Unique和索引信息。select * FROM information_schema.TABLE_CONSTRAINTSWHERE TABLE_NAME='tb_uniqueidx' AND CONSTRAINT_TYPE='UNIQUE'/*结果CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPEdef shenl name shenl tb_uniqueidx UNIQUEdef shenl idx_name shenl tb_uniqueidx UNIQUE*/
修改表时指定唯一索引
CREATE UNIQUE INDEX idx_addr ON tb_uniqueidx (addr (30) DESC);ALTER TABLE tb_uniqueidx ADD UNIQUE INDEX idx_addr2 (addr (30) DESC);-- 通过key关键字和index效果一样。ALTER TABLE tb_uniqueidx ADD UNIQUE KEY idx_addr2 (addr (30) DESC);
查看索引信息
show index from tb_unique2/* 部分结果tb_unique2 0 PRIMARY 1 idtb_unique2 0 com_nameadrr 1 nametb_unique2 0 com_nameadrr 2 addrtb_unique2 1 age 1 age*/
删除索引
DROP INDEX idx_addr ON tb_uniqueidxalter table tb_uniqueidx drop index idx_addr2;
唯一索引与NULL
CREATE TABLE tb_uniqueidx(id int,name varchar(20) UNIQUE,addr varchar(30),age int,PRIMARY KEY(id),UNIQUE INDEX idx_name(addr(30) ASC))-- 插入多条为NULL的值到建立唯一索引的字段里INSERT INTO tb_uniqueidx VALUES(1,'Tom','Shanghai',30);INSERT INTO tb_uniqueidx VALUES(2,'Tim',NULL,40);INSERT INTO tb_uniqueidx VALUES(3,'John',NULL,40);
由此可见唯一索引对NULL值是免疫的，即并不能约束它(NULL)。
触发器约束唯一索引里的NULL
-- 通过约束只能输入一次NULL值,触发器需要在表没有数据时即创建。CREATE TRIGGER trg_tb_uniqueidxBEFORE INSERT ON tb_uniqueidxFOR EACH ROWBEGINDECLARE v_errmsg, v_value text;IF EXISTS(SELECT 1 FROM tb_uniqueidx WHERE addr <=> NEW.addr) THENIF NEW.addr IS NULL THENSET v_value = 'NULL';ELSESET v_value = CONCAT('''', NEW.addr, '''');END IF;SET v_errmsg = CONCAT('重复的值 ',v_value,' 出现在字段 addr 里');SIGNAL SQLSTATE '23000'SET MESSAGE_TEXT = v_errmsg,MYSQL_ERRNO = 1062;END IF;END-- 当我们再次插入NULL值时则会报错。INSERT INTO tb_uniqueidx VALUES(4,'Kate',NULL,50);
NOT NULL结合UNIQUE一起使用
该方法即将NOT NULL和UNIQUE约束一起使用。
CREATE TABLE tb_uniqueidx(id int,name varchar(20) UNIQUE,addr varchar(30) NOT NULL UNIQUE,age int,PRIMARY KEY(id),UNIQUE INDEX idx_name(addr(30) ASC))
