如何使用SELECT在ON DUPLICATE KEY UPDATE时设置重复键自定义值

你给出的SQL写法无法实现需求，存在多处语法和逻辑错误，具体问题及修正方案如下：

存在的问题 #

• 语法结构错误：MySQL的INSERT语句仅支持INSERT ... VALUES()、INSERT ... SELECT两种插入值的写法，不能在VALUES批量赋值后额外追加独立SELECT子句，你当前的写法本身就会触发语法报错。
• 查询逻辑错误：你写的SELECT CONCAT(...) as inv_codes from invoices where id_invoice = id_invoice的WHERE条件是恒成立的，会返回表中所有行的inv_code处理结果，完全无法匹配到冲突行对应的编码值。
• ON DUPLICATE KEY UPDATE 用法错误：
  1. VALUES(id_invoice+1)写法不符合规范，VALUES()函数仅支持传入列名作为参数，运算逻辑要放在函数外面，正确写法为VALUES(id_invoice) + 1。
  2. inv_code = VALUES(inv_codes)完全无效，你INSERT语句的字段列表里没有inv_codes这个字段，VALUES()无法读取到不存在的字段值。

正确实现方案 #

如果你是单条插入场景，要在唯一键冲突时自动递增id_invoice和inv_code，可以直接在ON DUPLICATE KEY UPDATE子句里写赋值逻辑，无需额外加SELECT：

INSERT INTO `invoices` (`id_invoice`, `inv_code`, `inv_date`, `customer_id`, `outlet_id`, `marketing_source_id`, `consultant_id`, `therapist_id`, `staff_id`, `agent_id`, `expired_date`, `total`, `remaining`, `remarks`, `inv_remarks`, `status_inv`, `void_invoice`, `customer_sign`, `created_at`, `updated_at`)
VALUES
(4, 'HD-I-202002-0002', '2020-02-08', 2, 1, 3, 1, 3, 3, 0, '2021-02-08', 440000, 0, NULL, NULL, 1, 0, NULL, '2020-02-08 04:54:09', '2020-02-08 04:54:09')
ON DUPLICATE KEY UPDATE 
  id_invoice = VALUES(id_invoice) + 1,
  inv_code = CONCAT(LEFT(VALUES(inv_code), 12), LPAD(RIGHT(VALUES(inv_code), 4)+1, 4 ,'0'));

如果是批量插入多条数据的场景，上述写法对每条插入数据单独处理冲突逻辑也可生效，但需要注意：如果单条数据连续多次触发唯一键冲突，单次+1的逻辑可能依然会触发重复，这种场景更建议将id_invoice设置为自增主键，避免手动赋值带来的冲突问题。

另外由于你设置了两个唯一约束列，只要任意一列的值重复就会触发UPDATE逻辑，如果你需要区分是哪一列触发的冲突，还需要额外增加逻辑判断。

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