mysql将文件插入数据库_mysql的load data,高速将文本文件,插入数据库中

1语法

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 [ LOW_PRIORITY | CONCURRENT ] [ LOCAL ] INFILE 'file_name.txt'

[ REPLACE | IGNORE ]

INTO TABLE tbl_name

1LOW_PRIORITY:   加载数据被延迟，直道没有其他客户端从表中读取为止

CONCURRENT:   当加载的数据正在进行的时，如果有用户请求则开启新的线程来获取数据

2如果指定了LOCAL 关键字，它将对连接的客户端做出解释：

• 如果指定了LOCAL，客户端主机上的客户端组件读取文件并发送到服务器。可以给出文件的完整路径以确定其精确位置。如果给出的是相对路径，则文件名是相对于客户端组件启动时所在的目录。

• 如果没有指定LOCAL，文件是位于服务器的主机上，并且直接被服务器读取。

当从服务器主机定位文件时，服务器使用下列规则：

• 如果给定完整的路径，服务器使用该路径名。

• 如果给定一个或多个前置构件的相对路径，服务器以相对服务器的数据目录搜索文件。

• 如果给定没有前置构件的文件名，服务器从当前数据库的数据库目录搜寻文件。

3REPLACE 和IGNORE 关键字处理那些与已存在的主键值重复的输入记录

如果指定了REPLACE，输入行将会代替已存在的行(也就是说，主索引值相同的行将作为存在的行)。参考REPLACE 语法 。

如果指定了IGNORE，与已存在行主键值重复的输入行将被跳过。如果不指定二者中的任一个，则操作行为将依赖是否指定了LOCAL关键字。

没有指定LOCAL，则如果发现有重复的键值，将产生一个错误，并忽略文本文件的其余部分。

如果指定了LOCAL，则缺省的操作行为将与指定了IGNORE 的相同；这是因为，在操作过程中，服务器没有办法终止文件的传送。

[ FIELDS

[ TERMINATED BY 'string' ]

[ [OPTIONALLY] ENCLOSED BY 'char' ]   //enclosed by描述的是字段的括起字符。

[ ESCAPED BY 'char'  ]          //escaped by描述的转义字符。默认的是反斜杠 (backslash：\ )

]

[ LINES

[ STARTING BY 'string' ]

[ TERMINATED BY 'string' ]

]

4

数据格式:

"中华之子","100","威武的不行了"

"大米","63","我爱吃"

"苹果","15","好吃"

load data infile 'D:/aa.txt' ignore into table 表名 character set gbk fields terminated by ',' enclosed by '"' lines terminated by '\r\n' (表中的字段1,表中的字段2,表中的字段3);

terminated by ','  用什么风格字符

enclosed by '"'    字符用什么包起来的

lines terminated by '\r\n'   行的末尾换行符是什么(liunx改为\n)

[ IGNORE number LINES ]

[ (col_name_or_user_var,...) ]

[ SET col_name = expr,...] ]

5IGNORE number LINES选项可被用来忽略在文件的某一行

假设数据文件如下：

Book1.csv

编号,名称,说明

1,测试数据1,"测试CSV文件中,有逗号"

2,测试数据2,"测试CSV文件中有""双引号"""

3,测试数据3,"测试CSV文件中,有逗号和""双引号"""

4,测试数据4,普通数据

mysql> CREATE TABLE Test_Book1 (

->   id    int,

->   name  VARCHAR(10),

->   data  VARCHAR(100)

-> );

Query OK, 0 rows affected (0.05 sec)

下面的 lines terminated by '\r\n' 是 要求换行符号,为 windows的换行

下面的 ignore 1 lines是 忽略第一行的标题行。

mysql> LOAD DATA INFILE 'f:/Book1.csv'

-> INTO TABLE Test_Book1

-> FIELDS TERMINATED BY ','

-> OPTIONALLY ENCLOSED BY '"'

-> lines terminated by '\r\n'

-> ignore 1 lines

-> (id, name, data);

mysql> select * from test_book1;

+------+-----------+--------------------------------+

| id   | name      | data                           |

+------+-----------+--------------------------------+

|    1 | 测试数据1 | 测试CSV文件中,有逗号           |

|    2 | 测试数据2 | 测试CSV文件中有"双引号"        |

|    3 | 测试数据3 | 测试CSV文件中,有逗号和"双引号" |

|    4 | 测试数据4 | 普通数据                       |

+------+-----------+--------------------------------+

ignore 1 lines 忽略第一行的标题行。

6@dummy的使用

假设数据文件如下：

Book1.csv

1,测试数据1,"测试CSV文件中,有逗号"

2,测试数据2,"测试CSV文件中有""双引号"""

3,测试数据3,"测试CSV文件中,有逗号和""双引号"""

4,测试数据4,普通数据

mysql> CREATE TABLE Test_Book1 (

->   id    int,

->   name  VARCHAR(10),

->   data  VARCHAR(100)

-> );

Query OK, 0 rows affected (0.05 sec)

我只需要第一,二列的数据的时候

mysql> LOAD DATA INFILE 'f:/test_main.txt'

-> INTO TABLE test_main6

-> FIELDS TERMINATED BY ','

-> OPTIONALLY ENCLOSED BY ''''

-> (id,name, @dummy);

mysql> select * from test_book1;

+------+-----------+--------------------------------+

| id   | name      | data                           |

+------+-----------+--------------------------------+

|    1 | 测试数据1 |                                |

|    2 | 测试数据2 |                                |

|    3 | 测试数据3 |                                |

|    4 | 测试数据4 |                                |

+------+-----------+--------------------------------+

这里的关键点， 在于那个   @dummy。

对你而言， 如果文件有 10列， 你只导 2 ， 7 的话， 那就是

@dummy,列2,@dummy,@dummy,@dummy,@dummy,列7,@dummy,@dummy,@dummy

7set子句

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, @var1)

SET column2 = @var1/100;

也可以让前后列的数据产生关系(假设第一列是一个日期格式的数据,我的第三列可以根据第一列的日期计算出星期信息然后存入数据库)

LOAD DATA INFILE 'file.txt'

INTO TABLE t1

(column1, column2)

SET column3 = SET name=CASE WHEN DATE_FORMAT(date,'%w') =1 THEN "星期一"

WHEN DATE_FORMAT(date,'%w') =2 THEN "星期二"

end;

使用列/变量列表和SET子句要受到一下限制：

• SET 子句中的赋值列名应当只能在赋值操作符的左边。

• 在SET 赋值语句中，可以使用子查询。此查询返回一个将被赋予列的值，它可能仅仅是一个标量查询。不能用子查询去查询将被导入的表。

• 对于列/变量列表或SET 子句，由于使用IGNORE 子句而被忽略的行就不会被处理。

• 由于用户变量没有显示宽度，当导入的数据是行固定格式时，将不能使用用户变量。

8设置存入时候使用的字符

gbk  utf8

load data infile 'C:\\Users\\edgewalk\\Desktop\\test.txt' ignore into table test character set gbk fields terminated by ','

lines terminated by '\n' (

date,

money,

desctipt

)

2使用load data infile导入导出备份数据

如果要导出一个表中的部分字段或者部分符合条件的记录，需要用到了mysql的into outfile 和 load data infile 。 例如下面的mysql命令是把select的mytable表中的数据导出到C:\\Users\\7q\\Desktop\\test1.txt。

select * from test into outfile 'C:\\Users\\7q\\Desktop\\test1.txt' fields terminated by ','  lines terminated by '\r\n' ;

假如要导入刚才备份的数据，可以使用load file方法，例如下面的mysql命令，把导出的数据导入了mytable_bak的表中：

load data infile 'C:\\Users\\7q\\Desktop\\test1.txt' into table mytable_bak fields terminated by ',' lines terminated by '\r\n';

这种方法的好处是，导出的数据可以自己规定格式，并且导出的是纯数据，不存在建表信息，你可以直接导入另外一个同数据库的不同表中.