MySQL通过自增一列在Select ... into outfile...里面实现CSV导出带字段的效果

2024-07-14 05:51:26   215人阅读

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导出带字段的效果

https://www.u72.net/daima/uwef.html