Mysql INTO OUTFILE 嵌套联合与 NULL 导致整数格式问题

Question

我对 SELECT INTO OUFILE 有奇怪的行为，但我没有找到答案。

要恢复，我有一个这样的表:

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL auto_increment,
  `field1` decimal(10,2) default NULL,
  `field2` int(11) default NULL,
  `field3` tinyint(4) default NULL,
  PRIMARY KEY  (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

当我执行这样的查询时:

SELECT * INTO OUTFILE '/tmp/output.dat' 
FIELDS TERMINATED BY '|' 
OPTIONALLY ENCLOSED BY '\"' 
ESCAPED BY '' 
LINES TERMINATED BY '\n' 
FROM mytable;

我在 output.dat 中得到这样的结果:

"12345678"|"20.00^@^@^@^@^@^@^@"|"1426513906^@"|"0^@^@^@"
"95863214"|"20.00^@^@^@^@^@^@^@"|"1426514075^@"|"1^@^@^@"

没有 ESCAPED BY :

SELECT * INTO OUTFILE '/tmp/output.dat' 
FIELDS TERMINATED BY '|' 
OPTIONALLY ENCLOSED BY '\"' 
LINES TERMINATED BY '\n' 
FROM mytable;

我有这样的结果:

"12345678"|"20.00\0\0\0\0\0\0\0"|"1426513906\0"|"0\0\0\0"
"95863214"|"20.00\0\0\0\0\0\0\0"|"1426514075\0"|"1\0\0\0"

Mysql环境:

"protocol_version";"10"
"version";"5.0.67-community-log"
"version_comment";"MySQL Community Edition (GPL)"
"version_compile_machine";"x86_64"
"version_compile_os";"redhat-linux-gnu"

似乎 mysql 试图用这个特殊字符填充以在 mytble 的结构中设置大小。使用 TRIM 我没有这些字符。但是我想知道只有整数和小数才有这种行为是正常的还是 bug ？我还想知道是否还有其他解决方案可以避免在每个字段中使用 TRIM？因为我有很多比这个更复杂的查询。

感谢您的帮助以及您花时间阅读我的帖子

问候

编辑: 我认为问题可能仅来自 INTO OUFILE 和类型字段。在这种情况下，我没想到要提及嵌套联合。所以我更改了标题并回答了我的问题。

Answer 1

上次编辑: 要恢复，找到的最佳解决方案是用反斜杠强制转义并且不要留空强>:

SELECT * INTO OUTFILE '/tmp/output1.dat' 
FIELDS TERMINATED BY '|' 
ENCLOSED BY '\"' 
ESCAPED BY '\\'
LINES TERMINATED BY '\n' 
FROM 
    ((SELECT
    id_test,
    field2,
    field3
    FROM mytable1)

    UNION

    (SELECT 
    * 
    FROM 
        ((SELECT
        id_test,
        field2,
        NULL AS field3
        FROM mytable2)

        UNION

        (SELECT
        id_test,
        NULL AS field2,
        NULL AS field3
        FROM mytable3)
        ) test)
    ) tmptable;

来自 mysql documentation 的注释:

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

....

Also note that if you specify an empty ESCAPED BY value, it is possible to inadvertently generate output that cannot be read properly by LOAD DATA INFILE.

以前的解决方案及解释:

我找到了问题的答案。它来自具有 NULL 值的嵌套联合。我会用例子来解释它比长篇大论更好。

这里是 mysql 上下文:

DROP TABLE IF EXISTS `mytable1`;

CREATE TABLE `mytable1` (
  `id_test` INT(11) NOT NULL AUTO_INCREMENT,
  `field1` DECIMAL(10,2) DEFAULT NULL,
  `field2` INT(11) DEFAULT NULL,
  `field3` TINYINT(4) DEFAULT NULL,
  PRIMARY KEY  (`id_test`)
) ENGINE=MYISAM AUTO_INCREMENT=95863215 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `mytable` */

INSERT  INTO `mytable1`(`id_test`,`field1`,`field2`,`field3`) VALUES (12345678,20.00,1426513906,0),(95863214,20.00,1426514075,1);

/*Table structure for table `mytable2` */

DROP TABLE IF EXISTS `mytable2`;

CREATE TABLE `mytable2` (
  `id_test` INT(11) NOT NULL AUTO_INCREMENT,
  `field1` DECIMAL(10,2) DEFAULT NULL,
  `field2` INT(11) DEFAULT NULL,
  PRIMARY KEY  (`id_test`)
) ENGINE=MYISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `mytable2` */

INSERT  INTO `mytable2`(`id_test`,`field1`,`field2`) VALUES (1,25.00,12345),(2,11.00,52146);

/*Table structure for table `mytable3` */

DROP TABLE IF EXISTS `mytable3`;

CREATE TABLE `mytable3` (
  `id_test` INT(11) NOT NULL AUTO_INCREMENT,
  `field1` DECIMAL(10,2) DEFAULT NULL,
  `field3` TINYINT(4) DEFAULT NULL,
  PRIMARY KEY  (`id_test`)
) ENGINE=MYISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `mytable3` */

INSERT  INTO `mytable3`(`id_test`,`field1`,`field3`) VALUES (2,12.00,2),(4,23.00,31);

还有环境:

"protocol_version";"10"
"version";"5.0.67-community-log"
"version_comment";"MySQL Community Edition (GPL)"
"version_compile_machine";"x86_64"
"version_compile_os";"redhat-linux-gnu"

或者用这个:

"protocol_version";"10"
"version";"5.0.95-log"
"version_bdb";"Sleepycat Software: Berkeley DB 4.1.24: (December 16, 2011)"
"version_comment";"Source distribution"
"version_compile_machine";"x86_64"
"version_compile_os";"redhat-linux-gnu"

我们用这种查询重现案例:

SELECT * INTO OUTFILE '/tmp/output1.dat' 
FIELDS TERMINATED BY '|' 
ENCLOSED BY '\"' 
ESCAPED BY ''
LINES TERMINATED BY '\n' 
FROM 
    ((SELECT
    id_test,
    field2,
    field3
    FROM mytable1)

    UNION

    (SELECT 
    * 
    FROM 
        ((SELECT
        id_test,
        field2,
        NULL AS field3
        FROM mytable2)

        UNION

        (SELECT
        id_test,
        NULL AS field2,
        NULL AS field3
        FROM mytable3)
        ) test)
    ) tmptable;

我们得到这个结果:

"12345678"|"1426513906"|"0^@^@^@"
"95863214"|"1426514075"|"1^@^@^@"
"1"|"12345"|NULL
"2"|"52146"|NULL
"2"|NULL|NULL
"4"|NULL|NULL

当我们执行相同的查询时，例如将最后一个 NULL 替换为 1 或删除最后一个联合:

SELECT * INTO OUTFILE '/tmp/output1.dat' 
FIELDS TERMINATED BY '|' 
ENCLOSED BY '\"' 
ESCAPED BY ''
LINES TERMINATED BY '\n' 
FROM 
    ((SELECT
    id_test,
    field2,
    field3
    FROM mytable1)

    UNION

    (SELECT 
    * 
    FROM 
        ((SELECT
        id_test,
        field2,
        NULL AS field3
        FROM mytable2)

        UNION

        (SELECT
        id_test,
        NULL AS field2,
        1 AS field3
        FROM mytable3)
        ) test)
    ) tmptable;

我们得到了预期的正确结果:

"12345678"|"1426513906"|"0"
"95863214"|"1426514075"|"1"
"1"|"12345"|NULL
"2"|"52146"|NULL
"2"|NULL|1
"4"|NULL|1

这只是一个简单的例子，没有WHERE 来解释。在我的例子中，使用了嵌套联合，因为我们在不同的条件下有不同的值。

因此，在使用 INTO OUTFILE 导出的情况下，请小心使用带有 NULL 值的嵌套 UNION。对于我的情况，解决方案是在没有 UNION 的情况下在一个查询中融合嵌套联合。

请注意，我没有在 Windows 7 上使用 Mysql 5.6.23 MySQL Community Server 重现此案例。