Is there a drawback to blindly using INSERT in MySQL?

Question

Often I want to add a value to a table or update a value if its key already exists. This can be done in several ways, assuming that the primary or unique key is specified in the columns "user_id" and "pref_key" in the example:

1. Blind insert, update upon receipt of a duplicate key error:

// Try to insert as a new value
INSERT INTO my_prefs 
(user_id, pref_key, pref_value)
VALUES (1234, 'show_help', 'true');

// If a duplicate-key error occurs run an update query
UPDATE my_prefs 
SET pref_value = 'true'
WHERE user_id=1234 AND pref_key='show_help';

2. Check availability, then select or update:

// Check for existence
SELECT COUNT(*) 
FROM my_prefs
WHERE user_id=1234 AND pref_key='show_help';

// If count is zero, insert
INSERT INTO my_prefs 
(user_id, pref_key, pref_value) 
VALUES (1234, 'show_help', 'true');

// If count is one, update
UPDATE my_prefs 
SET pref_value = 'true' 
WHERE user_id=1234 AND pref_key='show_help';

The first method seems to be preferable, since only one request for new insertions and two for updating will be required for updating, moreover, since the second method always requires two requests. Is there something I am missing out on, although it would be a bad idea to blindly insert?

Answer 1

INSERT ? ?

"ON DUPLICATE" ( - , ) , , MySQL.

" " , . ( INSERT - , , UPDATE , . , - .) "ON DUPLICATE", , , : , .

" " , INSERT. SELECT UPDATE. - , "" " - " ( , MySQL). , , , , " ", , , UPDATE .

Answer 2

ON DUPLICATE KEY http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Answer 3

MySQL, RDBMS

INSERT INTO my_prefs 
(user_id, pref_key, pref_value) 
VALUES (1234, 'show_help', 'true')
ON DUPLICATE KEY 
UPDATE pref_value = 'true'

Answer 4

( ), /.

, (, ), , , .

Answer 5

"", , , SQL ( / ), "SQL" - ( -):

int i = SQL("UPDATE my_prefs ...");
if(i==0) {
    SQL("INSERT INTO my_prefs ...");
}

, - , .

Answer 6

REPLACE , MySQL, " INSERT... ON DUPLICATE KEY UPDATE"

, , " MySQL", , .

Answer 7

- , , .

Answer 8

DAO id.

• null/-1/whatever, .

• ( ), id .

• Your persist method can check the identifier and pass it to the update () or add () implementation.

• Disadvantages: exit from synchronization with the database, etc. I am sure there are more of them, but I really have to do some work ...

Answer 9

While you are using MySQL, you can use the keyword ON DUPLICATE. For instance:

INSERT INTO my_prefs (user_id, pref_key, pref_value) VALUES (1234, 'show_help', 'true') 
ON DUPLICATE KEY UPDATE (pref_key, pref_value) VALUES ('show_help', 'true');