Data Manipulation: SELECT, INSERT, UPDATE, DELETE

SELECT Syntax

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
      [LIMIT [offset,] rows]
      [PROCEDURE procedure_name]
      [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables. select_expression indicates the columns you want to retrieve. SELECT may also be used to retrieve rows computed without reference to any table. For example:

mysql> SELECT 1 + 1;
         -> 2

All keywords used must be given in exactly the order shown in the preceding example. For instance, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause.

  • A SELECT expression may be given an alias using AS. The alias is used as the expression’s column name and can be used with ORDER BY or HAVING clauses. For example:

    mysql> SELECT CONCAT(last_name,', ',first_name) AS full_name
    FROM mytable ORDER BY full_name;

  • You cannot use a column alias in a WHERE clause because the column value may not yet be determined when the WHERE clause is executed. See Section A.5.4.

  • The FROM table_references clause indicates the tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see Section 6.4.1.1. For each table specified, you may optionally specify an alias:

    table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)]

    As of MySQL Version 3.23.12, you can give hints about which index MySQL should use when retrieving information from a table. This is useful if EXPLAIN shows that MySQL is using the wrong index. By specifying USE INDEX (key_list), you can tell MySQL to use only one of the specified indexes to find rows in the table. The alternative syntax IGNORE INDEX (key_list) can be used to tell MySQL to not use some particular index. USE/IGNORE KEY are synonyms for USE/IGNORE INDEX.

  • You can refer to a column as col_name, tbl_name.col_name, or db_name.tbl_name.col_name. You need not specify a tbl_name or db_name.tbl_name prefix for a column reference in a SELECT statement unless the reference would be ambiguous. See Section 6.1.2, for examples of ambiguity that require the more explicit column reference forms.

  • A table reference may be aliased using tbl_name [AS] alias_name:

    mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
    ->        WHERE t1.name = t2.name;
mysql> SELECT t1.name, t2.salary FROM employee t1, info t2
    ->        WHERE t1.name = t2.name;

  • Columns selected for output may be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions begin with 1:

    mysql> SELECT college, region, seed FROM tournament
    ->        ORDER BY region, seed;
mysql> SELECT college, region AS r, seed AS s FROM tournament
    ->        ORDER BY r, s;
mysql> SELECT college, region, seed FROM tournament
    ->        ORDER BY 2, 3;

    To sort in reverse order, add the DESC (descending) keyword to the name of the column in the ORDER BY clause that you are sorting by. The default is ascending order; this may be specified explicitly using the ASC keyword.

  • You can in the WHERE clause use any of the functions that MySQL supports. See Section 6.3.

  • The HAVING clause can refer to any column or alias named in the select_expression. It is applied last, just before items are sent to the client, with no optimisation. Don’t use HAVING for items that should be in the WHERE clause. For example, do not write this:

    mysql> SELECT col_name FROM tbl_name HAVING col_name > 0;

    Write this instead:

    mysql> SELECT col_name FROM tbl_name WHERE col_name > 0;

    In MySQL Version 3.22.5 or later, you can also write queries like this:

    mysql> SELECT user,MAX(salary) FROM users
    ->        GROUP BY user HAVING MAX(salary)>10;

    In older MySQL versions, you can write this instead:

    mysql> SELECT user,MAX(salary) AS sum FROM users
    ->        group by user HAVING sum>10;

  • The options DISTINCT, DISTINCTROW, and ALL specify whether duplicate rows should be returned. The default is (ALL), in which case all matching rows are returned. DISTINCT and DISTINCTROW are synonyms and specify that duplicate rows in the result set should be removed.

  • All options beginning with SQL_, STRAIGHT_JOIN, and HIGH_PRIORITY are MySQL extensions to ANSI SQL.

  • HIGH_PRIORITY will give the SELECT higher priority than a statement that updates a table. You should only use this for queries that are very fast and must be done at once. A SELECT HIGH_PRIORITY query will run if the table is locked for read even if an update statement is waiting for the table to be free.

  • SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimiser that the result set will have many rows. In this case, MySQL will directly use disk-based temporary tables if needed. MySQL will also, in this case, prefer sorting to doing a temporary table with a key on the GROUP BY elements.

  • SQL_BUFFER_RESULT will force the result to be put into a temporary table. This will help MySQL free the table locks early and will help in cases where it takes a long time to send the result set to the client.

  • SQL_SMALL_RESULT, a MySQL-specific option, can be used with GROUP BY or DISTINCT to tell the optimiser that the result set will be small. In this case, MySQL will use fast temporary tables to store the resulting table instead of using sorting. In MySQL Version 3.23 this shouldn’t normally be needed.

  • SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in the result, disregarding any LIMIT clause. The number of rows can be obtained with SELECT FOUND_ROWS( ). See Section 6.3.6.2.

  • SQL_CACHE tells MySQL to store the query result in the query cache if you are using SQL_QUERY_CACHE_TYPE=2 (DEMAND). See Section 6.9.

  • SQL_NO_CACHE tells MySQL to not allow the query result to be stored in the query cache. See Section 6.9.

  • If you use GROUP BY, the output rows will be sorted according to the GROUP BY as if you would have had an ORDER BY over all the fields in the GROUP BY. MySQL has extended the GROUP BY so that you can also specify ASC and DESC to GROUP BY:

    SELECT a,COUNT(b) FROM test_table GROUP BY a DESC

  • MySQL has extended the use of GROUP BY to allow you to select fields which are not mentioned in the GROUP BY clause. If you are not getting the results you expect from your query, please read the GROUP BY description. See Section 6.3.7.

  • STRAIGHT_JOIN forces the optimiser to join the tables in the order in which they are listed in the FROM clause. You can use this to speed up a query if the optimiser joins the tables in non-optimal order. See Section 5.2.1.

  • The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. The arguments must be integer constants.

    If two arguments are given, the first specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

    mysql> SELECT * FROM table LIMIT 5,10;  # Retrieve rows 6-15

    If one argument is given, it indicates the maximum number of rows to return:

    mysql> SELECT * FROM table LIMIT 5;     # Retrieve first 5 rows

    In other words, LIMIT n is equivalent to LIMIT 0,n.

  • The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host and cannot already exist (among other things, this prevents database tables and files such as /etc/passwd from being destroyed). You must have the file privilege on the server host to use this form of SELECT.

    SELECT ... INTO OUTFILE is mainly intended to let you very quickly dump a table on the server machine. If you want to create the resulting file on some host other than the server host you can’t use SELECT ... INTO OUTFILE. In this case you should instead use some client program like mysqldump --tab or mysql -e "SELECT ..." > outfile to generate the file.

    SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE; the syntax for the export_options part of the statement consists of the same FIELDS and LINES clauses that are used with the LOAD DATA INFILE statement. See Section 6.4.9.

    In the resulting text file, only the following characters are escaped by the ESCAPED BY character:

    • The ESCAPED BY character

    • The first character in FIELDS TERMINATED BY

    • The first character in LINES TERMINATED BY

    Additionally, ASCII 0 is converted to ESCAPED BY followed by 0 (ASCII 48).

    This is because you must escape any FIELDS TERMINATED BY, ESCAPED BY, or LINES TERMINATED BY characters to reliably be able to read the file back. ASCII 0 is escaped to make it easier to view with some pagers.

    As the resulting file doesn’t have to conform to the SQL syntax, nothing else need be escaped.

    Here is an example of getting a file in the format used by many old programs:

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

  • If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL will only write one row into the file, without any column or line terminations and without any escaping. This is useful if you want to store a blob in a file.

  • Note that any file created by INTO OUTFILE and INTO DUMPFILE is going to be readable for all users! The reason is that MySQL server can’t create a file that is owned by anyone other than the user it’s running as (you should never run mysqld as root). Therefore, the file has to be word-readable so that you can retrieve the rows.

  • If you are using FOR UPDATE on a table handler with page/row locks, the examined rows will be write-locked.

JOIN syntax

MySQL supports the following JOIN syntaxes for use in SELECT statements:

table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference join_condition
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference LEFT [OUTER] JOIN table_reference
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference join_condition
table_reference RIGHT [OUTER] JOIN table_reference
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

Where table_reference is defined as: 

table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)]

and join_condition is defined as:

ON conditional_expr |
USING (column_list)

You should never have any conditions in the ON part that are used to restrict which rows you have in the result set. If you want to restrict which rows should be in the result, you have to do this in the WHERE clause.

Note that in versions before Version 3.23.17, the INNER JOIN didn’t take a join_condition!

The last LEFT OUTER JOIN syntax shown in the preceding example exists only for compatibility with ODBC:

  • A table reference may be aliased using tbl_name AS alias_name or tbl_name alias_name:

    mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
    ->        WHERE t1.name = t2.name;

  • The ON conditional is any conditional of the form that may be used in a WHERE clause.

  • If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:

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

    This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL, of course. See Section 5.2.6.

  • The USING (column_list) clause names a list of columns that must exist in both tables. A USING clause such as:

    A LEFT JOIN B USING (C1,C2,C3,...)

    is defined to be semantically identical to an ON expression like this:

    A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...

  • The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.

  • INNER JOIN and , (comma) are semantically equivalent. Both do a full join between the tables used. Normally, you specify how the tables should be linked in the WHERE condition.

  • RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it’s recommended to use LEFT JOIN instead of RIGHT JOIN.

  • STRAIGHT_JOIN is identical to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases where the join optimiser puts the tables in the wrong order.

  • As of MySQL Version 3.23.12, you can give hints about which index MySQL should use when retrieving information from a table. This is useful if EXPLAIN shows that MySQL is using the wrong index. By specifying USE INDEX (key_list), you can tell MySQL to use only one of the specified indexes to find rows in the table. The alternative syntax IGNORE INDEX (key_list) can be used to tell MySQL to not use some particular index. USE/IGNORE KEY are synonyms for USE/IGNORE INDEX.

Some examples:

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
    ->          LEFT JOIN table3 ON table2.id=table3.id;
mysql> SELECT * FROM table1 USE INDEX (key1,key2)
    ->          WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
    ->          WHERE key1=1 AND key2=2 AND key3=3;

See Section 5.2.6.

UNION syntax

SELECT ...
UNION [ALL]
SELECT ...
  [UNION
   SELECT ...]

UNION is implemented in MySQL 4.0.0.

UNION is used to combine the result from many SELECT statements into one result set.

The SELECT commands are normal select commands, but with the following restrictions:

  • Only the last SELECT command can have INTO OUTFILE.

If you don’t use the keyword ALL for the UNION, all returned rows will be unique, as if you had done a DISTINCT for the total result set. If you specify ALL, you will get all matching rows from all the used SELECT statements.

If you want to use an ORDER BY for the total UNION result, you should use parentheses:

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

HANDLER Syntax

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

The HANDLER statement provides direct access to the MyISAM table handler interface, bypassing the SQL optimiser. Thus, it is faster than SELECT.

The first form of the HANDLER statement opens a table, making it accessible via subsequent HANDLER ... READ statements. This table object is not shared by other threads and will not be closed until the thread calls HANDLER tbl_name CLOSE or the thread dies.

The second form fetches one row (or more, specified by the LIMIT clause) where the index specified complies to the condition and the WHERE condition is met. If the index consists of several parts (spans over several columns), the values are specified in comma-separated lists. Providing values only for the few first columns is possible.

The third form fetches one row (or more, specified by the LIMIT clause) from the table in index order, matching the WHERE condition.

The fourth form (without index specification) fetches one row (or more, specified by the LIMIT clause) from the table in natural row order (as stored in the data file) matching the WHERE condition. It is faster than HANDLER tbl_name READ index_name when a full table scan is desired.

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

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 is issued, table data can be modified (by this or another thread) and these modifications may appear only partially in HANDLER ... NEXT or HANDLER ... PREV scans.

INSERT Syntax

    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression, ...

INSERT inserts new rows into an existing table. The INSERT ... VALUES form of the statement inserts rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. The INSERT ... VALUES form with multiple value lists is supported in MySQL Version 3.22.5 or later. The col_name=expression syntax is supported in MySQL Version 3.22.10 or later.

tbl_name is the table into which rows should be inserted. The column name list or the SET clause indicates which columns the statement specifies values for:

  • If you specify no column list for INSERT ... VALUES or INSERT ... SELECT, values for all columns must be provided in the VALUES( ) list or by the SELECT. If you don’t know the order of the columns in the table, use DESCRIBE tbl_name to find out.

  • Any column not explicitly given a value is set to its default value. For example, if you specify a column list that doesn’t name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 6.5.3.

    MySQL always has a default value for all fields. This is something that is imposed on MySQL to be able to work with both transactional and non-transactional tables.

    Our view is that field content checks should be done in the application and not in the database server.

  • An expression may refer to any column that was set earlier in a value list. For example, you can say this:

    mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

    but not this:

    mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);

  • If you specify the keyword LOW_PRIORITY, execution of the INSERT is delayed until no other clients are reading from the table. In this case the client has to wait until the insert statement is completed, which may take a long time if the table is in heavy use. This is in contrast to INSERT DELAYED, which lets the client continue at once. See Section 6.4.4. Note that LOW_PRIORITY should normally not be used with MyISAM tables, as this disables concurrent inserts. See Section 7.1.

  • If you specify the keyword IGNORE in an INSERT with many value rows, any rows that duplicate an existing PRIMARY or UNIQUE key in the table are ignored and are not inserted. If you do not specify IGNORE, the insert is aborted if any row duplicates an existing key value. You can determine with the C API function mysql_info( ) how many rows were inserted into the table.

  • If MySQL was configured using the DONT_USE_DEFAULT_FIELDS option, INSERT statements generate an error unless you explicitly specify values for all columns that require a non-NULL value. See Section 2.3.3.

  • You can find the value used for an AUTO_INCREMENT column with the mysql_insert_id function. See Section 8.4.3.126.

If you use INSERT ... SELECT or an INSERT ... VALUES statement with multiple value lists, you can use the C API function mysql_info( ) to get information about the query. The format of the information string is shown here:

Records: 100 Duplicates: 0 Warnings: 0

Duplicates indicates the number of rows that couldn’t 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:

  • Inserting NULL into a column that has been declared NOT NULL. The column is set to its default value.

  • Setting a numeric column to a value that lies outside the column’s range. The value is clipped to the appropriate endpoint of the range.

  • Setting a numeric column to a value such as '10.34 a'. The trailing garbage is stripped and the remaining numeric part is inserted. If the value doesn’t make sense as a number, the column is set to 0.

  • Inserting a string into a CHAR, VARCHAR, TEXT, or BLOB column that exceeds the column’s maximum length. The value is truncated to the column’s maximum length.

  • Inserting a value into a date or time column that is illegal for the column type. The column is set to the appropriate zero value for the type.

INSERT ... SELECT syntax

INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ...

With the INSERT ... SELECT statement you can quickly insert many rows into a table from one or many tables:

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;

The following conditions hold for an INSERT ... SELECT statement:

  • The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query because it’s forbidden in ANSI SQL to SELECT from the same table into which you are inserting. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using sub-select clauses, the situation could easily be very confusing!)

  • AUTO_INCREMENT columns work as usual.

  • You can use the C API function mysql_info( ) to get information about the query. See Section 6.4.3.

  • To ensure that the update log/binary log can be used to re-create the original tables, MySQL will not allow concurrent inserts during INSERT ... SELECT.

You can, of course, also use REPLACE instead of INSERT to overwrite old rows.

INSERT DELAYED Syntax

INSERT DELAYED ...

The DELAYED option for the INSERT statement is a MySQL-specific option that is very useful if you have clients that can’t wait for the INSERT to complete. This is a common problem when you use MySQL for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete. DELAYED was introduced in MySQL Version 3.22.15. It is a MySQL extension to ANSI SQL92.

INSERT DELAYED only works with ISAM and MyISAM tables. Note that as MyISAM tables support concurrent SELECT and INSERT, if there are no free blocks in the middle of the data file you very seldom need to use INSERT DELAYED with MyISAM. See Section 7.1.

When you use INSERT DELAYED, the client will get an OK at once and the row will be inserted when the table is not in use by any other thread.

Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than doing many separate inserts.

Note that currently the queued rows are only stored in memory until they are inserted into the table. This means that if you kill mysqld the hard way (kill -9) or if mysqld dies unexpectedly, any queued rows that weren’t written to disk are lost!

The following describes in detail what happens when you use the DELAYED option to INSERT or REPLACE. In this description, the “thread” is the thread that received an INSERT DELAYED command and “handler” is the thread that handles all INSERT DELAYED statements for a particular table.

  • When a thread executes a DELAYED statement for a table, a handler thread is created to process all DELAYED statements for the table, if no such handler already exists.

  • The thread checks whether the handler has acquired a DELAYED lock already; if not, it tells the handler thread to do so. The DELAYED lock can be obtained even if other threads have a READ or WRITE lock on the table. However, the handler will wait for all ALTER TABLE locks or FLUSH TABLES to ensure that the table structure is up to date.

  • The thread executes the INSERT statement, but instead of writing the row to the table, it puts a copy of the final row into a queue that is managed by the handler thread. Any syntax errors are noticed by the thread and reported to the client program.

  • The client can’t report the number of duplicates or the AUTO_INCREMENT value for the resulting row; it can’t obtain them from the server because the INSERT returns before the insert operation has been completed. If you use the C API, the mysql_info( ) function doesn’t return anything meaningful, for the same reason.

  • The update log is updated by the handler thread when the row is inserted into the table. In case of multiple-row inserts, the update log is updated when the first row is inserted.

  • After every delayed_insert_limit row is written, the handler checks whether any SELECT statements are still pending. If so, it allows these to execute before continuing.

  • When the handler has no more rows in its queue, the table is unlocked. If no new INSERT DELAYED commands are received within delayed_insert_timeout seconds, the handler terminates.

  • If more than delayed_queue_size rows are pending already in a specific handler queue, the thread requesting INSERT DELAYED waits until there is room in the queue. This is done to ensure that the mysqld server doesn’t use all memory for the delayed memory queue.

  • The handler thread will show up in the MySQL process list with delayed_insert in the Command column. It will be killed if you execute a FLUSH TABLES command or kill it with KILL thread_id. However, it will first store all queued rows into the table before exiting. During this time it will not accept any new INSERT commands from another thread. If you execute an INSERT DELAYED command after this, a new handler thread will be created.

    Note that this means that INSERT DELAYED commands have higher priority than normal INSERT commands if an INSERT DELAYED handler is already running! Other update commands will have to wait until the INSERT DELAYED queue is empty, someone kills the handler thread (with KILL thread_id), or someone executes FLUSH TABLES.

  • The following status variables provide information about INSERT DELAYED commands:

    Variable

    Meaning

    Delayed_insert_threads

    Number of handler threads

    Delayed_writes

    Number of rows written with INSERT DELAYED

    Not_flushed_delayed_rows

    Number of rows waiting to be written

    You can view these variables by issuing a SHOW STATUS statement or by executing a mysqladmin extended-status command.

Note that INSERT DELAYED is slower than a normal INSERT if the table is not in use. There is also the additional overhead for the server to handle a separate thread for each table on which you use INSERT DELAYED. This means that you should only use INSERT DELAYED when you are really sure you need it!

UPDATE Syntax

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2, ...]
    [WHERE where_definition]
    [LIMIT #]

UPDATE updates columns in existing table rows with new values. The SET clause indicates which columns to modify and the values they should be given. The WHERE clause, if given, specifies which rows should be updated. Otherwise, all rows are updated. If the ORDER BY clause is specified, the rows will be updated in the order that is specified.

If you specify the keyword LOW_PRIORITY, execution of the UPDATE is delayed until no other clients are reading from the table.

If you specify the keyword IGNORE, the update statement will not abort even if we get duplicate key errors during the update. Rows that would cause conflicts will not be updated.

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

mysql> UPDATE persondata SET age=age+1;

UPDATE assignments are evaluated from left to right. For example, the following statement doubles the age column, then increments it:

mysql> UPDATE persondata SET age=age*2, age=age+1;

If you set a column to the value it currently has, MySQL notices this and doesn’t update it.

UPDATE returns the number of rows that were actually changed. In MySQL Version 3.22 or later, the C API function mysql_info( ) returns the number of rows that were matched and updated and the number of warnings that occurred during the UPDATE.

In MySQL Version 3.23, you can use LIMIT # to ensure that only a given number of rows are changed.

DELETE Syntax

DELETE [LOW_PRIORITY | QUICK] FROM table_name
       [WHERE where_definition]
       [ORDER BY ...]
       [LIMIT rows]

or

DELETE [LOW_PRIORITY | QUICK] table_name[.*] [,table_name[.*] ...]
       FROM table-references
       [WHERE where_definition]

or

DELETE [LOW_PRIORITY | QUICK]
       FROM table_name[.*], [table_name[.*] ...]
       USING table-references
       [WHERE where_definition]

DELETE deletes rows from table_name that satisfy the condition given by where_definition, and returns the number of records deleted.

If you issue a DELETE with no WHERE clause, all rows are deleted. If you do this in AUTOCOMMIT mode, this works as TRUNCATE. See Section 6.4.7. In MySQL 3.23, DELETE without a WHERE clause will return zero as the number of affected records.

If you really want to know how many records are deleted when you are deleting all rows, and are willing to suffer a speed penalty, you can use a DELETE statement of this form:

mysql> DELETE FROM table_name WHERE 1>0;

Note that this is much slower than DELETE FROM table_name with no WHERE clause because it deletes rows one at a time.

If you specify the keyword LOW_PRIORITY, execution of the DELETE is delayed until no other clients are reading from the table.

If you specify the word QUICK, the table handler will not merge index leaves during delete, which may speed up certain kinds of deletes.

In MyISAM tables, deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. To reclaim unused space and reduce file-sizes, use the OPTIMIZE TABLE statement or the myisamchk utility to reorganise tables. OPTIMIZE TABLE is easier, but myisamchk is faster. See Section 4.5.1 and Section 4.4.6.10.

The first multi-table delete format is supported starting from MySQL 4.0.0. The second multi-table delete format is supported starting from MySQL 4.0.2.

The idea is that only matching rows from the tables listed before the FROM or before the USING clause are deleted. The effect is that you can delete rows from many tables at the same time and also have additional tables that are used for searching.

The .* after the table names is there just to be compatible with Access:

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

or

DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

In the preceding case we delete matching rows just from tables t1 and t2.

ORDER BY and the use of multiple tables in the DELETE statement are supported in MySQL 4.0.

If an ORDER BY clause is used, the rows will be deleted in that order. This is really only useful in conjunction with LIMIT. For example:

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

This will delete the oldest entry (by timestamp) where the row matches the WHERE clause.

The MySQL-specific LIMIT rows 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 specific DELETE command doesn’t take too much time. You can simply repeat the DELETE command until the number of affected rows is less than the LIMIT value.

TRUNCATE Syntax

TRUNCATE TABLE table_name

In 3.23 TRUNCATE TABLE is mapped to COMMIT ; DELETE FROM table_name. See Section 6.4.6.

TRUNCATE TABLE differs from DELETE FROM ... in the following ways:

  • Truncate operations drop and re-create the table, which is much faster than deleting rows one by one.

  • Not transaction-safe; you will get an error if you have an active transaction or an active table lock.

  • Doesn’t return the number of deleted rows.

  • As long as the table definition file table_name.frm is valid, the table can be re-created this way, even if the data or index files have become corrupted.

TRUNCATE is an Oracle SQL extension.

REPLACE Syntax

    REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression,...

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a unique index, the old record is deleted before the new record is inserted. See Section 6.4.3.

In other words, you can’t access the values of the old row from a REPLACE statement. In some old MySQL versions it appeared that you could do this, but that was a bug that has been corrected.

When you use a REPLACE command, mysql_affected_rows( ) will return 2 if the new row replaced an old row. This is because one row was inserted and then the duplicate was deleted.

This fact makes it easy to determine whether REPLACE added or replaced a row: check whether the affected-rows value is 1 (added) or 2 (replaced).

LOAD DATA INFILE Syntax

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES TERMINATED BY '\n']
    [IGNORE number LINES]
    [(col_name,...)]

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. If the LOCAL keyword is specified, the file is read from the client host. If LOCAL is not specified, the file must be located on the server. (LOCAL is available in MySQL Version 3.22.6 or later.)

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 on the server host. See Section 4.2.7.

In MySQL 3.23.49 and MySQL 4.0.2 LOCAL will only work if you have not started mysqld with --local-infile=0 or if you have not enabled your client to support LOCAL. See Section 4.2.4.

If you specify the keyword LOW_PRIORITY, execution of the LOAD DATA statement is delayed until no other clients are reading from the table.

If you specify the keyword CONCURRENT with a MyISAM table, other threads can retrieve data from the table while LOAD DATA is executing. Using this option will, of course, affect the performance of LOAD DATA a bit even if no other thread is using the table at the same time.

Using LOCAL will be a bit slower than letting the server access the files directly because the contents of the file must travel from the client host to the server host. On the other hand, you do not need the file privilege to load local files.

If you are using MySQL before Version 3.23.24 you can’t read from a FIFO with LOAD DATA INFILE. If you need to read from a FIFO (for example, the output from gunzip), use LOAD DATA LOCAL INFILE instead.

You can also load data files by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE command 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.

When locating files on the server host, the server uses the following rules:

  • If an absolute pathname is given, the server uses the pathname as is.

  • If a relative pathname with one or more leading components is given, the server searches for the file relative to the server’s data directory.

  • If a filename with no leading components is given, the server looks for the file in the database directory of the current database.

Note that these rules mean a file given as ./myfile.txt is read from the server’s data directory, whereas a file given as myfile.txt is read from the database directory of the current database. For example, the following LOAD DATA statement reads the file data.txt from the database directory for db1 because db1 is the current database, even though the statement explicitly loads the file into a table in the db2 database:

mysql> USE db1;
mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;

The REPLACE and IGNORE keywords control handling of input records that duplicate existing records on unique key values. If you specify REPLACE, new rows replace existing rows that have the same unique key value. If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you don’t specify either option, an error occurs when a duplicate key value is found, and the rest of the text file is ignored.

If you load data from a local file using the LOCAL keyword, the server has no way to stop transmission of the file in the middle of the operation, so the default bahavior is the same as if IGNORE were specified.

If you use LOAD DATA INFILE on an empty MyISAM table, all non-unique indexes are created in a separate batch (like in REPAIR). This normally makes LOAD DATA INFILE much faster when you have many indexes.

LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. See Section 6.4.1. To write data from a database to a file, use SELECT ... INTO OUTFILE. To read the file back into the database, use LOAD DATA INFILE. The syntax of the FIELDS and LINES clauses is the same for both commands. 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 don’t specify a FIELDS clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

If you don’t specify a LINES clause, the default is the same as if you had written this:

LINES TERMINATED BY '\n'

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

  • Look for line boundaries at newlines.

  • Break lines into fields at tabs.

  • Do not expect fields to be enclosed within any quoting characters.

  • Interpret occurrences of tab, newline, or \ preceded by \ as literal characters that are part of field values.

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

  • Write tabs between fields.

  • Do not enclose fields within any quoting characters.

  • Use \ to escape instances of tab, newline, or \ that occur within field values.

  • Write newlines at the ends of lines.

Note that to write FIELDS ESCAPED BY '\\', you must specify two backslashes for the value to be read as a single backslash.

The IGNORE number LINES option can be used to ignore a header of column names at the start of the file:

mysql> LOAD DATA INFILE "/tmp/file_name" 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 commands must match. Otherwise, LOAD DATA INFILE will not interpret the contents of the file properly. Suppose you use SELECT ... INTO OUTFILE to write a file with fields delimited by commas:

mysql> SELECT * INTO OUTFILE 'data.txt'
    ->          FIELDS TERMINATED BY ','
    ->          FROM ...;

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

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

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

mysql> 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, too. For example, a file in dBASE format will have fields separated by commas and enclosed in double quotes. If lines in the file are terminated by newlines, the following command illustrates the field and line handling options you would use to load the file:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
    ->           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    ->           LINES TERMINATED BY '\n';

Any of the field or line handling options may 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 and LINES TERMINATED BY values may 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.

For example, to read into a SQL table a file of jokes that are separated with a line of %%, you can do:

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 CHAR and VARCHAR fields:

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

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 generate output that cannot be read properly by LOAD DATA INFILE. For example, the preceding output 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.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

For input, the ENCLOSED BY character, if present, is stripped from the ends of field values. (This is true 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. In addition, duplicated ENCLOSED BY characters occurring within fields are interpreted as single ENCLOSED BY characters if the field itself starts with that character. For example, if ENCLOSED BY '"' is specified, quotes are handled as shown here:

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

FIELDS ESCAPED BY controls how to write or read special characters. If the FIELDS ESCAPED BY character is not empty, it is used to prefix the following characters on output:

  • The FIELDS ESCAPED BY character.

  • The FIELDS [OPTIONALLY] ENCLOSED BY character.

  • The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values.

  • ASCII 0 (what is actually written following the escape character is ASCII '0', not a zero-valued byte).

If the FIELDS ESCAPED BY character is empty, no characters are escaped. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

For input, if the FIELDS ESCAPED BY character is not empty, occurrences of that character are stripped and the following character is taken literally as part of a field value. The exceptions are an escaped 0 or N (for example, \0 or \N if the escape character is \). These sequences are interpreted as ASCII 0 (a zero-valued byte) and NULL.

For more information about \-escape syntax, see Section 6.1.1.

In certain cases, field and line handling options interact:

  • If LINES TERMINATED BY is an empty string and FIELDS TERMINATED BY is non-empty, lines are also terminated with FIELDS TERMINATED BY.

  • If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty ("), a fixed-row (non-delimited) format is used. With fixed-row format, no delimiters are used between fields. Instead, column values are written and read using the “display” widths of the columns. For example, if a column is declared as INT(7), values for the column are written using 7-character fields. On input, values for the column are obtained by reading 7 characters. Fixed-row format also affects handling of NULL values. Note that fixed-size format will not work if you are using a multi-byte character set.

Handling of NULL values varies, depending on the FIELDS and LINES options you use:

  • For the default FIELDS and LINES values, NULL is written as \N for output and \N is read as NULL for input (assuming the ESCAPED BY character is \).

  • If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL as its value is read as a NULL value (this differs from the word NULL enclosed within FIELDS ENCLOSED BY characters, which is read as the string 'NULL').

  • If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.

  • With fixed-row format (which happens when FIELDS TERMINATED BY and FIELDS ENCLOSED BY are both empty), NULL is written as an empty string. Note that this causes both NULL values and empty strings in the table to be indistinguishable when written to the file because they are both written as empty strings. If you need to be able to tell the two apart when reading the file back in, you should not use fixed-row format.

Some cases are not supported by LOAD DATA INFILE:

  • Fixed-size rows (FIELDS TERMINATED BY and FIELDS ENCLOSED BY both empty) and BLOB or TEXT columns.

  • If you specify one separator that is the same as or a prefix of another, LOAD DATA INFILE won’t be able to interpret the input properly. For example, the following FIELDS clause would cause problems:

    FIELDS TERMINATED BY '"' ENCLOSED BY '"'

  • If FIELDS ESCAPED BY is empty, a field value that contains an occurrence of FIELDS ENCLOSED BY or LINES TERMINATED BY followed by the FIELDS TERMINATED BY value will cause LOAD DATA INFILE to stop reading a field or line too early. This happens because LOAD DATA INFILE cannot properly determine where the field or line value ends.

The following example loads all columns of the persondata table:

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

No field list is specified, so LOAD DATA INFILE expects input rows to contain a field for each table column. The default FIELDS and LINES values are used.

If you wish to load only some of a table’s columns, specify a field list:

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

You must also specify a field 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 up input fields with table columns.

If a row has too few fields, the columns for which no input field is present are set to default values. Default value assignment is described in Section 6.5.3.

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

  • For string types, the column is set to the empty string.

  • For numeric types, the column is set to 0.

  • For date and time types, the column is set to the appropriate “zero” value for the type. See Section 6.2.2.

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

TIMESTAMP columns are only set to the current date and time if there is a NULL value for the column, or (for the first TIMESTAMP column only) if the TIMESTAMP column is left out from the field list when a field list is specified.

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

LOAD DATA INFILE regards all input as strings, so you can’t 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!

If you are using the C API, you can get information about the query by calling the API function mysql_info( ) when the LOAD DATA INFILE query finishes. The format of the information string is shown here:

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

Warnings occur under the same circumstances as when values are inserted via the INSERT statement (see Section 6.4.3), 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 only be used as an indication if everything went well. If you get warnings and want to know exactly why you got them, one way to do this is to use SELECT ... INTO OUTFILE into another file and compare this to your original input file.

If you need LOAD DATA to read from a pipe, you can use the following trick:

mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
cat < /dev/tcp/10.1.1.12/4711 > /nt/mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

If you are using a version of MySQL older than 3.23.25 you can only do this with LOAD DATA LOCAL INFILE.

For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, see Section 5.2.9.

DO Syntax

DO expression, [expression, ...]

Execute the expression but don’t return any results. This is a shorthand of SELECT expression, expression, but has the advantage that it’s slightly faster when you don’t care about the result.

This is mainly useful with functions that have side effects, like RELEASE_LOCK.

Get MySQL Reference Manual now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial