场景:库中有一张用户产品信息表 t_user_product_info 表:
表中有主键自增长id,user_id,product_id,validate_time,create_time,updated_time五个字段,其中user_id和product_id分别是用户和产品信息表的主键自增长id。t_user_product_info表中,通过user_id和prodcut_id两个字段确定一条唯一的记录,即表中不能存在两条记录user_id和product_id都相同。这个时候我们就可以使用到联合唯一索引,示例代码如下(三种方式均可):
方式一(UNIQUE INDEX)
ALTER TABLE t_user_product_info ADD UNIQUE INDEX(user_id, product_id);
方式二 (UNIQUE INDEX INDEXNAME)
ALTER TABLE t_user_product_info ADD UNIQUE INDEX idx_user_product_id(user_id, product_id);
方式三(UNIQUE KEY)
ALTER TABLE t_user_product_info ADD UNIQUE KEY key_user_product_id(user_id, product_id);
如果向表中添加重复记录的时候,会返回错误以下信息:
INSERT INTO t_user_product_info VALUES(1, 134678, '766001510010', '2019-11-15 10:21:00', '2019-11-15 10:21:00', NULL);
INSERT INTO t_user_product_info VALUES(2, 134678, '766001510010', '2019-11-16 10:22:00', '2019-12-15 10:22:00', NULL);
[SQL]INSERT INTO t_user_product_info VALUES(2, 134678, '766001510010', '2019-11-15 10:21:00', '2019-11-15 10:21:00', NULL);
[Err] 1062 - Duplicate entry '134678-766001510010' for key 'idx_user_product_id'
我们使用INSERT INTO…ON DUPLICATE KEY UPDATE…来使用就不会报错,存在相同的记录,新记录会更新掉原有的记录。
INSERT INTO t_user_product_info (id, user_id, product_id, validate_time, created_time, updated_time)
VALUES(2, 134678, '766001510010', '2019-11-16 10:22:00', '2019-12-15 10:22:00', NULL) ON DUPLICATE KEY UPDATE id = VALUES(id), user_id = VALUES(user_id), product_id = VALUES(product_id), validate_time = VALUES(validate_time), created_time = VALUES(created_time), updated_time = VALUES(updated_time)
另外还有一种情况就是,当我们需要创建联合唯一索引的表中已经存在重复的记录,我们可以采用以下方式的代码,它会删除重复的记录(仅保留一条),然后建立联合唯一索引。
ALTER IGNORE TABLE t_user_product_info ADD UNIQUE INDEX(user_id, product_id);