Previous Section  < Day Day Up >  Next Section

7.1 The INSERT and REPLACE Statements

The INSERT and REPLACE statements add new records to a table. The two have very similar syntax. The primary difference between them lies in how they handle duplicate records.

7.1.1 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_name1 = value1, column_name2 = value2, ... ;

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 statement shown here 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. 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 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.

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. In this case, the VALUES 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, 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');

  • You can insert multiple records with a single statement by providing several values lists after the VALUES keyword. This is discussed in section 7.1.1.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.

7.1.1.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 a sequence value to that column 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 will not work:







mysql> INSERT INTO t (i) VALUES(1,2,3,4,5);

ERROR 1136: 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 will vary per INSERT statement. They 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 nonzero if the statement includes the IGNORE keyword. The action of this keyword is described in section 7.1.4, "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.

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 4.10.6, "Automatic Type Conversion and Value Clipping."

7.1.2 The REPLACE Statement

If a table contains a unique-valued index and you attempt to insert a record containing a key value that already exists in the index, a duplicate-key violation occurs and the row is not inserted. What if you want the new record to take priority over the existing one? You could remove the existing record with DELETE and then use INSERT to add the new record. However, MySQL provides REPLACE as an alternative that is easier to use and is more efficient because it performs both actions with a single statement. REPLACE is like INSERT except that it deletes old records as necessary when a duplicate unique key value is present in a new record. Suppose that you're inserting a record into the people table, which has id as a PRIMARY KEY:

  • If the new record doesn't duplicate an existing id value, MySQL just inserts it.

  • If the new record does duplicate an existing id value, MySQL deletes the old records first before inserting the new one.

An advantage of using REPLACE instead of an equivalent DELETE (if needed) and INSERT is that REPLACE is performed as a single atomic operation. There's no need to do any explicit table locking as there might be were you to issue separate DELETE and INSERT statements.

For a comparison of REPLACE with UPDATE, see section 7.2, "The UPDATE Statement."

The action of REPLACE in replacing rows with duplicate keys depends on the table having a unique-valued index:

  • In the absence of any such indexes, REPLACE is equivalent to INSERT because no duplicates will ever be detected.

  • Even in the presence of a unique-valued index, if an indexed column allows NULL values, it allows multiple NULL values. A new record with a NULL value in that column does not cause a duplicate-key violation and no replacement occurs.

REPLACE returns an information string indicating how many rows it affected. If the count is one, the row was inserted without replacing an existing row. If the count is two, a row was deleted before the new row was inserted. If the count is greater than two, it means the table has multiple unique-valued indexes and the new record matched key values in multiple rows, resulting in multiple duplicate-key violations. This causes multiple rows to be deleted, a situation that's described in more detail later in this section.

REPLACE statement syntax is similar to that for INSERT. The following are each valid forms of REPLACE. They're analogous to examples shown earlier in the chapter for INSERT:

  • A single-record REPLACE with separate column and value lists:

    




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

  • A multiple-record REPLACE that inserts several rows:

    




    
REPLACE INTO people (id,name,age)

VALUES(12,'William',25),(13,'Bart',15),(14,'Mary',12);

    The rows-affected count for a multiple-row REPLACE often is greater than two because the statement may insert (and delete) several records in a single operation.

  • A single-record REPLACE with a SET clause that lists column assignments:

    




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

If a table contains multiple unique-valued indexes, a new record added with REPLACE might cause duplicate-key violations for multiple existing records. In this case, REPLACE replaces each of those records. The following table has three columns, each of which has a UNIQUE index:







CREATE TABLE multikey

(

    i INT NOT NULL UNIQUE,

    j INT NOT NULL UNIQUE,

    k INT NOT NULL UNIQUE

);

Suppose that the table has these contents:







mysql> SELECT * FROM multikey;

+---+---+---+

| i | j | k |

+---+---+---+

| 1 | 1 | 1 |

| 2 | 2 | 2 |

| 3 | 3 | 3 |

| 4 | 4 | 4 |

+---+---+---+

Using REPLACE to add a record that duplicates a row in each column causes several records to be replaced with the new row:







mysql> REPLACE INTO multikey (i,j,k) VALUES(1,2,3);

Query OK, 4 rows affected (0.00 sec)

mysql> SELECT * FROM multikey;

+---+---+---+

| i | j | k |

+---+---+---+

| 1 | 2 | 3 |

| 4 | 4 | 4 |

+---+---+---+

The REPLACE statement reports a row count of four because it deletes three records and inserts one.

7.1.3 Handling Illegal Values

If you insert an invalid value into a row, MySQL normally attempts to convert it to the closest valid value, rather than generating an error:

  • A numeric value that's out of range for the column type is clipped to the nearest value that's in range. For example, TINYINT has a range from –128 to 127. If you attempt to insert –1000 and 1000 into a TINYINT column, MySQL stores them as –128 and 127. A similar conversion is applied to temporal values: Out-of-range values are converted to the nearest value that's in range.

  • String values that are too long are truncated to fit in the column. If you attempt to store 'Goodbye' into a CHAR(4) column, MySQL stores it as 'Good'.

  • Values that are completely invalid are converted to the default value for the column type: 0 for numeric columns, the empty string for string columns, and the "zero" temporal value for temporal columns.

  • For a single-row INSERT statement, an attempt to insert NULL into a NOT NULL column results in an error. For a multiple-row INSERT, MySQL treats NULL like any other invalid value and converts it to the default value for the column type. In addition, the warning count is incremented.

See section 4.10.6, "Automatic Type Conversion and Value Clipping," for additional discussion about data value conversion.

7.1.4 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.)

  • With INSERT, 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. Clients that terminate on statement errors will abort with INSERT but not with INSERT IGNORE.

  • With REPLACE, MySQL deletes the old record and inserts the new one.

These three behaviors also apply in another context: The LOAD DATA INFILE statement performs bulk insert operations and supports IGNORE and REPLACE modifiers to control how to handle records with duplicate key values. See Chapter 9, "Importing and Exporting Data."

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.

    Previous Section  < Day Day Up >  Next Section