MySQL：从SQL查询中转储数据库 [英] MySQL: Dump a database from a SQL query

问题描述

我正在编写一个测试框架，需要在其中捕获MySQL数据库状态（表结构，内容等）。

I'm writing a test framework in which I need to capture a MySQL database state (table structure, contents etc.).

我需要使用它来实现一个检查某些操作后状态是否未更改。 （可以允许更改自动增量值 ，但是我认为我可以处理。）

I need this to implement a check that the state was not changed after certain operations. (Autoincrement values may be allowed to change, but I think I'll be able to handle this.)

转储最好放在一种人类可读的格式（最好是SQL代码，如mysqldump一样）。

The dump should preferably be in a human-readable format (preferably an SQL code, like mysqldump does).

我希望将测试框架限制为仅使用MySQL连接。要捕获状态，应该不调用mysqldump或访问文件系统（例如复制* .frm文件或将其选择到文件中，但管道还是可以的）。

I wish to limit my test framework to use a MySQL connection only. To capture the state it should not call mysqldump or access filesystem (like copy *.frm files or do SELECT INTO a file, pipes are fine though).

由于这将是仅测试代码，因此我不关心性能。我确实需要可靠的行为。

As this would be test-only code, I'm not concerned by the performance. I do need reliable behavior though.

实现我所需功能的最佳方法是什么？

What is the best way to implement the functionality I need?

I猜猜我应该将代码建立在一些现有的开源备份工具上...哪一种是最好的工具？

I guess I should base my code on some of the existing open-source backup tools... Which is the best one to look at?

更新：我没有指定我使用的语言（不，那不是PHP），因为我认为我无法像现在那样重用代码。我的情况很特殊（出于实际目的，假设使用MySQL C API）。代码将在Linux上运行。

Update: I'm not specifying the language I write this in (no, that's not PHP), as I don't think I would be able to reuse code as is — my case is rather special (for practical purposes, lets assume MySQL C API). Code would be run on Linux.

推荐答案

鉴于您的要求，我认为您只剩下（伪代码+ SQL）

Given your requirements, I think you are left with (pseudo-code + SQL)

tables = mysql_fetch "SHOW TABLES"
foreach table in tables
    create = mysql_fetch "SHOW CREATE TABLE table"
    print create
    rows = mysql_fetch "SELECT * FROM table"
    foreach row in rows
        // or could use VALUES (v1, v2, ...), (v1, v2, ...), .... syntax (maybe preferable for smaller tables)
        insert = "INSERT (fiedl1, field2, field2, etc) VALUES (value1, value2, value3, etc)"
        print insert

基本上，获取所有表的列表，然后遍历每个表并生成<$手动为每一行使用c $ c> INSERT 语句（大多数api都有一种简单的方法来获取列名列表，否则您可以退回调用 DESC TABLE ）。

Basically, fetch the list of all tables, then walk each table and generate INSERT statements for each row by hand (most apis have a simple way to fetch the list of column names, otherwise you can fall back to calling DESC TABLE).

SHOW CREATE TABLE 已为您完成，但我很公平可以肯定，没有什么可比的操作 SHOW INSERT ROWS 。

SHOW CREATE TABLE is done for you, but I'm fairly certain there's nothing analogous to do SHOW INSERT ROWS.

当然，除了打印转储，您还可以用它做任何您想做的事。

And of course, instead of printing the dump you could do whatever you want with it.

这篇关于MySQL：从SQL查询中转储数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！