INSERT and UPDATE of large set of data in MySQL 4.x

When you are working in MySQL (4.x and lower) you are doomed to work w/o stored procedures, triggers, UDFs on which you used to work with, on all major database systems. So, in scenario when you need to update table with large number of cells, and each cell is represented as single row in table, you will face yourself with problems:

1. Avoid of inserting duplicated data
a) Insertion of new data (let’s say, added by entering data in cell)
b) Updating of existing data in table (changing the present value in cell)

2. Executing one query per cell will potentially cause bandwidth bottlenecks (large number queries sent from your client to database) and low performance of your application

Enough talking about troubles when we have solution:

CREATE TABLE ` MyTable ` (
`ID` int(10) unsigned NOT NULL auto_increment,
`a` int(10) unsigned,
`b` int(10) unsigned,
`c` int(10) unsigned
PRIMARY KEY (`ID`),
UNIQUE KEY `IU_ABC` (`a`,`b`,’c’)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO MyTable (a,b,c)
VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE
a=VALUES(a),b=VALUES(b),c=VALUES(c);

There are 2 crucial things to point to:

1. UNIQUE KEY in talbe creation script + ON DUPLICATE KEY UPDATE in INSERT statement
2. And VALUES (…),(…)

Let’s explain:

1. ON DUPLICATE KEY UPDATE works as it says: Do update if unique key (UI_ABC in our example) constraint is violated

2. VALUE (…),(…) is same as “select 1,2,3 union all select 4,5,6” or, better to say, “set of data” you want to insert/update


That’s it. Whatever programming language you use, you will be able to accomplish this by building query using string concatenation.

Good luck.

Reference:
http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html

Interesting to read:
http://www.xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql/