如何使用mysql中的列名将查询结果保存到excel文件中

2 回答

• 1

  使用以下查询：

  SELECT empid as employeeid,sname as staffname FROM employeemaster INTO
  OUTFILE 'c:/order.csv' FIELDS 
  TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
  

  回复于 2024-05-19T20:38:45+08:00
• 2

  有两种可能的解决方案：

  简单的一个：自己对colum名称进行硬编码，并使用查询结果加入 Headers 行：

  SELECT 'Name_I_Want_For_Col1', 'Name_I_Want_For_Col1', 'Name_I_Want_For_Col1'
  UNION ALL
  SELECT ColName1, ColName2, ColName3
      FROM YourTable
      INTO OUTFILE '/path/outfile'
  

  更直接（但更复杂）：从信息模式中获取列名称：

  select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'"))
  from INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'my_table'
  AND TABLE_SCHEMA = 'my_schema'
  order BY ORDINAL_POSITION
  UNION ALL
      SELECT ColName1, ColName2, ColName3
          FROM YourTable
          INTO OUTFILE '/path/outfile'
  

  回复于 2024-05-19T20:38:45+08:00