MySQL如何基于datetime列的日期部分与mid字段创建唯一约束

需求说明 #

要实现sampletable表中，同一个mid值对应createdttm的同一日期仅存在一条记录，即mid + createdttm日期部分组合唯一。

方案1：MySQL 5.7及以上版本（推荐） #

直接创建函数型唯一索引，无需新增表字段，语句如下：

CREATE UNIQUE INDEX idx_uq_mid_create_date ON `sampletable` (`mid`, DATE(`createdttm`));

方案2：低版本MySQL兼容方案 #

通过新增虚拟生成列实现，生成列会自动同步createdttm的日期值，无需手动维护：

1. 新增虚拟日期列

ALTER TABLE `sampletable` 
ADD COLUMN `create_date` DATE GENERATED ALWAYS AS (DATE(`createdttm`)) VIRTUAL;

2. 基于mid和生成列创建唯一约束

ALTER TABLE `sampletable`
ADD UNIQUE KEY `uq_mid_create_date` (`mid`, `create_date`);

前置检查提示 #

执行约束创建前，请先校验表中是否已存在重复数据，否则会导致约束创建失败，校验语句：

SELECT `mid`, DATE(`createdttm`) AS record_date, COUNT(*) AS repeat_count
FROM `sampletable`
GROUP BY `mid`, record_date
HAVING repeat_count > 1;

如果查询有返回结果，需先清理对应重复数据后再执行约束创建操作。

补充：如果使用MySQL 5.6及更早版本，既不支持函数索引也不支持虚拟列，可以新增普通create_date DATE字段，编写INSERT、UPDATE触发器自动将DATE(createdttm)的值同步到create_date字段，再创建mid+create_date的唯一约束即可。

内容的提问来源于stack exchange，提问作者vellai durai