无主键实现MySQL表不存在则插入、存在则更新操作

原SQL报错原因 #

MySQL并不支持ON EXISTS语法，你尝试的语句不符合MySQL的UPSERT（插入或更新）规范，这是报错的核心原因。

实现步骤 #

要实现「存在code='code2'则更新，不存在则插入」的需求，需要先给code字段添加唯一索引（用于判断记录是否存在），再结合UPDATE和INSERT语句完成逻辑。

1. 添加唯一索引

因为要基于code字段做存在性判断，必须给它添加唯一约束：

ALTER TABLE myTable ADD UNIQUE INDEX idx_code (code);

2. 执行UPSERT逻辑

先尝试更新匹配code='code2'的记录，如果没有匹配到（即记录不存在），再插入新数据：

-- 尝试更新目标记录
UPDATE myTable 
SET code = 'code2edited', name = 'name2edited' 
WHERE code = 'code2';

-- 若更新影响行数为0，说明记录不存在，执行插入
INSERT INTO myTable (code, name) 
SELECT 'code2edited', 'name2edited' 
FROM DUAL 
WHERE ROW_COUNT() = 0;

逻辑说明 #

• UPDATE语句会定位到code='code2'的记录，将其code和name更新为目标值；
• ROW_COUNT()函数会返回上一条SQL的影响行数，若为0则说明没有找到匹配的记录，此时执行INSERT插入新行（id会自动自增为3）。

替代方案（单语句实现） #

如果想用单条SQL实现，可借助INSERT ... SELECT结合ON DUPLICATE KEY UPDATE，逻辑如下：

INSERT INTO myTable (code, name)
SELECT 'code2', 'name2edited'
FROM DUAL
ON DUPLICATE KEY UPDATE code = 'code2edited', name = 'name2edited';

注：此语句会先尝试插入code='code2'的记录，若该记录存在则更新为目标值；若不存在则插入code='code2'的记录。如果需要不存在时直接插入code='code2edited'，仍推荐使用「UPDATE+INSERT」的组合语句。

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