as usual, and for the combination of columns you want to be unique, simply add UNIQUE(column_name1,column_name2.)Īt the end of your CREATE TABLE statement, for any combination of the specified columns you want to be unique. In this case, when you create your corresponding table, define your primary key etc. Note that this solution is very handy if you're initiating your database tables. I came across this post because I needed what's written in the title, and I found a pretty handy solution, but no one mentioned it here, so I thought of pasting it here. The two can be confused because a single parameter switches which of these values the client is returned.Īlso note, if some_text and some_other_text are not actually modified (and the record is not otherwise changed) when you perform the update, those checks on affected-rows = 0 will misfire. Note: affected-rows in these examples mean affected rows and not found rows. handle this as a unique check constraint violation Some_other_text = if(record_id = VALUES(record_id), VALUES(some_other_text), some_other_text) Some_text = if(record_id = VALUES(record_id), VALUES(some_text), some_text), ON DUPLICATE KEY UPDATE and do this in a single statement: INSERT INTO table (record_id. If you don't mind a bit of ugliness, you can actually use INSERT. retry from UPDATE OR ignore this conflict and defer to the other session , some_text, some_other_text) VALUES (.) If there tends to be more updates than inserts: UPDATE table SET some_text =. retry from INSERT OR ignore this conflict and defer to the other session Here are the options: If there tends to be more inserts than updates: INSERT INTO table (record_id. ON DUPLICATE KEY UPDATE and work regardless of if it's autocommit=true and not depend on a transaction with an isolation level of REPEATABLE READ or greater.Īny solution performing check-then-act across multiple statements would not satisfy this. Mysql> CALL `sp_upsert`(4, 'new_text_0', 'new_text_1') Ī flexible solution should retain the atomicity offered by INSERT. | 1 | 1 | update_text_0 | update_text_1 | | record_id | person_id | some_text | some_other_text | Mysql> CALL `sp_upsert`(1, 'update_text_0', 'update_text_1') ON DUPLICATE KEY UPDATE `some_text` = `p_some_text`, (`person_id`, `some_text`, `some_other_text`) UNIQUE KEY `person_id_index` (`person_id`) UNIQUE KEY `record_id_index` (`record_id`), `record_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that Improvements are welcome.ġ3.2.5.3 INSERT. I have explored other variants of the same idea, such as with a LEFT JOIN and WHERE, but they all looked more convoluted. HAVING COUNT(*) = 0 inverts that, resulting in a row if the id is new, or no row if it already exists. As for the INSERT: WHERE id = 1 results in a row if the id exists, or no row if it does not. Only one of these queries will have an effect. Unique_name = 'one', update_datetime = NOW()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |