MysqlDump 为 CSV 格式。（封闭的字符串字段，第一个描述行，没有 sql 文件）

发布于 2024-11-25 18:51:38 字数 942 浏览 4 评论 0原文

我想知道 mysqldump 参数，以便进入目录，每个表均采用 CSV 格式（分隔+第一行描述）。我不需要任何其他文件，例如 .sql。

例如，如果我的架构中只有下表，

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `lastname` varchar(45) NOT NULL,
  `email` varchar(320) NOT NULL,
) ENGINE=InnoDB

在输出中我只会得到一个像这样的 user.csv 文件

id;name;lastname;email
1;"Mark";"Lenders";"[email protected]"
...

。如果可能的话，我也想只包含 varchar 字段。

我使用这个 mysqldump 调用

mysqldump -u root -p -t -T /tmp dbschema --fields-enclosed-by=\" --fields-terminated-by=;

但我仍然有这些错误：

我没有得到带有表字段的第一个描述行（列名标题）
我得到 .txt 文件而不是 .csv
我得到 .sql 空文件 appart from 。 txt
中" 括起来的字段不仅是varchar 字段，而且是数字字段

服务器操作系统是Debian6。

谢谢！！

原文

I would like to know mysqldump parameters in order to get in a directory, every table in CSV format (, separated + a description first row). I don' want any other files like .sql.

For example, if I have only the following table in the schema

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `lastname` varchar(45) NOT NULL,
  `email` varchar(320) NOT NULL,
) ENGINE=InnoDB

In the output I would get only a user.csv file like this

id;name;lastname;email
1;"Mark";"Lenders";"[email protected]"
...

If possible, I also want to enclose only varchar fields.

I use this mysqldump call

mysqldump -u root -p -t -T /tmp dbschema --fields-enclosed-by=\" --fields-terminated-by=;

But I still have these mistakes:

I don't get the first description row with the fields of the table (column names header)
I get .txt files instead of .csv
I get .sql empty files appart from .txt
The fields enclosed by " are not only the varchar ones, but also the numerics fields

Server has a Debian6 operating system.

Thanks!!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ι不睡觉的鱼゛ 2024-12-02 18:51:38

DELIMITER $

CREATE PROCEDURE write_tables_to_csv()
BEGIN

  DECLARE ts, tn  VARCHAR(64);
  DECLARE cur CURSOR FOR SELECT table_schema
                              , table_name
                         FROM
                           information_schema.tables
                         WHERE
                           table_schema = 'test'
                           AND table_name LIKE 'table1%';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur;
  REPEAT
    FETCH cur INTO ts, tn;
    IF NOT done THEN
      SET @sql = CONCAT('SELECT * FROM ', ts, '.', tn, ' INTO OUTFILE ''', tn, '.csv'' FIELDS OPTIONALLY ENCLOSED BY ''"''');
      PREPARE stmt1 FROM @sql;
      EXECUTE stmt1;
      DEALLOCATE PREPARE stmt1;
    END IF;
  UNTIL done
  END REPEAT;

  CLOSE cur;
END
$

DELIMITER ;

该过程读取表名、生成 SELECT 语句并执行它们。
更改架构和表的条件。

编辑

此外，您可以使用dbForge Studio for MySQL<中的数据导出工具< /a>.它允许一次以任何格式导出一些表。 CSV 格式写入列标题，字符串字段由指定符号括起来。

DELIMITER $

CREATE PROCEDURE write_tables_to_csv()
BEGIN

  DECLARE ts, tn  VARCHAR(64);
  DECLARE cur CURSOR FOR SELECT table_schema
                              , table_name
                         FROM
                           information_schema.tables
                         WHERE
                           table_schema = 'test'
                           AND table_name LIKE 'table1%';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur;
  REPEAT
    FETCH cur INTO ts, tn;
    IF NOT done THEN
      SET @sql = CONCAT('SELECT * FROM ', ts, '.', tn, ' INTO OUTFILE ''', tn, '.csv'' FIELDS OPTIONALLY ENCLOSED BY ''"''');
      PREPARE stmt1 FROM @sql;
      EXECUTE stmt1;
      DEALLOCATE PREPARE stmt1;
    END IF;
  UNTIL done
  END REPEAT;

  CLOSE cur;
END
$

DELIMITER ;

This procedure reads table names, generates SELECT statements and executes them.
Change the condition for your schema and tables.

EDIT

Also, you clould use Data Export tool in dbForge Studio for MySQL. It alows to export some tables in any format at once. CSV format writes column header, and string fields are enclosed by specified symbol.

回复收藏 0 原文

~没有更多了~