如何获得mysql db中按外键依赖项排序的所有表的列表？

Question

我想要一张按外键订购的表。因此，如果表" Book“对表" Author”具有FK依赖性，那么作者将被列在第一位，而Book则被列在第一位。我没有循环依赖循环，但我确实有一些自连接关系(表本身有FK依赖关系)。我想要一个脚本来生成这个列表。(或者可能是mysql 8中的递归CTE？)

我可以从：(mysql查询)获取外键依赖项。

select CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
from information_schema.referential_constraints
where constraint_schema = 'DBName'
order by TABLE_NAME;

表表来自：

show full tables where table_type <> "VIEW";

背景:我想将mysql schema复制到内存中的H2数据库中进行测试。但是H2不支持完整的mysql语法。特别是，不支持FOREIGN_KEY_CHECKS=0，H2 SET REFERENTIAL_INTEGRITY FALSE只适用于插入/更新-约束检查，而不是表创建。因此，我的表需要按照正确的顺序创建，根据FK依赖项。

我可以用mysqldump转储模式。但是mysqldump不按外键命令表的创建。如果表“子”对表“父”有FK依赖，则mysqldump会将表创建按字母顺序排列，因此尽管它依赖于父表，但它将在父表之前创建。它依赖于在创建所有表时禁用外键检查。

因此，如果我有一个表名列表，按fk顺序，我可以将其传递给mysqldump以转储模式。

Answer 1

这应该会让你开始：

我在层次结构中创建了一组表，因此t1是t2和t3的父级，t2是t4和t5的父级，t3是t6的父级，t6是t7的父级。

with recursive cte as (
 select t.table_name, 0 as depth 
 from information_schema.tables t
 left join information_schema.referential_constraints r 
  on t.table_name =  r.table_name
 where t.table_schema = 'test' and r.table_name is null
 union all
 select r.table_name, cte.depth+1 
 from cte join information_schema.referential_constraints r 
  on cte.table_name = r.referenced_table_name
) select * from cte order by depth asc;

+------------+-------+
| TABLE_NAME | depth |
+------------+-------+
| t1         |     0 |
| t2         |     1 |
| t3         |     1 |
| t4         |     2 |
| t5         |     2 |
| t6         |     2 |
| t7         |     3 |
+------------+-------+

我没有测试任何自我引用的表。也许您需要在CTE中有一个条件来排除r.table_name=cte.table_name的情况。我会把这个留给你。

Answer 2

我最初的python脚本解决方案：(允许定制订单的半手工方式)

find-tables-order-by-referential-constraints.py

from collections import defaultdict

# show full tables where table_type <> "VIEW";
tables = [
  "Book",
  "Author",
  "Illustrator",
  "Gender",
  "PublicationArticle",
  "OrphanTable",
  "OrphanTableDependency"
]

# FK-dependencies from:
#  (used sublime multiple-cursors to reformat all rows at the same time)
# select CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
# from information_schema.referential_constraints
# where constraint_schema = 'DBName'
# order by TABLE_NAME;
dependencies = defaultdict(list)
dependencies["Book"].append("Author")
dependencies["Book"].append("Illustrator")
dependencies["Author"].append("Gender")
dependencies["PublicationArticle"].append("Author")
dependencies["OrphanTable"].append("OrphanTableDependency")

reversedeps = defaultdict(list)
for k, v in dependencies.items():
  for reftable in v:
    reversedeps[reftable].append(k)

result = []

def add_dependencies_then_you(table):
  if table in result:
    return
  for reftable in dependencies[table]:
    if reftable != table and reftable not in result:
      add_dependencies_then_you(reftable)
  print(table)
  result.append(table)

# add dependency-root table(s)
add_dependencies_then_you("Book")

# optional: custom order chosen to make more sense than random
# add_dependencies_then_you("PreferredNextTable1")
# add_dependencies_then_you("PreferredNextTable2")

# if any results have other tables depending on them, add those next
while(True):
  print
  result_count = len(result)
  for completed_table in result:
    for refs_me_table in reversedeps[completed_table]:
      add_dependencies_then_you(refs_me_table)
  if(result_count == len(result)):
    break;

# add any missing tables not hit above
for table in list(tables):
  if table not in result:
    add_dependencies_then_you(table)