|
#创建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导出都可以)
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
将分隔符换成逗号","
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: 11 Deleted: 0 Skipped: 0 Warnings: 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 FIELDS TERMINATED 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
|
|