MySQL Forums
Forum List  »  Newbie

Re: Mysql INTO OUTFILE adding specials characters in the end of interger and decimal
Posted by: M Mansour
Date: March 27, 2015 10:21AM

I thought that the problem probably comes from INTO OUFILE and type fields only. I didn't think to mention nested unions in this case.

I found the answer to my problem anyway. It's come from the nested unions with NULL values. I'll explain with example it's better than long speach.

Here the mysql context :

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);

And with environment :

"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"

Or with this one :

"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"

We reproduce the case with this kind of query :

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;

We obtain this result :

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

When we execute the same query with replacing the last NULL with 1 for example or with removing the last union :

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;

We obtain the expected rigth result :

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

This is just a simple example without WHERE to explain. In my case, the nested union is used because we have different values in different conditions.

So be careful with nested UNION with NULL values in the case of export with INTO OUTFILE. For my case the solution was the fuse nested unions in one query without UNION.

Note that I didn't reproduce this case with Mysql 5.6.23 MySQL Community Server on Windows 7.

Options: ReplyQuote


Subject
Written By
Posted
Re: Mysql INTO OUTFILE adding specials characters in the end of interger and decimal
March 27, 2015 10:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.