MySQL automatically adds a column in Select... into outfile...to show the effect of csvleading fields, outfile..csv
Select... into outfile... it only exports data without field names, so you need to find another way to implement it. Here is a clumsy way to construct a field column by yourself, for example:
1. Create test table data
Create table test. c SELECT 1 AS pid, 1 AS item, 15.0 AS wgt UNION ALL
SELECT 1 AS pid, 2 AS item, 20.0 AS wgt UNION ALL
SELECT 1 AS pid, 3 AS item, 30.0 AS wgt UNION ALL
SELECT 1 AS pid, 4 AS item, 29.0 AS wgt;
SELECT * FROM test. c;
Mysql> SELECT * FROM test. c;
+ ----- + ------ +
| Pid | item | wgt |
+ ----- + ------ +
| 1 | 1 | 15.0 |
| 1 | 2 | 20.0 |
| 1 | 3 | 30.0 |
| 1 | 4 | 29.0 |
+ ----- + ------ +
4 rows in set (0.00 sec)
2. Use union all to implement the field column and rank it in the first row.
SELECT * FROM (
SELECT 'pid 'AS pid, 'item' AS item, 'wgt' AS wgt
UNION ALL
SELECT * FROM test. c
) A into outfile '/tmp/c.csv'
Fields terminated ','
Optionally enclosed '"'
Lines terminated by '\ n ';
Run the following command:
Mysql> SELECT * FROM (
-> SELECT 'pid 'AS pid, 'item' AS item, 'wgt' AS wgt
-> UNION ALL
-> SELECT * FROM test. c
->) A into outfile '/tmp/c.csv'
-> Fields terminated ','
-> Optionally enclosed '"'
-> Lines terminated by '\ n ';
Query OK, 5 rows affected (0.00 sec)
3. Open the c.csv file to view the effect.
Mysql export csv
In this way, you can:
Select * from mysql table into outfile 'csv table'