超大文件CSV导入MySQL

最新推荐文章于 2024-06-18 16:11:11 发布

道空无名

最新推荐文章于 2024-06-18 16:11:11 发布

阅读量1.3k

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qiangweiyan/article/details/105321677

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

使用导入命令之前，先创建好表

CREATE TABLE `demo` (
  `receive_time` varchar(255) DEFAULT NULL,
  `startcode` varchar(255) DEFAULT NULL,
  `commandcode` varchar(255) DEFAULT NULL,
  `ackcode` varchar(255) DEFAULT NULL,
  `vin` varchar(255) DEFAULT NULL,
  `encryption` varchar(255) DEFAULT NULL,
  `messagelength` varchar(255) DEFAULT NULL,
  `messagetype` varchar(255) DEFAULT NULL,
  `database64` text,
  `messagetime` varchar(255) DEFAULT NULL,
  `basemessagetime` varchar(255) DEFAULT NULL,
  `datatype` varchar(255) DEFAULT NULL,
  `highalarmlevel` varchar(255) DEFAULT NULL,
  `generalalarmsign` varchar(255) DEFAULT NULL,
  `totalfailurenumofreess` varchar(255) DEFAULT NULL,
  `failurecodeofreess` varchar(255) DEFAULT NULL,
  `totalfailurenumofdrivingmotor` varchar(255) DEFAULT NULL,
  `failurecodeofdrivingmotor` varchar(255) DEFAULT NULL,
  `totalfailurenumofengine` varchar(255) DEFAULT NULL,
  `failurecodeofengine` varchar(255) DEFAULT NULL,
  `totalfailurenumofother` varchar(255) DEFAULT NULL,
  `failurecodeofother` varchar(255) DEFAULT NULL,
  `drivingmotordata` text,
  `drivingmotortotal` varchar(255) DEFAULT NULL,
  `enginestate` varchar(255) DEFAULT NULL,
  `rmp` varchar(255) DEFAULT NULL,
  `specificfuelrate` varchar(255) DEFAULT NULL,
  `subsystemofmaxvoltagebattery` varchar(255) DEFAULT NULL,
  `unitcodeofmaxvoltagebattery` varchar(255) DEFAULT NULL,
  `maxcellvoltage` varchar(255) DEFAULT NULL,
  `subsystemofminvoltagebattery` varchar(255) DEFAULT NULL,
  `unitcodeofminvoltagebattery` varchar(255) DEFAULT NULL,
  `mincellvoltage` varchar(255) DEFAULT NULL,
  `subsystemofmaxtemperature` varchar(255) DEFAULT NULL,
  `probecodeofmaxtemperature` varchar(255) DEFAULT NULL,
  `maxtemperature` varchar(255) DEFAULT NULL,
  `subsystemofmintemperature` varchar(255) DEFAULT NULL,
  `probecodeofmintemperature` varchar(255) DEFAULT NULL,
  `mintemperature` varchar(255) DEFAULT NULL,
  `fullcellvoltage` varchar(255) DEFAULT NULL,
  `fullcellcurrent` varchar(255) DEFAULT NULL,
  `fuelrate` varchar(255) DEFAULT NULL,
  `fullcellprobetemperaturenum` varchar(255) DEFAULT NULL,
  `fullcellprobetemperature` varchar(255) DEFAULT NULL,
  `hightemperature` varchar(255) DEFAULT NULL,
  `probecodeofhightemperature` varchar(255) DEFAULT NULL,
  `hydrogencmax` varchar(255) DEFAULT NULL,
  `sensorcodeofhydrogencmax` varchar(255) DEFAULT NULL,
  `hydrogenmaxpressure` varchar(255) DEFAULT NULL,
  `sensorcodeofhydrogenpmax` varchar(255) DEFAULT NULL,
  `dcstateofhighpressure` varchar(255) DEFAULT NULL,
  `vehiclestate` varchar(255) DEFAULT NULL,
  `chargingstate` varchar(255) DEFAULT NULL,
  `runningstate` varchar(255) DEFAULT NULL,
  `vehiclespeed` varchar(255) DEFAULT NULL,
  `cumulativejourney` varchar(255) DEFAULT NULL,
  `totalvoltage` varchar(255) DEFAULT NULL,
  `totalcurrent` varchar(255) DEFAULT NULL,
  `soc` varchar(255) DEFAULT NULL,
  `dcstate` varchar(255) DEFAULT NULL,
  `gear` varchar(255) DEFAULT NULL,
  `drivingforce` varchar(255) DEFAULT NULL,
  `brakingforce` varchar(255) DEFAULT NULL,
  `insulationresistance` varchar(255) DEFAULT NULL,
  `acceleratorpedal` varchar(255) DEFAULT NULL,
  `brakepedalstate` varchar(255) DEFAULT NULL,
  `reesstemperaturedata` text,
  `temperaturesubsystemtotal` varchar(255) DEFAULT NULL,
  `reessvoltagedata` text,
  `voltagesubsystemtotal` varchar(255) DEFAULT NULL,
  `locationstate` varchar(255) DEFAULT NULL,
  `longitude` varchar(255) DEFAULT NULL,
  `latitude` varchar(255) DEFAULT NULL,
  `id` varchar(255) DEFAULT NULL,
  `data_date` varchar(255) DEFAULT NULL,
  `receive_newtime` varchar(255) DEFAULT NULL,
  `data_newdate` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

一、LOAD DATA INFILE

mysql --local-infile=1 -uroot -p

load data infile "/var/lib/mysql-files/demo.csv" ignore into table demo character set utf8 fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 lines;

mysql> load data infile "/var/lib/mysql-files/demo.csv" ignore into table demo character set utf8 fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 lines;
Query OK, 2518516 rows affected (5 min 58.12 sec)
Records: 2518516  Deleted: 0  Skipped: 0  Warnings: 0

~~todo 后面再更新，先尝试mysqlimport~~

二、`mysqlimport`工具

格式：

mysqlimport -u root -p [--local] dbname filename.txt [OPTION]

在mysql的bin目录下执行mysqlimport就会出来参数提示

示例：

mysqlimport -uroot -p --local --fields-terminated-by=, --fields-enclosed-by=\" --lines-terminated-by=\n --ignore-lines=1 changcheng C:\Users\adam\Downloads\baiduyun\demo.csv -dfil

如果参数有误，输入密码后，会有一长串的提示，方便检查参数，查错。

执行成功的会有如下输出，数据量大的话，会稍微消耗点时间。

Enter password: ******
changcheng.demo: Records: 2518516  Deleted: 0  Skipped: 0  Warnings: 0

常用参数：

--fields-terminated-by=字符串：设置字符串为字段之间的分隔符，可以为单个或多个字符。默认值为制表符“\t”。

--fields-enclosed-by=字符：设置字符来括住字段的值，只能为单个字符。

--fields-optionally-enclosed-by=字符：设置字符括住CHAR、VARCHAR和TEXT等字符型字段，只能为单个字符。

--fields-escaped-by=字符：设置转义字符，默认值为反斜线“\”。

--lines-terminated-by=字符串：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”。

--ignore-lines=n：表示可以忽略前n行。

--character-sets-dir=path ：字符集的安装目录

--default-character-set=charset_name：导入数据前默认的字符集，这个通常可以避免好多乱码问题。

-d or --delete：新数据导入数据表中之前清空表数据。

-f or --force：不管是否遇到错误，mysqlimport将强制继续插入数据

-i or --ignore：mysqlimport跳过或者忽略那些有相同唯一关键字的行，导入文件中的数据将被忽略。

-l or -lock-tables：数据被插入之前锁住表，这样就防止了，你在更新数据库时，用户的查询和更新受到影响。

-r or -replace：这个选项与－i选项的作用相反；此选项将替代表中有相同唯一关键字的记录。

三、错误解决

1、没有成功执行

由于引号问题，导致了参数解析不正确，如下图所示，--fields-enclosed-by=",双引号被解析成了后面字符串的包裹符号：fields-enclosed-by --lines-terminated-by=\n --ignore-lines=1 changcheng C:\demo.csv

C:\>mysqlimport -uroot -p --local --fields-terminated-by=, --fields-enclosed-by=" --lines-terminated-by=\n --ignore-lines=1 changcheng C:\demo.csv
......
Usage: mysqlimport [OPTIONS] database textfile...
......
The following groups are read: mysqlimport client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
......
Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
bind-address                      (No default value)
......
fields-terminated-by              ,
fields-enclosed-by                 --lines-terminated-by=\n --ignore-lines=1 changcheng C:\demo.csv
fields-optionally-enclosed-by     (No default value)
fields-escaped-by                 (No default value)
ignore-lines                      0
......

解决方法：

将双引号转移，参数改为--fields-enclosed-by=\"

2、–secure-file-priv

执行以下命令时：

C:\>mysqlimport -uroot -p --fields-terminated-by=, --fields-enclosed-by=\" --lines-terminated-by=\n --ignore-lines=1 changcheng C:\demo.csv -dfil
Enter password: ******

报如下错误：

mysqlimport: Error: 1290, The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, when using table: demo

原因是没有开启--local参数，具体原因同下面一个错误command is not allowed with this MySQL version

改成

C:\>mysqlimport -uroot -p --local --fields-terminated-by=, --fields-enclosed-by=\" --lines-terminated-by=\n --ignore-lines=1 changcheng C:\demo.csv -dfil

即可

也可以参考下面的方法A

3、command is not allowed with this MySQL version

报错

mysqlimport: Error: 1148, The used command is not allowed with this MySQL version, when using table: demo

同样的原因，如果用的是load data infile命令，显示如下错误
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

原因

涉及到数据库安全的问题，本地导入文件的参数没有打开。

mysql> SHOW VARIABLES LIKE '%local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

方法A：移动dump文件至指定文件夹下

mysql> show variables like '%secure%';
+--------------------------+------------------------------------------------+
| Variable_name            | Value                                          |
+--------------------------+------------------------------------------------+
| require_secure_transport | OFF                                            |
| secure_file_priv         | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+--------------------------+------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

然后把csv文件，移动至secure_file_priv对应的文件夹下即可

注意：这个操作在windows下，会出现问题，因为文件目录出现了空格，导致命令解析错误，可以用引号把路径括起来

方法B：关闭参数

修改local_infile=ON,或者local_infile=1

mysql> set global local_infile = 'ON';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

注意：

如果用的是load data infile命令需要重新登录，才能生效

在导入完成之后，建议关闭local_infile

道空无名

关注

0
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
超大文件CSV导入MySQL

一、LOAD DATA INFILEtodo 后面再更新，先尝试mysqlimport二、mysqlimport工具格式：mysqlimport -u root -p [--local] dbname filename.txt [OPTION]在mysql的bin目录下执行mysqlimport就会出来参数提示示例：mysqlimport -uroot -p --local ...
复制链接

扫一扫