7.11 DELETE
句法
DELETE [LOW_PRIORITY] FROM tbl_name [WHERE where_definition] [LIMIT rows]
DELETE
从tbl_name
表中删除满足由where_definition
给出的条件的行,并且返回删除记录的个数。
如果你发出一个没有WHERE
子句的DELETE
,所有行都被删除。MySQL通过创建一个空表来完成,它比删除每行要快。在这种情况下,DELETE
返回零作为受影响记录的数目。(MySQL不能返回实际上被删除的行数,因为进行再创建而不是打开数据文件。只要表定义文件“tbl_name.frm”是有效的,表才能这样被再创建,即使数据或索引文件破坏了)。
如果你确实想要知道在你正在删除所有行时究竟有对少记录被删除,并且愿意承受速度上的惩罚,你可以这种形式的一个ELETE
语句:
mysql> DELETE FROM tbl_name WHERE 1>0;
注意这比没有WHERE
子句的DELETE FROM tbl_name
慢的多了,因为它一次删除一行。
如果你指定关键词LOW_PRIORITY
,DELETE
的执行被推迟到没有其他客户读取表后。
删除的记录以一个链接表维持并且随后的INSERT
操作再次使用老的记录位置。为了回收闲置的空间并减小文件大小,使用OPTIMIZE TABLE
语句或myisamchk
实用程序重新组织表。OPTIMIZE TABLE
较容易,但是myisamchk
更快。见7.9 OPTIMIZE TABLE
句法和13.4.3 表优化。
MySQL对DELETE
特定的LIMIT rows
选项告诉服务器在控制被返回到客户之前,将要删除的最大行数,这可以用来保证一个特定DELETE
命令不会花太多的时间。你可以简单地重复DELETE
命令直到受影响的行数小于LIMIT
值。
7.12 SELECT
句法
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL] select_expression,... [INTO {OUTFILE | DUMPFILE} 'file_name' export_options] [FROM table_references [WHERE where_definition] [GROUP BY col_name,...] [HAVING where_definition] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...] [LIMIT [offset,] rows] [PROCEDURE procedure_name] ]
SELECT
被用来检索从一个或多个表中精选的行。select_expression
指出你想要检索的列。SELECT
也可以用来检索不引用任何表的计算行。例如:
mysql> SELECT 1 + 1; -> 2
所有使用的关键词必须精确地以上面的顺序给出。例如,一个HAVING
子句必须跟在GROUP BY
子句之后和ORDER BY
子句之前。
- 一个
SELECT
表达式可以用一个AS
给定一个别名,别名被用作表达式的列名并且能使用在ORDER BY
或HAVING
子句中。例如:mysql> select concat(last_name,', ',first_name) AS full_name from mytable ORDER BY full_name;
-
FROM table_references
子句指出从哪个表中检索行。如果你命名多个表,你正在执行一个联结(join)。对于联结的句法信息,见7.13JOIN
句法。 - 你可以引用一个列为
col_name
、tbl_name.col_name
或db_name.tbl_name.col_name
,你不必在一个SELECT
语句中指定一个tbl_name
或db_name.tbl_name
是一个列引用的前缀,除非引用有二义性。见7.1.5 数据库、表、索引、列和别名命名。对于二义性的例子要求更加显式的列引用格式。 - 一个表引用可以使用
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;
- 精选输出的列可以用列名、列别名或列位置在
ORDER BY
和GROUP BY
子句引用,列位置从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;
为了以降序排列,把
DESC
(下降 )关键词加到ORDER BY
子句中你要排序的列名前。缺省是升序;这也可以用ASC
关键词明确指定。 -
HAVING
子句能引用任何列或在select_expression
中命名的别名,它最后运用,就在项目被送到客户之前,没有优化。不要对因该在WHERE
子句中的项目使用HAVING
。例如,不能写成这样:mysql> select col_name from tbl_name HAVING col_name > 0;
相反写成这样:
mysql> select col_name from tbl_name WHERE col_name > 0;
在MySQL 3.22.5或以后,你也能这样写查询:
mysql> select user,max(salary) from users group by user HAVING max(salary)>10;
在里面更老的MySQL版本中,你能这样写:
mysql> select user,max(salary) AS sum from users group by user HAVING sum>10;
-
SQL_SMALL_RESULT
、SQL_BIG_RESULT
、STRAIGHT_JOIN
和HIGH_PRIORITY
是MySQL对ANSI SQL92的扩展。 -
STRAIGHT_JOIN
强制优化器以其列在FROM
子句的次序联结(join)桌子。如果优化器以非最佳次序联结表,你能使用它加速查询。见7.22EXPLAIN
句法(得到关于SELECT
的信息)。 -
SQL_SMALL_RESULT
能与GROUP BY
或DISTINCT
一起使用告诉优化器结果集将很小。在这种情况下,MySQL将使用快速临时表存储最终的表而不是使用排序。SQL_SMALL_RESULT
是一个MySQL扩展。 -
SQL_BIG_RESULT
能与GROUP BY
或DISTINCT
一起使用以告诉优化器结果集合将有很多行。在这种情况下,如果需要,MySQL将直接使用基于磁盘的临时表。MySQL
在这种情况下将选择用GROUP BY
单元上的键值进行排序而不是做一个临时表。 -
HIGH_PRIORITY
将赋予SELECT
比一个更新表的语句更高的优先级,你应该仅对非常快的并且必须一次完成的查询使用它。 如果表为读而锁定或甚至有一个等待表释放的更新语句,一个SELECT HIGH_PRIORITY
将运行。 -
LIMIT
子句可以被用来限制SELECT
语句返回的行数。LIMIT
取1个或2个数字参数,如果给定2个参数,第一个指定要返回的第一行的偏移量,第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15
如果给定一个参数,它指出返回行的最大数目。
mysql> select * from table LIMIT 5; # Retrieve first 5 rows
换句话说,
LIMIT n
等价于LIMIT 0,n
。 -
SELECT ... INTO OUTFILE 'file_name'
格式的SELECT
语句将选择的行写入一个文件。文件在服务器主机上被创建,并且不能是已经存在的(不管别的,这可阻止数据库表和文件例如“/etc/passwd”被破坏)。在服务器主机上你必须有file权限以使用这种SELECT
。SELECT ... INTO OUTFILE
是LOAD DATA INFILE
逆操作;语句的export_options
部分的语法与用在LOAD DATA INFILE
语句中的FIELDS
和LINES
子句的相同。见7.16LOAD DATA INFILE
句法。在最终的文本文件中,只有下列字符由ESCAPED BY
字符转义:-
ESCAPED BY
字符 - 在
FIELDS TERMINATED BY
中的第一个字符 - 在
LINES TERMINATED BY
中的第一个字符
另外,
ASCII 0
被变换到ESCAPED BY
后跟0(ASCII 48
)。上述的原因是你必须转义任何FIELDS TERMINATED BY
、ESCAPED BY
或LINES TERMINATED BY
字符以便能可靠地能读回文件。ASCII 0
被转义使它更容易用分页器观看。因为最终的文件不必须遵循SQL句法,没有别的东西需要转义。 -
如果你使用INTO DUMPFILE
而不是INTO OUTFILE
,MySQL将只写一行到文件中,没有任何列或行结束并且没有任何转义。如果你想要在一个文件存储一个blob,这是很有用的。
7.13 JOIN
句法
MySQL支持下列用于SELECT
语句的JOIN
句法:
table_reference, table_reference table_reference [CROSS] JOIN table_reference table_reference INNER JOIN table_reference table_reference STRAIGHT_JOIN table_reference table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr table_reference LEFT [OUTER] JOIN table_reference USING (column_list) table_reference NATURAL LEFT [OUTER] JOIN table_reference { oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
上述最后的LEFT OUTER JOIN
的句法只是为了与ODBC兼容而存在的。
- 一个表可以是使用aliased
tbl_name AS alias_name
或tbl_name alias_name
的起的别名。mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
-
INNER JOIN
和,
(逗号)在语义上是等价的,都是进行一个在使用的表之间的全联结。通常,你指定表应该如何用WHERE
条件联结起来。 -
ON
条件是可以用在一个WHERE
子句形式的任何条件。 - 如果在一个
LEFT JOIN
中没有右表的匹配记录,一个所有列设置为NULL
的行被用于右表。你可以使用这个事实指出表中在另一个表中没有对应记录的记录:mysql> select table1.* from table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id is NULL;
这个例子找出在
table1
中所有的行,其id
值在table2
中不存在(即,所有table1
中的在table2
中没有对应行的行)。当然这假定table2.id
被声明为NOT NULL
。 -
USING
(column_list)
子句命名一系列必须存在于两个表中的列。 例如一个USING
子句:A LEFT JOIN B USING (C1,C2,C3,...)
被定义成在语义上等同一个这样的
ON
表达式:A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...
- 2个表的
NATURAL LEFT JOIN
被定义为在语义上等同于一个有USING
子句命名在两表中存在的所有列的一个LEFT JOIN
。 -
STRAIGHT_JOIN
等同于JOIN
,除了左表在右表之前被读入,这能用于这些情况,联结优化器将表的顺序放错了。
一些例子:
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;
见10.5.4 MySQL怎样优化LEFT JOIN
。
7.14 INSERT
句法
INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES (expression,...),(...),... 或 INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... 或 INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name SET col_name=expression, col_name=expression, ...
INSERT
把新行插入到一个存在的表中,INSERT ... VALUES
形式的语句基于明确指定的值插入行,INSERT ... SELECT
形式插入从其他表选择的行,有多个值表的INSERT ... VALUES
的形式在MySQL 3.22.5或以后版本中支持,col_name=expression
语法在MySQL 3.22.10或以后版本中支持。
tbl_name
是行应该被插入其中的表。列名表或SET
子句指出语句为那一列指定值。
- 如果你为
INSERT ... VALUES
或INSERT ... SELECT
不指定列表,所有列的值必须在VALUES()
表或由SELECT
提供。如果你不知道表中列的顺序,使用DESCRIBE tbl_name
来找出。 - 任何没有明确地给出值的列被设置为它的缺省值。例如,如果你指定一个列表并没命名表中所有列,未命名的列被设置为它们的缺省值。缺省值赋值在7.7
CREATE TABLE
句法中描述。 - 一个
expression
可以引用在一个值表先前设置的任何列。例如,你能这样:mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
但不能这样:
mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
- 如果你指定关键词
LOW_PRIORITY
,INSERT
的执行被推迟到没有其他客户正在读取表。在这种情况下,客户必须等到插入语句完成后,如果表频繁使用,它可能花很长时间。这与INSERT DELAYED
让客马上继续正好相反。 - 如果你在一个有许多值行的
INSERT
中指定关键词IGNORE
,表中任何复制一个现有PRIMARY
或UNIQUE
键的行被忽略并且不被插入。如果你不指定IGNORE
,插入如果有任何复制现有关键值的行被放弃。你可用C API函数mysql_info()
检查多少行被插入到表中。 - 如果MySQL用
DONT_USE_DEFAULT_FIELDS
选项配置,INSERT
语句产生一个错误,除非你明确对需要一个非NULL
值的所有列指定值。见4.7.3 典型configure
选项。 -
INSERT INTO ... SELECT
语句满足下列条件:- 查询不能包含一个
ORDER BY
子句。 -
INSERT
语句的目的表不能出现在SELECT
查询部分的FROM
子句,因为这在ANSI SQL中被禁止让从你正在插入的表中SELECT
。(问题是SELECT
将可能发现在同一个运行期间内先前被插入的记录。当使用子选择子句时,情况能很容易混淆) -
AUTO_INCREMENT
列象往常一样工作。
- 查询不能包含一个
如果你使用INSERT ... SELECT
或INSERT ... VALUES
语句有多个值列表,你可以使用C API函数mysql_info()
得到查询的信息。信息字符串的格式如下:
Records: 100 Duplicates: 0 Warnings: 0
Duplicates
指出不能被插入的行的数量,因为他们与现有的唯一的索引值重复。Warnings
指出在出现某些问题时尝试插入列值的次数。在下列任何条件下都可能发生错误:
- 插入
NULL
到被声明了NOT NULL
的列,列被设置为它的缺省值。 - 将超出列范围的值设置给一个数字列,值被剪切为范围内适当的端点值。
- 将数字列设成例如
'10.34 a'
的值,拖尾的垃圾被剥去并仍然是数字部分被插入。如果值根本不是一个数字,列被设置到0
。 - 把一个字符串插入到超过列的最大长度的一个
CHAR
、VARCHAR
、TEXT
或BLOB
列中。值被截断为列的最大长度。 - 把一个对列类型不合法的值插入到一个日期或时间列。列被设置为该列类型适当的“零”值。
对于INSERT
语句的DELAYED
选项是MySQL专属的选项-如果你客户有不能等到INSERT
完成,它是很有用的。当你为日记登录使用MySQL时,而且你也周期性地运行花很长时间完成的SELECT
语句,这是一个常见的问题。DELAYED
在面MySQL 3.22.15中被引入,它是MySQL对 ANSI SQL92 的一个扩展。
当你使用INSERT DELAYED
时,客户将马上准备好,并且当表不被任何其他的线程使用时,行将被插入。
另一个使用INSERT DELAYED
的主要好处是从很多客户插入被捆绑在一起并且写进一个块。这比做很多单独的插入要来的快。
注意,当前排队的行只是存储在内存中,直到他们被插入到表中。这意味着,如果你硬要杀死mysqld
(kill -9
)或如果mysqld
出人意料地死掉,没被写进磁盘的任何排队的行被丢失!
下列详细描述当你为INSERT
或REPLACE
使用DELAYED
选项时,发生什么。在这个描述中,“线程”是收到一个INSERT DELAYED
命令的线程并且“处理器”是处理所有对于一个特定表的INSERT DELAYED
语句。
- 当一个线程对一个表执行一个
DELAYED
语句时,如果不存在这样的处理程序,一个处理器线程被创建以处理对于该表的所有DELAYED
语句。 - 线程检查处理程序是否已经获得了一个
DELAYED
锁;如果没有,它告诉处理程序去获得。即使其他的线程有在表上的一个READ
或WRITE
锁,也能获得DELAYED
锁。然而,处理程序将等待所有ALTER TABLE
锁或FLUSH TABLES
以保证表结构是最新的。 - 线程执行
INSERT
语句,但不是将行写入表,它把最后一行的副本放进被处理器线程管理的一个队列。任何语法错误都能被线程发觉并报告给客户程序。 - 顾客不能报告结果行的重复次数或
AUTO_INCREMENT
值;它不能从服务器获得它们,因为INSERT
在插入操作完成前返回。如果你使用C API,同样原因,mysql_info()
函数不返回任何有意义的东西。 - 当行被插入到表中时,更新日志有处理器线程更新。在多行插入的情况下,当第一行被插入时,更新日志被更新。
- 在每写入
delayed_insert_limit
行后,处理器检查是否任何SELECT
语句仍然是未完成,如果这样,在继续之前允许执行这些语句。 - 当处理器在它的队列中没有更多行时,表被解锁。如果在
delayed_insert_timeout
秒内没有收到新的INSERT DELAYED
命令,处理器终止。 - 如果已经有多于
delayed_queue_size
行在一个特定的处理器队列中未解决,线程等待直到队列有空间。这有助于保证mysqld
服务器对延迟的内存队列不使用所有内存。 - 处理器线程将在
Command
列的MySQL进程表中显示delayed_insert
。如果你执行一个FLUSH TABLES
命令或以KILL thread_id
杀死它,它将被杀死,然而,它在退出前首先将所有排队的行存进表中。在这期间,这次它将不从其他线程接受任何新的INSERT
命令。如果你在它之后执行一个INSERT DELAYED
,将创建一个新的处理器线程。 - 注意,上述意味着,如果有一个
INSERT DELAYED
处理器已经运行,INSERT DELAYED
命令有比正常INSERT
更高的优先级!其他更新命令将必须等到INSERT DELAY
排队变空、杀死处理器线程(用KILL thread_id
)或执行FLUSH TABLES
。 - 下列状态变量提供了关于
INSERT DELAYED
命令的信息:Delayed_insert_threads
处理器线程数量 Delayed_writes
用 INSERT DELAYED
被写入的行的数量Not_flushed_delayed_rows
等待被写入的行数字 你能通过发出一个
SHOW STATUS
语句或通过执行一个mysqladmin extended-status
命令察看这些变量。
注意如果桌子不在使用,INSERT DELAYED
比一个正常的INSERT
慢。对服务器也有额外开销来处理你对它使用INSERT DELAYED
的每个表的一个单独线程。这意味着,你应该只在你确实肯定需要它的时候才使用INSERT DELAYED
!
7.15 REPLACE
句法
REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] VALUES (expression,...) 或 REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] SELECT ... 或 REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name=expression, col_name=expression,...
REPLACE
功能与INSERT
完全一样,除了如果在表中的一个老记录具有在一个唯一索引上的新记录有相同的值,在新记录被插入之前,老记录被删除。见7.14 INSERT
句法。
7.16 LOAD DATA INFILE
句法
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] [OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\\' ]] [LINES TERMINATED BY '\n'] [IGNORE number LINES] [(col_name,...)]
LOAD DATA INFILE
语句从一个文本文件中以很高的速度读入一个表中。如果指定LOCAL
关键词,从客户主机读文件。如果LOCAL
没指定,文件必须位于服务器上。(LOCAL
在MySQL3.22.6或以后版本中可用。)
为了安全原因,当读取位于服务器上的文本文件时,文件必须处于数据库目录或可被所有人读取。另外,为了对服务器上文件使用LOAD DATA INFILE
,在服务器主机上你必须有file的权限。见6.5 由MySQL提供的权限。
如果你指定关键词LOW_PRIORITY
,LOAD DATA
语句的执行被推迟到没有其他客户读取表后。
使用LOCAL
将比让服务器直接存取文件慢些,因为文件的内容必须从客户主机传送到服务器主机。在另一方面,你不需要file权限装载本地文件。
你也可以使用mysqlimport
实用程序装载数据文件;它由发送一个LOAD DATA INFILE
命令到服务器来运作。 --local
选项使得mysqlimport
从客户主机上读取数据。如果客户和服务器支持压缩协议,你能指定--compress
在较慢的网络上获得更好的性能。
当在服务器主机上寻找文件时,服务器使用下列规则:
- 如果给出一个绝对路径名,服务器使用该路径名。
- 如果给出一个有一个或多个前置部件的相对路径名,服务器相对服务器的数据目录搜索文件。
- 如果给出一个没有前置部件的一个文件名,服务器在当前数据库的数据库目录寻找文件。
注意这些规则意味着一个像“./myfile.txt”给出的文件是从服务器的数据目录读取,而作为“myfile.txt”给出的一个文件是从当前数据库的数据库目录下读取。也要注意,对于下列哪些语句,对db1
文件从数据库目录读取,而不是db2
:
mysql> USE db1; mysql> LOAD DATA INFILE "./data.txt" INTO TABLE db2.my_table;
REPLACE
和IGNORE
关键词控制对现有的唯一键记录的重复的处理。如果你指定REPLACE
,新行将代替有相同的唯一键值的现有行。如果你指定IGNORE
,跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项,当找到重复键键时,出现一个错误,并且文本文件的余下部分被忽略时。
如果你使用LOCAL
关键词从一个本地文件装载数据,服务器没有办法在操作的当中停止文件的传输,因此缺省的行为好像IGNORE
被指定一样。
LOAD DATA INFILE
是SELECT ... INTO OUTFILE
的逆操作,见7.12 SELECT
句法。为了将一个数据库的数据写入一个文件,使用SELECT ... INTO OUTFILE
,为了将文件读回数据库,使用LOAD DATA INFILE
。两个命令的FIELDS
和LINES
子句的语法是相同的。两个子句是可选的,但是如果指定两个,FIELDS
必须在LINES
之前。
如果你指定一个FIELDS
子句,它的每一个子句(TERMINATED BY
, [OPTIONALLY] ENCLOSED BY
和ESCAPED BY
)也是可选的,除了你必须至少指定他们之一。
如果你不指定一个FIELDS
子句,缺省值与如果你这样写的相同:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
如果你不指定一个LINES
子句,缺省值与如果你这样写的相同:
LINES TERMINATED BY '\n'
换句话说,缺省值导致读取输入时,LOAD DATA INFILE
表现如下:
- 在换行符处寻找行边界
- 在定位符处将行分进字段
- 不要期望字段由任何引号字符封装
- 将由“\”开头的定位符、换行符或“\”解释是字段值的部分字面字符
相反,缺省值导致在写入输出时,SELECT ... INTO OUTFILE
表现如下:
- 在字段之间写定位符
- 不用任何引号字符封装字段
- 使用“\”转义出现在字段中的定位符、换行符或“\”字符
- 在行尾处写换行符
注意,为了写入FIELDS ESCAPED BY '\\'
,对作为一条单个的反斜线被读取的值,你必须指定2条反斜线值。
IGNORE number LINES
选项可被用来忽略在文件开始的一个列名字的头:
mysql> LOAD DATA INFILE "/tmp/file_name" into table test IGNORE 1 LINES;
当你与LOAD DATA INFILE
一起使用SELECT ... INTO OUTFILE
将一个数据库的数据写进一个文件并且随后马上将文件读回数据库时,两个命令的字段和处理选项必须匹配,否则,LOAD DATA INFILE
将不能正确解释文件的内容。假定你使用SELECT ... INTO OUTFILE
将由逗号分隔的字段写入一个文件:
mysql> SELECT * FROM table1 INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM ...
为了将由逗号分隔的文件读回来,正确的语句将是:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY ',';
相反,如果你试图用下面显示的语句读取文件,它不会工作,因为它命令LOAD DATA INFILE
在字段之间寻找定位符:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY '\t';
可能的结果是每个输入行将被解释为单个的字段。
LOAD DATA INFILE
能被用来读取从外部来源获得的文件。例如,以dBASE格式的文件将有由逗号分隔并用双引号包围的字段。如果文件中的行由换行符终止,下面显示的命令说明你将用来装载文件的字段和行处理选项:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
任何字段或行处理选项可以指定一个空字符串(''
)。如果不是空,FIELDS [OPTIONALLY] ENCLOSED BY
和FIELDS ESCAPED BY
值必须是一个单个字符。FIELDS TERMINATED BY
和LINES TERMINATED BY
值可以是超过一个字符。例如,写入由回车换行符对(CR+LF)终止的行,或读取包含这样行的一个文件,指定一个LINES TERMINATED BY '\r\n'
子句。
FIELDS [OPTIONALLY] ENCLOSED BY
控制字段的包围字符。对于输出(SELECT ... INTO OUTFILE
),如果你省略OPTIONALLY
,所有的字段由ENCLOSED BY
字符包围。对于这样的输出的一个例子(使用一个逗号作为字段分隔符)显示在下面:
"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"
如果你指定OPTIONALLY
,ENCLOSED BY
字符仅被用于包围CHAR
和VARCHAR
字段:
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
注意,一个字段值中的ENCLOSED BY
字符的出现通过用ESCAPED BY
字符作为其前缀来转义。也要注意,如果你指定一个空ESCAPED BY
值,可能产生不能被LOAD DATA INFILE
正确读出的输出。例如,如果转义字符为空,上面显示的输出显示如下。注意到在第四行的第二个字段包含跟随引号的一个逗号,它(错误地)好象要终止字段:
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
对于输入,ENCLOSED BY
字符如果存在,它从字段值的尾部被剥去。(不管是否指定OPTIONALLY
都是这样;OPTIONALLY
对于输入解释不起作用)由ENCLOSED BY
字符领先的ESCAPED BY
字符出现被解释为当前字段值的一部分。另外,出现在字段中重复的ENCLOSED BY
被解释为单个ENCLOSED BY
字符,如果字段本身以该字符开始。例如,如果ENCLOSED BY '"'
被指定,引号如下处理:
"The ""BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss The ""BIG"" boss -> The ""BIG"" boss
FIELDS ESCAPED BY
控制如何写入或读出特殊字符。如果FIELDS ESCAPED BY
字符不是空的,它被用于前缀在输出上的下列字符:
-
FIELDS ESCAPED BY
字符 -
FIELDS [OPTIONALLY] ENCLOSED BY
字符 -
FIELDS TERMINATED BY
和LINES TERMINATED BY
值的第一个字符 - ASCII
0
(实际上将后续转义字符写成 ASCII'0'
,而不是一个零值字节)
如果FIELDS ESCAPED BY
字符是空的,没有字符被转义。指定一个空转义字符可能不是一个好主意,特别是如果在你数据中的字段值包含刚才给出的表中的任何字符。
对于输入,如果FIELDS ESCAPED BY
字符不是空的,该字符的出现被剥去并且后续字符在字面上作为字段值的一个部分。例外是一个转义的“0”或“N”(即,\0
或\N
,如果转义字符是“\”)。这些序列被解释为ASCII 0
(一个零值字节)和NULL
。见下面关于NULL
处理的规则。
对于更多关于“\”- 转义句法的信息,见7.1 文字:怎样写字符串和数字。
在某些情况下,字段和行处理选项相互作用:
- 如果
LINES TERMINATED BY
是一个空字符串并且FIELDS TERMINATED BY
是非空的,行也用FIELDS TERMINATED BY
终止。 - 如果
FIELDS TERMINATED BY
和FIELDS ENCLOSED BY
值都是空的(''
),一个固定行(非限定的)格式被使用。用固定行格式,在字段之间不使用分隔符。相反,列值只用列的“显示”宽度被写入和读出。例如,如果列被声明为INT(7)
,列的值使用7个字符的字段被写入。对于输入,列值通过读取7个字符获得。固定行格式也影响NULL
值的处理;见下面。注意如果你正在使用一个多字节字符集,固定长度格式将不工作。
NULL
值的处理有多种,取决于你使用的FIELDS
和LINES
选项:
- 对于缺省
FIELDS
和LINES
值,对输出,NULL
被写成\N
,对输入,\N
被作为NULL
读入(假定ESCAPED BY
字符是“\”)。 - 如果
FIELDS ENCLOSED BY
不是空的,包含以文字词的NULL
作为它的值的字段作为一个NULL
值被读入(这不同于包围在FIELDS ENCLOSED BY
字符中的字NULL
,它作为字符串'NULL'
读入)。 - 如果
FIELDS ESCAPED BY
是空的,NULL
作为字NULL
被写入。 - 用固定行格式(它发生在
FIELDS TERMINATED BY
和FIELDS ENCLOSED BY
都是空的时候),NULL
作为一个空字符串被写入。注意,在写入文件时,这导致NULL
和空字符串在表中不能区分,因为他们都作为空字符串被写入。如果在读回文件时需要能区分这两者,你应该不使用固定行格式。
一些不被LOAD DATA INFILE
支持的情况:
- 固定长度的行(
FIELDS TERMINATED BY
和FIELDS ENCLOSED BY
都为空)和BLOB
或TEXT
列。 - 如果你指定一个分隔符与另一个相同,或是另一个的前缀,
LOAD DATA INFILE
不能正确地解释输入。例如,下列FIELDS
子句将导致问题:FIELDS TERMINATED BY '"' ENCLOSED BY '"'
- 如果
FIELDS ESCAPED BY
是空的,一个包含跟随FIELDS TERMINATED BY
值之后的FIELDS ENCLOSED BY
或LINES TERMINATED BY
的字段值将使得LOAD DATA INFILE
过早地终止读取一个字段或行。这是因为LOAD DATA INFILE
不能正确地决定字段或行值在哪儿结束。
下列例子装载所有persondata
表的行:
mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
没有指定字段表,所以LOAD DATA INFILE
期望输入行对每个表列包含一个字段。使用缺省FIELDS
和LINES
值。
如果你希望仅仅装载一张表的某些列,指定一个字段表:
mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
如果在输入文件中的字段顺序不同于表中列的顺序,你也必须指定一个字段表。否则,MySQL不能知道如何匹配输入字段和表中的列。
如果一个行有很少的字段,对于不存在输入字段的列被设置为缺省值。缺省值赋值在7.7 CREATE TABLE
句法中描述。
如果字段值缺省,空字段值有不同的解释:
- 对于字符串类型,列被设置为空字符串。
- 对于数字类型,列被设置为
0
。 - 对于日期和时间类型,列被设置为该类型的适当“零”值。见7.3.6 日期和时间类型。
如果列有一个NULL
,或(只对第一个TIMESTAMP
列)在指定一个字段表时,如果TIMESTAMP
列从字段表省掉,TIMESTAMP
列只被设置为当前的日期和时间。
如果输入行有太多的字段,多余的字段被忽略并且警告数字加1。
LOAD DATA INFILE
认为所有的输入是字符串,因此你不能像你能用INSERT
语句的ENUM
或SET
列的方式使用数字值。所有的ENUM
和SET
值必须作为字符串被指定!
如果你正在使用C API,当LOAD DATA INFILE
查询完成时,你可通过调用API函数mysql_info()
得到有关查询的信息。信息字符串的格式显示在下面:
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
当值通过INSERT
语句插入时,在某些情况下出现警告(见7.14 INSERT
句法),除了在输入行中有太少或太多的字段时,LOAD DATA INFILE
也产生警告。警告没被存储在任何地方;警告数字仅能用于表明一切是否顺利。如果你得到警告并且想要确切知道你为什么得到他们,一个方法是使用SELECT ... INTO OUTFILE
到另外一个文件并且把它与你的原版输入文件比较。
对于有关INSERT
相对LOAD DATA INFILE
的效率和加快LOAD DATA INFILE
的更多信息,见10.5.6 加速INSERT
查询。