MySQL批量导入数据
#创建user表CREATE TABLE `user` (
`username` varchar(10) DEFAULT NULL,
`sex` tinyint(4) DEFAULT NULL,
`birth` date DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
`phone` varchar(15) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#相关的数据（select 或者outfile导出都可以）
winner1 1993-11-25 甘肃西和县 18393354445 18393355445@163.com
linux 1 1993-11-25 甘肃西和县 18393354445 18393355445@163.com
linux 1 1993-11-27 上海松江 1839335442 1839335544@163.com
linux 1 1993-11-25 甘肃西和县 18393354445 18393355445@163.com
linux 1 1993-11-27 上海松江 1839335442 1839335544@163.com
linux 1 1993-11-27 上海松江 1839335442 1839335544@163.com
linux 1 1993-11-25 甘肃西和县 18393354445 18393355445@163.com
linux 1 1993-11-27 上海松江 1839335442 1839335544@163.com
linux 1 1993-11-27 上海松江 1839335442 1839335544@163.com
linux 1 1993-11-27 上海松江 1839335442 1839335544@163.com
docker1 1993-11-27 上海松江 1839335442 1839ss5544@163.com
将分隔符换成逗号","
awk -F"\t"'{print $1","$2","$3","$4","$5","$6}'user.txt>user.awk
或者：sed"s/\t/,/g"user.txt >user.txt
winner,1,1993-11-25,甘肃西和县,18393354445,18393355445@163.com
linux,1,1993-11-25,甘肃西和县,18393354445,18393355445@163.com
linux,1,1993-11-27,上海松江,1839335442,1839335544@163.com
linux,1,1993-11-25,甘肃西和县,18393354445,18393355445@163.com
linux,1,1993-11-27,上海松江,1839335442,1839335544@163.com
linux,1,1993-11-27,上海松江,1839335442,1839335544@163.com
linux,1,1993-11-25,甘肃西和县,18393354445,18393355445@163.com
linux,1,1993-11-27,上海松江,1839335442,1839335544@163.com
linux,1,1993-11-27,上海松江,1839335442,1839335544@163.com
linux,1,1993-11-27,上海松江,1839335442,1839335544@163.com
docker,1,1993-11-27,上海松江,1839335442,1839ss5544@163.com
加载数据：
mysql> LOAD DATA INFILE '/var/lib/mysql/Docker/user.awk' INTO TABLE user;
Query OK, 11 rows affected, 66 warnings (0.05 sec)
Records: 11Deleted: 0Skipped: 0Warnings: 66
mysql> select* from user;
winner,1,1 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL
指定分隔符
LOAD DATA INFILE '/var/lib/mysql/Docker/user.awk' INTO TABLE user FIELDSTERMINATED BY ','
查看插入结果：
linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| linux,1,19 | NULL | NULL | NULL | NULL | NULL |
| docker,1,1 | NULL | NULL | NULL | NULL | NULL |
| winner | 1 | 1993-11-25 | 甘肃西和县 | 18393354445 | 18393355445@163.com |
| linux | 1 | 1993-11-25 | 甘肃西和县 | 18393354445 | 18393355445@163.com |
| linux | 1 | 1993-11-27 | 上海松江 | 1839335442| 1839335544@163.com|
| linux | 1 | 1993-11-25 | 甘肃西和县 | 18393354445 | 18393355445@163.com |
| linux | 1 | 1993-11-27 | 上海松江 | 1839335442| 1839335544@163.com|
| linux | 1 | 1993-11-27 | 上海松江 | 1839335442| 1839335544@163.com|
| linux | 1 | 1993-11-25 | 甘肃西和县 | 18393354445 | 18393355445@163.com |
| linux | 1 | 1993-11-27 | 上海松江 | 1839335442| 1839335544@163.com|
| linux | 1 | 1993-11-27 | 上海松江 | 1839335442| 1839335544@163.com|
| linux | 1 | 1993-11-27 | 上海松江 | 1839335442| 1839335544@163.com|
| docker | 1 | 1993-11-27 | 上海松江 | 1839335442| 1839ss5544@163.com
页:
[1]