INSERT或是否存在列组合UPDATE [英] INSERT or if column combination exists UPDATE

问题描述

我有一个用户元数据表，其中存储了一些其他值.我需要:

I have a user-meta-data table where I store some additional values. I need to:

A)插入新的user_id/meta_type组合+值

A) INSERT a new user_id/meta_type combination + value

或

B)如果已经有user_id/meta_type组合，则只需更新其值

B) if there already is the user_id/meta_type combination then just update its value

重要提示:

我找到了MySQL的"ON DUPLICATE KEY UPDATE"-但据我从手册中了解，似乎它仅检查PRIMARY KEY，所以问题是:

I found MySQL's "ON DUPLICATE KEY UPDATE" - but from what I understood from the manual it seems like it only checks for a PRIMARY KEY so the question is:

A)是否可以执行类似在DUPLICATE COLUMN1和COLUMN2 UPDATE值上"的操作?

A) Is there a way to do something like "ON DUPLICATE COLUMN1 AND COLUMN2 UPDATE value"?

B)在MySQL InnoDB中是否可以在多列上创建PRIMARY KEY?
   (例如meta_id + user_id + meta_type为PRIMARY KEY)

B) Is it possible in MySQL InnoDB to create PRIMARY KEY on multiple columns?
    (e.g. meta_id+user_id+meta_type would be the PRIMARY KEY)

C)我是否理解ON DUPLICATE KEY UPDATE手册有误?

C) Did I understand the ON DUPLICATE KEY UPDATE manual wrong?

推荐答案

如果您指定ON DUPLICATE KEY UPDATE，并且插入一行会导致UNIQUE索引或PRIMARY KEY中出现重复值，则MySQL将对老行.例如，如果将a列声明为UNIQUE并包含值1，则以下两个语句具有相似的作用:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

插入表(a，b，c)值(1,2,3) 重复键更新时c = c + 1;

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

等效的更新查询如下，

更新表SET c = c + 1 WHERE a = 1;

UPDATE table SET c=c+1 WHERE a=1;

如果a和b列是唯一的，则等效更新查询为，

If a and b column is unique, equivalent update query would be ,

更新表SET c = c + 1其中a = 1或b = 2 LIMIT 1;

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

这篇关于INSERT或是否存在列组合UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！