標籤：
CSV的範例中Select ... into outfile...是不帶欄位名的，只匯出資料，所以需要自己想另外的辦法實現，這裡有一個笨招，自己構造一個欄位列，樣本如下：
1，建立測試表資料
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，通過union all實現欄位列，並且排在第一行
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 BY ‘,‘
OPTIONALLY ENCLOSED BY ‘"‘
LINES TERMINATED BY ‘\n‘;
執行如下：
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 BY ‘,‘
-> OPTIONALLY ENCLOSED BY ‘"‘
-> LINES TERMINATED BY ‘\n‘;
Query OK, 5 rows affected (0.00 sec)
3，去開啟c.csv檔案查看效果
MySQL通過自增一列在Select ... into outfile...裡面實現CSV匯出帶欄位的效果
