如何提取DATE_TIMESTAMP列日期并与另一列组合设为MySQL唯一键？

要实现你需要的约束逻辑，核心是把column2的日期部分提取出来，和column1一起作为唯一键。MySQL不支持直接对函数结果创建唯一约束，所以可以用存储型生成列来存储提取后的日期值，再给这个生成列和column1加组合唯一约束。

针对已存在的表 #

执行以下SQL修改表结构：

ALTER TABLE your_table_name
-- 添加存储型生成列，自动提取column2的日期部分
ADD COLUMN column2_date DATE AS (DATE(column2)) STORED,
-- 创建column1和column2_date的组合唯一约束
ADD UNIQUE KEY idx_unique_col1_date (column1, column2_date);

针对新建表 #

如果是从头建表，可以直接把生成列和约束写进去：

CREATE TABLE your_table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column1 INT(11) NOT NULL,
    column2 DATETIME NOT NULL,
    -- 自动生成日期列
    column2_date DATE AS (DATE(column2)) STORED,
    -- 组合唯一约束
    UNIQUE KEY idx_unique_col1_date (column1, column2_date)
);

逻辑验证 #

• 插入第一条记录：INSERT INTO your_table_name (column1, column2) VALUES (111, '2022-10-10 12:34:56'); → 成功
• 插入同一column1+同一日期（不同时间）：INSERT INTO your_table_name (column1, column2) VALUES (111, '2022-10-10 09:00:00'); → 触发唯一约束错误
• 插入同一column1+不同日期：INSERT INTO your_table_name (column1, column2) VALUES (111, '2022-10-11 10:00:00'); → 成功
• 插入不同column1+同一日期：INSERT INTO your_table_name (column1, column2) VALUES (222, '2022-10-10 15:00:00'); → 成功

注意事项 #

• 这里用STORED生成列（存储到磁盘），兼容性更好，MySQL 5.7及以上版本都支持；如果用默认的VIRTUAL（仅计算不存储），需要MySQL 8.0.13及以上版本才能给它加唯一约束。
• 生成列的值会自动随column2的更新同步变化，不用手动维护。

内容的提问来源于stack exchange，提问作者Mean Coder