创建表 student
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(50),
`age` int(11),
`gender` varchar(1),
`cid` int(11),
`empty` varchar(255)
)
用程序生成测试数据 - 最后一列是测试导入空数据
1,姓名_0,1,女,1,
2,姓名_1,2,男,2,
3,姓名_2,3,女,3,
4,姓名_3,4,男,4,
5,姓名_4,5,女,5,
…此处省略很多行…
9999996,姓名_9999995,6,男,96,
9999997,姓名_9999996,7,女,97,
9999998,姓名_9999997,8,男,98,
9999999,姓名_9999998,9,女,99,
10000000,姓名_9999999,10,男,100,
导入注意事项
- 路径问题需配置 secure-file-priv= (啥都不写)
- TERMINATED BY 后面是自定义分隔符
- 导入时用变量接收 @val1、@val3、@val5,因为默认全是字符串,需将其转为数字后才能存入表 student,所以要 SET id = @val1+ 0, age=@val3+0, cid=@val5+0
- 导入的数量越多,时间就越长,建议不要一次导入千万条,分批导入会比较快
LOAD DATA INFILE 'C:/Users/xxx/Desktop/big_data.txt'
INTO TABLE student
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(@val1, name, @val3, gender, @val5)
SET id = @val1+ 0, age=@val3+0, cid=@val5+0