MySQL 5.1：如何基于字段部分内容创建唯一键及实现插入更新逻辑

嘿，针对你的MySQL 5.1.72的场景，首先得明确一个关键点：MySQL 5.1不支持生成列（Generated Columns），所以没法直接创建一个基于YEAR(pe_time)的唯一键约束。不过我们有两种靠谱的方案来实现你要的逻辑，下面详细说：

方案一：新增年份字段+唯一键（推荐，性能更优） #

既然没法直接用函数做唯一键，那我们可以手动加一个存储年份的字段，然后基于它创建唯一键，这样就能用你熟悉的INSERT...ON DUPLICATE KEY UPDATE了：

步骤1：添加并初始化年份字段 #

首先给pe表加一个pe_year字段，用来存储pe_time的年份：

-- 添加字段
ALTER TABLE pe ADD COLUMN pe_year INT(4) NOT NULL DEFAULT 0;
-- 给现有数据补全年份值
UPDATE pe SET pe_year = YEAR(pe_time);

步骤2：创建新的唯一键 #

删掉原来的uk_id（它是基于person_id+pe_time的，不符合你的需求），然后创建基于person_id+pe_year的唯一键：

-- 删除旧唯一键
ALTER TABLE pe DROP INDEX uk_id;
-- 创建新唯一键
ALTER TABLE pe ADD UNIQUE KEY uk_person_year (person_id, pe_year);

步骤3：自动维护年份字段（可选但推荐） #

为了避免每次插入/更新都手动计算年份，我们可以创建两个触发器，让数据库自动同步pe_year和pe_time的年份：

-- 插入数据前自动设置pe_year
DELIMITER //
CREATE TRIGGER tr_pe_before_insert BEFORE INSERT ON pe
FOR EACH ROW
BEGIN
    SET NEW.pe_year = YEAR(NEW.pe_time);
END //
DELIMITER ;

-- 更新数据前自动同步pe_year
DELIMITER //
CREATE TRIGGER tr_pe_before_update BEFORE UPDATE ON pe
FOR EACH ROW
BEGIN
    SET NEW.pe_year = YEAR(NEW.pe_time);
END //
DELIMITER ;

步骤4：使用插入/更新语句 #

现在你就可以用简洁的INSERT...ON DUPLICATE KEY UPDATE语句实现需求了：

-- 测试同person同年份的情况（会更新现有行）
INSERT INTO pe (person_id, height, weight, pe_time)
VALUES (10052, 172, 61, '2018-01-10')
ON DUPLICATE KEY UPDATE
    height = VALUES(height),
    weight = VALUES(weight),
    pe_time = VALUES(pe_time);

-- 测试同person不同年份的情况（会插入新行）
INSERT INTO pe (person_id, height, weight, pe_time)
VALUES (10051, 161, 57, '2018-01-10')
ON DUPLICATE KEY UPDATE
    height = VALUES(height),
    weight = VALUES(weight),
    pe_time = VALUES(pe_time);

方案二：无额外字段，用事务+存储过程实现 #

如果你不想修改表结构，可以用事务包裹更新和插入逻辑，保证原子性，我们把它封装成存储过程更方便调用：

创建存储过程 #

DELIMITER //
CREATE PROCEDURE upsert_pe(
    IN p_person_id INT(11),
    IN p_height INT(3),
    IN p_weight DOUBLE(5,1),
    IN p_pe_time DATE
)
BEGIN
    START TRANSACTION;

    -- 先尝试更新同person同年份的行
    UPDATE pe
    SET height = p_height, weight = p_weight, pe_time = p_pe_time
    WHERE person_id = p_person_id AND YEAR(pe_time) = YEAR(p_pe_time);

    -- 如果没更新到任何数据，就插入新行
    IF ROW_COUNT() = 0 THEN
        INSERT INTO pe (person_id, height, weight, pe_time)
        VALUES (p_person_id, p_height, p_weight, p_pe_time);
    END IF;

    COMMIT;
END //
DELIMITER ;

调用存储过程 #

直接调用存储过程就能完成需求：

-- 测试更新场景
CALL upsert_pe(10052, 172, 61, '2018-01-10');
-- 测试插入场景
CALL upsert_pe(10051, 161, 57, '2018-01-10');

补充说明 #

为什么不能直接创建基于年份的唯一键？因为MySQL 5.1没有生成列功能，唯一键只能作用于实际存在的列，没法直接用YEAR(pe_time)这样的函数表达式。如果是MySQL 5.7及以上版本，就可以创建生成列来实现，但你的版本不支持，所以只能用上面的两种方案。

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