MySQL 导出到 CSV - 字段包含 "but are not escaped by "

Question

我有一些表包含带双引号的字段，但它们是“英寸”名称或缩写的一部分，“不会转义该字段。

我需要将其导出到 CSV 文件，但 MySQL 不断破坏 "处的字段并在 CSV 中创建额外的列。如何处理字段值中的 "？

代码:

select
   makers.maker_name,
   baits.bait,
   depth_line.depth,
   depth_line.lineout a
from makers
   inner join baits on makers.maker_id = baits.maker_id
   inner join depth_line on depth_line.bait_id = baits.bait_id
order by
   makers.maker_name asc,
   baits.bait,
   depth_line.depth asc
into outfile '/tmp/muskie.csv'
fields terminated by ',';

诱饵表中的示例数据:

Answer 1

我无法重现该问题。

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.12    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `table0`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `table0` (
    ->   `column0` VARCHAR(255) DEFAULT NULL,
    ->   `column1` VARCHAR(255) DEFAULT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `table0`
    ->   (`column0`, `column1`)
    -> VALUES
    ->   ('"inches"', 'inches'),
    ->   ('4"', 'Lure name, 8", char, char'),
    ->   ('"VALUE 1"', 'NEW VALUE 1');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   `column0`,
    ->   `column1`
    -> FROM
    ->   `table0`
    -> INTO OUTFILE '/path/to/file/muskie.csv'
    -> FIELDS TERMINATED BY ',';
Query OK, 3 rows affected (0.00 sec)

文件:/path/to/file/muskie.csv

"inches",inches
4",Lure name\, 8"\, char\, char
"VALUE 1",NEW VALUE 1

更新

也许添加参数OPTIONALLY ENCLOSED BY '"'会很有用:

SELECT
  `column0`,
  `column1`
FROM
  `table0`
INTO OUTFILE '/path/to/file/muskie.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

文件:/path/to/file/muskie.csv

"\"inches\"","inches"
"4\"","Lure name, 8\", char, char"
"\"VALUE 1\"","NEW VALUE 1"