MySQL - INSERT

mysql

INSERT INTO customer ( name, address, city, state, country, zip, contact, email ) VALUES
('name', 'address', 'city', 'state', 'country', 'zip', 'contact', 'email'),
('name', 'address', 'city', 'state', 'country', 'zip', 'contact', 'email');
INSERT INTO people SET id = 12, name = 'William', age = 25;

You can omit the list of column names, but is is not recommended. Including the list of column names greatly increase the probability that your SQL will continue to function in the event that reordering of column occur on the table in the database.

Note that multi-row INSERT statement requires a separate parenthesized list for each row.

Multi-row INSERT statement is more efficient because the server can process all the rows at once rather than as separate operations. When you have many records to add, multi-row statements provide better performance and reduce the load on the server. On the other hand, such statements are more likely to reach the maximum size of the communication buffer used to transmit information to the server. This size is controlled by the max_allowed_packet variable which has a default value of 1MB.

For multi-row INSERT statements, MySQL returns an extra information strings containing several counts. Records indicates the number of records inserted. Duplicates indicates how may records were ignored because the contained duplicate unique key values. This values can be non-zero if the statement includes the IGNORE keyword. Warnings indicates the number of problems found in the data values. These can occur if values are converted.

If data retrieval is of utmost importance (as usually is), you can instruct MySQL to lower the priority of your INSERT statement by adding keyword LOW_PRIORITY between INSERT and INTO: INSERT LOW_PRIORITY INTO.

This also applies to UPDATE and DELETE.

To get the last insert id: SELECT last_insert_id();

INSERT SELECT:

INSERT INTO customer ( id, contact, email, name, address, city, state, zip, country) SELECT id, contact, email, name, address, city, state, zip, country FROM custnew;

Handling Duplicate Key Values:

If you don't explicitly specify how to handle duplicate, MySQL aborts the statement with an error and discards the new record. For multi-row INSERT statement, treatment of records inserted before that record that causes duplicate-key violation is dependent on the storage engine. For MyISAM, the records are inserted. For InnoDB, the entire statement fails and no records are inserted.

You can tell MySQL to ignore the new record (INSERT IGNORE instead of INSERT).

You can use the ON DUPLICATE KEY UPDATE clause to update specific columns of the existing record.

If you want to replace the old record with the new one when duplicate key occurs, use the REPLACE statement instead of INSERT.

By using the ON DUPLICATE KEY UPDATE clause with INSERT, you have the option of choosing to update one or more column of the existing row, rather than letting the INSERT statement fail or using REPLACE to replace the entire row.

The ON DUPLICATE KEY UPDATE clause allows you to do in one statement what otherwise require two (INSERT and UPDATE) statements. For non-transactional tables, it saves you from having to explicitly lock the table to prevent UPDATE error when the referenced row may have been deleted in between the INSERT and UPDATE.

One case where ON DUPLICATE KEY UPDATE clause is especially useful is when you have a table with counters that are tied to key values. You want to create a new record if none exist for the key, but just increment the counter if the key does exist. Given the following table:

CREATE TABLE log
(
    name CHAR(30) NOT NULL,
    location CHAR(30) NOT NULL,
    counter INT UNSIGNED NOT NULL,
    PRIMARY KEY (name, location)
);

Then, everytime we wish to log a sighting:

INSERT INTO log (name, location, counter) VALUES ('Tantor', 'Waterhole', 1) ON DUPLICATE KEY UPDATE counter = counter + 1;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License