CALL sp_name([parameter[,...]])CALL sp_name[()]

The CALL statement invokes a stored procedure that was defined previously with CREATE PROCEDURE.

Stored procedures that take no arguments can be invoked without parentheses. That is, CALL p() and CALL p are equivalent.

CALL can pass back values to its caller using parameters that are declared as OUT or INOUT parameters. When the procedure returns, a client program can also obtain the number of rows affected for the final statement executed within the routine: At the SQL level, call the ROW_COUNT() function; from the C API, call the mysql_affected_rows() function.

To get back a value from a procedure using an OUT or INOUT parameter, pass the parameter by means of a user variable, and then check the value of the variable after the procedure returns. (If you are calling the procedure from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an IN or INOUT parameter.) For an INOUT parameter, initialize its value before passing it to the procedure. The following procedure has an OUT parameter that the procedure sets to the current server version, and an INOUT value that the procedure increments by one from its current value:

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)BEGIN  # Set value of OUT parameter  SELECT VERSION() INTO ver_param;  # Increment value of INOUT parameter  SET incr_param = incr_param + 1;END;

Before calling the procedure, initialize the variable to be passed as the INOUT parameter. After calling the procedure, the values of the two variables will have been set or modified:

mysql> SET @increment = 10;mysql> CALL p(@version, @increment);mysql> SELECT @version, @increment;+--------------+------------+| @version | @increment |+--------------+------------+| 5.5.3-m3-log | 11 |+--------------+------------+

In prepared CALL statements used with PREPARE and EXECUTE, placeholders can be used for IN parameters. For OUT and INOUT parameters, placeholder support is available as of MySQL 5.5.3. These types of parameters can be used as follows:

mysql> SET @increment = 10;mysql> PREPARE s FROM 'CALL p(?, ?)';mysql> EXECUTE s USING @version, @increment;mysql> SELECT @version, @increment;+--------------+------------+| @version | @increment |+--------------+------------+| 5.5.3-m3-log | 11 |+--------------+------------+

Before MySQL 5.5.3, placeholder support is not available for OUT or INOUT parameters. To work around this limitation for OUT and INOUT parameters, forego the use of placeholders; instead, refer to user variables in the CALL statement itself and do not specify them in the EXECUTE statement:

mysql> SET @increment = 10;mysql> PREPARE s FROM 'CALL p(@version, @increment)';mysql> EXECUTE s;mysql> SELECT @version, @increment;+--------------+------------+| @version | @increment |+--------------+------------+| 5.5.0-m2-log | 11 |+--------------+------------+

To write C programs that use the CALL SQL statement to execute stored procedures that produce result sets, the CLIENT_MULTI_RESULTS flag must be enabled. This is because each CALL returns a result to indicate the call status, in addition to any result sets that might be returned by statements executed within the procedure. CLIENT_MULTI_RESULTS must also be enabled if CALL is used to execute any stored procedure that contains prepared statements. It cannot be determined when such a procedure is loaded whether those statements will produce result sets, so it is necessary to assume that they will.

CLIENT_MULTI_RESULTS can be enabled when you call mysql_real_connect(), either explicitly by passing the CLIENT_MULTI_RESULTS flag itself, or implicitly by passing CLIENT_MULTI_STATEMENTS (which also enables CLIENT_MULTI_RESULTS). As of MySQL 5.5.3, CLIENT_MULTI_RESULTS is enabled by default.

To process the result of a CALL statement executed using mysql_query() or mysql_real_query(), use a loop that calls mysql_next_result() to determine whether there are more results. For an example, see Section 22.8.13, "C API Support for Multiple Statement Execution".

For programs written in a language that provides a MySQL interface, there is no native method prior to MySQL 5.5.3 for directly retrieving the results of OUT or INOUT parameters from CALL statements. To get the parameter values, pass user-defined variables to the procedure in the CALL statement and then execute a SELECT statement to produce a result set containing the variable values. To handle an INOUT parameter, execute a statement prior to the CALL that sets the corresponding user variable to the value to be passed to the procedure.

The following example illustrates the technique (without error checking) for the stored procedure p described earlier that has an OUT parameter and an INOUT parameter:

mysql_query(mysql, "SET @increment = 10");mysql_query(mysql, "CALL p(@version, @increment)");mysql_query(mysql, "SELECT @version, @increment");result = mysql_store_result(mysql);row = mysql_fetch_row(result);mysql_free_result(result);

After the preceding code executes, row[0] and row[1] contain the values of @version and @increment, respectively.

As of MySQL 5.5.3, C programs can use the prepared-statement interface to execute CALL statements and access OUT and INOUT parameters. This is done by processing the result of a CALL statement using a loop that calls mysql_stmt_next_result() to determine whether there are more results. For an example, see Section 22.8.16, "C API Support for Prepared CALL Statements". Languages that provide a MySQL interface can use prepared CALL statements to directly retrieve OUT and INOUT procedure parameters.

Single-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition]

For the single-table syntax, the DELETE statement deletes rows from tbl_name and returns a count of the number of deleted rows. This count can be obtained by calling the ROW_COUNT() function (see Section 12.14, "Information Functions"). The WHERE clause, if given, specifies the conditions that identify which rows to delete. With no WHERE clause, all rows are deleted. If the ORDER BY clause is specified, the rows are deleted in the order that is specified. The LIMIT clause places a limit on the number of rows that can be deleted.

For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

where_condition is an expression that evaluates to true for each row to be deleted. It is specified as described in Section 13.2.9, "SELECT Syntax".

Currently, you cannot delete from a table and select from the same table in a subquery.

You need the DELETE privilege on a table to delete rows from it. You need only the SELECT privilege for any columns that are only read, such as those named in the WHERE clause.

As stated, a DELETE statement with no WHERE clause deletes all rows. A faster way to do this, when you do not need to know the number of deleted rows, is to use TRUNCATE TABLE. However, within a transaction or if you have a lock on the table, TRUNCATE TABLE cannot be used whereas DELETE can. See Section 13.1.33, "TRUNCATE TABLE Syntax", and Section 13.3.5, "LOCK TABLES and UNLOCK TABLES Syntax".

If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value is not reused for a MyISAM or InnoDB table. If you delete all rows in the table with DELETE FROM tbl_name (without a WHERE clause) in autocommit mode, the sequence starts over for all storage engines except InnoDB and MyISAM. There are some exceptions to this behavior for InnoDB tables, as discussed in Section 14.3.5.3, "AUTO_INCREMENT Handling in InnoDB".

For MyISAM tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. In this case, reuse of values deleted from the top of the sequence occurs even for MyISAM tables. See Section 3.6.9, "Using AUTO_INCREMENT".

The DELETE statement supports the following modifiers:

The speed of delete operations may also be affected by factors discussed in Section 8.2.2.3, "Speed of DELETE Statements".

In MyISAM tables, deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. To reclaim unused space and reduce file sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganize tables. OPTIMIZE TABLE is easier to use, but myisamchk is faster. See Section 13.7.2.4, "OPTIMIZE TABLE Syntax", and Section 4.6.3, "myisamchk - MyISAM Table-Maintenance Utility".

The QUICK modifier affects whether index leaves are merged for delete operations. DELETE QUICK is most useful for applications where index values for deleted rows are replaced by similar index values from rows inserted later. In this case, the holes left by deleted values are reused.

DELETE QUICK is not useful when deleted values lead to underfilled index blocks spanning a range of index values for which new inserts occur again. In this case, use of QUICK can lead to wasted space in the index that remains unreclaimed. Here is an example of such a scenario:

In this scenario, the index blocks associated with the deleted index values become underfilled but are not merged with other index blocks due to the use of QUICK. They remain underfilled when new inserts occur, because new rows do not have index values in the deleted range. Furthermore, they remain underfilled even if you later use DELETE without QUICK, unless some of the deleted index values happen to lie in index blocks within or adjacent to the underfilled blocks. To reclaim unused index space under these circumstances, use OPTIMIZE TABLE.

If you are going to delete many rows from a table, it might be faster to use DELETE QUICK followed by OPTIMIZE TABLE. This rebuilds the index rather than performing many index block merge operations.

The MySQL-specific LIMIT row_count option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a given DELETE statement does not take too much time. You can simply repeat the DELETE statement until the number of affected rows is less than the LIMIT value.

If the DELETE statement includes an ORDER BY clause, rows are deleted in the order specified by the clause. This is useful primarily in conjunction with LIMIT. For example, the following statement finds rows matching the WHERE clause, sorts them by timestamp_column, and deletes the first (oldest) one:

DELETE FROM somelog WHERE user = 'jcole'ORDER BY timestamp_column LIMIT 1;

ORDER BY may also be useful in some cases to delete rows in an order required to avoid referential integrity violations.

If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:

No other sessions can access the tables involved while RENAME TABLE executes, so the rename operation is not subject to concurrency problems. See Section 13.1.32, "RENAME TABLE Syntax".

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the particular condition in the WHERE clause. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 13.2.9.2, "JOIN Syntax".

For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3WHERE t1.id=t2.id AND t2.id=t3.id;

Or:

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3WHERE t1.id=t2.id AND t2.id=t3.id;

These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.

The preceding examples use INNER JOIN, but multiple-table DELETE statements can use other types of join permitted in SELECT statements, such as LEFT JOIN. For example, to delete rows that exist in t1 that have no match in t2, use a LEFT JOIN:

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

The syntax permits .* after each tbl_name for compatibility with Access.

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

DELETE t1 FROM test AS t1, test2 WHERE ...

Table aliases in a multiple-table DELETE should be declared only in the table_references part of the statement.

Correct:

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2WHERE a1.id=a2.id;DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2WHERE a1.id=a2.id;

Incorrect:

DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2WHERE a1.id=a2.id;DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2WHERE a1.id=a2.id;

Declaration of aliases other than in the table_references part of the statement should be avoided because that can lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table. This is such a statement:

DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2;

Before MySQL 5.5.3, alias declarations outside the table_references part of the statement are disallowed for the USING variant of multiple-table DELETE syntax. As of MySQL 5.5.3, alias declarations outside table_references are disallowed for all multiple-table DELETE statements.

Before MySQL 5.5.3, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1:

DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2WHERE a1.id=a2.id;

To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database:

DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2WHERE a1.id=a2.id;

As of MySQL 5.5.3, alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases.

DO expr [, expr] ...

DO executes the expressions but does not return any results. In most respects, DO is shorthand for SELECT expr, ..., but has the advantage that it is slightly faster when you do not care about the result.

DO is useful primarily with functions that have side effects, such as RELEASE_LOCK().

HANDLER tbl_name OPEN [ [AS] alias]HANDLER tbl_name READ index_name { = | <= | >= | < | > } (value1,value2,...) [ WHERE where_condition ] [LIMIT ... ]HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST } [ WHERE where_condition ] [LIMIT ... ]HANDLER tbl_name READ { FIRST | NEXT } [ WHERE where_condition ] [LIMIT ... ]HANDLER tbl_name CLOSE

The HANDLER statement provides direct access to table storage engine interfaces. It is available for InnoDB and MyISAM tables.

The HANDLER ... OPEN statement opens a table, making it accessible using subsequent HANDLER ... READ statements. This table object is not shared by other sessions and is not closed until the session calls HANDLER ... CLOSE or the session terminates. If you open the table using an alias, further references to the open table with other HANDLER statements must use the alias rather than the table name.

The first HANDLER ... READ syntax fetches a row where the index specified satisfies the given values and the WHERE condition is met. If you have a multiple-column index, specify the index column values as a comma-separated list. Either specify values for all the columns in the index, or specify values for a leftmost prefix of the index columns. Suppose that an index my_idx includes three columns named col_a, col_b, and col_c, in that order. The HANDLER statement can specify values for all three columns in the index, or for the columns in a leftmost prefix. For example:

HANDLER ... READ my_idx = (col_a_val,col_b_val,col_c_val) ...HANDLER ... READ my_idx = (col_a_val,col_b_val) ...HANDLER ... READ my_idx = (col_a_val) ...

To employ the HANDLER interface to refer to a table's PRIMARY KEY, use the quoted identifier `PRIMARY`:

HANDLER tbl_name READ `PRIMARY` ...

The second HANDLER ... READ syntax fetches a row from the table in index order that matches the WHERE condition.

The third HANDLER ... READ syntax fetches a row from the table in natural row order that matches the WHERE condition. It is faster than HANDLER tbl_name READ index_name when a full table scan is desired. Natural row order is the order in which rows are stored in a MyISAM table data file. This statement works for InnoDB tables as well, but there is no such concept because there is no separate data file.

Without a LIMIT clause, all forms of HANDLER ... READ fetch a single row if one is available. To return a specific number of rows, include a LIMIT clause. It has the same syntax as for the SELECT statement. See Section 13.2.9, "SELECT Syntax".

HANDLER ... CLOSE closes a table that was opened with HANDLER ... OPEN.

There are several reasons to use the HANDLER interface instead of normal SELECT statements:

HANDLER is a somewhat low-level statement. For example, it does not provide consistency. That is, HANDLER ... OPEN does not take a snapshot of the table, and does not lock the table. This means that after a HANDLER ... OPEN statement is issued, table data can be modified (by the current session or other sessions) and these modifications might be only partially visible to HANDLER ... NEXT or HANDLER ... PREV scans.

An open handler can be closed and marked for reopen, in which case the handler loses its position in the table. This occurs when both of the following circumstances are true:

TRUNCATE TABLE for a table closes all handlers for the table that were opened with HANDLER OPEN.

If a table is flushed with FLUSH TABLES tbl_name WITH READ LOCK was opened with HANDLER, the handler is implicitly flushed and loses its position.

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE  col_name=expr [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE  col_name=expr [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE  col_name=expr [, col_name=expr] ... ]

INSERT inserts new rows into an existing table. The INSERT ... VALUES and INSERT ... SET forms of the statement insert rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. INSERT ... SELECT is discussed further in Section 13.2.5.1, "INSERT ... SELECT Syntax".

You can use REPLACE instead of INSERT to overwrite old rows. REPLACE is the counterpart to INSERT IGNORE in the treatment of new rows that contain unique key values that duplicate old rows: The new rows are used to replace the old rows rather than being discarded. See Section 13.2.8, "REPLACE Syntax".

tbl_name is the table into which rows should be inserted. The columns for which the statement provides values can be specified as follows:

Column values can be given in several ways:

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

The values list for each row must be enclosed within parentheses. The following statement is illegal because the number of values in the list does not match the number of column names:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);

VALUE is a synonym for VALUES in this context. Neither implies anything about the number of values lists, and either may be used whether there is a single values list or multiple lists.

The affected-rows value for an INSERT can be obtained using the ROW_COUNT() function (see Section 12.14, "Information Functions"), or the mysql_affected_rows() C API function (see Section 22.8.3.1, "mysql_affected_rows()").

If you use an INSERT ... VALUES statement with multiple value lists or INSERT ... SELECT, the statement returns an information string in this format:

Records: 100 Duplicates: 0 Warnings: 0

Records indicates the number of rows processed by the statement. (This is not necessarily the number of rows actually inserted because Duplicates can be nonzero.) Duplicates indicates the number of rows that could not be inserted because they would duplicate some existing unique index value. Warnings indicates the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions:

If you are using the C API, the information string can be obtained by invoking the mysql_info() function. See Section 22.8.3.35, "mysql_info()".

If INSERT inserts a row into a table that has an AUTO_INCREMENT column, you can find the value used for that column by using the SQL LAST_INSERT_ID() function. From within the C API, use the mysql_insert_id() function. However, you should note that the two functions do not always behave identically. The behavior of INSERT statements with respect to AUTO_INCREMENT columns is discussed further in Section 12.14, "Information Functions", and Section 22.8.3.37, "mysql_insert_id()".

The INSERT statement supports the following modifiers:

Inserting into a table requires the INSERT privilege for the table. If the ON DUPLICATE KEY UPDATE clause is used and a duplicate key causes an UPDATE to be performed instead, the statement requires the UPDATE privilege for the columns to be updated. For columns that are read but not modified you need only the SELECT privilege (such as for a column referenced only on the right hand side of an col_name=expr assignment in an ON DUPLICATE KEY UPDATE clause).

An INSERTstatement that acts on a partitioned table using a storage engine such as MyISAM that employs table-level locks locks all partitions of the table. This does not occur with tables using storage engines such as InnoDB that employ row-level locking. This issue is resolved in MySQL 5.6. See Section 18.5.4, "Partitioning and Table-Level Locking", for more information.

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

INSERT INTO tbl_temp2 (fld_id)  SELECT tbl_temp1.fld_order_id  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

INSERT DELAYED ...

INSERT INTO table (a,b,c) VALUES (1,2,3)  ON DUPLICATE KEY UPDATE c=c+1;UPDATE table SET c=c+1 WHERE a=1;

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

INSERT INTO table (a,b,c) VALUES (1,2,3)  ON DUPLICATE KEY UPDATE c=3;INSERT INTO table (a,b,c) VALUES (4,5,6)  ON DUPLICATE KEY UPDATE c=9;

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [CHARACTER SET charset_name] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number {LINES | ROWS}] [(col_name_or_user_var,...)] [SET col_name = expr,...]

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.

LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. (See Section 13.2.9.1, "SELECT ... INTO Syntax".) To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA INFILE. The syntax of the FIELDS and LINES clauses is the same for both statements. Both clauses are optional, but FIELDS must precede LINES if both are specified.

For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, see Section 8.2.2.1, "Speed of INSERT Statements".

The character set indicated by the character_set_database system variable is used to interpret the information in the file. SET NAMES and the setting of character_set_client do not affect interpretation of input. If the contents of the input file use a character set that differs from the default, it is usually preferable to specify the character set of the file by using the CHARACTER SET clause. A character set of binary specifies "no conversion."

LOAD DATA INFILE interprets all fields in the file as having the same character set, regardless of the data types of the columns into which field values are loaded. For proper interpretation of file contents, you must ensure that it was written with the correct character set. For example, if you write a data file with mysqldump -T or by issuing a SELECT ... INTO OUTFILE statement in mysql, be sure to use a --default-character-set option with mysqldump or mysql so that output is written in the character set to be used when the file is loaded with LOAD DATA INFILE.

The character_set_filesystem system variable controls the interpretation of the file name.

You can also load data files by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE statement to the server. The --local option causes mysqlimport to read data files from the client host. You can specify the --compress option to get better performance over slow networks if the client and server support the compressed protocol. See Section 4.5.5, "mysqlimport - A Data Import Program".

If you use LOW_PRIORITY, execution of the LOAD DATA statement is delayed until no other clients are reading from the table. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).

If you specify CONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other threads can retrieve data from the table while LOAD DATA is executing. Using this option affects the performance of LOAD DATA a bit, even if no other thread is using the table at the same time.

Prior to MySQL 5.5.1, CONCURRENT was not replicated when using statement-based replication (see Bug #34628). However, it is replicated when using row-based replication, regardless of the version. See Section 16.4.1.15, "Replication and LOAD DATA INFILE", for more information.

The LOCAL keyword, if specified, is interpreted with respect to the client end of the connection:

Note that, in the non-LOCAL case, these rules mean that a file named as ./myfile.txt is read from the server's data directory, whereas the file named as myfile.txt is read from the database directory of the default database. For example, if db1 is the default database, the following LOAD DATA statement reads the file data.txt from the database directory for db1, even though the statement explicitly loads the file into a table in the db2 database:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Windows path names are specified using forward slashes rather than backslashes. If you do use backslashes, you must double them.

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See Section 6.2.1, "Privileges Provided by MySQL". For non-LOCAL load operations, if the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

Using LOCAL is a bit slower than letting the server access the files directly, because the contents of the file must be sent over the connection by the client to the server. On the other hand, you do not need the FILE privilege to load local files.

With LOCAL, the default duplicate-key handling behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation. IGNORE is explained further later in this section.

LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with --local-infile=0, LOCAL does not work. See Section 6.1.6, "Security Issues with LOAD DATA LOCAL".

On Unix, if you need LOAD DATA to read from a pipe, you can use the following technique (the example loads a listing of the / directory into the table db1.t1):

mkfifo /mysql/data/db1/ls.datchmod 666 /mysql/data/db1/ls.datfind / -ls > /mysql/data/db1/ls.dat &mysql -e "LOAD DATA INFILE 'ls.dat' INTO TABLE t1" db1

Note that you must run the command that generates the data to be loaded and the mysql commands either on separate terminals, or run the data generation process in the background (as shown in the preceding example). If you do not do this, the pipe will block until data is read by the mysql process.

The REPLACE and IGNORE keywords control handling of input rows that duplicate existing rows on unique key values:

If you want to ignore foreign key constraints during the load operation, you can issue a SET foreign_key_checks = 0 statement before executing LOAD DATA.

If you use LOAD DATA INFILE on an empty MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE). Normally, this makes LOAD DATA INFILE much faster when you have many indexes. In some extreme cases, you can create the indexes even faster by turning them off with ALTER TABLE ... DISABLE KEYS before loading the file into the table and using ALTER TABLE ... ENABLE KEYS to re-create the indexes after loading the file. See Section 8.2.2.1, "Speed of INSERT Statements".

For both the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements, the syntax of the FIELDS and LINES clauses is the same. Both clauses are optional, but FIELDS must precede LINES if both are specified.

If you specify a FIELDS clause, each of its subclauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY) is also optional, except that you must specify at least one of them.

If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'LINES TERMINATED BY '\n' STARTING BY ''

(Backslash is the MySQL escape character within strings in SQL statements, so to specify a literal backslash, you must specify two backslashes for the value to be interpreted as a single backslash. The escape sequences '\t' and '\n' specify tab and newline characters, respectively.)

In other words, the defaults cause LOAD DATA INFILE to act as follows when reading input:

Conversely, the defaults cause SELECT ... INTO OUTFILE to act as follows when writing output:

If all the lines you want to read in have a common prefix that you want to ignore, you can use LINES STARTING BY 'prefix_string' to skip over the prefix, and anything before it. If a line does not include the prefix, the entire line is skipped. Suppose that you issue the following statement:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test  FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

If the data file looks like this:

xxx"abc",1something xxx"def",2"ghi",3

The resulting rows will be ("abc",1) and ("def",2). The third row in the file is skipped because it does not contain the prefix.

The IGNORE number LINES option can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip over an initial header line containing column names:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

When you use SELECT ... INTO OUTFILE in tandem with LOAD DATA INFILE to write data from a database into a file and then read the file back into the database later, the field- and line-handling options for both statements must match. Otherwise, LOAD DATA INFILE will not interpret the contents of the file properly. Suppose that you use SELECT ... INTO OUTFILE to write a file with fields delimited by commas:

SELECT * INTO OUTFILE 'data.txt'  FIELDS TERMINATED BY ','  FROM table2;

To read the comma-delimited file back in, the correct statement would be:

LOAD DATA INFILE 'data.txt' INTO TABLE table2  FIELDS TERMINATED BY ',';

If instead you tried to read in the file with the statement shown following, it wouldn't work because it instructs LOAD DATA INFILE to look for tabs between fields:

LOAD DATA INFILE 'data.txt' INTO TABLE table2  FIELDS TERMINATED BY '\t';

The likely result is that each input line would be interpreted as a single field.

LOAD DATA INFILE can be used to read files obtained from external sources. For example, many programs can export data in comma-separated values (CSV) format, such that lines have fields separated by commas and enclosed within double quotation marks, with an initial line of column names. If the lines in such a file are terminated by carriage return/newline pairs, the statement shown here illustrates the field- and line-handling options you would use to load the file:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name  FIELDS TERMINATED BY ',' ENCLOSED BY '"'  LINES TERMINATED BY '\r\n'  IGNORE 1 LINES;

If the input values are not necessarily enclosed within quotation marks, use OPTIONALLY before the ENCLOSED BY keywords.

Any of the field- or line-handling options can specify an empty string (''). If not empty, the FIELDS [OPTIONALLY] ENCLOSED BY and FIELDS ESCAPED BY values must be a single character. The FIELDS TERMINATED BY, LINES STARTING BY, and LINES TERMINATED BY values can be more than one character. For example, to write lines that are terminated by carriage return/linefeed pairs, or to read a file containing such lines, specify a LINES TERMINATED BY '\r\n' clause.

To read a file containing jokes that are separated by lines consisting of %%, you can do this

CREATE TABLE jokes  (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  joke TEXT NOT NULL);LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes  FIELDS TERMINATED BY ''  LINES TERMINATED BY '\n%%\n' (joke);

FIELDS [OPTIONALLY] ENCLOSED BY controls quoting of fields. For output (SELECT ... INTO OUTFILE), if you omit the word OPTIONALLY, all fields are enclosed by the ENCLOSED BY character. An example of such output (using a comma as the field delimiter) is shown here:

"1","a string","100.20""2","a string containing a , comma","102.20""3","a string containing a \" quote","102.20""4","a string containing a \", quote and comma","102.20"

If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose values from columns that have a string data type (such as CHAR, BINARY, TEXT, or ENUM):

1,"a string",100.202,"a string containing a , comma",102.203,"a string containing a \" quote",102.204,"a string containing a \", quote and comma",102.20

Note that occurrences of the ENCLOSED BY character within a field value are escaped by prefixing them with the ESCAPED BY character. Also note that if you specify an empty ESCAPED BY value, it is possible to inadvertently generate output that cannot be read properly by LOAD DATA INFILE. For example, the preceding output just shown would appear as follows if the escape character is empty. Observe that the second field in the fourth line contains a comma following the quote, which (erroneously) appears to terminate the field:

1,"a string",100.202,"a string containing a , comma",102.203,"a string containing a " quote",102.204,"a string containing a ", quote and comma",102.20

For input, the ENCLOSED BY character, if present, is stripped from the ends of field values. (This is true regardless of whether OPTIONALLY is specified; OPTIONALLY has no effect on input interpretation.) Occurrences of the ENCLOSED BY character preceded by the ESCAPED BY character are interpreted as part of the current field value.

If the field begins with the ENCLOSED BY character, instances of that character are recognized as terminating a field value only if followed by the field or line TERMINATED BY sequence. To avoid ambiguity, occurrences of the ENCLOSED BY character within a field value can be doubled and are interpreted as a single instance of the character. For example, if ENCLOSED BY '"' is specified, quotation marks are handled as shown here:

"The ""BIG"" boss"  -> The "BIG" bossThe "BIG" boss  -> The "BIG" bossThe ""BIG"" boss -> The ""BIG"" boss

FIELDS ESCAPED BY controls how to read or write special characters:

In certain cases, field- and line-handling options interact:

Handling of NULL values varies according to the FIELDS and LINES options in use:

An attempt to load NULL into a NOT NULL column causes assignment of the implicit default value for the column's data type and a warning, or an error in strict SQL mode. Implicit default values are discussed in Section 11.5, "Data Type Default Values".

Some cases are not supported by LOAD DATA INFILE:

The following example loads all columns of the persondata table:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.

The column list can contain either column names or user variables. With user variables, the SET clause enables you to perform transformations on their values before assigning the result to columns.

User variables in the SET clause can be used in several ways. The following example uses the first input column directly for the value of t1.column1, and assigns the second input column to a user variable that is subjected to a division operation before being used for the value of t1.column2:

LOAD DATA INFILE 'file.txt'  INTO TABLE t1  (column1, @var1)  SET column2 = @var1/100;

The SET clause can be used to supply values not derived from the input file. The following statement sets column3 to the current date and time:

LOAD DATA INFILE 'file.txt'  INTO TABLE t1  (column1, column2)  SET column3 = CURRENT_TIMESTAMP;

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

LOAD DATA INFILE 'file.txt'  INTO TABLE t1  (column1, @dummy, column2, @dummy, column3);

Use of the column/variable list and SET clause is subject to the following restrictions:

When processing an input line, LOAD DATA splits it into fields and uses the values according to the column/variable list and the SET clause, if they are present. Then the resulting row is inserted into the table. If there are BEFORE INSERT or AFTER INSERT triggers for the table, they are activated before or after inserting the row, respectively.

If an input line has too many fields, the extra fields are ignored and the number of warnings is incremented.

If an input line has too few fields, the table columns for which input fields are missing are set to their default values. Default value assignment is described in Section 11.5, "Data Type Default Values".

An empty field value is interpreted differently than if the field value is missing:

These are the same values that result if you assign an empty string explicitly to a string, numeric, or date or time type explicitly in an INSERT or UPDATE statement.

TIMESTAMP columns are set to the current date and time only if there is a NULL value for the column (that is, \N) and the column is not declared to permit NULL values, or if the TIMESTAMP column's default value is the current timestamp and it is omitted from the field list when a field list is specified.

LOAD DATA INFILE regards all input as strings, so you cannot use numeric values for ENUM or SET columns the way you can with INSERT statements. All ENUM and SET values must be specified as strings.

BIT values cannot be loaded using binary notation (for example, b'011010'). To work around this, specify the values as regular integers and use the SET clause to convert them so that MySQL performs a numeric type conversion and loads them into the BIT column properly:

shell> cat /tmp/bit_test.txt2127shell> mysql testmysql> LOAD DATA INFILE '/tmp/bit_test.txt' -> INTO TABLE bit_test (@var1) SET b= CAST(@var1 AS UNSIGNED);Query OK, 2 rows affected (0.00 sec)Records: 2  Deleted: 0  Skipped: 0  Warnings: 0mysql> SELECT BIN(b+0) FROM bit_test;+----------+| bin(b+0) |+----------+| 10   || 1111111  |+----------+2 rows in set (0.00 sec)

When the LOAD DATA INFILE statement finishes, it returns an information string in the following format:

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

If you are using the C API, you can get information about the statement by calling the mysql_info() function. See Section 22.8.3.35, "mysql_info()".

Warnings occur under the same circumstances as when values are inserted using the INSERT statement (see Section 13.2.5, "INSERT Syntax"), except that LOAD DATA INFILE also generates warnings when there are too few or too many fields in the input row. The warnings are not stored anywhere; the number of warnings can be used only as an indication of whether everything went well.

You can use SHOW WARNINGS to get a list of the first max_error_count warnings as information about what went wrong. See Section 13.7.5.41, "SHOW WARNINGS Syntax".

For partitioned tables using storage engines that employ table locks, such as MyISAM, any locks caused by LOAD DATA perform locks on all partitions of the table. This does not apply to tables using storage engines which employ row-level locking, such as InnoDB. For more information, see Section 18.5.4, "Partitioning and Table-Level Locking".

LOAD XML [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE [db_name.]tbl_name [CHARACTER SET charset_name] [ROWS IDENTIFIED BY '<tagname>'] [IGNORE number {LINES | ROWS}] [(column_or_user_var,...)] [SET col_name = expr,...]

The LOAD XML statement reads data from an XML file into a table. The file_name must be given as a literal string. The tagname in the optional ROWS IDENTIFIED BY clause must also be given as a literal string, and must be surrounded by angle brackets (< and >).

LOAD XML acts as the complement of running the mysql client in XML output mode (that is, starting the client with the --xml option). To write data from a table to an XML file, use a command such as the following one from the system shell:

shell> mysql --xml -e 'SELECT * FROM mytable' > file.xml

To read the file back into a table, use LOAD XML INFILE. By default, the <row> element is considered to be the equivalent of a database table row; this can be changed using the ROWS IDENTIFIED BY clause.

This statement supports three different XML formats:

All 3 formats can be used in the same XML file; the import routine automatically detects the format for each row and interprets it correctly. Tags are matched based on the tag or attribute name and the column name.

The following clauses work essentially the same way for LOAD XML as they do for LOAD DATA:

See Section 13.2.6, "LOAD DATA INFILE Syntax", for more information about these clauses.

The IGNORE number LINES or IGNORE number ROWS clause causes the first number rows in the XML file to be skipped. It is analogous to the LOAD DATA statement's IGNORE ... LINES clause.

To illustrate how this statement is used, suppose that we have a table created as follows:

USE test;CREATE TABLE person ( person_id INT NOT NULL PRIMARY KEY, fname VARCHAR(40) NULL, lname VARCHAR(40) NULL, created TIMESTAMP);

Suppose further that this table is initially empty.

Now suppose that we have a simple XML file person.xml, whose contents are as shown here:

<?xml version="1.0"?><list>  <person person_id="1" fname="Pekka" lname="Nousiainen"/>  <person person_id="2" fname="Jonas" lname="Oreland"/>  <person person_id="3"><fname>Mikael</fname><lname>Ronstr�m</lname></person>  <person person_id="4"><fname>Lars</fname><lname>Thalmann</lname></person>  <person><field name="person_id">5</field><field name="fname">Tomas</field>  <field name="lname">Ulin</field></person>  <person><field name="person_id">6</field><field name="fname">Martin</field>  <field name="lname">Sk�ld</field></person></list>

Each of the permissible XML formats discussed previously is represented in this example file.

To import the data in person.xml into the person table, you can use this statement:

mysql> LOAD XML LOCAL INFILE 'person.xml' ->   INTO TABLE person ->   ROWS IDENTIFIED BY '<person>';Query OK, 6 rows affected (0.00 sec)Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

Here, we assume that person.xml is located in the MySQL data directory. If the file cannot be found, the following error results:

ERROR 2 (HY000): File '/person.xml' not found (Errcode: 2)

The ROWS IDENTIFIED BY '<person>' clause means that each <person> element in the XML file is considered equivalent to a row in the table into which the data is to be imported. In this case, this is the person table in the test database.

As can be seen by the response from the server, 6 rows were imported into the test.person table. This can be verified by a simple SELECT statement:

mysql> SELECT * FROM person;+-----------+--------+------------+---------------------+| person_id | fname  | lname  | created |+-----------+--------+------------+---------------------+| 1 | Pekka  | Nousiainen | 2007-07-13 16:18:47 || 2 | Jonas  | Oreland | 2007-07-13 16:18:47 || 3 | Mikael | Ronstr�m   | 2007-07-13 16:18:47 || 4 | Lars   | Thalmann   | 2007-07-13 16:18:47 || 5 | Tomas  | Ulin   | 2007-07-13 16:18:47 || 6 | Martin | Sk�ld  | 2007-07-13 16:18:47 |+-----------+--------+------------+---------------------+6 rows in set (0.00 sec)

This shows, as stated earlier in this section, that any or all of the 3 permitted XML formats may appear in a single file and be read in using LOAD XML.

The inverse of the above operation-that is, dumping MySQL table data into an XML file-can be accomplished using the mysql client from the system shell, as shown here:

shell> mysql --xml -e "SELECT * FROM test.person" > person-dump.xmlshell> cat person-dump.xml<?xml version="1.0"?><resultset statement="SELECT * FROM test.person" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  <row> <field name="person_id">1</field> <field name="fname">Pekka</field> <field name="lname">Nousiainen</field> <field name="created">2007-07-13 16:18:47</field>  </row>  <row> <field name="person_id">2</field> <field name="fname">Jonas</field> <field name="lname">Oreland</field> <field name="created">2007-07-13 16:18:47</field>  </row>  <row> <field name="person_id">3</field> <field name="fname">Mikael</field> <field name="lname">Ronstr�m</field> <field name="created">2007-07-13 16:18:47</field>  </row>  <row> <field name="person_id">4</field> <field name="fname">Lars</field> <field name="lname">Thalmann</field> <field name="created">2007-07-13 16:18:47</field>  </row>  <row> <field name="person_id">5</field> <field name="fname">Tomas</field> <field name="lname">Ulin</field> <field name="created">2007-07-13 16:18:47</field>  </row>  <row> <field name="person_id">6</field> <field name="fname">Martin</field> <field name="lname">Sk�ld</field> <field name="created">2007-07-13 16:18:47</field>  </row></resultset>

You can verify that the dump is valid by creating a copy of the person and then importing the dump file into the new table, like this:

mysql> USE test;mysql> CREATE TABLE person2 LIKE person;Query OK, 0 rows affected (0.00 sec)mysql> LOAD XML LOCAL INFILE 'person-dump.xml' ->   INTO TABLE person2;Query OK, 6 rows affected (0.01 sec)Records: 6  Deleted: 0  Skipped: 0  Warnings: 0mysql> SELECT * FROM person2;+-----------+--------+------------+---------------------+| person_id | fname  | lname  | created |+-----------+--------+------------+---------------------+| 1 | Pekka  | Nousiainen | 2007-07-13 16:18:47 || 2 | Jonas  | Oreland | 2007-07-13 16:18:47 || 3 | Mikael | Ronstr�m   | 2007-07-13 16:18:47 || 4 | Lars   | Thalmann   | 2007-07-13 16:18:47 || 5 | Tomas  | Ulin   | 2007-07-13 16:18:47 || 6 | Martin | Sk�ld  | 2007-07-13 16:18:47 |+-----------+--------+------------+---------------------+6 rows in set (0.00 sec)

Using a ROWS IDENTIFIED BY '<tagname>' clause, it is possible to import data from the same XML file into database tables with different definitions. For this example, suppose that you have a file named address.xml which contains the following XML:

<?xml version="1.0"?><list>  <person person_id="1"> <fname>Robert</fname> <lname>Jones</lname> <address address_id="1" street="Mill Creek Road" zip="45365" city="Sidney"/> <address address_id="2" street="Main Street" zip="28681" city="Taylorsville"/>  </person>  <person person_id="2"> <fname>Mary</fname> <lname>Smith</lname> <address address_id="3" street="River Road" zip="80239" city="Denver"/> <!-- <address address_id="4" street="North Street" zip="37920" city="Knoxville"/> -->  </person></list>

You can again use the test.person table as defined previously in this section, after clearing all the existing records from the table and then showing its structure as shown here:

mysql< TRUNCATE person;Query OK, 0 rows affected (0.04 sec)mysql< SHOW CREATE TABLE person\G*************************** 1. row ***************************   Table: personCreate Table: CREATE TABLE `person` (  `person_id` int(11) NOT NULL,  `fname` varchar(40) DEFAULT NULL,  `lname` varchar(40) DEFAULT NULL,  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON   UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`person_id`)) ENGINE=MyISAM DEFAULT CHARSET=latin11 row in set (0.00 sec)

Now create an address table in the test database using the following CREATE TABLE statement:

CREATE TABLE address ( address_id INT NOT NULL PRIMARY KEY, person_id INT NULL, street VARCHAR(40) NULL, zip INT NULL, city VARCHAR(40) NULL, created TIMESTAMP);

To import the data from the XML file into the person table, execute the following LOAD XML statement, which specifies that rows are to be specified by the <person> element, as shown here;

mysql> LOAD XML LOCAL INFILE 'address.xml' ->   INTO TABLE person ->   ROWS IDENTIFIED BY '<person>';Query OK, 2 rows affected (0.00 sec)Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

You can verify that the records were imported using a SELECT statement:

mysql> SELECT * FROM person;+-----------+--------+-------+---------------------+| person_id | fname  | lname | created |+-----------+--------+-------+---------------------+| 1 | Robert | Jones | 2007-07-24 17:37:06 || 2 | Mary   | Smith | 2007-07-24 17:37:06 |+-----------+--------+-------+---------------------+2 rows in set (0.00 sec)

Since the <address> elements in the XML file have no corresponding columns in the person table, they are skipped.

To import the data from the <address> elements into the address table, use the LOAD XML statement shown here:

mysql> LOAD XML LOCAL INFILE 'address.xml' ->   INTO TABLE address ->   ROWS IDENTIFIED BY '<address>';Query OK, 3 rows affected (0.00 sec)Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

You can see that the data was imported using a SELECT statement such as this one:

mysql> SELECT * FROM address;+----------+---------+-----------------+-----+--------------+---------------------+|address_id|person_id| street  |zip  | city | created |+----------+---------+-----------------+-----+--------------+---------------------+| 1 |   1 | Mill Creek Road |45365| Sidney   | 2007-07-24 17:37:37 || 2 |   1 | Main Street |28681| Taylorsville | 2007-07-24 17:37:37 || 3 |   2 | River Road  |80239| Denver   | 2007-07-24 17:37:37 |+----------+---------+-----------------+-----+--------------+---------------------+3 rows in set (0.00 sec)

The data from the <address> element that is enclosed in XML comments is not imported. However, since there is a person_id column in the address table, the value of the person_id attribute from the parent <person> element for each <address> is imported into the address table.

Security Considerations. As with the LOAD DATA statement, the transfer of the XML file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server's choosing rather than the file named by the client in the LOAD XML statement. Such a server could access any file on the client host to which the client user has read access.

In a Web environment, clients usually connect to MySQL from a Web server. A user that can run any command against the MySQL server can use LOAD XML LOCAL to read any files to which the Web server process has read access. In this environment, the client with respect to the MySQL server is actually the Web server, not the remote program being run by the user who connects to the Web server.

You can disable loading of XML files from clients by starting the server with --local-infile=0 or --local-infile=OFF. This option can also be used when starting the mysql client to disable LOAD XML for the duration of the client session.

To prevent a client from loading XML files from the server, do not grant the FILE privilege to the corresponding MySQL user account, or revoke this privilege if the client user account already has it.

For partitioned tables using storage engines that employ table locks, such as MyISAM, any locks caused by LOAD XML perform locks on all partitions of the table. This does not apply to tables using storage engines which employ row-level locking, such as InnoDB. For more information, see Section 18.5.4, "Partitioning and Table-Level Locking".

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

Or:

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name={expr | DEFAULT}, ...

Or:

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] SELECT ...

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.5, "INSERT Syntax".

REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL-that either inserts or updates-see Section 13.2.5.3, "INSERT ... ON DUPLICATE KEY UPDATE Syntax".

Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.

Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT. You cannot refer to values from the current row and use them in the new row. If you use an assignment such as SET col_name = col_name + 1, the reference to the column name on the right hand side is treated as DEFAULT(col_name), so the assignment is equivalent to SET col_name = DEFAULT(col_name) + 1.

To use REPLACE, you must have both the INSERT and DELETE privileges for the table.

The REPLACE statement returns a count to indicate the number of rows affected. This is the sum of the rows deleted and inserted. If the count is 1 for a single-row REPLACE, a row was inserted and no rows were deleted. If the count is greater than 1, one or more old rows were deleted before the new row was inserted. It is possible for a single row to replace more than one old row if the table contains multiple unique indexes and the new row duplicates values for different old rows in different unique indexes.

The affected-rows count makes it easy to determine whether REPLACE only added a row or whether it also replaced any rows: Check whether the count is 1 (added) or greater (replaced).

If you are using the C API, the affected-rows count can be obtained using the mysql_affected_rows() function.

Currently, you cannot replace into a table and select from the same table in a subquery.

MySQL uses the following algorithm for REPLACE (and LOAD DATA ... REPLACE):

It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables.

Because the results of REPLACE ... SELECT statements depend on the ordering of rows from the SELECT and this order cannot always be guaranteed, it is possible when logging these statements for the master and the slave to diverge. For this reason, in MySQL 5.5.18 and later, REPLACE ... SELECT statements are flagged as unsafe for statement-based replication. With this change, such statements produce a warning in the log when using the STATEMENT binary logging mode, and are logged using the row-based format when using MIXED mode. See also Section 16.1.2.1, "Advantages and Disadvantages of Statement-Based and Row-Based Replication".

A REPLACEstatement that acts on a partitioned table using a storage engine such as MyISAM that employs table-level locks locks all partitions of the table. This does not occur with tables using storage engines such as InnoDB that employ row-level locking. This issue is resolved in MySQL 5.6. See Section 18.5.4, "Partitioning and Table-Level Locking", for more information.

SELECT [ALL | DISTINCT | DISTINCTROW ]  [HIGH_PRIORITY]  [STRAIGHT_JOIN]  [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]  [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position}  [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position}  [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options  | INTO DUMPFILE 'file_name'  | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables, and can include UNION statements and subqueries. See Section 13.2.9.4, "UNION Syntax", and Section 13.2.10, "Subquery Syntax".

The most commonly used clauses of SELECT statements are these:

SELECT can also be used to retrieve rows computed without reference to any table.

For example:

mysql> SELECT 1 + 1; -> 2

You are permitted to specify DUAL as a dummy table name in situations where no tables are referenced:

mysql> SELECT 1 + 1 FROM DUAL; -> 2

DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced.

In general, clauses used must be given in exactly the order shown in the syntax description. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. The exception is that the INTO clause can appear either as shown in the syntax description or immediately following the select_expr list. For more information about INTO, see Section 13.2.9.1, "SELECT ... INTO Syntax".

The list of select_expr terms comprises the select list that indicates which columns to retrieve. Terms specify a column or expression or can use *-shorthand:

The following list provides additional information about other SELECT clauses:

Following the SELECT keyword, you can use a number of options that affect the operation of the statement. HIGH_PRIORITY, STRAIGHT_JOIN, and options beginning with SQL_ are MySQL extensions to standard SQL.

A SELECT from a partitioned table using a storage engine such as MyISAM that employs table-level locks locks all partitions of the table. This does not occur with tables using storage engines such as InnoDB that employ row-level locking. This issue is resolved in MySQL 5.6. See Section 18.5.4, "Partitioning and Table-Level Locking", for more information.

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  LINES TERMINATED BY '\n'  FROM test_table;

SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;

table_references: table_reference [, table_reference] ...table_reference: table_factor  | join_tabletable_factor: tbl_name [[AS] alias] [index_hint_list]  | table_subquery [AS] alias  | ( table_references )  | { OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr }join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]  | table_reference STRAIGHT_JOIN table_factor  | table_reference STRAIGHT_JOIN table_factor ON conditional_expr  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factorjoin_condition: ON conditional_expr  | USING (column_list)index_hint_list: index_hint [, index_hint] ...index_hint: USE {INDEX|KEY}  [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])  | IGNORE {INDEX|KEY}  [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)  | FORCE {INDEX|KEY}  [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)index_list: index_name [, index_name] ...

SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

SELECT * FROM table1, table2;SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id;SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;SELECT * FROM table1 LEFT JOIN table2 USING (id);SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id  LEFT JOIN table3 ON table2.id=table3.id;

tbl_name [[AS] alias] [index_hint_list]index_hint_list: index_hint [, index_hint] ...index_hint: USE {INDEX|KEY}  [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])  | IGNORE {INDEX|KEY}  [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)  | FORCE {INDEX|KEY}  [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)index_list: index_name [, index_name] ...

SELECT * FROM table1 USE INDEX (col1_index,col2_index)  WHERE col1=1 AND col2=2 AND col3=3;SELECT * FROM table1 IGNORE INDEX (col3_index)  WHERE col1=1 AND col2=2 AND col3=3;

IGNORE INDEX (i1)

IGNORE INDEX FOR JOIN (i1)IGNORE INDEX FOR ORDER BY (i1)IGNORE INDEX FOR GROUP BY (i1)

SELECT * FROM t1  USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE INDEX (i2);

SELECT * FROM t1   USE INDEX (i1,i2) IGNORE INDEX (i2);

SELECT * FROM t  USE INDEX (index1)  IGNORE INDEX (index1) FOR ORDER BY  IGNORE INDEX (index1) FOR GROUP BY  WHERE ... IN BOOLEAN MODE ... ;SELECT * FROM t  USE INDEX (index1)  WHERE ... IN BOOLEAN MODE ... ;

SELECT ...UNION [ALL | DISTINCT] SELECT ...[UNION [ALL | DISTINCT] SELECT ...]

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);+---------------+| REPEAT('a',1) |+---------------+| a || bbbbbbbbbb |+---------------+

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)UNION(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

(SELECT a FROM t1 WHERE a=10 AND B=1)UNION(SELECT a FROM t2 WHERE a=11 AND B=2)ORDER BY a LIMIT 10;

(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)UNION(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)UNION(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;

A subquery is a SELECT statement within another statement.

Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.

Here is an example of a subquery:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

In this example, SELECT * FROM t1 ... is the outer query (or outer statement), and (SELECT column1 FROM t2) is the subquery. We say that the subquery is nested within the outer query, and in fact it is possible to nest subqueries within other subqueries, to a considerable depth. A subquery must always appear within parentheses.

The main advantages of subqueries are:

Here is an example statement that shows the major points about subquery syntax as specified by the SQL standard and supported in MySQL:

DELETE FROM t1WHERE s11 > ANY (SELECT COUNT(*) /* no hint */ FROM t2  WHERE NOT EXISTS   (SELECT * FROM t3 WHERE ROW(5*t2.s1,77)= (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM  (SELECT * FROM t5) AS t5)));

A subquery can return a scalar (a single value), a single row, a single column, or a table (one or more rows of one or more columns). These are called scalar, column, row, and table subqueries. Subqueries that return a particular kind of result often can be used only in certain contexts, as described in the following sections.

There are few restrictions on the type of statements in which subqueries can be used. A subquery can contain many of the keywords or clauses that an ordinary SELECT can contain: DISTINCT, GROUP BY, ORDER BY, LIMIT, joins, index hints, UNION constructs, comments, functions, and so on.

A subquery's outer statement can be any one of: SELECT, INSERT, UPDATE, DELETE, SET, or DO.

In MySQL, you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, UPDATE, and (because subqueries can be used in the SET clause) LOAD DATA INFILE.

For information about how the optimizer handles subqueries, see Section 8.13.12, "Optimizing Subqueries with EXISTS Strategy". For a discussion of restrictions on subquery use, including performance issues for certain forms of subquery syntax, see Section E.4, "Restrictions on Subqueries".

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);INSERT INTO t1 VALUES(100, 'abcde');SELECT (SELECT s2 FROM t1);

CREATE TABLE t1 (s1 INT);INSERT INTO t1 VALUES (1);CREATE TABLE t2 (s1 INT);INSERT INTO t2 VALUES (2);

SELECT (SELECT s1 FROM t2) FROM t1;

SELECT UPPER((SELECT s1 FROM t1)) FROM t2;

non_subquery_operand comparison_operator (subquery)

=  >  <  >=  <=  <>  !=  <=>

... WHERE 'a' = (SELECT column1 FROM t1)

non_subquery_operand LIKE (subquery)

SELECT * FROM t1  WHERE column1 = (SELECT MAX(column2) FROM t2);

SELECT * FROM t1 AS t  WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

operand comparison_operator ANY (subquery)operand IN (subquery)operand comparison_operator SOME (subquery)

=  >  <  >=  <=  <>  !=

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

operand comparison_operator ALL (subquery)

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

=  >  <  >=  <=  <>  !=  <=>

SELECT * FROM t1  WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);SELECT * FROM t1  WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

SELECT * FROM t1 WHERE ROW(1) = (SELECT column1 FROM t2)

SELECT * FROM t1 WHERE (column1,column2) = (1,1);SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

SELECT column1,column2,column3  FROM t1  WHERE (column1,column2,column3) IN (SELECT column1,column2,column3 FROM t2);

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

SELECT * FROM t1  WHERE column1 = ANY (SELECT column1 FROM t2   WHERE t2.column2 = t1.column2);

SELECT column1 FROM t1 AS x  WHERE x.column1 = (SELECT column1 FROM t2 AS x WHERE x.column1 = (SELECT column1 FROM t3  WHERE x.column2 = t3.column1));

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

SELECT ... FROM (subquery) [AS] name ...

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

INSERT INTO t1 VALUES (1,'1',1.0);INSERT INTO t1 VALUES (2,'2',2.0);SELECT sb1,sb2,sb3  FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb  WHERE sb1 > 1;

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

SELECT AVG(sum_column1)  FROM (SELECT SUM(column1) AS sum_column1 FROM t1 GROUP BY column1) AS t1;

mysql> CREATE DATABASE d1;Query OK, 1 row affected (0.00 sec)mysql> USE d1;Database changedmysql> CREATE TABLE t1 (c1 INT);Query OK, 0 rows affected (0.15 sec)mysql> CREATE TABLE t2 (c1 INT);Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER //mysql> CREATE FUNCTION f1(p1 INT) RETURNS INTmysql>   BEGINmysql> INSERT INTO t2 VALUES (p1);mysql> RETURN p1;mysql>   END //Query OK, 0 rows affected (0.01 sec)mysql> DELIMITER ;

mysql> SELECT * FROM t2;Empty set (0.00 sec)mysql> EXPLAIN SELECT f1(5);+--+-----------+-----+----+-------------+----+-------+----+----+--------------+|id|select_type|table|type|possible_keys|key |key_len|ref |rows|Extra |+--+-----------+-----+----+-------------+----+-------+----+----+--------------+| 1|SIMPLE |NULL |NULL|NULL |NULL|NULL   |NULL|NULL|No tables used|+--+-----------+-----+----+-------------+----+-------+----+----+--------------+1 row in set (0.00 sec)mysql> SELECT * FROM t2;Empty set (0.00 sec)

mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;+--+-----------+-----+----+-------------+----+-------+----+----+--------------+|id|select_type|table|type|possible_keys|key |key_len|ref |rows|Extra |+--+-----------+-----+----+-------------+----+-------+----+----+--------------+| 1|PRIMARY |NULL |NULL|NULL |NULL|NULL   |NULL|NULL|No tables used|+--+-----------+-----+----+-------------+----+-------+----+----+--------------+1 row in set, 1 warning (0.00 sec)mysql> SHOW WARNINGS;+-------+------+------------------------------------------+| Level | Code | Message  |+-------+------+------------------------------------------+| Note  | 1249 | Select 2 was reduced during optimization |+-------+------+------------------------------------------+1 row in set (0.00 sec)mysql> SELECT * FROM t2;Empty set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;+----+-------------+------------+--------+---------------+------+---------+------+| id | select_type | table  | type   | possible_keys | key  | key_len | ref  |+----+-------------+------------+--------+---------------+------+---------+------+|  1 | PRIMARY | a1 | system | NULL  | NULL | NULL | NULL ||  1 | PRIMARY | <derived2> | system | NULL  | NULL | NULL | NULL ||  2 | DERIVED | NULL   | NULL   | NULL  | NULL | NULL | NULL |+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+ rows | Extra   |------+---------------------+ 0 | const row not found | 1 | | NULL | No tables used  |------+---------------------+3 rows in set (0.00 sec)mysql> SELECT * FROM t2;+------+| c1   |+------+| 5 |+------+1 row in set (0.00 sec)

EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id;

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

SELECT table1.*  FROM table1 LEFT JOIN table2 ON table1.id=table2.id  WHERE table2.id IS NULL;

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition]

For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values. The SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value. The WHERE clause, if given, specifies the conditions that identify which rows to update. With no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order that is specified. The LIMIT clause places a limit on the number of rows that can be updated.

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

where_condition is an expression that evaluates to true for each row to be updated. For expression syntax, see Section 9.5, "Expression Syntax".

table_references and where_condition are is specified as described in Section 13.2.9, "SELECT Syntax".

You need the UPDATE privilege only for columns referenced in an UPDATE that are actually updated. You need only the SELECT privilege for any columns that are read but not modified.

The UPDATE statement supports the following modifiers:

In MySQL 5.5.18 and later, UPDATE IGNORE statements, including those having an ORDER BY clause, are flagged as unsafe for statement-based replication. (This is because the order in which the rows are updated determines which rows are ignored.) With this change, such statements produce a warning in the log when using statement-based mode and are logged using the row-based format when using MIXED mode. (Bug #11758262, Bug #50439) See Section 16.1.2.3, "Determination of Safe and Unsafe Statements in Binary Logging", for more information.

If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column. For example, the following statement sets col1 to one more than its current value:

UPDATE t1 SET col1 = col1 + 1;

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

If you set a column to the value it currently has, MySQL notices this and does not update it.

If you update a column that has been declared NOT NULL by setting to NULL, an error occurs if strict SQL mode is enabled; otherwise, the column is set to the implicit default value for the column data type and the warning count is incremented. The implicit default value is 0 for numeric types, the empty string ('') for string types, and the "zero" value for date and time types. See Section 11.5, "Data Type Default Values".

UPDATE returns the number of rows that were actually changed. The mysql_info() C API function returns the number of rows that were matched and updated and the number of warnings that occurred during the UPDATE.

You can use LIMIT row_count to restrict the scope of the UPDATE. A LIMIT clause is a rows-matched restriction. The statement stops as soon as it has found row_count rows that satisfy the WHERE clause, whether or not they actually were changed.

If an UPDATE statement includes an ORDER BY clause, the rows are updated in the order specified by the clause. This can be useful in certain situations that might otherwise result in an error. Suppose that a table t contains a column id that has a unique index. The following statement could fail with a duplicate-key error, depending on the order in which rows are updated:

UPDATE t SET id = id + 1;

For example, if the table contains 1 and 2 in the id column and 1 is updated to 2 before 2 is updated to 3, an error occurs. To avoid this problem, add an ORDER BY clause to cause the rows with larger id values to be updated before those with smaller values:

UPDATE t SET id = id + 1 ORDER BY id DESC;

You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 13.2.9.2, "JOIN Syntax". Here is an example:

UPDATE items,month SET items.price=month.priceWHERE items.id=month.id;

The preceding example shows an inner join that uses the comma operator, but multiple-table UPDATE statements can use any type of join permitted in SELECT statements, such as LEFT JOIN.

If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly. See Section 14.3.5.4, "FOREIGN KEY Constraints".

Currently, you cannot update a table and select from the same table in a subquery.

Index hints (see Section 13.2.9.3, "Index Hint Syntax") are accepted but ignored for UPDATE statements.

An UPDATE on a partitioned table using a storage engine such as MyISAM that employs table-level locks locks all partitions of the table. This does not occur with tables using storage engines such as InnoDB that employ row-level locking. This issue is resolved in MySQL 5.6. See Section 18.5.4, "Partitioning and Table-Level Locking", for more information.