启动
MySQL客户端(通过–local-infile=1选项)和服务器端(通过修改配置文件)都需要指定可以从LOCAL导入数据,例如:
/mysqld_safe --datadir=/data/mysqldata --skip-grant-tables --local-infile=1
例子
原始数据:
1 Heart disease kills·1.2
2 one out of every two·2.3
3 people in America.·4.5
数据库表结构:
CREATE TABLE foo(myid INT, mymessage VARCHAR(255), mydecimal DECIMAL(8,4));
--- 按照固定格式导入
LOAD DATA LOCAL INFILE '/data/home/gerryyang/test/mysql_load/in.data' INTO TABLE foo COLUMNS TERMINATED BY '\t';
--- 选择字段导入
LOAD DATA LOCAL INFILE '/data/home/gerryyang/test/mysql_load/in.data' INTO TABLE foo
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
(@col1,@col2,@col3) set myid=@col1,mydecimal=@col3;
Refer
http://stackoverflow.com/questions/13579810/how-to-import-data-from-text-file-to-mysql-database
http://downloads.mysql.com/docs/refman-5.0-en.pdf
http://dev.mysql.com/doc/refman/5.7/en/load-data.html