VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
INSERT ... ON DUPLICATE KEY UPDATE 在它不存在时写入它,在它存在时修改它,只用 1 条语句,而且不需要先检查。每一个”保存这个设置”和每一个必须先创建才能递增的计数器,背后都是这条语句。
这些示例使用来自 INSERT 的 sakila_practice 数据库。开始之前,请再次运行那里的设置代码块,然后用 mysql -u root -p sakila_practice 打开客户端。
工作原理
TABLE_NAME 替换为表,COLUMN_LIST 和 VALUE_LIST 替换为与普通插入相同的列和值,ROW_ALIAS 替换为您所提供的行的名字,COLUMN_NAME 替换为该行已存在时要修改的列。
ON DUPLICATE KEY UPDATE 子句只在插入会违反 PRIMARY KEY 或 UNIQUE 索引的唯一性时运行。别名指代您试图插入的那一行,所以 ROW_ALIAS.COLUMN_NAME 是您提供的值,而裸列名是已存储的值。
示例
- 它所避免的冲突
rating_count 以 rating 为主键。写入一行。
- 插入,或更新挡路的那一行
2 rows affected。这个计数是语句告诉您它走了哪条分支的方式:插入一行是 1,更新一行是 2,更新发现值已经正确则是 0。
- 同一条语句作用于不存在的行
1 row affected 是插入分支。这条语句除了值之外什么都没变,这正是它的意义所在:调用者不必知道那一行是否存在。
刷新整个汇总表
行可以来自一个查询,这就是用 1 条语句把汇总表更新到最新的方法。别名形式在那里是不允许的。Duplicates: 1 是 G 那一行,它原来存的是 179,现在是 178。3 个新分级占了 5 个受影响行中的 3 个,那一次更新占了另外 2 个。R 已经是 195,所以它完全没有计入。
旧代码用 VALUES() 而不是别名
旧语句用VALUES(films) 代替别名来读取所提供的值。这种形式仍然可用,但会引发一个警告。
\G 像分号一样结束语句,并把每一列打印成一行,这是阅读一条宽得放不进表格的消息的方法。新语句请用别名来写,并预期在早先写的代码中遇到 VALUES()。
总结
- 当某一行可能存在也可能不存在时,用
INSERT ... ON DUPLICATE KEY UPDATE。 - 预期该子句只在
PRIMARY KEY或UNIQUE冲突时运行。 - 把受影响的行数读作:插入为 1,更新为 2,无变化为 0。
- 用
AS alias给所提供的行命名,并以alias.column读取它的值。 - 当行来自
SELECT时,把查询包进派生表。 - 把
VALUES(col)换成别名形式,这正是警告 1287 所要求的。
另请参阅
- INSERT — 普通形式和
ERROR 1062 - INSERT SELECT — 来自查询的行
- UPDATE — 修改您知道存在的行
- MySQL 中的 UPSERT — REPLACE、INSERT IGNORE,以及该选哪一个