没有唯一列的MySQL'DUPLICATE KEY更新'? [英] MySQL 'UPDATE ON DUPLICATE KEY' without a unique column?
问题描述
人们对执行以下查询的最高效的方式有何看法:
What are peoples' thoughts on the most performance efficient way to do the following query:
-
3列表
3 column table
,如果col_1
和col_2
值的组合已经存在UPDATE col_3
if the combination of col_1
and col_2
values already exist UPDATE col_3
其他INSERT
新行
我假设我需要某种对UP DUMPLICATE KEY的更新(我以前从未使用过),但是我没有一个"KEY",而是一对两个值(列)来构成一个钥匙...
I assume i need some kind if UPDATE ON DUPLICATE KEY (which i've never used before), however I do not have a 'KEY' but instead a pair of two values (columns) to make a key...
推荐答案
您可以在MySQL中的多个列中创建一个PRIMARY
或UNIQUE
键(称为复合键),这将允许ON DUPLICATE KEY
工作正常.
You can create a PRIMARY
or UNIQUE
key out of multiple columns (called a composite key) in MySQL, which'll allow ON DUPLICATE KEY
to work just fine.
// create a composite index
CREATE INDEX my_composite_index ON my_table (column1, column2);
// insert or update
INSERT INTO my_table (column1, column2) VALUES ('value1', 'value2') ON DUPLICATE KEY UPDATE column3=column3+1;
这篇关于没有唯一列的MySQL'DUPLICATE KEY更新'?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!