mysql大批量插入数据
侧边栏壁纸
博主昵称
404

  • 累计撰写 17 篇文章
  • 累计收到 0 条评论

mysql大批量插入数据

404
404
2022-07-04 / 0 评论 / 5 阅读 / 正在检测是否收录...
客户端连接时加上参数--local-infile
mysql --local-infile -u root -p
设置全局参数local_infile = 1,开启从本地加载文件导入数据的开关
select @@local_infile; //查看是否开启 
set global local_infile = 1;//开启
上传表结构
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `brithday` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `sex` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
wc -l ceshi.sql //显示567002行数据
567002 ceshi.sql 

head ceshi.sql //查看前面十行的记录
null,sadasdasd,qweqwe,qweqwe,2020-10-14,1
null,sadasdasd,qweqwe,qweqwe,2020-10-14,1
null,sadasdasd,qweqwe,qweqwe,2020-10-14,1
null,sadasdasd,qweqwe,qweqwe,2020-10-14,1
null,sadasdasd,qweqwe,qweqwe,2020-10-14,1
null,sadasdasd,qweqwe,qweqwe,2020-10-14,1
null,sadasdasd,qweqwe,qweqwe,2020-10-14,1
null,sadasdasd,qweqwe,qweqwe,2020-10-14,1
null,sadasdasd,qweqwe,qweqwe,2020-10-14,1
null,sadasdasd,qweqwe,qweqwe,2020-10-14,1

`

load data local infile '/Applications/MxSrvs/www/ceshi.sql' into table user fields terminated by ',' lines terminated by '\n';

into table user //往user表插入数据
fields terminated by ',' //每一个字段使用,号分隔
lines terminated by '\n' //每一行使用\n分隔

----------------------------------------------------------------------
mysql> load data local infile '/Applications/MxSrvs/www/ceshi.sql' into table user fields terminated by ',' lines terminated by '\n';
Query OK, 567002 rows affected, 65535 warnings (15.44 sec)
Records: 567002  Deleted: 0  Skipped: 0  Warnings: 567012

mysql> mysql> select*) from user;
+----------+
| count(*) |
+----------+
|   567002 |
+----------+
1 row in set (0.05 sec)
```` 


0

评论 (0)

取消