MySQL Select Into Outfile Without Quotes

Question

Is it possible, and if so how, can I SELECT ... INTO OUTFILE and have it not enclose with any character.

So far this doesn't work:

  SELECT hour_stamp,
         day_stamp,
         month_stamp,
         hour,
         day,
         month,
         year,
         quarter,
         day_of_week,
         week_of_year,
         SUBSTR(hour_text,1,24),
         SUBSTR(day_text,1,24)
    FROM date_dim
  INTO OUTFILE '/media/ssd0/temp/dates.tsv' 
  FIELDS TERMINATED BY '\t'
  ENCLOSED BY '';

I'm not sure if the engine matters in this case, but it may be important to note that I am using InfoBright on a Linux machine.

The output is as follows:

1293840000000   1293840000000   1293840000000   0   1   1   2011    1   5   52  "2011-01-01T00:00:00"   "2011-01-01T00:00:00"
1293843600000   1293840000000   1293840000000   1   1   1   2011    1   5   52  "2011-01-01T01:00:00"   "2011-01-01T00:00:00"
1293847200000   1293840000000   1293840000000   2   1   1   2011    1   5   52  "2011-01-01T02:00:00"   "2011-01-01T00:00:00"
1293850800000   1293840000000   1293840000000   3   1   1   2011    1   5   52  "2011-01-01T03:00:00"   "2011-01-01T00:00:00"

If you're *positive* that the `"` character will never occur in the output you can pipe it through `tr -d '"'`. — Sammitch, Jan 04 '13 at 22:51
yeah, I am positive of there is no `"` in there. I'm beginning to think I'll have to to do it this way. — Russ Bradberry, Jan 04 '13 at 23:06

score 0 · Answer 1 · answered Jan 04 '13 at 22:23

0

Adding OPTIONALLY ENCLOSED BY '' might have the desired effect.

answered Jan 04 '13 at 22:23

tadman

208,517
23
234
262

RolandoMySQLDBA · Answer 2 · 2013-01-04T22:49:22.333

Try it without anything:

  SELECT hour_stamp,
         day_stamp,
         month_stamp,
         hour,
         day,
         month,
         year,
         quarter,
         day_of_week,
         week_of_year,
         SUBSTR(hour_text,1,24),
         SUBSTR(day_text,1,24)
    FROM date_dim
  INTO OUTFILE '/media/ssd0/temp/dates.tsv';

Here is a sample

mysql> desc veto.prova;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| provaemail | varchar(255) | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
2 rows in set (0.05 sec)

mysql> select * from veto.prova;
+----+--------------------------+
| id | provaemail               |
+----+--------------------------+
|  1 | redwards@logicworks.net  |
|  2 | rolandoedwards@yahoo.com |
+----+--------------------------+
2 rows in set (0.00 sec)

mysql> select id,provaemail from prova into outfile 'C:/lwdba/prova.txt';
Query OK, 2 rows affected (0.01 sec)

mysql>

What does it look like on disk ???

C:\>cd lwdba

C:\LWDBA>type prova.txt
1       redwards@logicworks.net
2       rolandoedwards@yahoo.com

C:\LWDBA>

I tried something weird. I terminated with \0

mysql> select id,provaemail,substr(provaemail,1,5) from prova into outfile 'C:/lwdba/prova9.txt' fields terminated by '\0';
Query OK, 2 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                |
+---------+------+------------------------------------------------------------------------------------------------------------------------+
| Warning | 1475 | First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY |
+---------+------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select id,provaemail,substr(provaemail,1,5) from prova into outfile 'C:/lwdba/prova8.txt' fields enclosed by '\0';
Query OK, 2 rows affected (0.00 sec)

mysql>

The files look like the this:

C:\LWDBA>type prova9.txt
1 redwards@logicworks.net redwa
2 rolandoedwards@yahoo.com rolan

C:\LWDBA>type prova8.txt
 1       redwards@logicworks.net         redwa
 2       rolandoedwards@yahoo.com        rolan

C:\LWDBA>

I used \0 because it is a null character.

The double quote anomaly you are seeing is probably due to the InfoBright Storage Engine and how it renders character output of function calls.

Here is weird suggestion, but I don't know if it will work...

If you make a subquery, data are always stored in a MySQL temp table. Alter the query:

mysql> select * from (select id,provaemail,substr(provaemail,1,5) as stuff from prova) A
-> A into outfile 'C:/lwdba/prova444.txt' fields terminated by '\0' enclosed by '\0';
Query OK, 2 rows affected (0.00 sec)

mysql>

In you case, that would be

SELECT * FROM (
  SELECT hour_stamp,
         day_stamp,
         month_stamp,
         hour,
         day,
         month,
         year,
         quarter,
         day_of_week,
         week_of_year,
         SUBSTR(hour_text,1,24) ht,
         SUBSTR(day_text,1,24) dt
    FROM date_dim) A
  INTO OUTFILE '/media/ssd0/temp/dates.tsv';

See if that does something different

They are probably terminated that way because they are function calls. — RolandoMySQLDBA, Jan 04 '13 at 22:30
if I remove the function calls I get the same effect but with a right pad of about 100 characters. the function call was to remove the pad. — Russ Bradberry, Jan 04 '13 at 22:33
Sorry, I failed to mention I am doing this with MySQL 5.5. for Windows — RolandoMySQLDBA, Jan 04 '13 at 22:38
I'm using the InfoBright Engine on Linux. I'm not sure where the differences lie between MySQL and InfoBright. — Russ Bradberry, Jan 04 '13 at 22:40
I wouldn't recommend using `\0` in your output unless whatever you're using to read them in *also* knows how the nulls are being used. — Sammitch, Jan 04 '13 at 22:50
@Sammitch I know my suggestion is not "one size fits all." Here it could be worse. I could have said use backspace, `\b`. — RolandoMySQLDBA, Jan 04 '13 at 22:52

score 0 · Answer 3 · answered Nov 12 '14 at 18:05

Did you try with "Null"?

SELECT hour_stamp,
     day_stamp,
     month_stamp,
     hour,
     day,
     month,
     year,
     quarter,
     day_of_week,
     week_of_year,
     SUBSTR(hour_text,1,24),
     SUBSTR(day_text,1,24)
FROM date_dim
INTO OUTFILE '/media/ssd0/temp/dates.tsv' 
FIELDS TERMINATED BY '\t'
ENCLOSED BY 'NULL';

MySQL Select Into Outfile Without Quotes

3 Answers3