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], ...
[WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] row_count | row_count OFFSET offset]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]
SELECT is used to retrieve rows selected from one or more tables. Each select_expression indicates a column 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 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.
A SELECT expression may be given an alias using AS alias_name. 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;
The AS keyword is optional when aliasing a SELECT expression. The preceding example could have been written like this:
mysql SELECT CONCAT(last_name,', ',first_name) full_name
FROM mytable ORDER BY full_name;
Because the AS is optional, a subtle problem can occur if you forget the comma between two SELECT expressions: MySQL will interpret the second as an alias name. For example, in the following statement, columnb is treated as an alias name:
mysql SELECT columna columnb FROM mytable;
It is not allowed to use a column alias in a WHERE clause, because the column value may not yet be determined when the WHERE clause is executed. Section A.5.4, “Problems with alias”.
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, “JOIN Syntax ”. For each table specified, you may optionally specify an alias.
table_name [[AS] alias] [[USE INDEX (key_list)] | [IGNORE INDEX (key_list)] | FORCE 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 from the list of possible indexes. By specifying USE INDEX (key_list), you can tell MySQL to use only one of the possible 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.
In MySQL 4.0.9 you can also use FORCE INDEX. This acts likes USE INDEX (key_list) but with the addition that a table scan is assumed to be VERY expensive. In other words a table scan will only be used if there is no way to use one of the given index to find rows in the table.
USE/IGNORE/FORCE KEY are synonyms for USE/IGNORE/FORCE INDEX.
In MySQL 4.0.14 you can use SET MAX_SEEKS_FOR_KEY=# as an alternative way to force MySQL to prefer key scans instead of table scans.
You can refer to a table as tbl_name (within the current database), or as dbname.tbl_name to explicitly specify a database. 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, “Database, Table, Index, Column, and Alias Names ”, for examples of ambiguity that require the more explicit column reference forms.
From version 4.1.0, you are allowed to specify DUAL as a dummy table name, in situations where no tables are referenced. This is purely compatibility feature, some other servers require this syntax.
mysql SELECT 1 + 1 FROM DUAL;
- 2
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.
In the WHERE clause, you can use any of the functions that MySQL supports, except for aggregate (summary) functions. Section 6.3, “Functions for Use in SELECT and WHERE Clauses ”.
The HAVING clause can refer to any column or alias named in the select_expression. It is applied nearly last, just before items are sent to the client, with no optimisation. (LIMIT is applied after HAVING.) 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 sum10;
The options DISTINCT, DISTINCTROW and ALL specify whether duplicate rows should be returned. The default is (ALL), all matching rows are returned. DISTINCT and DISTINCTROW are synonyms and specify that duplicate rows in the result set should be removed.
STRAIGHT_JOIN, HIGH_PRIORITY, and options beginning with SQL_ are MySQL extensions to SQL-99.
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. Section 5.2.1, “EXPLAIN Syntax (Get Information About a SELECT) ”.
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 there is an update statement that 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 forces the result to be put into a temporary table. This helps MySQL free the table locks early and helps 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 uses 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 (version 4.0.0 and up) tells MySQL to calculate how many rows there would be in the result set, disregarding any LIMIT clause. The number of rows can then be retrieved with SELECT FOUND_ROWS(). Section 6.3.6.2, “Miscellaneous Functions ”.
Please note that in versions prior to 4.1.0 this does not work with LIMIT 0, which is optimised to return instantly (resulting in a row count of 0). Section 5.2.9, “How MySQL Optimises LIMIT”.
SQL_CACHE tells MySQL to store the query result in the query cache if you are using QUERY_CACHE_TYPE=2 (DEMAND). Section 6.9, “MySQL Query Cache ”. In case of query with UNIONs and/or subqueries this option will take effect to be used in any SELECT of the query.
SQL_NO_CACHE tells MySQL not to store the query result in the query cache. Section 6.9, “MySQL Query Cache ”. In case of query with UNIONs and/or subqueries this option will take effect to be used in any SELECT of the query.
If you use GROUP BY, the output rows will be sorted according to the GROUP BY as if you had an ORDER BY over all the fields in the GROUP BY. MySQL has extended the GROUP BY clause so that you can also specify ASC and DESC after columns named in the clause:
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 that 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. Section 6.3.7, “Functions and Modifiers for Use with GROUP BY Clauses ”.
As of MySQL 4.1.1, GROUP BY allows a WITH ROLLUP modifier. Section 6.3.7.2, “GROUP BY Modifiers ”.
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be integer constants.
With one argument, the value specifies the number of rows to return from the beginning of the result set. With two arguments, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):
To be compatible with PostgreSQL MySQL also supports the syntax: LIMIT row_count OFFSET offset.
mysql SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15
To retrieve all rows from a certain offset up to the end of the result set, you can use -1 for the second parameter:
mysql SELECT * FROM table LIMIT 95,-1; # Retrieve rows 96-last.
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.
The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table on the server machine. If you want to create the resulting file on some other host 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. Section 6.4.9, “LOAD DATA INFILE Syntax ”.
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).
The reason for the above is that 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 follows 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 value in a file.
Note that any file created by INTO OUTFILE and INTO DUMPFILE will be writeable by all users on the server host! The reason is that the MySQL server can't create a file that is owned by anyone else than the user it's running as (you should never run mysqld as root). The file thus must be world-writeable so that you can manipulate its contents.
A PROCEDURE clause names a procedure that should process the data in the result set. For an example, see Section 12.3.1, “Procedure Analyse ”.
If you use FOR UPDATE on a storage engine with page or row locks, the examined rows are write locked until the end of the current transaction.
MySQL supports the following JOIN syntaxes for use in SELECT statements:
table_reference, table_reference
table_reference [INNER | CROSS] JOIN table_reference [join_condition]
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference [join_condition]
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 NATURAL [RIGHT [OUTER]] JOIN table_reference
Where table_reference is defined as:
table_name [[AS] alias] [[USE INDEX (key_list)] | [IGNORE INDEX (key_list)] | [FORCE INDEX (key_list)]]
and join_condition is defined as:
ON conditional_expr | USING (column_list)
You should generally not have any conditions in the ON part that are used to restrict which rows you want in the result set, but rather specify these conditions in the WHERE clause. There are exceptions to this rule.
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 list 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. Section 5.2.7, “How MySQL Optimises LEFT JOIN and RIGHT JOIN”.
The USING(column_list) clause names a list of columns that must exist in both tables. The following two clauses are semantically identical:
a LEFT JOIN b USING (c1,c2,c3) a LEFT JOIN b ON 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 in the absence of a join condition: both will produce a Cartesian product between the specified tables (i.e. each and every row in the first table will be joined onto all rows in the second table).
RIGHT JOIN works analogously as 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 from the list of possible indexes. By specifying USE INDEX (key_list), you can tell MySQL to use only one of the possible 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.
In MySQL 4.0.9 you can also use FORCE INDEX. This acts likes USE INDEX (key_list) but with the addition that a table scan is assumed to be VERY expensive. In other words a table scan will only be used if there is no way to use one of the given index to find rows in the table.
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;
Section 5.2.7, “How MySQL Optimises LEFT JOIN and RIGHT JOIN”.
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 columns listed in the select_expression portion of the SELECT should have the same type. The column names used in the first SELECT query will be used as the column names for the results returned.
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, then 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 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 storage engine interface.
The first form of 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 LIMIT clause) where the index specified complies to the condition and WHERE condition is met. If the index consists of several parts (spans over several columns) the values are specified in comma-separated list, providing values only for few first columns is possible.
The third form fetches one row (or more, specified by LIMIT clause) from the table in index order, matching WHERE condition.
The fourth form (without index specification) fetches one row (or more, specified by LIMIT clause) from the table in natural row order (as stored in datafile) matching 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.
Note: If you're using HANDLER interface for PRIMARY KEY you should remember to quote the keyword PRIMARY with backticks: HANDLER tbl READ `PRIMARY` (...)
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 any other thread) and these modifications may appear only partially in HANDLER ... NEXT or HANDLER ... PREV scans.
The reasons to use this interface instead of normal SQL are:
It's faster than SELECT because:
A designated storage engine is allocated for the thread in HANDLER OPEN.
There is less parsing involved.
No optimiser and no query checking overhead.
The used table doesn't have to be locked between two handler requests.
The handler interface doesn't have to provide a consistent look of the data (for example, dirty-reads are allowed), so the storage engine can do optimisations that SQL doesn't normally allow.
It makes it much easier to port applications that uses an ISAM like interface to MySQL.
It allows one to traverse a database in a manner that is not easy (in some cases impossible) to do with SQL. The handler interface is more natural way to look at data when working with applications that provide an interactive user interfaces to the database.
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ((expression | DEFAULT),...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=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 | DEFAULT), ...
[ ON DUPLICATE KEY UPDATE 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 in the table 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, “CREATE TABLE Syntax ”.
You can also use the keyword DEFAULT to set a column to its default value. (New in MySQL 4.0.3.) This makes it easier to write INSERT statements that assign values to all but a few columns, because it allows you to avoid writing an incomplete VALUES() list (a list that does not include a value for each column in the table). Otherwise, you would have to write out the list of column names corresponding to each value in the VALUES() list.
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 checking of fields content 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 DELAYED, the server puts the row or rows to be inserted into a buffer, and the client issuing the INSERT DELAYED statement then may continue on. If the table is busy, the server holds the rows. When the table becomes free, it begins inserting rows, checking periodically to see if there are new read requests for the table. If there are, the delayed row queue is suspended until the table becomes free again.
If you specify the keyword LOW_PRIORITY, execution of the INSERT is delayed until no other clients are reading from the table. This includes other clients that began reading while existing clients are reading, and while the INSERT LOW_PRIORITY statement is waiting. It is possible therefore for a client that issues an INSERT LOW_PRIORITY statement to wait for a very long time (or even forever) in a read-heavy environment. (This is in contrast to INSERT DELAYED, which lets the client continue at once.) Section 6.4.4, “INSERT DELAYED Syntax ”. Note that LOW_PRIORITY should normally not be used with MyISAM tables as this disables concurrent inserts. Section 7.1, “MyISAM Tables ”.
If you specify the keyword IGNORE in an INSERT with many 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 there is any row that duplicates an existing key value. You can determine with the C API function mysql_info() how many rows were inserted into the table.
If you specify ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and a row is inserted that would cause a duplicate value in a PRIMARY or UNIQUE key, an UPDATE of the old row is performed. For example, the command:
mysql INSERT INTO table (a,b,c) VALUES (1,2,3) -- ON DUPLICATE KEY UPDATE c=c+1;
in case of column a is declared as UNIQUE and already holds 1 once, would be identical to the
mysql UPDATE table SET c=c+1 WHERE a=1;
Note: that if column b is unique too, the UPDATE command would be written as
mysql UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
and if a=1 OR b=2 matches several rows, only one row will be updated! In general, one should try to avoid using ON DUPLICATE KEY clause on tables with multiple UNIQUE keys.
Since MySQL 4.1.1 one can use function VALUES(col_name) to refer to the column value in the INSERT part of the INSERT ... UPDATE command - that is the value that would be inserted if there would be no duplicate key conflict. This function especially useful in multiple-row inserts. Naturally VALUES() function is only meaningful in INSERT ... UPDATE command and returns NULL otherwise.
Example:
mysql INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) -- ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
The command above is identical to
mysql INSERT INTO table (a,b,c) VALUES (1,2,3) -- ON DUPLICATE KEY UPDATE c=3; mysql INSERT INTO table (a,b,c) VALUES (4,5,6) -- ON DUPLICATE KEY UPDATE c=9;
When one uses ON DUPLICATE KEY UPDATE, the DELAYED option is ignored.
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. Section 2.3.3, “Typical configure Options ”.
You can find the value used for an AUTO_INCREMENT column with the mysql_insert_id function. Section 9.1.3.31, “mysql_insert_id()”.
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 the default value appropriate for the column type. This is 0 for numeric types, the empty string (") for string types, and the "zero" value for date and time types.
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 at all, 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 [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ...
With 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. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using subquery clauses, the situation could easily be very confusing.)
AUTO_INCREMENT columns work as usual.
In C programs, you can use the C API function mysql_info() to get information about the query. Section 6.4.3, “INSERT Syntax ”.
To ensure that the binary log can be used to re-create the original tables, MySQL will not allow concurrent inserts during INSERT ... SELECT.
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.
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 SQL-92.
INSERT DELAYED only works with ISAM and MyISAM tables. Note that as MyISAM tables supports concurrent SELECT and INSERT, if there is no free blocks in the middle of the datafile, you very seldom need to use INSERT DELAYED with MyISAM. Section 7.1, “MyISAM Tables ”.
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 binary log is updated by the handler thread when the row is inserted into the table. In case of multiple-row inserts, the binary log is updated when the first row is inserted.
After every delayed_insert_limit rows are 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 the above means that INSERT DELAYED commands have higher priority than normal INSERT commands if there is an INSERT DELAYED handler 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 [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
or
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
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. If you update a column that has been declared NOT NULL by setting to NULL, the column is set to the default value appropriate for the column type and the warning count is incremented. The default value is is 0 for numeric types, the empty string (") for string types, and the "zero" value for date and time types.
Starting from MySQL version 3.23, you can use LIMIT row_count to ensure that only a given number of rows are changed. MySQL will stop the update as soon as it has found LIMIT rows that satisfies the WHERE clause, independent of the rows changed content or not.
If an ORDER BY clause is used (available from MySQL 4.0.0), the rows will be updated in that order. This is really only useful in conjunction with LIMIT.
Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables:
UPDATE items,month SET items.price=month.price WHERE items.id=month.id;
Note: you can not use ORDER BY or LIMIT with multi-table UPDATE.
DELETE [LOW_PRIORITY] [QUICK] FROM table_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
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. Section 6.4.7, “TRUNCATE Syntax ”. 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 10;
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.
For MyISAM tables, if you specify the word QUICK then the storage engine will not merge index leaves during delete, which may speed up certain kind of deletes.
The speed of delete operations may also be affected by factors discussed in Section 5.2.12, “Speed of DELETE Queries ”.
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, “OPTIMIZE TABLE Syntax ” and Section 4.4.6.10, “Table Optimisation ”.
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 above case we delete matching rows just from tables t1 and t2.
If an ORDER BY clause is used (available from MySQL 4.0.0), 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 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 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.
From MySQL 4.0, you can specify multiple tables in the DELETE statement to delete rows from one or more tables depending on a particular condition in multiple tables. However, you can not use ORDER BY or LIMIT in a multi-table DELETE.
TRUNCATE TABLE table_name
In 3.23 TRUNCATE TABLE is mapped to COMMIT ; DELETE FROM table_name. Section 6.4.6, “DELETE Syntax ”.
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 TABLE is an Oracle SQL extension. This statement was added in MySQL 3.23.28, although from 3.23.28 to 3.23.32, the keyword TABLE must be omitted.
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 or PRIMARY KEY, the old record is deleted before the new record is inserted. Section 6.4.3, “INSERT Syntax ”.
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.
To be able to use REPLACE you must have INSERT and DELETE privileges for the table.
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 after 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).
Note that unless the table has a UNIQUE index or PRIMARY KEY, using a REPLACE command makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.
Here follows the used algorithm in more detail: (This is also used with LOAD DATA ... REPLACE.
- Insert the row into the table
- While duplicate key error for primary or unique key
- Revert changed keys
- Read conflicting row from the table through the duplicate key value
- Delete conflicting row
- Try again to insert the original primary key and unique keys in the tree
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
[STARTING BY '']
[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, it is interpreted with respect to the client end of the connection. When LOCAL is specified, the file is read by the client program on the client host and sent to the server. If LOCAL is not specified, the file must be located on the server host and is read directly by 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. Section 4.2.7, “Privileges Provided by MySQL ”.
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. Section 4.2.4, “Security issues with LOAD DATA LOCAL”.
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, then 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 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.
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 datafiles by using the mysqlimport utility; it operates by sending a LOAD DATA INFILE command to the server. The -local option causes mysqlimport to read datafiles 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 named as ./myfile.txt is read from the server's data directory, whereas the same file named 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, input rows replace existing rows (in other words rows that has the same value for a primary or unique index as an existing row). Section 6.4.8, “REPLACE Syntax ”.
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, the behaviour depends on whether or not the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behaviour 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.
If you want to ignore foreign key constraints during load you can do SET FOREIGN_KEY_CHECKS=0 before executing LOAD DATA.
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. Normally this is very fast, but in some extreme cases you can create the indexes even faster by turning them off with ALTER TABLE .. DISABLE KEYS and use ALTER TABLE .. ENABLE KEYS to recreate the indexes. Section 4.4.6, “Using myisamchk for Table Maintenance and Crash Recovery ”.
LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. Section 6.4.1, “SELECT 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 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'
Note: If you have generated the text file on a Windows system you may have to change the above to: LINES TERMINATED BY '\r\n' as Windows uses two characters as a line terminator. Some programs, like wordpad, may use \r as a line terminator.
If all the lines you want to read in has a common prefix that you want to skip, you can use LINES STARTING BY prefix_string for this.
In other words, the defaults cause LOAD DATA INFILE to act as follows when reading input:
Look for line boundaries at newlines.
If LINES STARTING BY prefix is used, read until prefix is found and start reading at character after prefix. If line doesn't include prefix it will be skipped.
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 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:
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 statement shown here, 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 command shown here 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 a file of jokes, that are separated with a line of %%, into an SQL table 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 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.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. See below for the rules on NULL handling.
For more information about \-escape syntax, see Section 6.1.1, “Literals: How to Write Strings and Numbers ”.
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 (but you can still have a line terminator). 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.
LINES TERMINATED BY is still used to separate lines. If a line doesn't contain all fields, the rest of the fields will be set to their default values. If you don't have a line terminator, you should set this to ". In this case the text file must contain all fields for each row.
Fixed-row format also affects handling of NULL values; see below. 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, “CREATE TABLE Syntax ”.
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. Section 6.2.2, “Date and Time Types ”.
Note that 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 only set to the current date and time if there is a NULL value for the column (that is, \N), or (for the first TIMESTAMP column only) if the TIMESTAMP column is omitted 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. Note that before MySQL 4.1.1 the warnings is just a number to indicate that something went wrong. In MySQL 4.1.1 you can do SHOW WARNINGS to get more information for what went wrong.
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 (Section 6.4.3, “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 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 the above with LOAD DATA LOCAL INFILE.
In MySQL 4.1.1 you can use SHOW WARNINGS to get a list of the first max_error_count warnings. Section 4.5.7.9, “SHOW WARNINGS | ERRORS”.
For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, Section 5.2.10, “Speed of INSERT Queries ”.
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 has side effects, like RELEASE_LOCK.