搜索到
1
篇与
的结果
-
mysql大批量插入数据 客户端连接时加上参数--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) ````
