1. 首页
  2. 问题
  3. 是否可以 mysqldump 重现查询所需的数据库子集？
一尘不染

是否可以 mysqldump 重现查询所需的数据库子集？

mysql

背景

我想提供重现select查询所需的数据库子集。我的目标是使我的计算工作流程可重现（如在可重现研究中）。

问题

有没有一种方法可以将此 select 语句合并到将查询数据转储到新数据库中的脚本中，以便可以将数据库安装在新的 mysql 服务器上，并且该语句可以与新数据库一起使用。除了已在查询中使用的记录之外，新数据库不应包含记录。

更新： 为澄清起见，我对查询结果的 csv 转储不感兴趣。我需要做的是转储数据库子集，以便它可以安装在另一台机器上，然后查询本身可以是可重现的（并且可以针对同一数据集进行修改）。

例子

例如，我的分析可能会查询需要来自多个（在本例 3 中）表中的记录的数据子集：

select table1.id, table1.level, table2.name, table2.level 
       from table1 join table2 on table1.id = table2.table1_id 
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum');

阅读 67

收藏
2022-10-21

共2个答案

一尘不染

mysqldump具有–where选项来为给定表执行 WHERE 子句。

尽管无法 mysqldump 连接查询，但您可以从每个表中导出特定的行，以便以后从每个表中获取的每一行都将参与到连接中。

对于您给定的查询，您需要 mysqldump 三次：

首先，mysqldump所有table3行，名称在(‘fee’,’fi’,’fo’,’fum’)：

mysqldump -u... -p... --where="name in ('fee','fi','fo','fum')" mydb table3 > table3.sql

接下来，从第一个 mysqldump 中 mysqldump 具有匹配 table3_id 值的所有 table2 行：

mysqldump -u... -p... --lock-all-tables --where="table3_id in (select id from table3 where name in ('fee','fi','fo','fum'))" mydb table2 > table2.sql

然后，从第二个 mysqldump 中 mysqldump 具有匹配 table1_id 值的所有 table1 行：

mysqldump -u... -p... --lock-all-tables --where="id in (select table1_id from table2 where table3_id in (select id from table3 where name in ('fee','fi','fo','fum')))" mydb table1 > table1.sql

注意：由于第二个和第三个 mysqldump 需要使用多个表，因此必须使用 –lock-all-tables。

创建新数据库：

mysqladmin -u... -p... mysqladmin create newdb

最后，将三个 mysqldump 加载到另一个数据库中，并尝试在新数据库中加入。

mysql -u... -p... -D newdb < table1.sql
mysql -u... -p... -D newdb < table2.sql
mysql -u... -p... -D newdb < table3.sql

在 mysql 客户端中，运行您的连接查询

mysql> use newdb
mysql> select table1.id, table1.level, table2.name, table2.level 
       from table1 join table2 on table1.id = table2.table1_id 
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum');

试试看 ！！！

警告：如果没有正确索引，第二个和第三个 mysqldumps 可能需要永远！！！

以防万一，索引以下列：

ALTER TABLE table2 ADD INDEX (table1_id);
ALTER TABLE table2 ADD INDEX (table3_id);
ALTER TABLE table3 ADD INDEX (name,id);
2022-10-21
一尘不染

我会考虑使用 ‘outfile’作为 SELECT 的一部分而不是 mysqldump 来解决这个问题。您可以生成所需的任何 SELECT 语句，然后在末尾附加“INTO OUTFILE ‘/path/to/outfile.csv’ …”，并为 CSV 样式输出提供适当的配置。然后，您可以简单地使用类似 ‘ LOAD DATA INFILE …’ 的语法将数据加载到新的架构位置。

例如，使用您的 SQL：

select table1.id, table1.level, table2.name, table2.level 
       from table1 join table2 on table1.id = table2.table1_id 
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum')
INTO OUTFILE '/tmp/fee-fi-fo-fum.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
;

请记住，您需要在目标磁盘分区上有足够的可用存储空间。

2022-10-21