解析 mysqldump 的输出以创建带有字段名称标题的 CSV 文件

Question

我正在尝试编写一个 bash 脚本，根据本地 MySQL 数据库的名称，该脚本将其所有表中的数据导出到 CSV 文件中，该文件的标题行包含字段名称。例如，如果数据库有表customers、orders和inventory，我想转储三个文件customers.csv、orders.csv 和 inventory.csv，包括每个文件标题中的字段名称。

通过修改 mysqldump，我成功生成了所需的所有 .csv 文件，但没有字段名称标题行。该命令还在 SQL 命令中创建一组仅包含表结构的 .sql 文件。在谷歌搜索了很多之后，我找不到任何人能够在不“重新发明轮子”并编写自己的 MySQL 转储脚本的情况下解决这个问题。 mysqldump 很棒，只是缺少这个小功能。我需要的所有字段名称都在这些 SQL 文件中，只需将它们解析出来并在每个 CSV 文件前面添加一行字段名称，对吗？

我的问题：我是一个 shell 脚本新手，我不知道如何解决这个问题。

这是我当前使用的 mysqldump 命令：

mysqldump --host=localhost --user=myusername --password=mypassword \
          --tab=/tmp/db/ --verbose mydatabase \
          --fields-enclosed-by=\" --fields-terminated-by=,

假设数据库 mydatabase 有表 customers orders 和 inventory。此命令将在目录 /tmp/db 中生成六个文件：customers.sql、customers.txt、orders.sql、order.txt、inventory.sql、inventory.txt。 （txt 文件是 CSV 文件，我的脚本稍后将文件扩展名更改为 .csv）

.sql 文件看起来像这样（以客户为例）：

-- MySQL dump 10.13  Distrib 5.1.54, for debian-linux-gnu (i686)
--
-- Host: localhost    Database: mydatabase
-- ------------------------------------------------------
-- Server version       5.1.54-1ubuntu4

/* (i removed some generated comments here) */;

--
-- Table structure for table `customers`
--

DROP TABLE IF EXISTS `customers`;
/* (i removed some generated comments here) */;
CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(256) NOT NULL,
  `last_name` varchar(256) NOT NULL,
  `email` varchar(256) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `notes` longtext NOT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1865 DEFAULT CHARSET=latin1;

/* (i removed some generated comments here) */;
-- Dump completed on 2011-05-01 13:03:02

.txt 文件看起来就像您期望的 CSV 一样（行“data”、“like”、“this”），但没有字段名称标题。

如您所见，我需要的字段名称就位于“CREATE TABLE...”之后的 .sql 文件中。

我尝试编写的理想脚本将执行以下操作：

1. 运行上面详细介绍的 mysqldump 命令。
2. 循环遍历所有匹配 /tmp/db/*.sql 的文件，并对每个文件：
   
   1. 解析出字段名称并生成“string”、“of”、“them”、“like”、“this”
   2. 在匹配的 .txt 文件中，在第一行之前插入字段名称字符串。
3. 将所有 .txt 文件重命名为 .csv 并删除所有 .sql 文件。

有什么建议吗？我会整天修补这个问题，直到弄清楚为止。

Answer 1

我在windows下做了一个简单的测试。

create database if not exists test;

use test;

create table csv_header(
id int not null auto_increment primary key,
fname varchar(50),
lname varchar(50),
dob date)
engine = myisam;

insert into csv_header (fname,lname,dob) values 
('nick','smith','2000-12-05'),
('john','white','1990-12-05');

set @str = (select concat("select * from (select ", group_concat(concat("'",column_name,"'"))," union
            select * from ", table_name, ") as t into outfile 'd:/",table_name,".txt'
        fields terminated by ',' 
        lines terminated by '\r\n'")
            from information_schema.columns
            where table_schema = 'test' and table_name = 'csv_header'
            order by ordinal_position);

-- select @str;

prepare stmt from @str;
execute stmt;
deallocate prepare stmt;

这是我的 csv_header.txt 的内容：

id,fname,lname,dob
1,nick,smith,2000-12-05
2,john,white,1990-12-05 

如果这就是您正在寻找的内容，那么创建一个带有游标的存储过程很简单，该游标循环架构中的所有表并对每个表执行相同的操作。让我知道。 :)

Answer 2

我找不到一种方法来做到这一点，因为：

• 我的数据库服务器是一台远程计算机，
• 没有 NFS
• ，也无法写入本地共享。

我执行了标准 SQL 转储并将文件转换为 CSV 格式。

Answer 3

set group_concat_max_len = 5000;

set @qry = (select concat("select ",group_concat(CONCAT('''', column_name, '''' ) ), " UNION SELECT * FROM " ,table_name,  ' INTO OUTFILE ', " '/tmp/test.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'  ESCAPED BY '\"' LINES TERMINATED BY '\\n' " ) from information_schema.columns where table_schema = database() and table_name = 'spree_users');

prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;