背景

我想提供重现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');


评论

确定，因此没有其他记录。您只需要查询指定的列吗？
@Richard我没有考虑过-很高兴知道如何做到这一点。
这是一个非常独特的问题，我相信有些人想知道并且需要回答。 +1公开此类型的问题。
未来的读者：除了接受的答案之外，请参阅randomx的答案，该答案专门转储查询所需的数据。

#1 楼

mysqldump具有--where选项，可以为给定的表执行WHERE子句。

虽然mysqldump不可能进行联接查询，但是您可以从每个表中导出特定的行，以便从中获取每个行。每个表将在稍后参与连接。

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

首先，mysqldump所有具有（ '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


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

创建新数据库：

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


最后，将三个mysqldumps加载到另一个数据库中，并尝试在新的数据库中连接se。

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');


试试看！ ！

警告：如果未正确索引，则第二个和第三个mysqldump可能会永远占用！！！

以防万一，请索引以下各列：

ALTER TABLE table2 ADD INDEX (table1_id);
ALTER TABLE table2 ADD INDEX (table3_id);
ALTER TABLE table3 ADD INDEX (name,id);


我假设id是table3的主键。

评论

感谢您的详细示例！我错过了文档中的--where子句；我有机会尝试一下后，它将告诉您这是如何工作的。

– David LeBauer
2011年8月15日在22:52


对于此问题，我比--tables方法更喜欢+1。通常，我最终会使用--tables，但是--where是一个非常不错的选择。

–理查德
11年8月16日在0:44
当您mysqldump单个表时，不使用--lock-all-tables。因为where子句涉及的表不是转储的表，所以必须告诉mysqldump --lock-all-tables。 --lock-all-tables选项对转储一个或多个数据库有效，而不对单个表有效。我试图执行第二个和第三个mysqldumps，但是它对此有所抱怨。一旦我手动发出--lock-all-tables，错误就消失了，并且mysqldump成功了。另外，请注意我答案中的第一个mysqldump没有--lock-all-tables。

– RolandoMySQLDBA
2011-09-2 17:44


@Rolando谢谢您的帮助。这很完美

– David LeBauer
2011年9月2日在18:34
@Rolando对不起，我没有注意到您在删除它之前已经回答了我的评论/问题。我遇到了同样的错误。重新阅读该手册后，我看到--lock-tables仅锁定正在转储的表。我很困惑，因为--lock-all-tables锁定所有数据库中的所有表，而仅使用单个数据库时则不需要。

– David LeBauer
2011年9月2日在18:48

#2 楼

我会考虑使用“输出文件”作为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'
;


请记住，目标磁盘分区上将需要足够的可用存储空间。 br />

评论

我喜欢这样的数据加载。您仍然需要将架构转移到新数据库中，但是使用其他技巧很容易实现。

–理查德
11年8月16日在0:46
我也很喜欢，因为有些人可能不希望基表，而只是将合并结果作为单个CSV导入。 +1 !!!

– RolandoMySQLDBA
11年8月16日在1:28


@randy谢谢您的回答，但是我认为这不能解决我的问题，因为我对查询结果的csv转储不感兴趣。我需要做的是转储数据库子集，以便可以将其安装在另一台计算机上，然后查询本身可以重现（并且可以针对同一数据集进行修改）。目标是支持可重复研究的计算工作流程。

– David LeBauer
2011年8月16日在16:21


对于未来的读者，请重新发表David的评论：正如Richard所提到的，您需要分别导出所涉及表的模式。这些模式可以轻松地加载到新数据库中。然后，正如randomx所说，您可以使用“加载数据文件”将.csv加载到该新数据库中。现在，可以执行查询。

–ToolmakerSteve
19年4月6日在13:33
我刚刚意识到这种技术的局限性在于查询输出与原始表不在同一组织中。尽管我仍然喜欢这种方法，但是要重新创建原始的表结构：对每个表运行单独的查询，以导出该表所需的数据。

–ToolmakerSteve
19年4月6日在13:42

#3 楼

mysqldump util具有--tables选项，可用于指定要转储的表。它可以让您指定表列表。

我不知道有任何更简单的（自动）方式。

评论

感谢您的帮助，但我只想导出每个表的选定行，而不仅仅是导出必需的表。我可以有一个脚本，该脚本在转储后跟table1中的delete从中删除，其中id不在（.....）中；如果这是最简单的方法，则只要该脚本可以自动化，就不必使用特定工具存在。

– David LeBauer
11年8月15日在22:21
您应该获得+1，因为--tables会更简单，并且删除不需要的数据只会在新服务器上花费更多精力，尤其是当每个涉及的表超过1GB时。这样一来，大多数人会感到更舒适，因为就步骤而言，这样做才有意义。我的回答只需要一点计划和更多风险。

– RolandoMySQLDBA
2011年8月15日在22:56


#4 楼

对我有用的是：

mysqldump -u db_user -p db_name table_name --no_create_info \
--lock-all-tables --where 'id in (SELECT tn.id FROM table_name AS tn \
JOIN related_table AS rt ON tn.related_table_id = rt.id \
WHERE rt.some_field = 1)' > data.sql


来自http://krosinski.blogspot.com/2012/12/using-table-join-with-mysqldump .html

#5 楼

您是否尝试过在mysql中使用quote函数？

SELECT CONCAT("insert into table4(id,level,name,levelt2) VALUES(",   quote(table1.id),   ",",    quote(table1.level),   ",",    quote(table2.name),   ",",    quote(table2.level),    ");") as q
       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');


将以上内容保存为query.sql 

cat query.sql|mysql --skip-column-names --raw > table4.sql


#6 楼

在MySQL中：

SHOW CREATE TABLE table1; -- use these two create statements
SHOW CREATE TABLE table2; -- to design table4's create statement
CREATE TABLE table4( .... );
INSERT INTO table4(id,level,name,levelt2)
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');


在命令行上：

mysqldump mydb table4 |gzip > table4.sql.gz


在目标服务器上，设置〜/。 my.cnf 

[client]
default-character-set=utf8


在目标服务器上导入

zcat table4.sql.gz | mysql


#7 楼

我为类似的问题写了一个小脚本，它是：
https://github.com/digitalist/mysql_slice 




include ('queryDumper.php');


$exampleQuery="select * from information_schema.columns c1 
left join information_schema.columns c2 on 1=1 limit 1";

//define credentials
$exampleMysqli = new mysqli($host, $user, $password, $database);
$exampleResult=$exampleMysqli->query($exampleQuery);

//if  mysqlnd (native driver installed), otherwise use wrapper
$exampleData=fetchAll($exampleResult);
$exampleMeta=$exampleResult->fetch_fields();

/*
 * field content removal options
 * column name => function name in queryDumper.php, namespace QueryDumperHelpers
 * 
 * */

$forbiddenFields=array(
'password'=>'replacePassword', //change password -> md5("password")
'login'=>'replaceLogin', //change login vasya@mail.ru -> vasya@example.com
'comment'=>'sanitizeComment' //lorem ipsum or 
);


//get tables dump
$dump=(\queryDumper\dump($exampleData, $exampleMeta, $forbiddenFields));



$dropDatabase=true; //default false
$dropTable=true; //default false

$dbAndTablesCreationDump=\QueryDumperDatabaseAndTables\dump($exampleMysqli,$exampleMeta, $dropDatabase, $dropTable);

$databases=$dbAndTablesCreationDump['databases'];
$tables=$dbAndTablesCreationDump['tables'];
$eol=";\n\n";
echo implode($eol, $databases)."\n";
echo implode($eol, $tables).";\n";
echo "\n";

//consider using array_unique($dump) before imploding
echo implode("\n\n", $dump);
echo "\n";
?>


ie您有以下查询：

 SELECT * FROM employees.employees e1 
LEFT JOIN employees.employees e2 ON 1=1 
LIMIT 1;


您得到了此转储：

 DROP DATABASE `employees`;

CREATE DATABASE `employees`;
CREATE TABLE `employees` ( /* creation code */ ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT IGNORE INTO `employees`.`employees` VALUES ("10001","1953-09-02","Georgi","Facello","M","1986-06-26");

INSERT IGNORE INTO `employees`.`employees` VALUES ("10001","1953-09-02","Georgi","Facello","M","1986-06-26");