|< Day Day Up >|
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
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);
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;
The VALUES form of INSERT has some variations:
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.
18.104.22.168 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:
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:
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."
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:
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 );
mysql> SELECT * FROM multikey; +---+---+---+ | i | j | k | +---+---+---+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | | 4 | 4 | 4 | +---+---+---+
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 | +---+---+---+
7.1.3 Handling Illegal Values
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:
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.
|< Day Day Up >|