从mysqldump备份执行相互依赖的视图 [英] Execute interdependent views from mysqldump backup

问题描述

mysqldump 使用按字母顺序列出的表(和视图)创建转储.当表之间存在外键关系时，这不是很方便，但是，通过运行以下命令可以轻松解决该问题:

mysqldump creates a dump with the tables (and views) listed alphabetically. When there are foreign key relationships between the tables this is not very convenient, however, the problem is easily resolved by running:

SET FOREIGN_KEY_CHECKS=0;

我有一种情况，例如视图vwapple依赖于视图vworange.使用mysqldump时，会在vworange之前列出并执行vwapple，这是有问题的，因为我们将收到"view vworange不存在"错误消息.

I have a situation whereby the view, vwapple, is dependent on the view, vworange, say. With the mysqldump, vwapple is listed and executed before vworange, which is problematic as we'll get a "view vworange does not exist" error message.

这种情况如何解决?视图是否具有与表类似的解决方案?还是使每个视图独立以解决该问题更好?

How is this situation solved? Do views have a similar solutions as tables have? Or is it better to make each view independent just to get round this problem?

注意
我正在使用python脚本还原数据库.每个视图和表都位于其单独的.sql文件中，因为我想独立地对每个数据库对象进行源代码控制.

NOTE
I'm restoring the database using a python script. Each view and table is in its separate .sql file as I want to source control each database object independently.

推荐答案

mysqldump的最新版本，在转储整个数据库时，应通过以下方式解决问题:对于数据库中的每个视图，它们首先使用相同的内容创建一个空表结构，然后他们创建视图，并在创建视图之前删除每个占位符表.似乎用视图替换表不会损害依赖于该表的视图.

Recent versions of mysqldump, when dumping whole databases, solve things this way: for every view in the database, they first create an empty table with the same structure, then they create the views, dropping each placeholder table before creating a view. It seems that replacing a table with a view won't harm views that depend on it.

我对此有所了解，因为这种方法还有另一个缺陷，这使我感到痛苦:视图中的行可能比表中的行包含更多的数据，因此某些占位符表无法创建，从而导致某些转储在没有人工干预的情况下无法恢复.因此，最好对视图进行拓扑排序，尽管这可能需要一些工作.

I know about this because this approach has another flaw, which bit me: rows in views may contain more data than rows in tables, so some of the placeholder tables cannot be created, causing some dumps to be unrestorable without manual intervention. For this reason, a topological sorting of views would be preferable, although it might require some work.

这篇关于从mysqldump备份执行相互依赖的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！