在haocong的博客 《Mysql数据库和Linux系统常用命令 》中看到8:用文本方式将数据装入数据库表中(例如D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE;请问文本文件mysql.txt中的数据是什么结构啊?能给个例子吗?我用这种方法导入数据后只有Id有值,其他列的值都为空。
...全文
5225打赏收藏
用文本方式将数据装入数据库表中
在haocong的博客 《Mysql数据库和Linux系统常用命令 》中看到8:用文本方式将数据装入数据库表中(例如D:/mysql.txt) mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE;请问文本文件mysql.txt中的数据是什么结构啊?能给个例子吗?我用这种方法导入数据后只有Id有值,其他列的值都为空。
mysql> CREATE TABLE jokes -> (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> joke TEXT NOT NULL);mysql> 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),如果您忽略了词语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"如果您指定了OPTINALLY,则ENCLOSED BY字符只被用于包含具有字符串数据类型(比如CHAR, BINARY, TEXT或ENUM)的列中的值:
1,"a string",100.202,"a string containing a , comma",102.203,"a string containing a \" quote",102.204,"a string containing a \", quote and comma",102.20注意,如果在字段值内出现ENCLOSED BY字符,则通过使用ESCAPED BY字符作为前缀,对ENCLOSED BY字符进行转义。另外,要注意,如果您指定了一个空的ESCAPED BY值,则可能会生成不能被LOAD DATA INFILE正确读取的输出值。例如,如果转义符为空字符,则刚显示的先前输出值应显示如下。请观察,第四行中的第二个字段在引号后面包含一个逗号,该引号(错误地)显示出来,作为字段的结尾:
1,"a string",100.202,"a string containing a , comma",102.203,"a string containing a " quote",102.204,"a string containing a ", quote and comma",102.20对于输入值,ENCLOSED BY字符被从字段字的末尾剥离。(不论OPTIONALLY是否被指定都会剥离;OPTIONALLY对输入值的解释没有影响。)如果ENCLOSED BY字符前面带有ESCAPED BY字符,则被理解为当前字段值的一部分。
如果字段以ENCLOSED BY字符为开始,当出现这类字符时,只有后面接着字段或行TERMINATED BY序列时,这类字符被认为是一个字段值的结尾。为了避免意思不明确,当在一个字段值中出现ENCLOSED BY字符时,此字符可以重复书写,并被理解为单一的字符。例如,如果指定了ENCLOSED BY '"',则按照以下方法操作引号:
"The ""BIG"" boss" -> The "BIG" bossThe "BIG" boss -> The "BIG" bossThe ""BIG"" boss -> The ""BIG"" bossFIELDS ESCAPED BY用于控制如何写入或读取特殊字符。如果FIELDS ESCAPED BY字符不是空字符,则可以在输出中用于对以下字符加前缀:
13.2.5. LOAD DATA INFILE语法
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...)]LOAD DATA INFILE语句用于高速地从一个文本文件中读取行,并装入一个表中。文件名称必须为一个文字字符串。
要了解有关INSERT和LOAD DATA INFILE的效率的对比和有关LOAD DATA INFILE加速的更多信息,请参见7.2.16节,“INSERT语句的速度”。
您也可以通过使用mysqlimport应用程序载入数据文件;通过向服务器发送一个LOAD DATA INFILE语句实现此功能。--local选项用于使mysqlimport从客户主机中读取数据文件。如果客户端和服务器支持压缩协议,则您可以指定—compress选项提高在慢速网络中的性能。请参见8.10节,“mysqlimport:数据导入程序。
如果您对一个空的MyISAM表使用LOAD DATA INFILE,则所有的非唯一索引会被创建在一个独立批中(对于REPAIR TABLE)。当您有许多索引时,这通常会使LOAD DATA INFILE大大加快。通常,LOAD DATA INFILE的速度会非常快,但是在某些极端情况下,您可以在把文件载入到表中之前使用ALTER TABLE...DISABLE KEYS关闭LOAD DATA INFILE,或者在载入文件之后使用ALTER TABLE...ENABLE KEYS再次创建索引,使创建索引的速度更快。请参见7.2.16节,“INSERT语句的速度”。
LOAD DATA INFILE是SELECT...INTO OUTFILE的补语。(见13.2.7节,“SELECT语法”。)要从一个表中把数据写入一个文件中,应使用SELECT...INTO OUTFILE。要读取文件,放回到表中,应使用LOAD DATA INFILE。FIELDS和LINES子句的语法对于两个语句是一样的。两个子句都是自选的,但是如果两个都被指定了,FIELDS必须位于LINES的前面。
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'如果您不指定LINES子句,则默认值为假设您写下如下语句时的值:
LINES TERMINATED BY '\n' STARTING BY ''换句话说,当读取输入值时,默认值会使LOAD DATA INFILE按如下方式运行:
· 在新行处寻找行的边界。
· 不会跳过任何行前缀。
· 在制表符处把行分解为字段。
· 不希望字段被包含在任何引号字符之中。
· 出现制表符、新行、或在‘\’前有‘\’时,理解为作为字段值一部分的文字字符。
相反的,当编写输出值时,默认值会使SELECT...INTO OUTFILE按如下方式运行:
· 在字段之间写入制表符。
· 不把字段包含在任何引号字符中。
· 当字段值中出现制表符、新行或‘\’时,使用‘\’进行转义。
· 在行的末端写入新行。
注意,要写入FIELDS ESCAPED BY ‘\\’,您必须为待读取的值指定两个反斜杠,作为一个单反斜杠使用。
注释:如果您已经在Windows系统中生成了文本文件,您可能必须使用LINES TERMINATED BY ‘\r\n’来正确地读取文件,因为Windows程序通常使用两个字符作为一个行终止符。部分程序,比如WordPad,当编写文件时,可能会使用\r作为行终止符。要读取这样的文件,应使用LINES TERMINATED BY ‘\r’。
IGNORE number LINES选项可以被用于在文件的开始处忽略行。例如,您可以使用IGNORE 1 LINES来跳过一个包含列名称的起始标题行:
mysql> LOAD DATA INFILE '/tmp/test.txt' -> INTO TABLE test IGNORE 1 LINES;当您联合使用SELECT...INTO OUTFILE和LOAD DATA INFILE来从一个数据库中把数据写入一个文件中,然后再读取文件,返回到数据库中时,用于两个语句的field-和line-handling选项必须匹配。否则,LOAD DATA INFILE不会正确地理解文件的内容。假设您使用SELECT...INTO OUTFILE来编写一个的文件,字段由逗号分隔:
mysql> SELECT * INTO OUTFILE 'data.txt' -> FIELDS TERMINATED BY ',' -> FROM table2;要读取由逗号分隔的文件并返回,则正确的语句应该是:
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格式的文件具有以逗号分隔并且包含在双引号中的字段。如果文件中的各行以新行为结尾,则此处所示的语句描述了您可以用于载入文件的field-和line-handling选项:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' -> LINES TERMINATED BY '\n';所有field-或line-handling选项都可以指定一个空字符串('')。如果字符串不是空的,则FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必须为单一字符。FIELDS TERMINATED BY, LINES STARTING BY和LINES TERMINATED BY值可以超过一个字符。例如,要编写由回车/换行成对字符作为结尾的行,或读取包含这类行的文件,则应指定一个LINES TERMINATED BY ‘\r\n’子句。