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屋!