如何通过列表批量更新MySQL表的transaction_date列

问题背景 #

目标表结构：

CREATE TABLE `orderslip_transaction` (
  `id` bigint(20) NOT NULL,
  `transaction_number` varchar(20) NOT NULL,
  `transaction_date` datetime(6) NOT NULL,
  `transaction_is_active` tinyint(1) NOT NULL,
  `store_id` bigint(20) NOT NULL,
  `created_on` datetime(6) NOT NULL,
  `updated_on` datetime(6) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `orderslip_transaction`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `transaction_number` (`transaction_number`),
  ADD KEY `orderslip_transaction_user_id_dd45478c_fk_auth_user_id` (`user_id`),
  ADD KEY `store_id-fk-store` (`store_id`);

现有数据包含多条记录，需更新transaction_number为1545 | ALLCPB、1546 | ALLCPB等4条记录的transaction_date字段。尝试使用INSERT...ON DUPLICATE KEY UPDATE语句仅通过transaction_number匹配，执行后无报错但transaction_date未更新。环境为XAMPP v3.3.0、MySQL 5+。

可行解决方案 #

方案1：直接使用UPDATE语句（推荐） #

通过CASE语句批量匹配transaction_number并更新对应transaction_date，逻辑清晰且避免主键/唯一键冲突问题：

UPDATE orderslip_transaction
SET transaction_date = CASE transaction_number
    WHEN '1545 | ALLCPB' THEN '2022-12-10 07:19:08'
    WHEN '1546 | ALLCPB' THEN '2022-12-12 10:14:45'
    WHEN '1577 | ALLCPB' THEN '2022-12-23 08:36:15'
    WHEN '1583 | ALLCPB' THEN '2022-12-24 13:30:31'
END
WHERE transaction_number IN ('1545 | ALLCPB', '1546 | ALLCPB', '1577 | ALLCPB', '1583 | ALLCPB');

方案2：修改INSERT语句触发唯一键冲突 #

若坚持使用INSERT...ON DUPLICATE KEY UPDATE，需显式指定已知的id值，触发主键冲突从而执行UPDATE分支：

INSERT INTO orderslip_transaction (id, transaction_number, transaction_date, transaction_is_active, store_id, created_on, updated_on, user_id)
VALUES
(247, '1545 | ALLCPB', '2022-12-10 07:19:08', 1, 1, '2022-12-10 11:09:10.739268', '2022-12-10 11:09:10.739268', 1),
(248, '1546 | ALLCPB', '2022-12-12 10:14:45', 1, 1, '2022-12-10 11:19:31.026184', '2022-12-10 11:19:31.026184', 1),
(262, '1577 | ALLCPB', '2022-12-23 08:36:15', 1, 1, '2022-12-10 12:09:34.693302', '2022-12-10 12:09:34.693302', 1),
(271, '1583 | ALLCPB', '2022-12-24 13:30:31', 1, 1, '2022-12-10 12:02:36.909668', '2022-12-10 12:02:36.925296', 1)
ON DUPLICATE KEY UPDATE transaction_date = VALUES(transaction_date);

方案3：使用REPLACE INTO（谨慎使用） #

REPLACE INTO会先删除冲突记录再插入新数据，适合需完全替换记录的场景，但会消耗自增ID序列，且若新数据字段与原记录不一致会覆盖原数据：

REPLACE INTO orderslip_transaction (transaction_number, transaction_date, transaction_is_active, store_id, created_on, updated_on, user_id)
VALUES
('1545 | ALLCPB', '2022-12-10 07:19:08', 1, 1, '2022-12-10 11:09:10.739268', '2022-12-10 11:09:10.739268', 1),
('1546 | ALLCPB', '2022-12-12 10:14:45', 1, 1, '2022-12-10 11:19:31.026184', '2022-12-10 11:19:31.026184', 1),
('1577 | ALLCPB', '2022-12-23 08:36:15', 1, 1, '2022-12-10 12:09:34.693302', '2022-12-10 12:09:34.693302', 1),
('1583 | ALLCPB', '2022-12-24 13:30:31', 1, 1, '2022-12-10 12:02:36.909668', '2022-12-10 12:02:36.925296', 1);

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