[FIXED] How can I output MySQL query results in CSV format?

Issue

Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format?

Here's what I'm doing now:

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/        /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ

It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.

Solution

From Save MySQL query results into a text or CSV file:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: That syntax may need to be reordered to

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

in more recent versions of MySQL.

Using this command, columns names will not be exported.

Also note that /var/lib/mysql-files/orders.csv will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.

Answered By - Paul Tomblin

Answer Checked By - Timothy Miller (PHPFixing Admin)

Read More

[FIXED] How can I output MySQL query results in CSV format?

Issue

Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format?

Here's what I'm doing now:

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/        /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ

It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.

Solution

From Save MySQL query results into a text or CSV file:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: That syntax may need to be reordered to

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

in more recent versions of MySQL.

Using this command, columns names will not be exported.

Also note that /var/lib/mysql-files/orders.csv will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.

Answered By - Paul Tomblin

Answer Checked By - Gilberto Lyons (PHPFixing Admin)

Read More

[FIXED] How do I 'exec' a bash command like this where an option is multi-word?

Issue

On the commandline I can run my binary this way:

theBinary_exe -q 'more than one word' -f foo -b bar

When I put that in a bash script and run the script it's all fine. But if I do this in the script:

CMD="theBinary_exe -q 'more than one word' -f foo -b bar"
exec $CMD

The 'q' argument only passes the first word to the executable. I tried using \" as well with no luck. Is there a wrapping I can use to prevent that when I use exec?

Solution

Do not use a regular parameter.

Use an array:

args=( -q 'more than one word' -f foo -b bar)
theBinary_exe "${args[@]}"

Answered By - chepner

Answer Checked By - Mildred Charles (PHPFixing Admin)

Read More

[FIXED] How to use reserved words without backquotes in MySQL

Issue

I would like to use reserved words such as "user" or "right" as table or column names in my databases. Until now I've been using back quotes, but I read somewhere that they are MySQL specific, and I would like to preserve database compatibility. I've also read about using ANSI mode with MySQL to avoid using back quotes, but I want everything in my apps to be UTF-8.

What can I do to use reserved words without using back quotes or losing cross database compatibility ?

Solution

MySQL supports the ANSI standard of using double quotes surrounding identifiers such as table and column names. You have to enable this option since by default MySQL recognizes both single and double quotes as enclosing string literals:

http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_ansi_quotes

Answered By - bobwienholt

Answer Checked By - Willingham (PHPFixing Volunteer)

Read More