MySQL/Language/Data manipulation

INSERT
The syntax is as follows:

Insert value1 into Column1, value2 into Column2, and value3 into Column3: Insert one record (values are inserted in the order that the columns appear in the database): Insert two records: You can also insert records 'selected' from other table. Performance tips:


 * To insert many rows, consider using LOAD DATA INFILE instead.
 * If bulk INSERTs are too slow and they operate on indexed non-empty tables, maybe you should increase the value of bulk_insert_buffer_size.
 * Before performing bulk inserts, you may want to disable the keys.
 * LOCKing a table also speeds up the INSERT.

UPDATE
The syntax is: Examples are: With ORDER BY you can order the rows before updating them, and only update a given number of rows (LIMIT).

It is currently not possible to update a table while performing a subquery on the same table. For example, if I want to reset a password I forgot in SPIP: mysql> UPDATE spip_auteurs SET pass = (SELECT pass FROM spip_auteurs WHERE login='paul') where login='admin'; ERROR 1093 (HY000): You can't specify target table 'spip_auteurs' for update in FROM clause

TODO: describes a work-around that I couldn't make to work with MySQL 4.1. Currently the work-around is not use 2 subqueries, possibly with transactions.

Performance tips


 * UPDATEs speed depends of how many indexes are updated.
 * If you UPDATE a MyISAM table which uses dynamic format, if you make rows larger they could be split in more than one part. This causes reading overhead. So, if your applications often do this, you may want to regularly run an OPTIMIZE TABLE statement.
 * Performing many UPDATEs all together on a LOCKed table is faster than performing them individually.

REPLACE
REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.

IGNORE
Since MySQL 5.5, "INSERT IGNORE" and "REPLACE IGNORE" allow, when a duplicate key error occurs, to display some warnings and avoid the statement to abort.

Prior to MySQL 4.0.1, INSERT ... SELECT implicitly operates in IGNORE mode. As of MySQL 4.0.1, specify IGNORE explicitly to ignore records that would cause duplicate-key violations.

DELETE and TRUNCATE
You can order the rows before deleting them, and then delete only a given number of rows.
 * If you don't use a WHERE clause with DELETE, all records will be deleted.
 * It can be very slow in a large table, especially if the table has many indexes.
 * If the table has many indexes, you can make the cache larger to try making the DELETE faster (key_buffer_size variable).
 * For indexed MyISAM tables, in some cases DELETEs are faster if you specify the QUICK keyword (DELETE QUICK FROM ...). This is only useful for tables where DELETEd index values will be reused.
 * TRUNCATE will delete all rows quickly by DROPping and reCREATE-ing the table (not all Storage Engines support this operation).
 * TRUNCATE is not transaction-safe nor lock-safe.
 * DELETE informs you how many rows have been removed, but TRUNCATE doesn't.
 * After DELETing many rows (about 30%), an OPTIMIZE TABLE command should make next statements faster.
 * For a InnoDB table with FOREIGN KEYs constraints, TRUNCATE behaves like DELETE.