MySQL method of exporting data in csv format

There are many kinds of schemes. Let me just say 1 briefly:

1. into outfile

SELECT * FROM mytable  

 INTO OUTFILE '/tmp/mytable.csv'  

 FIELDS TERMINATED BY ','  

 OPTIONALLY ENCLOSED BY '"'  

 LINES TERMINATED BY '\n';

 

During my use, I found a particularly serious problem, in which query conditions cannot be inserted, such as where, which means that the whole table can only be exported. I wonder if there is something wrong with my writing method, and if there is something wrong with my writing, please leave a message to me.

The second problem is that path 1 of outfile must have write permissions, and our process permissions 1 of mysql are generally mysql users, so it is best to export to the /tmp directory.

2. By combining sed

mysql -uroot test -e "select ip from server where a.name like '%abc%'"  -N -s | sed -e 's/^/"/g;s/$/"\n/g'; > /tmp/test.csv

The sql statement is executed using the -e argument of the mysql command, followed by -ES27en to remove column names from the output, and -ES28en to remove various underscores from the output.

The sed command is then used to replace all relevant data in the output, with 3, 1. Add ", "at the beginning of the line, and" wrap "at the end of the line," between each field, "to separate.

3. Through mysqldump

mysqldump -u username -p -t  -T/path/to/directory dbname table_name --fields-terminated-by=',' 

That's pretty much the same thing as plan 1.

Let's call it a note