代码之家 › 专栏 › 技术社区 › Mansour

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

into-outfile myisam redhat union mysql

Mansour · 技术社区 · 9 年前

我有奇怪的行为 选择到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^@^@^@"

没有 逃脱者 :

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试图填充这个特殊字符,以在mytable的结构中设置大小。具有修剪我没有这个角色。但我想知道,只有整数和小数才有这种行为是正常的还是错误?我还想知道是否有其他解决方案可以避免使用修剪每个字段?因为我有很多比这个更复杂的查询。

感谢你的帮助和花时间阅读我的帖子

当做

编辑: 我想问题可能来自 进入OUFILE 和类型字段。我没有想到在这种情况下会提到嵌套联合。所以我改变了标题,我在回答我的问题。

1 回复 | 直到 9 年前

Mansour 9 年前

上次编辑: 继续 ,找到的最佳解决方案是强制逃跑带反斜杠和 不要让它空着 :

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 :

如果FIELDS ESCAPED BY字符为空,则不会转义任何字符并且NULL输出为NULL,而不是\N。这可能不是一个好主意指定一个空转义字符,特别是在您的数据包含刚才给出的列表中的任何字符。

....

还要注意,如果指定空的ESCAPED BY值可能无意中生成无法正确读取的输出通过加载数据文件。

以前的解决方案及说明:

我找到了问题的答案。它来自具有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

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

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

这只是一个简单的例子,没有哪里解释一下。在我的例子中,使用嵌套并集是因为我们在不同的条件下具有不同的值。

所以要小心嵌套 联合,联合 具有 无效的 使用导出时的值 进入外场 对于我的情况,解决方案是在一个查询中使用fuse嵌套联合 联合,联合 .

注意,我并没有在Windows7上使用Mysql 5.6.23 Mysql Community Server来重现这种情况。