Geek Answers Handbook

MySQL Create a two-column UNIQUE index for ON DUPLICATE KEY

I have a table with 4 columns. I want to be able to INSERT or UPDATE a column of values ​​at creation (I do not need index row identifiers).

CREAT TABLE tablename (
 id,   (primary)
 user_id, (index)
 setting_id, (index)
 value  (index)
);

Initially, I was going to run the "REPLACE IN ..." query every time I wanted to keep the value. But then I read about the problems and instead chose INSERT INTO ... ON DUPLICATE KEY UPDATE.

The problem is that I do not have a single column for "INSERT INTO ... ON DUPLICATE KEY UPDATE" to use to find out if this is a new line or if there is an existing line that needs to be updated.

So, I decided that I would use the UNIQUE key, which is a combination of two columns that made this row unique (

CREATE UNIQUE INDEX index_name ON table(user_id, setting_id);

However, I am not sure how to move from here. How to structure my queries to check this new INDEX when trying to figure out which row already exists with these two column values?

INSERT INTO `tablename` (value, user_id, setting_id) VALUES (1,34,15) ON DUPLICATE KEY UPDATE

: EDIT:

By removing only the original identifier column (setting_id, user_id and value), I was able to create a PRIMARY index (setting_id, user_id), and then the following query worked. more details here .

INSERT INTO tablename (user_id, setting_id, value)
VALUES (42, 1, 12)
ON DUPLICATE KEY UPDATE value = 12
+5
source share
1 answer

The query you have will look something like this ...

INSERT INTO table (value,user_id,setting_id) VALUES (1,34,15)
  ON DUPLICATE KEY UPDATE value = 1, user_id = 34, setting_id = 15;
+8
source

More articles: