{"id":2422,"date":"2015-02-10T01:58:04","date_gmt":"2015-02-10T01:58:04","guid":{"rendered":"http:\/\/www.sitekickr.com\/blog\/?p=2422"},"modified":"2015-02-10T02:06:35","modified_gmt":"2015-02-10T02:06:35","slug":"mysql-bulk-insert-update","status":"publish","type":"post","link":"https:\/\/www.sitekickr.com\/blog\/mysql-bulk-insert-update\/","title":{"rendered":"MySQL Bulk Insert or Update"},"content":{"rendered":"

If you’re not familiar with the MySQL\u00a0Insert or Update statement<\/a>, check it out before reading on. It’s essentially a way to leverage a database table’s primary key to reduce the number of queries required to insert or update a record, by eliminating the need to perform a select query first.<\/p>\n

Many aren’t aware that this technique can also be applied in bulk queries. Okay I admit, I didn’t know until today!<\/p>\n

By combining MySQL’s Bulk Insert syntax with the Insert or Update syntax, you are able to reduce a very large number of queries and statements down to one single statement.<\/p>\n

Let’s using the following cross-reference table as an example. Assuming we have a users table and a preferences table. The user_preference table provides a many-to-many cross-reference table between the two.<\/p>\n

user_preference<\/strong>\r\nuser_id INT PRIMARY_KEY\r\npreference_id INT PRIMARY_KEY\r\npref_value VARCHAR(16)<\/pre>\n
Let’s say we wanted to assign group of user’s a value of ‘true’ to preference_id 1. We can do that in a single query as seen in the example below:<\/p>\n
INSERT INTO user_preference (user_id, preference_id, pref_value) \r\nVALUES \r\n(1, 1, 'true'), (2, 1, 'true'), (3, 1, 'true')\r\nON DUPLICATE KEY UPDATE\r\nvalue = 'true'<\/code><\/pre>\n
This is only the tip of the iceberg though. The Insert or Update syntax also allows you to use the VALUES() function to access the group of values that would<\/strong> be inserted had they not already existed.<\/p>\n
This comes in handy if each row will have a different value for non-key fields, as seen below:<\/p>\n
INSERT INTO user_preference (user_id, preference_id, value)\u00a0\r\nVALUES\u00a0\r\n(1, 1, 'true'), (2, 1, 'true'), (3, 1, 'false')\r\nON DUPLICATE KEY UPDATE\r\npref_value = VALUES(pref_value)<\/code><\/pre>\n
Let’s wrap this up with some quick PHP code to demonstrate one way to put a bulk Insert or Update query together, given an array of users.<\/p>\n
Please note, I have left out all sql-injection prevention code for clarity.<\/p>\n
$sql = 'INSERT INTO user_preference\r\n        (user_id, preference_id, pref_value) \r\n        VALUES ';\r\n\r\n $userCount = count($users);\r\n\r\n foreach ($users as $key => $user) {\r\n     $sql .= $user['user_id'] . ', 1, true)';\r\n     if (($key + 1) < $userCount) $sql .= ', ';\r\n }\r\n\r\n $sql .= ' ON DUPLICATE KEY UPDATE \r\n           pref_value = VALUES(pref_value)';<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
Any technique that saves round trips to the database pays for itself exponentially over time. MySQLs bulk Insert or Update syntax is one of those techniques.<\/p>\n","protected":false},"author":1,"featured_media":2423,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"amp_status":""},"categories":[13],"tags":[88],"_links":{"self":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/2422"}],"collection":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/comments?post=2422"}],"version-history":[{"count":4,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/2422\/revisions"}],"predecessor-version":[{"id":2428,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/2422\/revisions\/2428"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/media\/2423"}],"wp:attachment":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/media?parent=2422"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/categories?post=2422"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/tags?post=2422"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}