文章目录
- 一. SELECT ... INTO OUTFILE语句简介
- 二.LOAD DATA语句简介
- 2.1 load data语句概述
- 2.2 load data 语法
- 2.2.1 Non-LOCAL Versus LOCAL Operation
- 2.2.2 Input File Character Set
- 2.2.3 Input File Location
- 2.2.4 Security Requirements
- 2.2.5 Duplicate-Key and Error Handling
- 2.2.6 Index Handling
- 2.2.7 Field and Line Handling
- 2.2.8 Column List Specification
- 2.2.9 Input Preprocessing
- 2.2.10 Column Value Assignment
- 2.2.11 Partitioned Table Support
- 2.2.12 Concurrency Considerations
- 2.2.13 Statement Result Information
- 2.2.14 Replication Considerations
- 2.2.15 Miscellaneous Topics
- 2.3 load data案例
- 三.案例篇
- 参考:
一. SELECT … INTO OUTFILE语句简介
1.1 SELECT … INTO OUTFILE的使用场景
在工作中，经常遇到需要将数据导出到excel表给到数据分析的同事，当数据量较大的时候，通过mysql的图形界面工具navcait无法导出，此时可以尝试使用SELECT … INTO OUTFILE的方法。
另外，在关系型数据库MySQL与Hive进行数据交互的时候，除了使用sqoop工具，也可以导出成csv或txt文件，然后在Hive端通过load data的方式进行导入。
当然，我们也可以通过SELECT … INTO OUTFILE与Load data来迁移不同MySQL实例上的数据。
1.2 SELECT … INTO OUTFILE官方文档介绍
SELECT … INTO OUTFILE ‘file_name’ 形式将选定的行写入文件。该文件是在服务器主机上创建的，因此您必须拥有file特权才能使用此语法。File_name不能是已存在的文件，这样可以防止修改/etc/passwd和数据库表等文件。character_set_filesystem系统变量控制文件名的解释。
SELECT … INTO OUTFILE句旨在启用将表转储到服务器主机上的文本文件。要在其他主机上创建结果文件，这个命令是不合适的，因为没有办法编写文件相对于服务器主机文件系统的路径，除非可以使用服务器主机文件系统上的网络映射路径访问远程主机上的文件位置。
或者，如果MySQL客户端软件安装在远程主机上，您可以使用一个客户端命令，如MySQL -e “SELECT…”> file_name生成该主机上的文件。
SELECT … INTO OUTFILE 是LOAD DATA的补充。列值被写入转换为character set子句中指定的字符集。如果没有这样的子句，则使用二进制字符集转储值。实际上，不存在字符集转换。如果结果集包含多个字符集中的列，那么输出数据文件也是如此，可能无法正确地重新加载文件。
语句的export_options部分的语法由与LOAD DATA语句使用的相同的FIELDS和LINES子句组成。有关FIELDS和LINES子句的信息，包括它们的默认值和允许值。
FIELDS ESCAPED BY 控制如何编写特殊字符。如果FIELDS转义BY字符不为空，则在必要时使用它作为输出中后面字符的前缀以避免歧义:
- 按字符转义的字段
- 字段[可选]由字符包围
- FIELDS TERMINATED BY和LINES TERMINATED BY值的第一个字符
- ASCII NUL(零值字节;转义字符后面实际写的是ASCII 0，而不是零值字节)
- 必须对以FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, or LINES TERMINATED BY 字符结束的行进行转义，以便可以可靠地读取回文件。ASCII NUL被转义，以便使用某些分页器更容易查看。
- 生成的文件不需要符合SQL语法，因此不需要转义任何其他内容。
- 如果FIELDS ESCAPED BY 字符为空，则不转义任何字符，NULL输出为NULL，而不是\N。指定空转义字符可能不是一个好主意，特别是如果数据中的字段值包含刚才给出的列表中的任何字符。
- 当您希望将表的所有列转储到文本文件时，也可以使用INTO OUTFILE与TABLE语句一起使用。在这种情况下，可以使用ORDER BY和LIMIT控制行顺序和行数;这些条款必须在出库文件之前。表格INTO OUTFILE支持相同的export_options as does SELECT…在写入到文件系统时，它受到相同的限制。
1.3 SELECT … INTO OUTFILE语法
我们通过一个例子来看SELECT … INTO OUTFILE的语法
select * from employees ORDER BY lname LIMIT 1000
INTO OUTFILE '/tmp/employee_data_1.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY '\'
LINES TERMINATED BY '\n';
FIELDS TERMINATED BY 表示列分隔符
[OPTIONALLY] ENCLOSED 表示列用什么字符包括起来，如果使用了OPTIONALLY则只有CHAR和VERCHAR被包括ESCAPED 表示当需要转义时用什么作为转义字符
LINES TERMINATED 表示每行记录之间用什么分隔
1.4 SELECT … INTO OUTFILE的配置
部分MySQL环境不能使用SELECT … INTO OUTFILE语句，会有如下报错:
mysql> select * from fact_sale into outfile "/home/backup/fact_sale_20210526.txt";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
遇到这类问题，可能是MySQL的导出目录不正确，或者没有设置MySQL的导出目录。
接下来，我们来查看MySQL的导出目录:
mysql> SELECT @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| NULL |
+---------------------------+
1 row in set (0.00 sec)
如上所示，MySQL的导出目录没有设置，此时需要修改配置文件，添加secure-file-priv配置，并重启MySQL服务。
vi /etc/my.cnf
secure-file-priv = /home/backup
service mysqld restart
重新进行导出:
mysql> select * from fact_sale into outfile "/var/lib/mysql-files/fact_sale_20210526.txt";
Query OK, 767830000 rows affected (12 min 31.91 sec)
二.LOAD DATA语句简介
2.1 load data语句概述
LOAD DATA语句以非常快的速度将行从文本文件读入表中。该文件可以从服务器主机或客户端主机读取，这取决于是否给出了LOCAL修饰符。LOCAL还影响数据解释和错误处理。
LOAD DATA是SELECT … INTO OUTFILE的补充，要将数据从表写入文件，请使用SELECT…INTO OUTFILE。若要将文件读回表中，请使用LOAD DATA。FIELDS和LINES子句的语法对于这两个语句是相同的。
2.2 load data 语法
语法:
LOAD DATA
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var
[, col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT}
[, col_name={expr | DEFAULT}] ...]
2.2.1 Non-LOCAL Versus LOCAL Operation
与非LOCAL操作相比，LOCAL修饰符影响LOAD DATA的这些方面:
- 它改变了输入文件的预期位置;请参阅输入文件位置。
- 它改变了语句安全需求;看到安全需求。
- 它与IGNORE修饰符对输入文件内容的解释和错误处理具有相同的效果;请参阅重复键和错误处理以及列值分配。
2.2.2 Input File Character Set
文件名必须以字面值字符串的形式给出。在Windows上，在路径名中指定反斜杠为正斜杠或双反斜杠。服务器使用character_set_filesystem系统变量指定的字符集来解释文件名。
默认情况下，服务器使用character_set_database系统变量指定的字符集来解释文件内容。如果文件内容使用与此默认值不同的字符集，最好使用character set子句指定该字符集。二进制字符集指定“无转换”。
SET NAMES和character_set_client的设置不会影响文件内容的解释。
LOAD DATA将文件中的所有字段解释为具有相同的字符集，而不考虑字段值加载到的列的数据类型。为了正确解释文件，您必须确保它是用正确的字符集编写的。例如，如果你写一个数据文件使用mysqldump -T或发出SELECT…在mysql的OUTFILE语句中，一定要使用——default-character-set选项，将输出写入到LOAD DATA加载文件时使用的字符集中。
2.2.3 Input File Location
这些规则确定LOAD DATA输入文件的位置:
-
如果未指定LOCAL，则该文件必须位于服务器主机上。服务器直接读取文件，定位方法如下:
1.1) 如果文件名是绝对路径名，服务器将按照给定的方式使用它。
1.2) 如果文件名是包含前导组件的相对路径名，则服务器将相对于其数据目录查找该文件。
1.3) 如果文件名没有前置组件，服务器将在默认数据库的数据库目录中查找该文件。 -
如果指定了LOCAL，则文件必须位于客户端主机上。客户端程序读取文件，定位方法如下:
2.1) 如果文件名是绝对路径名，客户端程序将按照给定的方式使用它。
2.2) 如果文件名是相对路径名，客户端程序将相对于其调用目录查找该文件。
当使用LOCAL时，客户机程序读取文件并将其内容发送给服务器。服务器在存储临时文件的目录中创建该文件的一个副本。
非local规则意味着服务器相对于其数据目录读取名为./myfile.txt的文件，而它从默认数据库的数据库目录读取名为myfile.txt的文件。例如，如果执行下面的LOAD DATA语句，而db1是默认数据库，则服务器从db1的数据库目录中读取文件DATA .txt，即使该语句显式地将文件加载到db2数据库中的一个表中:
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
2.2.4 Security Requirements
对于非local加载操作，服务器读取位于服务器主机上的文本文件，因此必须满足以下安全需求:
- 您必须拥有FILE权限。
- 该操作以系统变量secure_file_priv的设置为准:
- 如果变量值是非空目录名，则文件必须位于该目录中。
- 如果变量值为空(不安全)，则该文件只需要由服务器读取。
对于LOCAL加载操作，客户端程序读取位于客户端主机上的文本文件。因为文件内容是通过客户机连接发送到服务器的，所以使用LOCAL比服务器直接访问文件要慢一些。另一方面，您不需要FILE特权，并且文件可以位于客户机程序可以访问的任何目录中。
2.2.5 Duplicate-Key and Error Handling
REPLACE和IGNORE修饰符控制新(输入)行在唯一键值(主键或唯一索引值)上重复现有表行的处理:
- 使用REPLACE，与现有行中唯一键值相同的新行将替换现有行。
- 使用IGNORE，在唯一键值上重复现有行的新行将被丢弃。
LOCAL修饰符的作用与IGNORE相同。这是因为服务器没有办法在操作过程中停止文件传输。
如果没有指定REPLACE、IGNORE或LOCAL，则当找到重复的键值时将发生错误，并忽略文本文件的其余部分。
除了像刚才描述的那样影响重复键处理之外，IGNORE和LOCAL还影响错误处理:
- 如果不使用IGNORE或LOCAL，则数据解释错误将终止操作。
- 使用IGNORE或LOCAL，数据解释错误将变成警告，加载操作将继续进行，即使SQL模式是受限的。
2.2.6 Index Handling
要在load操作期间忽略外键约束，请在执行load DATA之前执行SET foreign_key_checks = 0语句。
如果您在一个空MyISAM表上使用LOAD DATA，那么所有非惟一索引将在单独的批处理中创建(就像REPAIR table一样)。通常，当您有许多索引时，这会使LOAD DATA速度快得多。在某些极端情况下，通过使用ALTER TABLE关闭索引，可以更快地创建索引…禁用KEYS之前加载文件到表和重新创建索引ALTER table…加载文件后启用密钥.
2.2.7 Field and Line Handling
对于LOAD DATA和SELECT…INTO OUTFILE语句中，FIELDS和LINES子句的语法是相同的。这两个子句都是可选的，但如果指定了两个子句，则FIELDS必须位于LINES之前。
如果您指定FIELDS子句，其每个子句(TERMINATED BY，[任选地]ENCLOSED BY, and转义BY)也是可选的，除非您必须至少指定其中的一个。这些子句的参数只允许包含ASCII字符。
如果你没有指定FIELDS或LINES子句，默认值就像你写的一样:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''
反斜杠是SQL语句中字符串中的MySQL转义字符。因此，要指定一个字面值反斜杠，必须指定两个反斜杠才能将值解释为单个反斜杠。转义序列’\t’和’\n’分别指定制表符和换行符。
换句话说，当读取输入时，默认值导致LOAD DATA的行为如下:
- 在换行处查看行边界。
- 不要跳过任何行前缀。
- 在制表符中将行拆分为字段。
- 不要期望字段包含在任何引用字符中。
- 将前面有转义字符\的字符解释为转义序列。例如，“\t”、“\n”、“\”分别表示制表符、换行符、反斜杠。有关转义序列的完整列表，请参阅后面关于FIELDS escaping BY的讨论。
相反，默认值会导致SELECT…当写入输出时，INTO OUTFILE的行为如下:
- 在字段之间写制表符。
- 不要用引号括起来字段。
- 使用\转义字段值中出现的制表符、换行符或\实例。
- 在行尾写换行。
如果所有的输入行都有一个您想要忽略的公共前缀，您可以使用lines STARTING BY 'prefix_string’来跳过该前缀和它之前的任何内容。如果某一行不包含前缀，则跳过整行。假设您发出以下声明:
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx';
如果数据文件是这样的:
xxx"abc",1
something xxx"def",2
"ghi",3
结果行是(“abc”，1)和(“def”，2)。文件中的第三行被跳过，因为它不包含前缀。
IGNORE number LINES子句可用于忽略文件开头的行。例如，你可以使用IGNORE 1 LINES来跳过包含列名的首标题行:
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;
当你使用SELECT…INTO OUTFILE与LOAD DATA一起将数据从数据库写入文件，然后稍后将文件读回数据库，这两个语句的字段和行处理选项必须匹配。否则，LOAD DATA将不能正确解释文件的内容。假设你使用SELECT…INTO OUTFILE写入一个以逗号分隔的字段的文件:
SELECT * INTO OUTFILE 'data.txt'
FIELDS TERMINATED BY ','
FROM table2;
要读取以逗号分隔的文件，正确的语句是:
LOAD DATA INFILE 'data.txt' INTO TABLE table2
FIELDS TERMINATED BY ',';
如果你尝试用下面的语句读取文件，它不会工作，因为它指示LOAD DATA在字段之间查找制表符:
LOAD DATA INFILE 'data.txt' INTO TABLE table2
FIELDS TERMINATED BY '\t';
可能的结果是，每个输入行将被解释为单个字段。
LOAD DATA可用于读取从外部来源获得的文件。例如，许多程序可以以逗号分隔值(CSV)格式导出数据，例如，行中有用逗号分隔的字段，并用双引号括起来，并使用列名的首行。如果这样一个文件中的行以回车/换行对结束，这里显示的语句说明了你将用于加载文件的字段和行处理选项:
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
如果输入值不一定用引号括起来，请在enclosed BY选项之前使用optional。
任何字段处理或行处理选项都可以指定一个空字符串(")。如果不为空，则 FIELDS [OPTIONALLY] ENCLOSED BY and FIELDS ESCAPED BY值必须是单个字符。“FIELDS TERMINATED BY”、“LINES started BY”和“LINES TERMINATED BY”值可以多于一个字符。例如，要写以回车/换行对结尾的行，或读取包含此类行的文件，请指定lines terminated by '\r\n’子句。
要读取包含笑话的文件，这些笑话由由%%组成的行隔开，您可以这样做
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控制字段的引用。输出(SELECT…INTO OUTFILE)，如果您可选地省略该词，所有字段将由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"
如果您可选地指定，则封闭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正确读取的输出。例如，如果转义字符为空，前面显示的输出将如下所示。注意，第四行第二个字段在引号后包含一个逗号，这(错误地)似乎是终止字段:
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字符，则从字段值的末尾删除。(无论是否指定了optional，这都是正确的;选择性对输入解释没有影响。)出现在ENCLOSED 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 控制如何读取或写入特殊字符:
| 字符 | 转义序列 |
|---|---|
| \0 | ASCII NUL (X’00’) 字符 |
| \b | 退格字符 |
| \n | 换行字符 |
| \r | 回车字符 |
| \t | 一个制表符 |
| \Z | ASCII 26 (Control+Z) |
| \N | NULL |
- 对于输入，如果FIELDS ESCAPED BY字符不为空，则剥离出现的该字符，并按照字面意思将下面的字符作为字段值的一部分。一些双字符序列是例外，其中第一个字符是转义字符。这些序列如下表所示(转义字符使用)。
有关-转义语法的更多信息。
如果FIELDS ESCAPED BY 字符为空，则不会发生转义序列解释。
对于输出，如果FIELDS ESCAPED BY 字符不为空，它用于在输出中作为以下字符的前缀:
- The FIELDS ESCAPED BY 字符.
- The FIELDS [OPTIONALLY] ENCLOSED BY 字符.
- 如果ENCLOSED BY字符为空或未指定，则FIELDS TERMINATED BY和LINES TERMINATED BY值的第一个字符。
- ASCII 0(转义字符后面实际写的是ASCII 0，而不是零值字节)。
如果FIELDS ESCAPED BY字符为空，则不转义任何字符，NULL输出为NULL，而不是\N。指定空转义字符可能不是一个好主意，特别是如果数据中的字段值包含刚才给出的列表中的任何字符。
在某些情况下，字段和行处理选项相互作用:
- 如果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(假设转义的BY字符是)。
- 如果FIELDS ENCLOSED BY不为空，则将包含字面值NULL的字段读取为NULL值。这不同于在FIELDS enclosed BY characters中包含的单词NULL，它被读为字符串’NULL’。
- 如果FIELDS转义BY为空，则NULL被写成单词NULL。
- 使用固定行格式(当FIELDS TERMINATED BY和FIELDS ENCLOSED BY都为空时使用)，NULL被写成空字符串。这将导致表中的NULL值和空字符串在写入文件时无法区分，因为两者都是作为空字符串写入的。如果需要在重新读入文件时区分这两者，那么不应该使用固定行格式。
根据列值赋值中描述的规则，尝试将NULL加载到NOT NULL列中会产生警告或错误。
LOAD DATA不支持某些情况:
- 固定大小的行(由空结束的字段和由空包围的字段)和BLOB或TEXT列。
- 如果指定的分隔符与另一个分隔符相同或前缀相同，则LOAD DATA无法正确解释输入。例如，下面的FIELDS子句会导致问题:
FIELDS TERMINATED BY '"' ENCLOSED BY '"'
如果FIELDS ESCAPED BY为空，则包含FIELDS ENCLOSED BY或LINES TERMINATED BY后跟FIELDS TERMINATED BY value的字段值将导致LOAD DATA过早停止读取字段或行。这是因为LOAD DATA不能正确地确定字段或行值的结束位置。
2.2.8 Column List Specification
下面的示例加载persondata表的所有列:
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
默认情况下，当LOAD DATA语句末尾没有提供列列表时，输入行将包含每个表列的字段。如果你想加载一个表的一些列，指定一个列列表:
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata
(col_name_or_user_var [, col_name_or_user_var] ...);
如果输入文件中字段的顺序与表中列的顺序不同，还必须指定列列表。否则，MySQL无法判断如何将输入字段与表列匹配。
2.2.9 Input Preprocessing
LOAD DATA语法中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子句，被忽略的行不会被处理。
- 当以固定行格式加载数据时，不能使用用户变量，因为用户变量没有显示宽度。
2.2.10 Column Value Assignment
要处理输入行，LOAD DATA将其分割为字段，并根据列/变量列表和SET子句(如果存在的话)使用这些值。然后将生成的行插入到表中。如果表中有BEFORE INSERT或AFTER INSERT触发器，则分别在插入行之前或之后激活它们。
字段值的解释和表列的赋值取决于以下因素:
- SQL模式(sql_mode系统变量的值)。模式可以是非严格的，也可以有多种限制。例如，可以启用严格SQL模式，或者该模式可以包含NO_ZERO_DATE或NO_ZERO_IN_DATE等值。
- IGNORE和LOCAL修饰符的存在或不存在。
这些因素结合在一起产生了LOAD data的限制性或非限制性数据解释:
- 如果SQL模式是受限的，且既没有指定IGNORE修饰符，也没有指定LOCAL修饰符，则数据解释是受限的。错误终止加载操作。
- 如果SQL模式是非限制性的，或者指定了IGNORE或LOCAL修饰符，则数据解释是非限制性的。(特别是，指定的修饰符会覆盖限制性SQL模式。)错误变成警告，加载操作继续进行。
限制性数据解释使用以下规则:
- 过多或过少的字段会导致错误。
- 将NULL(即\N)赋值给非NULL列会导致错误。
- 超出列数据类型范围的值将导致错误。
- 无效值会产生错误。例如，数值列的值如’x’将导致错误，而不是转换为0。
相比之下，非限制性数据解释使用以下规则:
- 如果一个输入行有太多的字段，额外的字段将被忽略，警告的数量将增加。
- 如果输入行字段太少，则为缺少输入字段的列赋默认值。
- 将NULL(即\N)赋值给非NULL列会导致为列数据类型分配隐式缺省值。
- 无效值产生警告而不是错误，并被转换为列数据类型的“最近”有效值。例子:
4.1) 数值列的值(如’x’)会转换为0。
4.2) 超出范围的数值或时间值被剪辑到列数据类型范围的最近端点。
4.3) 无论SQL模式NO_ZERO_DATE设置如何，都将插入DATETIME、DATE或TIME列的无效值作为隐式默认值。隐式默认值是适合该类型的“0”值(‘0000-00-00 00:00:00’，‘0000-00-00’，或’00:00:00’)。 - LOAD DATA对空字段值的解释与缺失字段不同:
5.1) 对于字符串类型，列被设置为空字符串。
5.2) 对于数字类型，列被设置为0。
5.3) 对于日期和时间类型，该列被设置为适合该类型的“零”值。
如果在INSERT或UPDATE语句中显式地将空字符串赋给字符串、数字类型或日期或时间类型，则会得到相同的值。
时间戳列设置为当前日期和时间只有一个空值的列(即\ N)和列不是宣布允许NULL值,或者时间戳列的默认值是当前时间戳和省略从指定的字段列表,当一个字段列表。
LOAD DATA将所有输入视为字符串，所以不能像INSERT语句那样对ENUM或SET列使用数值。所有的ENUM和SET值必须指定为字符串。
BIT值不能使用二进制表示法直接加载(例如，b’011010’)。为了解决这个问题，使用SET子句去掉前导b’和末尾b’，并执行base-2到base-10的转换，以便MySQL正确地将值加载到BIT列中:
shell> cat /tmp/bit_test.txt
b'10'
b'1111111'
shell> mysql test
mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
INTO TABLE bit_test (@var1)
SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-3), 2, 10) AS UNSIGNED);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT BIN(b+0) FROM bit_test;
+----------+
| BIN(b+0) |
+----------+
| 10 |
| 1111111 |
+----------+
2 rows in set (0.00 sec)
对于0b二进制表示法中的BIT值(例如0b011010)，使用这个SET子句来去掉前导的0b:
SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-2), 2, 10) AS UNSIGNED)
2.2.11 Partitioned Table Support
LOAD DATA支持使用带有一个或多个分区、子分区或两者以逗号分隔的名称列表的partition子句进行显式分区选择。当使用这个子句时，如果文件中的任何行不能插入到列表中指定的任何分区或子分区中，语句会失败，错误是Found a row not matching the给定的分区集。
2.2.12 Concurrency Considerations
使用LOW_PRIORITY修饰符，LOAD DATA语句的执行会被延迟，直到没有其他客户端从表中读取数据。这只影响仅使用表级锁的存储引擎(如MyISAM、MEMORY和MERGE)。
使用CONCURRENT修饰符和满足并发插入条件的MyISAM表(也就是说，它中间不包含空闲块)，其他线程可以在执行LOAD data时从表中检索数据。即使没有其他线程同时使用这个表，这个修饰符也会略微影响LOAD DATA的性能。
2.2.13 Statement Result Information
当LOAD DATA语句结束时，它将返回如下格式的信息字符串:
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
警告发生在与使用INSERT语句插入值相同的情况下，除了LOAD DATA在输入行中字段太少或太多时也会产生警告。
您可以使用SHOW WARNINGS获取第一个max_error_count警告的列表，作为出错原因的信息。
如果您使用的是C API，您可以通过调用mysql_info()函数获得关于该语句的信息。看到mysql_info()。
2.2.14 Replication Considerations
LOAD DATA对于基于语句的复制是不安全的。如果使用带有binlog_format=STATEMENT的LOAD DATA，则要应用更改的每个副本都会创建一个包含数据的临时文件。如果需要加密，则使用基于行的或混合二进制日志记录格式，副本不会为此创建临时文件。有关LOAD DATA和复制之间交互的更多信息
2.2.15 Miscellaneous Topics
在Unix上，如果需要LOAD DATA从管道中读取数据，可以使用以下技术(示例将/目录的列表加载到表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进程读取。
2.3 load data案例
下面是一个load data比较经典的例子，作为后期的模板。
命令:
LOAD DATA LOCAL INFILE '/tmp/2982/20200424/user.csv'
INTO TABLE t_user CHARACTER SET utf8mb4 FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(userName, userNo, age, homeAddr)
SET province = '浙江省', city='杭州市', creatorId=2982, createTime='2020-04-24 13:24:24'
三.案例篇
3.1 一个简单的outfile导入导出的例子
代码:
-- 导出
select * from fact_sale into outfile "/var/lib/mysql-files/fact_sale_20210526.txt";
cd /var/lib/mysql-files/
ls -lrth
-- 传输
scp ./fact_sale_20210526.txt root@10.31.1.124:/home/backup/
-- 导入
CREATE TABLE `fact_sale` (
`id` bigint NOT NULL AUTO_INCREMENT,
`sale_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`prod_name` varchar(200) NOT NULL,
`sale_nums` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
LOAD DATA INFILE '/home/backup/fact_sale_20210526.txt' INTO TABLE fact_sale;
测试记录:
mysql> select * from fact_sale into outfile "/var/lib/mysql-files/fact_sale_20210526.txt";
Query OK, 767830000 rows affected (12 min 31.91 sec)
[root@10-31-1-122 backup]# cd /var/lib/mysql-files/
[root@10-31-1-122 mysql-files]# ls -lrth
总用量 28G
-rw-rw-rw- 1 mysql mysql 28G 5月 26 11:52 fact_sale_20210526.txt
mysql> CREATE TABLE `fact_sale` (
-> `id` bigint NOT NULL AUTO_INCREMENT,
-> `sale_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> `prod_name` varchar(200) NOT NULL,
-> `sale_nums` int DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> LOAD DATA INFILE '/home/backup/fact_sale_20210526.txt' INTO TABLE fact_sale;
Query OK, 767830000 rows affected (1 hour 34 min 50.20 sec)
Records: 767830000 Deleted: 0 Skipped: 0 Warnings: 0
3.2 导出成csv文件
通过默认值导出成csv格式无效
mysql> select * from fact_sale limit 100 into outfile "/home/backup/fact_sale_20210527.csv";
Query OK, 100 rows affected (0.01 sec)
[root@hp2 backup]# more fact_sale_20210527.csv
1 2011-08-16 00:00:00 PROD4 28
2 2011-11-06 00:00:00 PROD6 19
3 2011-04-25 00:00:00 PROD8 29
4 2011-09-12 00:00:00 PROD2 88
5 2011-05-15 00:00:00 PROD5 76
6 2011-02-23 00:00:00 PROD6 64
7 2012-09-26 00:00:00 PROD2 38
8 2012-02-14 00:00:00 PROD6 45
9 2010-04-22 00:00:00 PROD8 57
10 2010-10-31 00:00:00 PROD5 65
11 2010-10-24 00:00:00 PROD2 33
12 2011-02-11 00:00:00 PROD9 27
需要使用如下命令:
-- 导出成csv文件
SELECT * FROM fact_sale limit 100
INTO OUTFILE "/home/backup/fact_sale_20210527.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
测试记录:
mysql> SELECT * FROM fact_sale limit 100
-> INTO OUTFILE "/home/backup/fact_sale_20210527.csv"
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
Query OK, 100 rows affected (0.00 sec)
mysql>
[root@hp2 backup]# more fact_sale_20210527.csv
1,"2011-08-16 00:00:00","PROD4",28
2,"2011-11-06 00:00:00","PROD6",19
3,"2011-04-25 00:00:00","PROD8",29
4,"2011-09-12 00:00:00","PROD2",88
5,"2011-05-15 00:00:00","PROD5",76
6,"2011-02-23 00:00:00","PROD6",64
7,"2012-09-26 00:00:00","PROD2",38
8,"2012-02-14 00:00:00","PROD6",45
9,"2010-04-22 00:00:00","PROD8",57
10,"2010-10-31 00:00:00","PROD5",65
打开后发现一个问题，没有表头:
导出表头命令:
select * from
(select 'id','sale_date','prod_name','sale_nums'
union all
SELECT * FROM fact_sale limit 100
) tmp
INTO OUTFILE "/home/backup/fact_sale_20210527_new1.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
测试记录:
mysql> select * from
-> (select 'id','sale_date','prod_name','sale_nums'
-> union all
-> SELECT * FROM fact_sale limit 100
-> ) tmp
-> INTO OUTFILE "/home/backup/fact_sale_20210527_new1.csv"
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
Query OK, 100 rows affected (0.00 sec)
mysql>
[root@hp2 backup]# more fact_sale_20210527_new1.csv
"id","sale_date","prod_name","sale_nums"
"1","2011-08-16 00:00:00","PROD4","28"
"2","2011-11-06 00:00:00","PROD6","19"
"3","2011-04-25 00:00:00","PROD8","29"
"4","2011-09-12 00:00:00","PROD2","88"
"5","2011-05-15 00:00:00","PROD5","76"
"6","2011-02-23 00:00:00","PROD6","64"
"7","2012-09-26 00:00:00","PROD2","38"
"8","2012-02-14 00:00:00","PROD6","45"
"9","2010-04-22 00:00:00","PROD8","57"
"10","2010-10-31 00:00:00","PROD5","65"
"11","2010-10-24 00:00:00","PROD2","33"
表头出来了:
参考:
- https://dev.mysql.com/doc/refman/8.0/en/select-into.html
- https://dev.mysql.com/doc/refman/8.0/en/load-data.html
- https://www.itbiancheng.com/article/5163.html