Mysql load data infile

Mysql load data infile可以将文件批量导入到数据库中，分为服务器和客户端导入数据；

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_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]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

对于客户端导入数据，使用load data local infile导入数据，而服务器端则无需使用local；

对于客户端导入数据而言，客户端先将数据放到服务器的系统temp目录下（如果有指定 secure_file_priv属性，则文件必须在该目录下），然后服务器读取文件数据，写入数据库；如果数据出现冲突，服务器无法停止写数据（服务器端则可以），可以使用REPLACE 或者 IGNORE规定如何处理冲突；replace为替换冲突；ignore则忽略新的冲突，保留第一个值，local默认是ignore；当服务器端发现冲突时，余下数据会被忽略，抛出异常（所有数据都不能入库）；

对于服务端而言，如果路径名是绝对路径，服务器则使用绝对路径读取；对于./myfile.txt类型的相对路径，则从sql服务器的data目录里寻找；对于myfile.txt类型的相对路径，则从默认的数据库的目录里寻找；

在windows生成文件（目前测试到手工生成，按enter键换行）需要使用到LINES TERMINATED BY '\r\n' ，因为windows系统使用两个字符作为一行的结束符。对于程序生成的，还未发现terminated需要用两个字符。

 使用LINES STARTING BY 'prefix_string'，如果一行中包含prefix_string，那么则将prefix_string后面的数据导入到数据库中，前面的数据舍弃；如果一行数据中不包括prefix_string，那么这行数据将被舍弃。

FIELDS [OPTIONALLY] ENCLOSED BY 输出的字段前后加入特定的单个字符，如果省略了OPTIONALLY，则所有输出字段都会添加，如（以,为分隔符，enclosed by '"'

"1","a string","100.20"

如果指定了OPTIONALLY，则仅对String类型的类添加enclosed by的字符，如CHAR, BINARY, TEXT, or ENUM，

1,"a string",100.20

 对于输入，ENCLOSED BY 字符如果存在，它将从字段值的尾部被剥离。（不管OPTIONALLY 是否被指定，都是这样；对于输入解释，OPTIONALLY 不会影响它。）如果在ENCLOSED BY 字符前存在ESCAPED BY 字符，那么它将被解释为当前字段值的一部分。假设enclosed by '"',escaped by '\\',那么”abc\""的值才会被解析成当前字段的一部分，如果是”abc\",则会抛出异常。

FIELDS ESCAPED BY 控制如何写入或读出特殊字符。如果FIELDS ESCAPED BY 字符不是空的，它将被用于做为下列输出字符的前缀：

•     FIELDS ESCAPED BY 字符

•     FIELDS [OPTIONALLY] ENCLOSED BY 字符 。

•     FIELDS TERMINATED BY 和LINES TERMINATED BY 值的第一个字符。

•     ASCII 0 （实际上在转义字符后写上ASCII '0'，而不是一个零值字节）。

默认情况下，当LOAD DATA INFILE 语句后没有提供字段列时，那么LOAD DATA INFILE 认为输入行包含表列中所有的字段。如果用户希望装载表中的某些列，那指定一个字段列表：

sqlcli> LOAD DATA INFILE 'persondata.txt'

-> INTO TABLE persondata (col1,col2,...);

可以使用set在load data时为某些字段设置值，如

LOAD DATA INFILE 'persondata.txt'

INTO TABLE persondata (col1,col2)

set col3 = current_time;

(col1,col2,...)在lines 后面。

 

个人实践后特殊值说明：

对于varchar类型的，如果文件中为空（即两个分隔符之间没有任何数据），数据库保存为空字符；

对于timestamp类型的，如果使用local属性，对于空数据，则会被设置为适合列类型的“零”值；如果没有指定local，则无法入库，直接抛出异常。

 

网上找到官网load data infile的翻译：http://blog.sina.com.cn/s/blog_7ed5a808010147oy.html