首先告诉大家关于“MySQL LOAD DATA INFILE - 加载没有主键的文件”是可以操作的，接下来就来实战一把
表imagecode结构可以看到有3列，其中id是自增列。
drop table `imagecode` ;
create table `imagecode` (
`id` int auto_increment,
`url` text,
`code` varchar(255)default null,
primary key(`id`)
)engine=innodb default charset=utf8;
文件imagecode.txt中只含有2列哟，分别对应url，code
# more /var/lib/mysql-files/imagecode.txt
http://www.qyule01.fun/media/videos/tmb/000/005/451/1.jpg###100000
http://pic.rmb.bdstatic.com/81923c4cd60b8731b09cc55a0ee82cb8.jpeg###100000
http://pcookie.cnzz.com/app.gif?&cna=Q/1fFKG8SggCAXRVIh2iBCTo###100000
http://inews.gtimg.com/newsapp_ls/0/6134659193_150120/0###100000
...
接下来我们就来执行导入操作吧
# mysql -h lzctest2 -u root -p'xxxxxx' -D work-dev
mysql> load data infile'/var/lib/mysql-files/imagecode.txt'into table imagecode fields terminated by'###'lines terminated by'\n'(`url`,`code`);
Query OK, 156312 rows affected (1.24 sec)
Records: 156312 Deleted: 0 Skipped: 0 Warnings: 0
进阶篇
MySQL是否支持远程导入数据LOAD DATA LOCAL INFILE呢，答案是可以的。
准备工作
server/client均需要开启“客户端导入本地文件的权限”
server端检查
mysql> show variables like 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set global local_infile = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.00 sec)
---------------------
client端
$ mysql -h lzctest2 -u root -p'xxxxxx' -D work-dev --local-infile=1
Server version: 5.7.23 MySQL Community Server (GPL)
mysql> load data local infile'/Users/home/xx/temp/xxyun/imagecode.txt'into table imagecode fields terminated by'###'lines terminated by'\n'(`url`,`code`);
Query OK, 157015 rows affected (1.94 sec)
Records: 157015 Deleted: 0 Skipped: 0 Warnings: 0
不登陆数据库执行 MySQL 命令
mysql -h lzctest2 -u root -p'xxxxxx' -D work-dev --local-infile=1 -e "load data local infile'/Users/xx/work/temp/xxx/imagecode.txt' into table imagecode fields terminated by'###' lines terminated by'\n' (url,code);"
