Mysql 加载数据本地infile，字段中有双引号和逗号

发布于03月22日

我正在try 将数据导入表.目前我正在努力

LOAD DATA local INFILE "C:/PRINT DAILY DOC_CommaDelimited.txt"
into table daily_doc_report_full
fields terminated by ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 5 LINES;

导入数据如下所示

"   1","SALES DEPT SALES             ","    0"," -285,723","

当它导入第4个字段中的逗号会扰乱数据导入的方式.

数据应该是这样的

| Line| Description      | today | Month_to_date|
|    1| Sales Dept Sales |   0.00|   -285,723.00|

相反，数据被截断并显示

| Line| Description      | today | Month_to_date|
|    1| Sales Dept Sales |   0.00|       -285.00|

我已经try 了对加载数据本地infile查询的各种更改，比如从ENCLOSED BY '"'中删除OPTIONALLY，但我就是不能让它按预期的方式工作.我使用MYSQL 8.0

推荐答案

MySQL不理解用逗号表示千位分隔符的数字.如果试图将包含逗号的字符串转换为数字，则它将忽略从逗号到结尾的所有字符.

这与LOAD DATA INFILE或括起的引号无关.我们可以用一个简单的CAST()表达式演示同样的问题:

mysql> select cast('-285,723' as decimal);
+-----------------------------+
| cast('-285,723' as decimal) |
+-----------------------------+
|                        -285 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select cast('-285723' as decimal);
+----------------------------+
| cast('-285723' as decimal) |
+----------------------------+
|                    -285723 |
+----------------------------+

您可以使用LOAD DATA INFILE解决这个问题，方法是先将字符串复制到用户变量，然后从用户变量中删除逗号.

我用MySQL 8.3测试了这一点(它在任何版本的MySQL上都应该是一样的):

mysql> LOAD DATA local INFILE 'c.csv' into table mytable 
  fields terminated by ',' 
  OPTIONALLY ENCLOSED BY '"' 
  (line, description, @today, @month_to_date) 
  set today = replace(@today, ',', ''), 
      month_to_date = replace(@month_to_date, ',', '');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from mytable;
+------+-------------------------------+-------+---------------+
| line | description                   | today | month_to_date |
+------+-------------------------------+-------+---------------+
|    1 | SALES DEPT SALES              |  0.00 |    -285723.00 |
+------+-------------------------------+-------+---------------+