11.2. The INSERT Statement

11.2. The INSERT Statement

The INSERT statement adds new records to a table. It has two basic formats, one of which allows for insertion of multiple rows using a single statement:

 INSERT INTO table_name (column_list) VALUES (value_list); INSERT INTO table_name     SET column_name = value [, column_name = value] ... ; 

The first syntax for INSERT uses separate column and value lists following the name of the table into which you want to add the record. The number of columns and values must be the same. The following statement uses this syntax to create a new record in the people table with id set to 12, name set to 'William', and age set to 25:

 INSERT INTO people (id,name,age) VALUES(12,'William',25); 

The second INSERT syntax follows the table name by a SET clause that lists individual column assignments separated by commas:

 INSERT INTO people SET id = 12, name = 'William', age = 25; 

The SET clause must assign a value to at least one column.

For any column not assigned an explicit value by an INSERT statement, MySQL sets it to its default value if it has one. For example, to have MySQL set the id column to its default, you can simply omit it from the statement. The following example shows statements using each INSERT syntax that assign no explicit id value:

 INSERT INTO people (name,age) VALUES('William',25); INSERT INTO people SET name = 'William', age = 25; 

In both statements, the effect for the people table is the same: The id column is set to its default value. id is an AUTO_INCREMENT column, so its default is the next sequence number.

In general, if a column has no default value, the effect of omitting it from the INSERT statement depends on whether it can take NULL values and on the SQL mode:

• If the column can take NULL values, it is set to NULL.

• If the column cannot take NULL values, it is set to the implicit default for the column data type if strict SQL mode is not enabled. If strict mode is enabled, an error occurs.

MySQL can be configured to allow or reject attempts to insert invalid data into a row. For details about handling of such values, see Section 5.8, "Handling Missing or Invalid Data Values."

The VALUES form of INSERT has some variations:

• If both the column list and the VALUES list are empty, MySQL creates a new record with each column set to its default:

   INSERT INTO people () VALUES(); 

  
  

  The preceding statement creates a record with id, name, and age set to their defaults (the next sequence number, the empty string, and 0, respectively).

• It's allowable to omit the list of column names and provide only the VALUES list. In this case, the list must contain one value for every column in the table. Furthermore, the values must be listed in the same order in which the columns are named in the table's definition. (This is the order in which the columns appear in the output from DESCRIBE table_name.) The following INSERT statement satisfies these conditions because it provides three column values in id, name, and age order:

   INSERT INTO people VALUES(12,'William',25); 

  
  

  On the other hand, this statement is illegal because it provides only two values for a three-column table:

   INSERT INTO people VALUES('William',25); 

  
  

  The following INSERT statement is syntactically legal because it provides a value for every column, but it assigns 25 to name and 'William' to age, which is not likely to serve any useful purpose:

   INSERT INTO people VALUES(12,25,'William'); 

  
  

  The statement also will cause an error in strict SQL mode because the age column requires a number and 'William' cannot be converted to a number.

• You can insert multiple records with a single statement by providing several values lists after the VALUES keyword. This is discussed in Section 11.2.1, "Adding Multiple Records with a Single INSERT Statement."

As noted, for an INSERT statement that provides data values in the VALUES list, it's permissible to omit the list of column names if the statement contains a data value for every column. However, it isn't necessarily advisable to do so. When you don't include the list of column names, the VALUES list must not only be complete, the data values must be in the same order as the columns in the table. If it's possible that you'll alter the structure of the table by adding, removing, or rearranging columns, such alterations might require any application that inserts records into the table to be modified. This is much more likely if the INSERT statements don't include a list of column names because they're more sensitive to the structure of the table. When you use an INSERT statement that names the columns, rearranging the table's columns has no effect. Adding columns has no effect, either, if it's appropriate to set the new columns to their default values.

11.2.1. Adding Multiple Records with a Single INSERT Statement

A single INSERT ... VALUES statement can add multiple records to a table if you provide multiple VALUES lists. To do this, provide a parenthesized list of values for each record and separate the lists by commas. For example:

 INSERT INTO people (name,age) VALUES('William',25),('Bart',15),('Mary',12); 

The statement shown creates three new people records, assigning the name and age columns in each record to the values listed. The id column is not listed explicitly, so MySQL assigns its default value (the next sequence value) in each record.

Note that a multiple-row INSERT statement requires a separate parenthesized list for each row. Suppose that you have a table t with a single integer column i:

 CREATE TABLE t (i INT); 

To insert into the table five records with values of 1 through 5, the following statement does not work:

 mysql> INSERT INTO t (i) VALUES(1,2,3,4,5); ERROR 1136 (21S01): Column count doesn't match value count at row 1 

The error occurs because the number of values between parentheses in the VALUES list isn't the same as the number of columns in the column list. To write the statement properly, provide five separate parenthesized lists:

 mysql> INSERT INTO t (i) VALUES(1),(2),(3),(4),(5); Query OK, 5 rows affected (0.00 sec) Records: 5  Duplicates: 0  Warnings: 0 

It's allowable to omit the list of column names in multiple-row INSERT statements. In this case, each parenthesized list of values must contain a value for every table column.

The preceding example illustrates something about multiple-row INSERT statements that isn't true for single-row statements: MySQL returns an extra information string containing several counts. The counts in each field of this string have the following meanings:

• Records indicates the number of records inserted.

• Duplicates indicates how many records were ignored because they contained duplicate unique key values. This value can be non-zero if the statement includes the IGNORE keyword. The action of this keyword is described in Section 11.2.2, "Handling Duplicate Key Values."

• Warnings indicates the number of problems found in the data values. These can occur if values are converted. For example, the warning count is incremented if an empty string is converted to 0 before being stored in a numeric column. To see what caused the warnings, issue a SHOW WARNINGS statement following the INSERT.

A multiple-row INSERT statement is logically equivalent to a set of individual single-row statements. However, the multiple-row 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, multiple-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.)

MySQL treats single-row and multiple-row INSERT statements somewhat differently for purposes of error-handling. These differences are described in Section 5.8, "Handling Missing or Invalid Data Values."

11.2.2. Handling Duplicate Key Values

If a table has a unique-valued index, it might not be possible to use INSERT to add a given record to the table. This happens when the new record contains a key value for the index that's already present in the table. Suppose that every person in the people table has a unique value in the id column. If an existing record has an id value of 347 and you attempt to insert a new record that also has an id of 347, it duplicates an existing key value. MySQL provides three ways to deal with duplicate values in a unique-valued index when adding new records to a table with INSERT:

• If you don't indicate explicitly how to handle a duplicate, MySQL aborts the statement with an error and discards the new record. This is the default behavior. (For multiple-record INSERT statements, treatment of records inserted before a record that causes a 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 without producing an error. To do this, modify the statement so that it begins with INSERT IGNORE rather than with INSERT. If the record does not duplicate a unique key value, MySQL inserts it as usual. If the record does contain a duplicate key, MySQL ignores it. Client programs that terminate on statement errors will abort with INSERT but not with INSERT IGNORE.

• 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 a duplicate key occurs, use the REPLACE statement instead of INSERT. (See Section 11.3, "The REPLACE Statement.")

Note that for a unique-valued index that can contain NULL values, inserting NULL into an indexed column that already contains NULL doesn't cause a duplicate-key violation. This is because such an index can contain multiple NULL values.

11.2.3. Using INSERT ... ON DUPLICATE KEY UPDATE

Normally, if you attempt to insert a row into a table that would result in a duplicate-key error for a unique-valued index, the insertion fails. In some cases, you can use the REPLACE statement instead, which deletes the old row and inserts the new one in its place. (See Section 11.3, "The REPLACE Statement.") However, REPLACE is not suitable if you wish to change only some columns of the old row. By using the ON DUPLICATE KEY UPDATE clause with INSERT, you have the option of choosing to update one or more columns 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 requires two (INSERT and UPDATE). Also, for non-transactional tables, it saves you from having to explicitly lock the table to prevent UPDATE errors when the referenced row may have been deleted in between the INSERT and UPDATE.

One case where this new behavior is especially useful is when you have a table with counters that are tied to key values. When it's time to increment a counter in the record for a given key, you want to create a new record if none exists for the key, but just increment the counter if the key does exist. For example, suppose that we are tracking elephants in the wild and want to count the number of times each elephant has been spotted at a given location. In this case, we can create a log table to log elephant sightings based on the unique key of elephant name and location:

 mysql> CREATE TABLE log (     ->   name     CHAR(30) NOT NULL,     ->   location CHAR(30) NOT NULL,     ->   counter  INT UNSIGNED NOT NULL,     ->   PRIMARY KEY (name, location)); Query OK, 0 rows affected (0.07 sec) 

Then, every time we wish to log a sighting, we can use INSERT without first checking whether the record exists. This simplifies application logic by reducing the number of conditions that must be tested. For example, if we have just created the table, and the first two sightings that occur are for the elephant "Tantor" over by the waterhole, we would use the same INSERT statement each time. The first instance of the statement inserts a record and the second causes it to be updated:

 mysql> INSERT INTO log (name, location, counter)     -> VALUES ('Tantor', 'Waterhole', 1)     -> ON DUPLICATE KEY UPDATE counter=counter+1; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM log; +--------+-----------+---------+ | name   | location  | counter | +--------+-----------+---------+ | Tantor | Waterhole |       1 | +--------+-----------+---------+ 1 row in set (0.00 sec) mysql> INSERT INTO log (name, location, counter)     -> VALUES ('Tantor', 'Waterhole', 1)     -> ON DUPLICATE KEY UPDATE counter=counter+1; Query OK, 2 rows affected (0.00 sec) mysql> SELECT * FROM log; +--------+-----------+---------+ | name   | location  | counter | +--------+-----------+---------+ | Tantor | Waterhole |       2 | +--------+-----------+---------+ 1 row in set (0.00 sec) 

Notice the difference in the "rows affected" value returned by the server for each INSERT statement: If a new record is inserted, the value is 1; if an already existing record is updated, the value is 2.