how to indent results of mysql SELECT INTO OUTFILE

I have an sql table that I want to dump to an outfile. The thing is, some of the results are 1 digit, i.e 1 or 2, while others are long numbers, i.e. 1.2323523

This causes the outfile to look terrible, something like this

1   a bbb
1.21321342 aaaa bbbbb 

meaning, the beginning of each column is not aligned. Does anyone know if there is a wa开发者_StackOverflow中文版y in sql to take care of that?

Thanks!!!

cast the columns as char(n), eg

select cast(my_numeric_column as char(10)) as my_numeric_column

This will be output fixed-width column data. They will however be left justified.

A better solution would be to give the same number of decimal places to all numbers, 1 would be output as "1.000000" etc. This is how to do that:

select cast(my_numeric_column as decimal(16,8)) as my_numeric_column

Chose the precision (number of places - here 8) you want.

All columns can be treated in this way to produce a nicely tabulated format.

use like this u get a csv file, so in excel its look pretty

SELECT * 
FROM mytable
INTO OUTFILE '/tmp/table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

You can use one of the string manipulation functions in mysql. Notably, LPAD or FORMAT.