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
uses separate column and value lists following the
INSERT INTO people (id,name,age) VALUES(12,'William',25);
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
table is the same: The
column is set to its default value.
column, so its default is the
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:
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:
As noted, for an
statement that provides data values in the
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
11.2.1. Adding Multiple Records with a Single INSERT Statement
INSERT ... VALUES
statement can add multiple records to a table if you provide multiple
lists. To do this, provide a
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:
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
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 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.
ON DUPLICATE KEY UPDATE
clause allows you to do in one statement what
One case where this new behavior is
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
without first checking whether the record exists. This
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.