Data Manipulation: `SELECT', `INSERT', `UPDATE', `DELETE'
=========================================================
`SELECT' Syntax
---------------
| 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]]
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. *Note 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 *Note
`JOIN': JOIN. 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'.
*Note:* `USE/IGNORE/FORCE INDEX' only affects which indexes are
used when MySQL decides how to find rows in the table and how to
do the join. It doesn't affect whether an index will be used when
resolving an `ORDER BY' or `GROUP BY'.
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 *Note Legal 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. *Note
Functions::.
* 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 sum>10;
* 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. *Note `EXPLAIN': EXPLAIN.
* `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()'. *Note 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). *Note LIMIT optimisation::.
* `SQL_CACHE' tells MySQL to store the query result in the
query cache if you are using `QUERY_CACHE_TYPE=2' (`DEMAND').
*Note 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. *Note 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. *Note Group by functions::.
* As of MySQL 4.1.1, `GROUP BY' allows a `WITH ROLLUP' modifier.
*Note `GROUP BY' Modifiers: GROUP-BY-Modifiers.
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'.
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. *Note `LOAD DATA': LOAD
DATA.
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;
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 *Note 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.
`JOIN' Syntax
.............
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)
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.
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;
is
declared `NOT NULL', of course. *Note LEFT JOIN optimisation::.
* 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
* `INNER JOIN' and `,' (comma) are semantically equivalent in the
absence of a join condition: both will produce a Cartesian product
between the specified tables (that is, 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.
`USE/IGNORE KEY' are synonyms for `USE/IGNORE INDEX'.
*Note:* `USE/IGNORE/FORCE INDEX' only affects which indexes are used
when MySQL decides how to find rows in the table and how to do the
join. It doesn't affect whether an index will be used when resolving an
`ORDER BY' or `GROUP BY'.
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;
*Note `LEFT JOIN' optimisation: LEFT JOIN optimisation.
`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'.
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
storage engine interface.
the
thread calls `HANDLER tbl_name CLOSE' or the thread dies.
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` > (...)'
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.
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' Syntax
---------------
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
*Note `CREATE TABLE': CREATE TABLE.
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.) *Note INSERT DELAYED::. Note that `LOW_PRIORITY' should
normally not be used with `MyISAM' tables as this disables
concurrent inserts. *Note MyISAM::.
* 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.
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;
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.
*Note `configure' options: configure options.
* You can find the value used for an `AUTO_INCREMENT' column with
the `mysql_insert_id' function. *Note `mysql_insert_id()':
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
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.
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 `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:
- Prior to MySQL 4.0.1, `INSERT ... SELECT' implicitly operates in
`IGNORE' mode. As of MySQL 4.0.1, you should specify `IGNORE'
explicitly to ignore records that would cause duplicate-key
violations.
- Prior to MySQL 4.0.14, the target table of the `INSERT' statement
cannot appear in the `FROM' clause of the `SELECT' part of the
query. This limitation is lifted in 4.0.14.
- `AUTO_INCREMENT' columns work as usual.
- In C programs, you can use the C API function `mysql_info()' to
get information about the query. *Note INSERT::.
- 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' 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 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'. *Note MyISAM::.
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' Syntax
---------------
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.
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' Syntax
---------------
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.
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.
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 *Note Delete speed::.
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 *Note `OPTIMIZE TABLE':
OPTIMIZE TABLE and *Note 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' Syntax
-----------------
TRUNCATE TABLE table_name
In 3.23 `TRUNCATE TABLE' is mapped to `COMMIT; DELETE FROM table_name'.
*Note DELETE::.
`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' Syntax
----------------
[(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. *Note `INSERT': INSERT.
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).
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 INFILE' Syntax
-------------------------
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.)
must have the
`FILE' privilege on the server host. *Note Privileges provided::.
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'. *Note 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.
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). *Note REPLACE::.
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'.
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. *Note Table maintenance::.
`LOAD DATA INFILE' is the complement of `SELECT ... INTO OUTFILE'.
*Note `SELECT': SELECT. 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);
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.
If the field begins with the `ENCLOSED BY' character, instances of that
character are recognized as terminating a field value only if followed
by the field or line `TERMINATED BY' sequence. To avoid ambiguity,
occurrences of the `ENCLOSED BY' character within a field value can be
doubled and will be interpreted as a single instance of the 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 *Note Literals::.
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'.
(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.
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 `\').
BY'
characters, which is read as the string `'NULL'').
* If `FIELDS ESCAPED BY' is empty, `NULL' is written as the word
`NULL'.
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,...);
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 *Note `CREATE TABLE': CREATE TABLE.
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. *Note 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
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. *Note SHOW WARNINGS::.
For more information about the efficiency of `INSERT' versus `LOAD DATA
INFILE' and speeding up `LOAD DATA INFILE', *Note Insert speed::.
`DO' Syntax
-----------
DO expression, [expression, ...]
This is mainly useful with functions that has side effects, like
`RELEASE_LOCK'.
[Назад] [Содержание] [Вперед]
| Главная |