Hi,

I'm trying to load some data from a primary MySQL DB into a VMware image for R&D purposes. Instead of doing a mysqldump of nearly 10G of data, I would like to just select a subset of it and load it into the VMware image.

I'm facing a snag with regard to NULL values.

I've tried doing

$mysql -u user -p -B -e "select * from table where colum='X' limit 50000" > /tmp/test.sql which results in a file of

id datetime varchar A2345 NULL ABC

where the table structure is like this

id varchar(12) null datetime null varchar(10) null

When I try to insert the data tinto my VMware image, I get lots of errors. One of which is that the NULL values is being treated as s string and I get a warning.

if I were to use mysql>select * into outfile '/tmp/test.sql' from temp_table where id='A2345'

I get id datetime varchar A2345 \N ABC

Note that the NULL is now \N which will be interpreted as NULL and the load data infile will work properly w/o errors. The other way to do it would be to use the fields terminated by, eclosed by etc..

However, based on http://dev.mysql.com/doc/refman/5.0/en/select.html it's said that I should be using this syntax instead

[quote] The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host. [/quote]

The problem with the above is that I would get literal NULLs instead of \N and I end up with errors/warnings.

Is there such a thing to be able to do fields terminated by/enclosed by etc??

Thanks