加载数据[low_priority | concurrent] [local] infile 'file_name'(替换|忽略)到表中tbl_name(分区(partition_name(,partition_name[字符集。charset_name[{字段|列}[以'终止字符串'][[可选]包含有'字符'][由'字符行[以' '开头]字符串'][以'结尾字符串']][忽视数量{lines | rows}] [(col_name_or_user_var(,col_name_or_user_var]…)][集col_name= {expr|默认}(,col_name= {expr|默认}]…]
的加载数据
语句以非常快的速度将文本文件中的行读入表中。可以从服务器主机或客户端主机读取文件,这取决于是否当地的
修饰符。当地的
还会影响数据解释和错误处理。
加载数据
是的补数选择……到输出文件
.(见第13.2.10.1节,“SELECT…”声明”)。要将数据从表写入文件,请使用选择……到输出文件
.要将文件读入表,请使用加载数据
.的语法字段
而且行
两个语句的子句是相同的。
的mysqlimport实用程序提供了加载数据文件的另一种方法;它通过发送一个加载数据
语句发送到服务器。看到第4.5.5节," mysqlimport -一个数据导入程序".
有关效率的信息插入
与加载数据
并加速加载数据
,请参阅第8.2.5.1节,“优化INSERT语句”.
的当地的
修饰符影响的这些方面加载数据
,与非相比当地的
操作:
当地的
只有当服务器和客户端都被配置为允许它时才有效。例如,如果mysqld开始于local_infile
系统变量残疾,当地的
产生一个错误。看到第6.1.6节,“本地加载数据的安全考虑”.
文件名必须以文字字符串的形式给出。在Windows上,将路径名中的反斜杠指定为正斜杠或双反斜杠。控件指定的字符集解析文件名character_set_filesystem
系统变量。
参数指定的字符集,服务器默认使用该字符集解释文件内容character_set_database
系统变量。如果文件内容使用与此默认值不同的字符集,最好使用字符集
条款。的字符集二进制
指定”没有转换。”
组名称
的设置character_set_client
不要影响文件内容的解释。
加载数据
将文件中的所有字段解释为具有相同的字符集,而不管加载字段值的列的数据类型是什么。为了正确地解释文件,必须确保使用正确的字符集编写它。例如,如果您使用, mysqldump - t或通过签发选择……到输出文件
声明mysql,一定要用——default-character-set
选项,用于将输出写入文件加载时使用的字符集加载数据
.
不可能加载使用ucs2
,utf16
,utf16le
,或utf32
字符集。
这些规则决定了加载数据
输入文件位置:
如果
当地的
则文件必须位于服务器主机上。服务器直接读取文件,定位如下:如果文件名是绝对路径名,服务器将按照给定的方式使用它。
如果文件名是一个带有前导组件的相对路径名,服务器将查找相对于其数据目录的文件。
如果文件名没有主导组件,服务器将在默认数据库的数据库目录中查找该文件。
如果
当地的
,文件必须位于客户端主机上。客户端程序读取文件,定位如下:如果文件名是绝对路径名,客户端程序将按给定的方式使用它。
如果文件名是相对路径名,客户端程序将查找相对于其调用目录的文件。
当
当地的
时,客户端程序读取文件并将其内容发送给服务器。服务器在存储临时文件的目录中创建该文件的副本。看到B.3.3.5节,“MySQL存储临时文件的地方”.在此目录中缺乏足够的空间用于复制会导致数据加载本地
语句失败。
非-当地的
规则意味着服务器读取名为的文件。/ myfile.txt
相对于它的数据目录,而它读取名为myfile.txt
从默认数据库的数据库目录。例如,如果如下加载数据
语句执行时db1
是默认数据库,服务器读取文件吗data.txt
的数据库目录db1
,即使语句显式地将文件加载到db2
数据库:
将DATA .txt文件导入表db2.my_table;
服务器也使用非当地的
规则来定位.sdi
文件导入表
声明。
对于一个非当地的
加载操作时,服务器读取位于服务器主机上的一个文本文件,因此这些安全要求必须满足:
你必须有
文件
特权。看到第6.2.2节“MySQL提供的特权”.该操作以
secure_file_priv
系统变量设置:如果变量值是非空目录名,则文件必须位于该目录中。
如果变量值为空(这是不安全的),则该文件只需要由服务器读取即可。
对于一个当地的
加载操作,客户端程序读取位于客户端主机上的文本文件。由于文件内容是由客户机通过连接发送到服务器的,因此使用当地的
比服务器直接访问文件稍微慢一些。另一方面,你不需要文件
特权,文件可以位于客户端程序可以访问的任何目录。
的取代
而且忽略
修饰符控制对新的(输入)行的处理,这些行在唯一键值上重复现有表行(主键
或独特的
索引值):
与
取代
,与现有行中唯一键值具有相同值的新行将替换现有行。看到第13.2.9节,“替换声明”.与
忽略
,将丢弃在唯一键值上复制现有行的新行。有关更多信息,请参见IGNORE对语句执行的影响.
的当地的
修饰符的效果与忽略
.发生这种情况是因为服务器没有办法在操作过程中停止文件的传输。
如果没有一个取代
,忽略
,或当地的
,则在找到重复的键值时发生错误,并忽略文本文件的其余部分。
除了如上所述的影响重复键处理之外,忽略
而且当地的
也会影响错误处理:
既无
忽略
也不当地的
,数据解释错误终止操作。与
忽略
或当地的
,数据解释错误变成警告,加载操作继续进行,即使SQL模式是限制性的。有关示例,请参见列赋值.
要在加载操作期间忽略外键约束,请执行设置foreign_key_checks = 0
语句在执行之前加载数据
.
如果你使用加载数据
在一个空的MyISAM
表中,所有非惟一索引都是在单独的批处理中创建的修理表
).通常,这使得加载数据
如果有很多索引,速度会快得多。在某些极端情况下,通过使用关闭索引,可以更快地创建索引ALTER TABLE……禁用的钥匙
在将文件加载到表中并重新创建索引之前ALTER TABLE……启用钥匙
加载文件后。看到第8.2.5.1节,“优化INSERT语句”.
为加载数据
而且选择……到输出文件
的语法字段
而且行
条款也是一样的。两个子句都是可选的,但是字段
必须先于行
如果两者都指定。
如果你指定一个字段
条款,其每个子条款(终止的
,(可选)包围
,逃跑了
)也是可选的,除非您必须至少指定其中一个。这些子句的参数只允许包含ASCII字符。
如果你指定no字段
或行
子句,默认值和你写的是一样的:
以“\t”结束的域,由“\\”转义,以“\n”结束的行,以“
反斜杠是SQL语句中字符串中的MySQL转义字符。因此,要指定字面反斜杠,必须指定两个反斜杠,以便将值解释为单个反斜杠。的转义序列' \ t '
而且' \ n '
分别指定制表符和换行符。
换句话说,默认的原因加载数据
读取输入时的操作如下:
在换行符处寻找行边界。
不要跳过任何行前缀。
用制表符将行分隔为字段。
不要期望字段用任何引号括起来。
解释转义字符前面的字符
\
转义序列。例如,\ t
,\ n
,\ \
分别表示制表符、换行符和反斜杠。参见字段逃跑了
稍后查看转义序列的完整列表。
相反,默认值会导致错误选择……到输出文件
在写入输出时执行如下操作:
在字段之间写入制表符。
不要用引号将字段括起来。
使用
\
转义制表符、换行符或\
发生在字段值中。在行尾写上换行符。
对于在Windows系统上生成的文本文件,可能需要正确读取文件以'\r\n'结尾的行
因为Windows程序通常使用两个字符作为行结束符。一些程序,例如写字板,可以使用r \
写入文件时作为行结束符。要读取这些文件,请使用以'\r'结尾的行
.
如果所有输入行都有一个希望忽略的公共前缀,则可以使用以'开头的行
跳过前缀在此之前的任何事情.如果一行中不包含该前缀,则跳过整行。假设您发出以下声明:prefix_string
'
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx';
如果数据文件是这样的:
Xxx“abc”,1某事Xxx“def”,2“ghi”,3
产生的行如下(“abc”,1)
而且(2)“def”
.跳过文件中的第三行,因为它不包含前缀。
的忽略
子句可用于忽略文件开头的行。例如,您可以使用数量
行忽略1行
跳过包含列名的初始标题行:
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;
当你使用选择……到输出文件
配合加载数据
要将数据从数据库写入文件,然后稍后将文件读入数据库,两条语句的字段处理和行处理选项必须匹配。否则,加载数据
没有正确解释文件的内容。假设您使用选择……到输出文件
编写一个以逗号分隔字段的文件:
SELECT * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM table2;
要读取以逗号分隔的文件,正确的语句是:
LOAD DATA INFILE ' DATA .txt' INTO TABLE table2 FIELDS TERMINATED BY ',';
相反,如果您尝试使用下面所示的语句读取文件,它将不起作用,因为它指示加载数据
在字段之间查找制表符:
LOAD DATA INFILE ' DATA .txt' INTO TABLE table2 FIELDS TERMINATED BY '\t';
可能的结果是,每个输入行将被解释为单个字段。
加载数据
可用于读取从外部源获取的文件。例如,许多程序可以以逗号分隔值(CSV)格式导出数据,例如,行中有用逗号分隔的字段,并用双引号括起来,初始行为列名。如果这样的文件中的行以回车/换行对结束,这里显示的语句说明了用于加载文件的字段和行处理选项:
LOAD DATA INFILE ' DATA .txt' INTO TABLEtbl_name字段终止于',' '封闭的' ' '行终止于'\r\n'忽略1行;
如果输入值不一定用引号括起来,则使用(可选)
之前包围
选择。
任何字段或行处理选项都可以指定一个空字符串(”
).如果不是空的,则包含的字段[可选]
而且字段逃跑了
值必须是单个字符。的字段终止的
,行开始,
,行终止,
值可以是多个字符。例如,要写入以回车/换行对结束的行,或要读取包含此类行的文件,请指定以'\r\n'结尾的行
条款。
读取包含笑话的文件,这些笑话由% %
,你能做到的
创建一个笑话表(AUTO_INCREMENT主键,文本不为空)加载数据文件'/tmp/joke .txt'到表玩笑字段终止于"行终止于'\n%%\n'(玩笑);
包含的字段[可选]
控制字段的引用。输出(选择……到输出文件
),如果省略这个词(可选)
,所有字段都由包围
的性格。这样输出的一个示例(使用逗号作为字段分隔符)如下所示:
"1","一个字符串","100.20" "2","一个字符串包含一个,逗号","102.20" "3","一个字符串包含一个\","102.20" "4","一个字符串包含一个\",引号和逗号","102.20"
如果您指定(可选)
,包围
字符仅用于包含具有字符串数据类型的列中的值(例如字符
,二进制
,文本
,或枚举
):
1,"一个字符串",100.20 2,"一个字符串包含一个,逗号",102.20 3,"一个字符串包含一个\"引用",102.20 4,"一个字符串包含一个\",引号和逗号",102.20
出现的包围
通过在字段值前添加前缀,可以对字段值中的字符进行转义逃跑了
的性格。另外,如果指定为空逃跑了
值时,可能无意中生成无法正确读取的输出加载数据
.例如,如果转义字符为空,刚才显示的输出将如下所示。注意,第四行中的第二个字段包含一个引号后的逗号,它(错误地)似乎终止了该字段:
1,"一个字符串",100.20 2,"一个字符串包含一个,逗号",102.20 3,"一个字符串包含一个"引用",102.20 4,"一个字符串包含一个",引号和逗号",102.20
对于输入,包围
字符(如果存在)将从字段值的结尾删除。(这是真的,不管是否(可选)
指定;(可选)
对输入解释没有影响。)出现的包围
字符之前的逃跑了
字符被解释为当前字段值的一部分。
如果字段以包围
字符,只有在后跟字段或行时,该字符的实例才被识别为终止字段值终止的
序列。为了避免歧义,出现包围
字段值中的字符可以被加倍,并被解释为该字符的单个实例。例如,如果封闭”的
,引号的处理如下所示:
“大”“老板”->“大”老板“大”老板->“大”老板“大”老板->“大”老板
字段逃跑了
控制如何读取或写入特殊字符:
对于输入,如果
字段逃跑了
如果字符不为空,则删除该字符的出现,并按字面意义将以下字符作为字段值的一部分。例外的一些双字符序列,其中第一个字符是转义字符。这些序列如下表所示(使用\
用于转义字符)。的规则零
处理将在本节后面介绍。字符 转义序列 \ 0
ASCII NUL ( X ' 00 '
)字符\ b
退格字符 \ n
换行符(换行符) r \
回车符 \ t
一个制表符。 \ Z
ASCII 26(控制+ Z) \ N
零 有关
\
摆脱语法,看第9.1.1节,"字符串字面值".如果
字段逃跑了
字符为空,则不发生转义序列解释。对于输出,如果
字段逃跑了
Character不为空,它用于在输出时作为以下字符的前缀:的
字段逃跑了
的性格。的
包含的字段[可选]
的性格。的第一个字符
字段终止的
而且行终止,
值,如果包围
字符为空或未指定。美国信息交换标准代码
0
(转义字符后面实际写的是ASCII0
,而不是零值字节)。
如果
字段逃跑了
字符为空,没有字符转义和零
输出是零
,而不是\ N
.指定空转义字符可能不是一个好主意,特别是当数据中的字段值包含刚刚给出的列表中的任何字符时。
在某些情况下,字段和行处理选项相互作用:
如果
行终止,
是空字符串和字段终止的
是非空的,行也以字段终止的
.如果
字段终止的
而且农田包围
值都是空的(”
),则使用固定行(非分隔)格式。对于固定行格式,字段之间不使用分隔符(但仍然可以使用行结束符)。相反,使用足以容纳字段中所有值的字段宽度读取和写入列值。为非常小的整数
,短整型
,MEDIUMINT
,INT
,长整型数字
,无论声明的显示宽度是多少,字段宽度分别是4、6、8、11和20。行终止,
仍然用于分隔行。如果一行不包含所有字段,则其余列将设置为它们的默认值。如果没有行结束符,则应该将其设置为”
.在这种情况下,文本文件必须包含每一行的所有字段。固定行格式也会影响
零
值,如后面所述。请注意如果使用多字节字符集,固定大小的格式无法工作。
的处理零
值根据字段
而且行
选择使用:
为默认
字段
而且行
值,零
的字段值写入\ N
的字段值\ N
是读零
对于输入(假设逃跑了
性格是\
).如果
农田包围
是否为空,字段中包含的字零
因为它的值被读取为零
价值。这与字面意思不同零
封闭的内农田包围
字符,读取为字符串“零”
.如果
字段逃跑了
是空的,零
是写成这个字的吗零
.使用固定行格式(该格式用于
字段终止的
而且农田包围
都是空的),零
被写成一个空字符串。这将导致两个零
将表中的值和空字符串写入文件时不可区分,因为它们都被写入为空字符串。如果在读入文件时需要区分这两者,则不应使用固定行格式。
加载尝试零
成一个非空
列根据中描述的规则生成警告或错误列赋值.
有些案例不被支持加载数据
:
的所有列persondata
表:
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata
的末尾不提供列列表时,默认为加载数据
语句中,输入行应该包含每个表列的字段。如果你想加载一个表的一些列,指定一个列列表:
将数据导入表格persondata ()col_name_or_user_var(,col_name_or_user_var]…);
如果输入文件中字段的顺序与表中列的顺序不同,则还必须指定列列表。否则,MySQL无法告诉如何匹配输入字段和表列。
的每个实例col_name_or_user_var
在加载数据
语法要么是列名,要么是用户变量。使用用户变量集
子句使您能够在将结果分配给列之前对它们的值执行预处理转换。
控件中的用户变量集
从句有几种用法。下面的示例直接使用第一个输入列作为的值t1.column1
,并将第二输入列赋给用户变量,该变量在被用作的值之前要接受除法运算t1.column2
:
将表1 (column1, @var1)中的数据导入到表1中。
的集
子句可用于提供非从输入文件派生的值。下面的语句设置column3
到当前日期和时间:
LOAD DATA 'file.txt' INTO TABLE t1 (column1, column2)
你也可以通过将输入值赋值给一个用户变量,而不将该变量赋值给表中的任何列,来丢弃输入值:
LOAD DATA file 'file.txt' INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);
使用列/变量列表和集
本条款受以下限制:
作业的
集
子句在赋值操作符的左边应该只有列名。的右边可以使用子查询
集
作业。返回要分配给列的值的子查询只能是标量子查询。另外,不能使用子查询从正在加载的表中进行选择。被忽略的行
忽略
子句不处理列/变量列表或数量
行集
条款。当以固定行格式加载数据时,不能使用用户变量,因为用户变量没有显示宽度。
要处理输入行,加载数据
将其拆分为字段,并根据列/变量列表和集
从句,如果他们在场。然后将产生的行插入到表中。如果有之前插入
或后插入
表的触发器,它们分别在插入行之前或之后被激活。
字段值的解释和表列的赋值取决于以下因素:
SQL模式(
sql_mode
系统变量)。模式可以是非严格的,也可以以各种方式限制。例如,可以启用严格的SQL模式,或者该模式可以包含如下值NO_ZERO_DATE
或NO_ZERO_IN_DATE
.存在或不存在
忽略
而且当地的
修饰符。
这些因素结合起来产生限制性或非限制性的数据解释加载数据
:
如果SQL模式是限制性的,那么数据解释就是限制性的
忽略
也没有当地的
修饰符指定。错误终止加载操作。如果SQL模式是非限制性的,则数据解释是非限制性的
忽略
或当地的
修饰符指定。(特别是,如果指定了修饰词,可以使用其中任何一个覆盖限制性SQL模式。)错误变成警告并继续加载操作。
限制性数据解释使用以下规则:
字段过多或过少都会导致错误。
分配
零
(即,\ N
)到一个非零
列将导致错误。超出列数据类型范围的值将导致错误。
无效值会产生错误。例如,这样的值
“x”
对于数字列,将导致错误,而不是转换为0。
相比之下,非限制性数据解释使用以下规则:
如果一个输入行有太多的字段,多余的字段将被忽略,警告的数量将增加。
如果输入行包含的字段太少,则为缺少输入字段的列分配默认值。中描述了默认值分配第11.6节,“数据类型默认值”.
分配
零
(即,\ N
)到一个非零
列将导致为列数据类型赋隐式默认值。中描述了隐式默认值第11.6节,“数据类型默认值”.无效值产生警告而不是错误,并被转换为”最亲密的”列数据类型的有效值。例子:
值,如
“x”
对于数值列,将导致转换为0。超出范围的数值或时间值被裁剪到列数据类型范围的最近端点。
的无效值
DATETIME
,日期
,或时间
列被插入为隐式默认值,而不管SQL模式如何NO_ZERO_DATE
设置。隐式默认值是合适的”零”类型的值(“0000-00-00”就是
,“0000-00-00”
,或“就是”
).看到第11.2节,“日期和时间数据类型”.
加载数据
对空字段值和缺失字段值的解释不同:对于字符串类型,列被设置为空字符串。
对于数值类型,该列设置为
0
.对于日期和时间类型,该列设置为适当的”零”类型的值。看到第11.2节,“日期和时间数据类型”.
时间戳
仅当存在时,列才设置为当前日期和时间零
值(即,\ N
),而该栏并没有声明允许零
值,或者如果时间戳
列默认值为当前时间戳,当指定字段列表时,该值将从字段列表中省略。
加载数据
将所有输入视为字符串,因此不能使用数字值枚举
或集
列的方式插入
语句。所有枚举
而且集
值必须指定为字符串。
位
不能使用二进制表示法直接加载值(例如,b‘011010’
).要解决这个问题,请使用集
子句剥去引线b”
和落后'
并执行base-2到base-10的转换,以便MySQL将值加载到位
列正确:
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);查询OK, 2行影响(0.00秒)记录:2删除:0跳过:0警告:0 mysql> SELECT BIN(b+0) FROM bit_test;+----------+ | 本(b + 0 ) | +----------+ | 10 | | 1111111 | +----------+ 2行集(0.00秒)
为位
值0 b
二进制表示法(例如,0 b011010
),使用这个集
从句而不是剥去引子0 b
:
SET b = CONV(MID(@var1, 3, LENGTH(@var1)-2), 2,10) AS UNSIGNED)
加载数据
方法支持显式分区选择分区
子句,包含一个或多个分区、子分区或两者的逗号分隔的名称列表。当使用此子句时,如果文件中的任何行不能插入到列表中命名的任何分区或子分区,则语句将失败,并返回错误找到与给定分区集不匹配的行.有关更多信息和示例,请参见第24.5节,“分区选择”.
与LOW_PRIORITY
修饰符,执行加载数据
语句被延迟,直到没有其他客户端从表中读取数据。这只影响只使用表级锁的存储引擎(例如MyISAM
,内存
,合并
).
与并发
修饰符和一个MyISAM
满足并发插入条件的表(即中间不包含空闲块),其他线程可以从表中检索数据,同时加载数据
是执行。此修饰符会影响性能加载数据
一点,即使没有其他线程同时使用该表。
当加载数据
语句结束时,返回一个如下格式的信息字符串:
记录:1删除:0跳过:0警告:0
控件插入值的情况与插入值的情况相同插入
声明(见第13.2.6节," INSERT语句"),除了加载数据
还会在输入行中字段太少或太多时生成警告。
您可以使用显示警告
拿到第一名的名单max_error_count
警告是关于错误的信息。看到第13.7.7.42节,"显示警告语句".
如果您使用的是C API,您可以通过调用mysql_info ()
函数。看到mysql_info ().
加载数据
对于基于语句的复制被认为是不安全的。如果你使用加载数据
与binlog_format =声明
,将应用更改的每个副本都会创建一个包含数据的临时文件。这个临时文件没有加密,即使源上的二进制日志加密是活动的。如果需要加密,则使用基于行或混合的二进制日志格式,副本不会为这种格式创建临时文件。有关相互作用的更多信息加载数据
和复制,请参阅第17.5.1.19节,“复制和加载数据”.