跳转到主要内容

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 是您提供的值,而裸列名是已存储的值。

示例

  1. 它所避免的冲突

rating_count 以 rating 为主键。写入一行。
用新的计数再写一次,插入失败。

  1. 插入,或更新挡路的那一行

存储了一行,语句却说 2 rows affected。这个计数是语句告诉您它走了哪条分支的方式:插入一行是 1,更新一行是 2,更新发现值已经正确则是 0。
那一行被找到了,而且已经是 179,所以没有什么可写。永远不要把受影响的行数当作表中的行数来读。

  1. 同一条语句作用于不存在的行

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 所要求的。

另请参阅