参考官方文档：

https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-data-manipulation.html

13.2.1 CALL 语法

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

CALL sp_name[()]

CALL语句调用之前使用CREATE PROCEDURE定义的存储过程。

没有参数的存储过程可以在没有括号的情况下调用。也就是说，CALL p（）和CALL p是等价的。

CALL可以使用声明为OUT或INOUT参数的参数将值传回给调用者。当过程返回时，客户端程序还可以获取在例程中执行的最终语句所影响的行数：在SQL级别，调用ROW_COUNT（）函数; 从C API调用mysql_affected_rows（）函数。

要使用OUT或INOUT参数从过程中获取值，请通过用户变量传递参数，然后在过程返回后检查变量的值。（如果要从另一个存储过程或函数内调用该过程，则还可以将例程参数或本地例程变量作为IN或INOUT参数传递。）对于INOUT参数，在将其传递给过程之前对其进行初始化。以下过程具有过程设置为当前服务器版本的OUT参数以及该过程从当前值增加1的INOUT值：

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_paramINT)

BEGIN

# Set value of OUT parameter

SELECT VERSION() INTO ver_param;# Increment value of INOUT parameter

SET incr_param = incr_param + 1;

END;

在调用过程之前，将要传递的变量初始化为INOUT参数。调用该过程后，两个变量的值将被设置或修改：

mysql> SET @increment = 10;

mysql> CALL p(@version, @increment);

mysql> SELECT @version, @increment;

+------------------+------------+

| @version | @increment |

+------------------+------------+

| 5.7.20-debug-log | 11 |

+------------------+------------+

在使用PREPARE和EXECUTE准备好的CALL语句中，占位符可用于IN参数，OUT和INOUT参数。这些类型的参数可以使用如下：

mysql> SET @increment = 10;

mysql> PREPARE s FROM 'CALL p(?, ?)';

mysql> EXECUTE s USING @version, @increment;

mysql> SELECT @version, @increment;

+------------------+------------+

| @version | @increment |

+------------------+------------+

| 5.7.20-debug-log | 11 |

+------------------+------------+

13.2.2 DELETE 语法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[PARTITION (partition_name [, partition_name] ...)]

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

DELETE语句删除tbl_name中的行并返回已删除行的数量。要检查删除的行数，请调用第12.14节“信息函数”中介绍的ROW_COUNT（）函数。

主要的子句有 where，order by 和limit

InnoDB 表

如果您从大表中删除多行，您可能会超过InnoDB表的锁表大小。为了避免这个问题，或者只是为了最小化表保持锁定的时间，以下策略（根本不使用DELETE）可能会有所帮助：

1.选择不要删除的行到与原始表具有相同结构的空表中：

INSERT INTO t_copy SELECT * FROM t WHERE ... ;

2.使用RENAME TABLE以原子方式移动原始表并将副本重命名为原始名称：

RENAME TABLE t TO t_old, t_copy TO t;

Drop 原始表：

DROP TABLE t_old;

执行RENAME TABLE时，没有其他会话可以访问涉及的表，因此重命名操作不受并发性问题的影响。

13.2.3 DO 语法

DO expr [, expr] ...

DO执行表达式但不返回任何结果。在大多数方面，DO都是SELECT expr的缩写，...但有一个好处，就是当你不关心结果时它会稍微快一点。

DO主要用于具有副作用的函数，如RELEASE_LOCK（）。

例子：这个SELECT语句暂停，但也产生一个结果集：

mysql> SELECT SLEEP(5);

+----------+

| SLEEP(5) |

+----------+

| 0 |

+----------+

1 row in set (5.02 sec)

另一方面，DO会暂停而不产生结果集。

mysql> DO SLEEP(5);

Query OK, 0 rows affected (4.99 sec)

这可能是有用的，例如在存储函数或触发器中，这会禁止生成结果集的语句。

DO只执行表达式。它不能用于所有可以使用SELECT的情况。例如，DO id FROM t1是无效的，因为它引用了一个表。

13.2.5 INSERT 语法

INSERT将新行插入到现有表中。 INSERT ... VALUES和INSERT ... SET语句根据明确指定的值插入行。 INSERT ... SELECT 插入从另一个或多个表中选择的行。如果要插入的行在UNIQUE索引或PRIMARY KEY中会导致重复值，则使用ON DUPLICATE KEY UPDATE子句插入可以更新现有行。

插入表需要表的INSERT特权。如果使用ON DUPLICATE KEY UPDATE子句，并且重复键导致执行UPDATE，则该语句需要UPDATE特权才能更新列。对于已读但尚未修改的列，只需要SELECT权限（例如对于仅在ON DUPLICATE KEY UPDATE子句中的col_name = expr赋值右侧引用的列）。

当插入到分区表中时，可以控制哪些分区和子分区接受新行。 PARTITION选项采用表的一个或多个分区或子分区（或两者）的逗号分隔名称列表。如果由给定INSERT语句插入的任何行与列出的分区之一不匹配，则INSERT语句会失败，并显示错误发现行不匹配给定的分区集。

您可以使用REPLACE而不是INSERT来覆盖旧的行。 REPLACE是INSERT IGNORE的对应部分，用于处理包含复制旧行的唯一键值的新行：新行替换旧行而不是丢弃。

使用VALUES语法的INSERT语句可以插入多行。为此，请包含逗号分隔列值的多个列表，列表中包含在括号内并用逗号分隔。例：

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

每个值列表必须包含与每行要插入的值完全相同的值。以下语句无效，因为它包含九个值的一个列表，而不是每个三个值的三个列表：

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

13.2.5.1 INSERT ... SELECT 语法

使用INSERT ... SELECT，您可以从SELECT语句的结果中快速插入许多行到表中，该语句可以从一个或多个表中进行选择。例如：

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

以下条件适用于INSERT ... SELECT语句：

指定IGNORE忽略会导致重复键违规的行。
INSERT语句的目标表可能出现在查询的SELECT部分的FROM子句中。但是，您不能插入到从子查询中进行选择的同一个表中。

从同一个表中选择并插入时，MySQL创建一个内部临时表来存放SELECT中的行，然后将这些行插入到目标表中。但是，当t是TEMPORARY表时，不能使用INSERT INTO t ... SELECT ... FROM t，因为TEMPORARY表不能在同一语句中引用两次

AUTO_INCREMENT 和往常一样
为确保二进制日志可用于重新创建原始表，MySQL不允许并发插入INSERT ... SELECT语句
为避免SELECT和INSERT引用同一个表时出现不明确的列引用问题，请为SELECT部分中使用的每个表提供唯一别名，并使用适当的别名限定该部分中的列名。
为避免SELECT和INSERT引用同一个表时出现不明确的列引用问题，请为SELECT部分中使用的每个表提供唯一别名，并使用适当的别名限定该部分中的列名。

您可以显式选择源表或目标表（或两者）的哪些分区或子分区（或两者）将与表名称后面的PARTITION选项一起使用。当PARTITION与语句的SELECT部分中的源表名称一起使用时，行仅从其分区列表中指定的分区或子分区中选择。当PARTITION与语句的INSERT部分的目标表名称一起使用时，必须可以将选定的所有行插入到选项后面的分区列表中指定的分区或子分区中。否则，INSERT ... SELECT语句失败。

没有ORDER BY子句的SELECT语句返回行的顺序是不确定的。这意味着，在使用复制时，不能保证这样的SELECT在主服务器和从服务器上以相同的顺序返回行，这会导致它们之间的不一致。为防止发生这种情况，请始终使用ORDER BY子句编写要在主服务器和从服务器上产生相同行顺序的INSERT ... SELECT语句。

由于此问题，INSERT ... SELECT ON DUPLICATE KEY UPDATE和INSERT IGNORE ... SELECT语句被标记为不安全的基于语句的复制。这些语句在使用基于语句的模式时会在错误日志中产生警告，并在使用MIXED模式时使用基于行的格式写入二进制日志。（bug＃11758262，bug＃50439）

使用MyISAM等存储引擎影响分区表的INSERT ... SELECT语句会锁定目标表的所有分区; 但是，只有实际从源表读取的分区才会被锁定。（这不会发生在使用存储引擎的表中，例如使用行级锁定的InnoDB。）

13.2.5.2 INSERT ..... ON DUPLICATE KEY UPDATE 语法

如果指定ON DUPLICATE KEY UPDATE子句并且要插入的行将在UNIQUE索引或PRIMARY KEY中导致重复值，则会发生旧行的UPDATE。例如，如果列a被声明为UNIQUE并且包含值1，则以下两个语句具有相似的效果：

INSERT INTO t1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATEc=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

（对于InnoDB表，其中a是一个自动增量列，效果不同。对于自动增量列，INSERT语句会增加自动增量值，但UPDATE不会。）

如果列b也是唯一的，则INSERT等效于此UPDATE语句：

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

如果a = 1或b = 2匹配多行，则只更新一行。通常，您应该尽量避免在具有多个唯一索引的表上使用ON DUPLICATE KEY UPDATE子句。

使用ON DUPLICATE KEY UPDATE时，如果行作为新行插入，则每行的受影响行值为1，如果更新现有行，则每行受影响行值为2，如果现有行被设置为其当前值，则每次的行值为0。如果在连接到mysqld时将mysql_real_connect（）C API函数指定为CLIENT_FOUND_ROWS标志，则如果现有行设置为其当前值，则受影响的行值为1（而不是0）。

如果一个表包含AUTO_INCREMENT列并且INSERT ... ON DUPLICATE KEY UPDATE插入或更新一行，则LAST_INSERT_ID（）函数将返回AUTO_INCREMENT值。

ON DUPLICATE KEY UPDATE子句可以包含多个列分配，用逗号分隔。

在ON DUPLICATE KEY UPDATE子句中的赋值表达式中，可以使用VALUES（col_name）函数来引用INSERT部分中的列值。换句话说，ON DUPLICATE KEY UPDATE子句中的VALUES（col_name）引用了在没有发生重复键冲突时将插入的col_name的值。这个函数在多行插入中特别有用。 VALUES（）函数仅在ON DUPLICATE KEY UPDATE子句或INSERT语句中有意义，否则返回NULL。例：

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

该语句与以下两种语句相同：

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

对于INSERT ... SELECT语句，这些规则适用于您可以在ON DUPLICATE KEY UPDATE子句中引用的可接受形式的SELECT查询表达式：

引用来自单个表（可能是派生表）的查询中的列。
引用多个表上的联接查询中的列。
来自DISTINCT查询的列的引用。
只要SELECT不使用GROUP BY，就可以引用其他表中的列。一个副作用是您必须限定对非唯一列名称的引用。

对来自UNION的列的引用无法可靠地工作。要解决此限制，请将UNION重写为派生表，以便将其行视为单表结果集。例如，此语句可能会产生不正确的结果：

INSERT INTO t1 (a, b)

SELECT c, d FROM t2

UNION SELECT e, f

FROM t3 ON DUPLICATE KEY UPDATE b = b + c;

相反，使用将UNION重写为派生表的等效语句：

INSERT INTO t1 (a, b)

SELECT * FROM (

SELECT c, d FROM t2 UNION SELECT e, f FROM t3

) AS dt

ON DUPLICATE KEY UPDATE b = b + c;

将查询重写为派生表的技术还支持引用GROUP BY查询中的列。

对于基于语句的复制，INSERT ... SELECT ON DUPLICATE KEY UPDATE语句被标记为不安全。这些语句在使用基于语句的模式时会在错误日志中产生警告，并在使用MIXED模式时使用基于行的格式写入二进制日志。针对具有多个唯一或主键的表的INSERT ... ON DUPLICATE KEY UPDATE语句也被标记为不安全。（Bug＃11765650，Bug＃58637）

13.2.5.3 INSERT DELAYED 语法

INSERT DELAYED ...

INSERT语句的DELAYED选项是对标准SQL的MySQL扩展。在以前的MySQL版本中，它可以用于某些类型的表（例如MyISAM），这样当客户端使用INSERT DELAYED时，它立即从服务器获得一个好的行，并且该行被排队插入未被任何其他线程使用的表。

DELAYED插入和替换在MySQL 5.6中已弃用。在MySQL 5.7中，DELAYED不受支持。服务器识别但忽略DELAYED关键字，将插入处理为非延迟插入，并生成ER_WARN_LEGACY_SYNTAX_CONVERTED警告（“不再支持INSERT DELAYED，语句已转换为INSERT”）。 DELAYED关键字计划在未来版本中删除。

13.2.6 LOAD DATA INFILE 语法

LOAD DATA INFILE语句以非常高的速度将文本文件中的行读入表中。 LOAD DATA INFILE是SELECT ... INTO OUTFILE的补充。（请参见第13.2.9.1节“SELECT ... INTO语法”。）要将表中的数据写入文件，请使用SELECT ... INTO OUTFILE。要将文件读回表中，请使用LOAD DATA INFILE。两条语句的FIELDS和LINES子句的语法是相同的。两个子句都是可选的，但是FIELDS必须在LINES之前，如果两者都被指定。

您还可以使用mysqlimport实用程序加载数据文件; 它通过向服务器发送LOAD DATA INFILE语句来运行。 --local选项会导致mysqlimport从客户端主机读取数据文件。如果客户端和服务器支持压缩协议，则可以指定--compress选项以在慢速网络上获得更好的性能

文件名必须以文字字符串形式给出。在Windows上，在路径名称中将反斜杠指定为正斜杠或反斜杠。 character_set_filesystem系统变量控制文件名的解析。

LOAD DATA支持使用PARTITION选项显式分区选择，其中包含分区，子分区或两者的一个或多个以逗号分隔的名称。使用此选项时，如果文件中的任何行无法插入到列表中指定的任何分区或子分区中，则语句会失败，并显示错误 Found a row not matching the given partition set。

服务器使用character_set_database系统变量指示的字符集来解释文件中的信息。 SET NAMES和character_set_client的设置不会影响输入的解析。如果输入文件的内容使用与默认值不同的字符集，则通常最好使用CHARACTER SET子句指定文件的字符集。指定 binary字符集表示“不转换”。

LOAD DATA INFILE将文件中的所有字段解释为具有相同的字符集，而不管装入字段值的列的数据类型如何。为了正确解释文件内容，您必须确保它是用正确的字符集编写的。例如，如果您使用mysqldump -T编写数据文件或通过在mysql中发出SELECT ... INTO OUTFILE语句，请务必使用--default-character-set选项，以便将输出写入字符集当文件使用LOAD DATA INFILE加载时使用。

使用LOCAL比让服务器直接访问文件要慢一些，因为文件的内容必须通过客户端连接发送到服务器。另一方面，您不需要FILE权限来加载本地文件。

要在加载操作期间忽略外键约束，请在执行LOAD DATA之前发出SET foreign_key_checks = 0语句。

如果在空的MyISAM表上使用LOAD DATA INFILE，则所有非唯一索引都将在单独的批处理中创建（与REPAIR TABLE相同）。通常情况下，当你有很多索引时，这会使LOAD DATA INFILE更快。在某些极端情况下，通过在将文件加载到表中并使用ALTER TABLE ... ENABLE KEYS在加载文件后重新创建索引之前使用ALTER TABLE ... DISABLE KEYS关闭索引，可以更快地创建索引。

如果您没有指定FIELDS或LINES子句，则默认值与您写下此内容相同：

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

LINES TERMINATED BY '\n' STARTING BY ''

（Backslash是SQL语句中的字符串中的MySQL转义字符，因此要指定文字反斜线，必须指定两个反斜线，以将该值解释为单个反斜线。转义序列'\t'和'\n'指定tab 和换行符）。）

换句话说，读取输入时，默认值会导致LOAD DATA INFILE的行为如下所示：

在换行符处查找行边界。
不跳过任何行前缀。
将tab中的行分成几个字段。
不要期望字段被包含在任何引用字符中。
将转义字符\前面的字符解释为转义序列。例如，\t，\n和\\分别表示制表符，换行符和反斜杠。稍后请参阅FIELDS ESCAPED BY的讨论，以获得完整的转义序列列表。

相反，在写入输出时，默认值会导致SELECT ... INTO OUTFILE的行为如下：

在列之间写tab
字段不会用任何引号括起来。
使用\转义实例字段值中出现的制表符，换行符或\。
在一行的结尾写入换行

注意：

如果您在Windows系统上生成了文本文件，则可能必须使用LINES TERMINATED BY '\ r\n'才能正确读取文件，因为Windows程序通常使用两个字符作为行终止符。某些程序（如写字板）在编写文件时可能使用\ r作为行终止符。要阅读这些文件，请使用LINES TERMINATED BY '\r'。

如果你想要读取的所有行都有一个你想忽略的公共前缀，你可以使用LINES STARTING BY 'prefix_string'来跳过前缀以及前面的任何内容。如果一行不包含前缀，则整行将被跳过。假设您发出以下语句：

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

如果数据文件如下：

xxx"abc",1

something xxx"def",2

"ghi",3

结果行将是（“abc”，1）和（“def”，2）。文件中的第三行被跳过，因为它不包含前缀。

IGNORE号码LINES选项可用于忽略文件开头处的行。例如，您可以使用IGNORE 1 LINES跳过包含列名称的初始标题行：

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

当您使用SELECT ... INTO OUTFILE与LOAD DATA INFILE配合使用将数据库中的数据写入文件并稍后将文件读回数据库时，两个语句的字段和行处理选项必须匹配。否则，LOAD DATA INFILE将不会正确解释文件的内容。假设你使用SELECT ... INTO OUTFILE来编写一个由逗号分隔的字段的文件：

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

重新读取逗号分隔的文件，正确的语句是：

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

如果您尝试使用下面显示的语句读取文件，则它不起作用，因为它会指示LOAD DATA INFILE在字段之间查找tab：

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

可能的结果是每条输入行将被解释为单

LOAD DATA INFILE可用于读取从外部获取的文件。例如，许多程序都可以使用逗号分隔值（CSV）格式导出数据，以便行中的字段用逗号分隔，并用双引号括起来，并使用最初的一行列名。如果此文件中的行由回车/换行符对终止，则此处显示的语句显示了用于加载文件的字段和行处理选项：

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

如果输入值未必包含在引号内，请在ENCLOSED BY关键字前使用OPTIONALLY。

任何字段或行处理选项都可以指定一个空字符串（''）。如果不为空，则FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必须是单个字符。 FIELDS TERMINATED BY, LINES STARTING BY, 和 LINES TERMINATED BY 的值可能不止一个字符。例如，要编写以回车/换行符对结尾的行或者读取包含这些行的文件，请指定一个LINES TERMINATED BY '\r\n'子句。

要读取包含由%%组成的行分隔的 jokes 的文件，可以执行此操作

CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,joke TEXT NOT NULL);

LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes FIELDSTERMINATED BY '' LINES TERMINATED BY '\n%%\n' (joke);

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，BINARY，TEXT或ENUM）的列的值：

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对输入解释没有影响。）以ESCAPED BY字符开头的ENCLOSED BY字符的出现被解释为当前字段值的一部分。

如果该字段以ENCLOSED BY字符开头，那么该字符的实例只有在后面紧跟字段或行TERMINATED 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字符为空，则不会发生转义序列解释

在某些情况下，列和行处理选项会相互影响：

如果LINES TERMINATED BY是一个空字符串，并且FIELDS TERMINATED BY非空，则行也以FIELDS TERMINATED BY结束。
如果FIELDS TERMINATED BY和FIELDS ENCLOSED BY值都为空（''），则使用固定行（非限制）格式。使用固定行格式，字段之间不使用分隔符（但仍可以有行终止符）。而是使用足够宽的字段来读取和写入列值，以保存字段中的所有值。对于TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT，无论声明的显示宽度是多少，字段宽度分别为4,6,8,11和20。

LINES TERMINATED BY仍然用于分隔行。如果一行不包含所有字段，则其余列将设置为其默认值。如果你没有行结束符，你应该把它设置为''。在这种情况下，文本文件必须包含每行的所有字段。

正如后面所述，固定行格式也会影响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都是空的）的BLOB或TEXT列。
如果您指定一个分隔符与另一个分隔符相同或作为另一个分隔符的前缀，则LOAD DATA INFILE无法正确解释输入。例如，以下FIELDS子句会导致问题：

FIELDS TERMINATED BY '"' ENCLOSED BY '"'

如果FIELDS ESCAPED BY为空，则包含FIELDS ENCLOSED BY或LINES TERMINATED BY的字段值，后跟FIELDS TERMINATED BY值会导致LOAD DATA INFILE过早停止读取字段或行。发生这种情况是因为LOAD DATA INFILE无法正确确定字段或行值结束的位置。

以下示例加载persondata表的所有列：

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

默认情况下，当在LOAD DATA INFILE语句末尾没有提供列列表时，输入行预计包含每个表列的字段。如果您只想加载某些表格的列，请指定字段列表：

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata(col_name_or_user_var [, col_name_or_user_var] ...);

如果输入文件中的字段顺序不同于表中列的顺序，则还必须指定列列表。否则，MySQL无法知道如何将输入字段与表列匹配。

每个col_name_or_user_var值可以是列名或用户变量。使用用户变量，SET子句可以在将结果分配给列之前对其值执行转换。

SET子句中的用户变量可以以多种方式使用。以下示例直接使用第一个输入列作为t1.column1的值，并将第二个输入列分配给在用于t1.column2的值之前进行除法运算的用户变量：

LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, @var1)

SET column2 = @var1/100;

SET子句可用于提供不是从输入文件派生的值。以下语句将column3设置为当前日期和时间：

LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, column2)

SET column3 = CURRENT_TIMESTAMP;

您还可以通过将输入值分配给用户变量并且不将该变量分配给表列来放弃输入值：

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

使用列/变量列表和SET子句受以下限制：

SET子句中的赋值应该只有在赋值运算符左侧的列名。
您可以在SET赋值的右侧使用子查询。返回要分配给列的值的子查询可能只是标量子查询。此外，您不能使用子查询从正在加载的表中进行选择。
不会为列/变量列表或SET子句处理由IGNORE子句忽略的行。
以固定行格式加载数据时，不能使用用户变量，因为用户变量没有显示宽度。

在处理输入行时，LOAD DATA会将其拆分为字段，并根据列/变量列表和SET子句使用这些值（如果存在）。然后将结果行插入表中。如果表中有BEFORE INSERT或AFTER INSERT触发器，则分别在插入行之前或之后激活它们。

如果输入行的字段太多，则额外的字段将被忽略，并且警告的数量会增加。

如果输入行的字段太少，则缺少输入字段的表列将被设置为其默认值。

空字段值的解释与缺失字段不同：

对于字符串类型，列被设置为空字符串。
对于数字类型，该列设置为0。
对于日期和时间类型，该列设置为该类型的相应“零”值。

如果您在INSERT或UPDATE语句中显式地将空字符串显式指定给字符串，数字或日期或时间类型，则这些值是相同的值。

如果SQL模式设置为限制值，则处理空白或不正确的字段值与刚描述的不同。例如，如果sql_mode设置为TRADITIONAL，那么为数值列转换空值或“x”等值会导致错误，而不会转换为0.（使用LOCAL或IGNORE时，会发生警告而不是错误，甚至具有限制性的sql_mode值，并且该行使用与非限制性SQL模式相同的最接近行为插入，这是因为服务器无法在操作过程中停止传输文件。

TIMESTAMP列只有在列的NULL值（即\ N）且列未声明为允许NULL值，或者TIMESTAMP列的默认值为当前时间戳时才设置为当前日期和时间，并且当指定字段列表时，它从字段列表中省略。

LOAD DATA INFILE将所有输入视为字符串，因此您不能像使用INSERT语句那样对ENUM或SET列使用数值。所有ENUM和SET值必须指定为字符串。

BIT值不能使用二进制符号直接加载（例如，b'011010'）。要解决此问题，请使用SET子句去除前导b'和尾部'并执行基2到基10的转换，以便MySQL将值正确加载到BIT列中

在Unix上，如果您需要LOAD DATA从管道中读取数据，则可以使用以下技术（该示例将/ directory的列表加载到表db1.t1中）：

mkfifo /mysql/data/db1/ls.dat

chmod 666 /mysql/data/db1/ls.dat

find / -ls > /mysql/data/db1/ls.dat &

mysql -e "LOAD DATA INFILE 'ls.dat' INTO TABLE t1" db1

在这里，您必须运行生成要加载的数据的命令以及在单独的终端上运行mysql命令，或者在后台运行数据生成过程（如上例所示）。如果你不这样做，管道将阻塞，直到mysql进程读取数据。

当LOAD DATA INFILE语句完成时，它将以下列格式返回一个信息字符串：

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

您可以使用SHOW WARNINGS获取第一个max_error_count警告的列表，作为有关出错的信息。

13.2.8 REPLACE 语法

REPLACE与INSERT完全相同，只是如果表中的旧行与PRIMARY KEY或UNIQUE索引的新行具有相同的值，则在插入新行之前删除旧行。

REPLACE是SQL标准的MySQL扩展。它要么插入，要么删除，插入。对于插入或更新标准SQL的另一个MySQL扩展 - 请参见第13.2.5.2节“INSERT ... ON DUPLICATE KEY UPDATE语法”。

注意：

只有当表具有PRIMARY KEY或UNIQUE索引时，REPLACE才有意义。否则，它就等同于INSERT，因为没有用于确定新行是否与其他行重复的索引。

所有列的值取自REPLACE语句中指定的值。任何缺失的列都会设置为其默认值，就像INSERT一样。您不能引用当前行中的值，并在新行中使用它们。如果使用诸如SET col_name = col_name + 1之类的分配，则对右侧列名称的引用将被视为DEFAULT（col_name），因此分配等同于SET col_name = DEFAULT（col_name）+1。

要使用REPLACE，您必须同时具有该表的INSERT和DELETE权限。

MySQL对REPLACE（和LOAD DATA ... REPLACE）使用以下算法：

尝试插入新行到表中
如果由于主键或者唯一索引的重复键导致插入失败：

a.从表中删除具有重复键值的冲突行

b.再次尝试将新行插入表中

在重复键错误的情况下，存储引擎可能会执行REPLACE作为更新而不是删除加插入，但语义相同。除了存储引擎如何增加Handler_xxx状态变量的可能差异之外，没有用户可见的影响。

由于REPLACE ... SELECT语句的结果取决于SELECT行中的行的顺序，并且不能始终保证该顺序，所以在记录主语句和从句语的这些语句时可能发生分歧。因此，对于基于语句的复制，REPLACE ... SELECT语句被标记为不安全。这些语句在使用基于语句的模式时会在错误日志中产生。

ITPub博客

13.2 数据操作语句（1）

最新文章