Integrated functional MySQL

In MySQL has enough built-in functions and features that simplify the code. Unfortunately, not all programmers are aware of this and use their «Bicycles».

ON DUPLICATE KEY UPDATE


For example, there is a table with some objects (users , posts , etc.д.). If the object with such aunique property of already exists, the update of some property he. If the object does not exist, then insert a new row. Often you can find this code:

// find the object $row = query('SELECT * FROM table WHERE id=1'); // check whether there is such an object if ($row) { // do update query('UPDATE table SET column=column+1 WHERE id=1'); } else { // do insert query('INSERT INTO table SET column=1, id=1'); }

Such a structure can be replaced with a single query without php, subject to the availability of a primary or unique key on the field id:

INSERT INTO table SET column = 1, id=1 ON DUPLICATE KEY UPDATE column = column + 1

INSERT IGNORE


Often with the addition of a table that has UNIQUE index or PRIMARY KEY, a new line, it is a very useful syntax INSERT IGNORE. Using this syntax is useful in case of an accidental duplicate key when you insert , the insert itself is not made , and the execution is terminated.
The usual algorithm:
1) check for rows in a table by a key (SELECT)
2) insert a line in the absence of duplicate key (INSERT)

// find the object<br>$row = query('SELECT * FROM table WHERE id=1'); // If no such object , then insert a new record if (!$row) { query('INSERT INTO table …'); }
Now let's write only one query INSERT IGNORE without php
query('INSERT IGNORE INTO table …') // insert