mysql数据库的批量文件导入

mysql -uroot -p123456

导出报错：

mysql> select * from bdastat.stat into outfile 'test.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

修改my.ini文件

[mysqld]    
secure_file_priv="D:\TEST"  

重启mysql

net stop mysql;net start mysql

登陆mysql查看

mysql> show variables like '%secur%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| require_secure_transport | OFF      |
| secure_auth              | ON       |
| secure_file_priv         | D:\TEST\ |
+--------------------------+----------+

然后再次执行就可以了

mysql> select * from bdastat.stat into outfile 'd:/test/test.txt';
Query OK, 801 rows affected (0.01 sec)

 

1、创建表，这里用的是TEXT字段类型

CREATE TABLE bdastat.`stat` (
  `field1` text,
  `field2` text,
  `field3` text,
  `field4` text,
  `field5` text,
  `field6` text,
  `field7` text,
  `field8` text,
  `field9` text,
  `field10` text,
  `field11` text,
  `field12` text,
  `field13` text,
  `field14` text,
  `field15` text,
  `field16` text,
  `field17` text,
  `field18` text,
  `field19` text,
  `field20` text,
  `field21` text,
  `field22` text,
  `field23` text,
  `field24` text,
  `field25` text,
  `field26` text
)

 

load data infile "D:\test\\文件名.txt" into table 表 fields terminated by '|' lines terminated by '\n' ;

mysql> load data infile "D:/TEST/node9.txt" into table bdastat.stat fields terminated by '|';
Query OK, 802 rows affected (0.13 sec)
Records: 802  Deleted: 0  Skipped: 0  Warnings: 0