加载数据[low_priority | concurrent] [local] file 'file_name' replace | ignore '到表中tbl_name(分区(partition_name(,partition_name[字符集。charset_name[{fields | columns][以'结尾]字符串'][[可选]由'字符'][通过'字符']][以'开头的行字符串'][以'结尾字符串']][忽略数量{lines | rows}] [(col_name_or_user_var(,col_name_or_user_var[集合,集合col_name= {expr|默认}[,col_name= {expr| default}]…
的加载数据
语句以非常快的速度将文本文件中的行读入表。可以从服务器主机或客户端主机读取该文件,这取决于当地的
给出了修饰符。当地的
还会影响数据解释和错误处理。
加载数据
是的补选择……到输出文件
.(见13.2.9.1节,“SELECT…”声明”)。若要将数据从表写入文件,请使用选择……到输出文件
.若要将文件读入表,请使用加载数据
.的语法字段
而且行
子句对于两个语句是相同的。
的mysqlimport实用程序提供了另一种加载数据文件的方法;它通过发送一个加载数据
语句发送给服务器。看到第4.5.5节“mysqlimport -一个数据导入程序”.
的效率信息插入
与加载数据
加速加载数据
,请参阅第8.2.4.1节,优化INSERT语句.
的当地的
修饰语影响着这些方面加载数据
,与非相比当地的
操作:
当地的
只有当服务器和客户端都已配置为允许时才有效。例如,如果mysqld是由local_infile
系统变量已禁用,当地的
产生错误。看到第6.1.6节“LOAD DATA LOCAL的安全注意事项”.
文件名必须以字面值字符串的形式给出。在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;
对于非当地的
加载操作时,服务器读取一个位于服务器主机上的文本文件,因此必须满足这些安全要求:
你必须有
文件
特权。看到章节6.2.2,“MySQL提供的特权”.操作以
secure_file_priv
系统变量设置:如果变量值为非空目录名,则文件必须位于该目录中。
如果变量值为空(这是不安全的),则该文件只需要服务器可读即可。
对于一个当地的
加载操作时,客户端程序读取位于客户端主机上的文本文件。因为文件内容是由客户端通过连接发送到服务器的,所以使用当地的
比服务器直接访问文件时稍慢。另一方面,你不需要文件
权限,文件可以位于客户端程序可以访问的任何目录中。
的取代
而且忽略
修饰符控制在唯一键值上重复现有表行的新(输入)行的处理(主键
或独特的
索引值):
与
取代
,与现有行中唯一键值相同的新行将替换现有行。看到第13.2.8节“REPLACE语句”.与
忽略
,在唯一键值上重复现有行的新行将被丢弃。有关更多信息,请参见IGNORE对语句执行的影响.
的当地的
修饰符的效果与忽略
.出现这种情况是因为服务器无法在操作过程中停止文件的传输。
如果没有取代
,忽略
,或当地的
,则在找到重复的键值时发生错误,并忽略文本文件的其余部分。
除了影响刚才描述的重复键处理之外,忽略
而且当地的
也会影响错误处理:
既无
忽略
也不当地的
,数据解释错误将终止操作。与
忽略
或当地的
,数据解释错误将成为警告,并且加载操作将继续,即使SQL模式是受限的。有关示例,请参见列值赋值.
若要在加载操作期间忽略外键约束,请执行SET foreign_key_checks = 0
语句。加载数据
.
如果你使用加载数据
空的MyISAM
表中,所有非唯一的索引都是在单独的批处理中创建的(例如修理表
).通常,这会使加载数据
当你有很多索引时,速度会快得多。在某些极端情况下,通过关闭索引可以更快地创建索引修改表…禁用的钥匙
在将文件加载到表中并使用修改表…启用钥匙
加载文件后。看到第8.2.4.1节,优化INSERT语句.
对于两者加载数据
而且选择……到输出文件
语句的语法字段
而且行
从句也是一样。这两个从句都是可选的,但是字段
必须先于行
如果两者都指定了。
如果您指定字段
条款,其每个子条款(终止的
,[选择性地]由
,逃跑了
)也是可选的,除非您必须指定至少其中一个。这些子句的参数只允许包含ASCII字符。
如果你指定no字段
或行
子句,默认值与您所写的相同:
以“\t”结尾的字段被“由以“\\”开头的“\\”行转义的“\\”所包围”
反斜杠是SQL语句中字符串中的MySQL转义字符。因此,要指定文字反斜杠,必须指定两个反斜杠,以便将值解释为单个反斜杠。转义序列' \ t '
而且' \ n '
分别指定制表符和换行符。
换句话说,默认值导致加载数据
读取输入时作如下动作:
在换行处寻找行边界。
不要跳过任何行前缀。
在制表符中将行分割为字段。
不要期望字段包含在任何引用字符内。
解释转义字符前面的字符
\
作为转义序列。例如,\ t
,\ n
,\ \
分别表示制表符、换行符和反斜杠。参见Fields被
稍后将介绍转义序列的完整列表。
相反,默认值导致选择……到输出文件
写入输出时执行如下操作:
在字段之间写制表符。
不要将字段包含在任何引用字符内。
使用
\
转义制表符、换行符或\
发生在字段值中。在行尾写入换行符。
对于在Windows系统上生成的文本文件,可能需要正确的文件读取以'\r\n'结尾的行
因为Windows程序通常使用两个字符作为行结束符。一些程序,如写字板,可能使用r \
作为写文件时的行结束符。要读取此类文件,请使用以'\r'结尾的行
.
如果所有输入行都有一个希望忽略的公共前缀,则可以使用以'开头的行
要跳过前缀以及之前的任何东西.如果一行中不包含前缀,则跳过整行。假设您发出以下声明:prefix_string
'
将“/tmp/test.txt”加载到以“xxx”开头的表测试字段中;
如果数据文件是这样的:
Xxx“abc”,1 something Xxx“def”,2“ghi”,3
结果行为(“abc”,1)
而且(2)“def”
.文件中的第三行被跳过,因为它不包含前缀。
的忽略
子句可用于忽略文件开头的行。例如,你可以使用数量
行忽略一行
跳过包含列名的初始标题行:
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
当你使用选择……到输出文件
与…同时加载数据
要将数据从数据库写入文件,然后再将文件读入数据库,两个语句的字段和行处理选项必须匹配。否则,加载数据
不能正确解释文件的内容。假设你使用选择……到输出文件
写入一个用逗号分隔字段的文件:
SELECT * INTO OUTFILE 'data.txt'字段结束于',' FROM table2;
要读取以逗号分隔的文件,正确的语句是:
加载数据filile ' DATA .txt'到表table2字段以','结束;
相反,如果尝试使用下面所示的语句读取文件,它将无法工作,因为它指示加载数据
查找字段之间的制表符:
LOAD DATA INFILE ' DATA .txt' INTO TABLE table2字段以'\t'结尾;
可能的结果是,每个输入行将被解释为单个字段。
加载数据
可用于读取从外部源获得的文件。例如,许多程序可以以逗号分隔值(CSV)格式导出数据,这样行中的字段用逗号分隔,并用双引号括起来,首行为列名。如果这样的文件中的行以回车/换行符对结束,这里显示的语句说明了您将使用的字段和行处理选项来加载文件:
加载数据INFILE ' DATA .txt'到表tbl_name字段以“”结尾,以“\r\n”结尾的行包围,忽略1行;
如果输入值不一定用引号括起来,请使用(可选)
之前包围
选择。
任何字段或行处理选项都可以指定空字符串(”
).如果不是空的,则包含的字段[可选]
而且Fields被
值必须为单个字符。的字段以
,开始的行
,以
值可以不止一个字符。例如,要写入以回车/换行符对结束的行,或读取包含此类行的文件,指定a以'\r\n'结尾的行
条款。
读取包含由以下行分隔的笑话的文件% %
你可以做到的
创建一个笑话表(INT NOT NULL AUTO_INCREMENT主键,笑话文本不空);加载数据INFILE '/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
对于输入,包围
如果存在字符,则从字段值的末尾删除。(这是正确的,不管是否(可选)
指定;(可选)
对输入解释没有影响。)发生的情况包围
字符前加逃跑了
字符被解释为当前字段值的一部分。
如果字段以包围
字符,只有当后跟字段或行时,该字符的实例才会被识别为字段值的终止终止的
序列。为了避免歧义,出现了包围
字段值中的字符可以被加倍,并被解释为该字符的单个实例。例如,如果由“”
,则按如下所示处理引号:
"大"老板"大"老板"大"老板-> "大"老板"大"老板-> "大"老板
Fields被
控制如何读写特殊字符:
对于输入,如果
Fields被
字符不为空,该字符的出现将被删除,并且后面的字符按字面意思作为字段值的一部分。一些双字符序列是例外,其中第一个字符是转义字符。这些序列显示在下表中(使用\
对于转义字符)。的规则零
处理将在本节后面描述。字符 转义序列 \ 0
一个ASCII null ( X ' 00 '
)字符\ b
退格字符 \ n
换行(换行)字符 r \
一个回车字符 \ t
制表符。 \ Z
ASCII 26 (Control+Z) \ N
零 欲了解更多有关
\
-escape语法,参见第9.1.1节,“字符串字面值”.如果
Fields被
字符为空,则不会发生转义序列解释。对于输出,如果
Fields被
Character不是空的,它用于在输出中为以下字符添加前缀:的
Fields被
的性格。的
包含的字段[可选]
的性格。的第一个字符
字段以
而且以
值,如果包围
字符为空或未指定。美国信息交换标准代码
0
(转义字符后面实际写的是ASCII0
,不是零值字节)。
如果
Fields被
字符为空,没有字符转义和零
输出为零
,而不是\ N
.指定空转义字符可能不是一个好主意,特别是如果数据中的字段值包含刚才给出的列表中的任何字符。
在某些情况下,字段处理选项和行处理选项相互作用:
如果
以
是空字符串和字段以
非空,行也以字段以
.如果
字段以
而且以下字段
值均为空(”
),则使用固定行(非分隔)格式。对于固定行格式,字段之间不使用分隔符(但您仍然可以使用行结束符)。相反,使用足够宽的字段宽度来保存字段中的所有值来读取和写入列值。为非常小的整数
,短整型
,MEDIUMINT
,INT
,长整型数字
,字段宽度分别为4、6、8、11和20,无论声明的显示宽度是什么。以
仍然用于分隔行。如果一行不包含所有字段,则其余列将被设置为默认值。如果没有行结束符,则应将其设置为”
.在这种情况下,文本文件必须包含每行的所有字段。固定行格式也会影响处理
零
值,如后面所述。请注意如果使用多字节字符集,固定大小格式将不起作用。
的处理零
的值不同字段
而且行
正在使用的选项:
对于默认
字段
而且行
值,零
写为字段值\ N
的字段值\ N
读作零
对于输入(假设逃跑了
性格是\
).如果
以下字段
不是空的,一个字段包含文字零
因为它的值被读取为零
价值。这和单词不一样零
封闭的内以下字段
字符,它被读取为字符串“零”
.如果
Fields被
是空的,零
是用单词写的吗零
.固定行格式(用于
字段以
而且以下字段
都是空的),零
写入为空字符串。这导致了零
将表中的值和空字符串写入文件时无法区分,因为两者都被写入为空字符串。如果在读入文件时需要能够区分这两者,则不应该使用固定行格式。
尝试加载零
成一个非空
列根据中描述的规则生成警告或错误列值赋值.
有些情况不支持加载数据
:
属性的所有列persondata
表:
将“persondata.txt”装入表persondata中;
的末尾未提供列列表时,默认情况下加载数据
语句时,每个表列的输入行都应包含一个字段。如果你只想加载一个表的一些列,指定一个列列表:
将“persondata.txt”装入表格persondata (col_name_or_user_var(,col_name_or_user_var]…);
如果输入文件中字段的顺序与表中列的顺序不同,还必须指定列列表。否则,MySQL无法告诉您如何将输入字段与表列匹配。
每一个实例col_name_or_user_var
在加载数据
语法是列名或用户变量。对于用户变量,集
子句使您能够在将结果分配给列之前对其值执行预处理转换。
中的用户变量集
从句有几种用法。下面的示例直接使用第一个输入列作为值t1.column1
,并将第二个输入列赋给用户变量,该用户变量在用于的值之前要进行除法运算t1.column2
:
将表t1 (columnn1, @var1) SET column2 = @var1/100;
的集
子句可用于提供非从输入文件派生的值。以下语句集column3
到当前日期和时间:
SET column3 = CURRENT_TIMESTAMP;
你也可以通过将输入值分配给用户变量而不将变量分配给任何表列来丢弃输入值:
LOAD DATA INFILE 'file.txt' INTO TABLE t1 (columnn1, @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”
和落后'
并执行以2为基数到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 sec)记录: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 = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-2), 2,10) AS UNSIGNED)
加载数据
控件支持显式的分区选择分区
子句,其中包含一个或多个以逗号分隔的分区、子分区或两者的名称。当使用此子句时,如果文件中的任何行不能插入到列表中指定的任何分区或子分区中,则语句失败并报错发现与给定分区集不匹配的行.有关更多信息和示例,请参见第22.5节“分区选择”.
对于使用使用表锁的存储引擎的分区表,例如MyISAM
,加载数据
不能修剪任何分区锁。这不适用于使用行级锁的存储引擎的表,例如InnoDB
.有关更多信息,请参见第22.6.4节“分区和锁定”.
与LOW_PRIORITY
修饰符,执行加载数据
语句将被延迟,直到没有其他客户端从表中读取为止。这只影响只使用表级锁的存储引擎(例如MyISAM
,内存
,合并
).
与并发
修饰符和MyISAM
表满足并发插入的条件(即中间不包含空闲块)时,其他线程可以同时从表中检索数据加载数据
是执行。此修饰符影响的性能加载数据
一点,即使没有其他线程同时使用该表。
当加载数据
语句结束后,返回一个信息字符串,格式如下:
记录:1删除:0跳过:0警告:0
方法插入值时,在相同的情况下会出现警告插入
声明(见第13.2.5节,“INSERT语句”),但加载数据
还在输入行中字段过少或过多时生成警告。
你可以使用显示警告
拿到第一名的名单max_error_count
警告是关于哪里出错的信息。看到第13.7.5.40节,“SHOW WARNINGS语句”.
如果您正在使用C API,则可以通过调用mysql_info ()
函数。看到mysql_info ().
有关加载数据
关于复制,请参见16.4.1.18节“复制和加载数据”.
在Unix上,如果需要的话加载数据
要从管道中读取数据,可以使用以下技术(本例加载/
目录到表中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 . db1 .dat
在这里,必须运行生成要加载的数据的命令和mysql命令可以在不同的终端上执行,也可以在后台运行数据生成过程(如前面的示例所示)。如果不这样做,管道将阻塞,直到数据被读取mysql的过程。