CODEWITHSUNDEEP
mysqlinto-outfileinfobright
Russ Bradberry
Russ Bradberry

Reputation: 10865

MySQL Select Into Outfile Without Quotes

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"

Upvotes: 2

Views: 3854

Answers (3)

Sebastián Vásquez
Sebastián Vásquez

Reputation: 135

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

Upvotes: 0

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44353

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 | [email protected]  |
|  2 | [email protected] |
+----+--------------------------+
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       [email protected]
2       [email protected]

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 [email protected] redwa
2 [email protected] rolan

C:\LWDBA>type prova8.txt
 1       [email protected]         redwa
 2       [email protected]        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

Upvotes: 0

tadman
tadman

Reputation: 211610

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

Upvotes: 0

Related Questions