MySQL找到无效的外键

brqmpdu1 于 2023-03-11 发布在 Mysql

关注(0)|答案(3)|浏览(125)

我们有一个包含几百个表的数据库，使用foreign_keys的表使用INNODB。
有时我们在开发、登台和生产数据库之间传输数据（使用mysqldump的单个表）。mysqldump禁用所有外键检查以简化数据导入。
因此，随着时间的推移，我们的一些非生产数据库最终会产生一些孤立的记录。
我正准备编写一个脚本来查找和检测整个MySQL数据库的任何无效（指向缺失记录的键）外键。
我知道我可以编写一个查询来逐个检查每个表和fkey，但是我想可能已经有一个工具可以做到这一点了。
我会在写这样的脚本之前检查一下，看看是否已经有了这样的脚本。
搜索了一下谷歌......令人惊讶的是，我什么也没找到。

mysql

来源：https://stackoverflow.com/questions/11907051/mysql-find-invalid-foreign-keys

3条答案

按热度按时间

anauzrmj1#

如果数据已经在中，并且您还没有设置删除父对象的fk约束或级联，那么您只需要：

SELECT * FROM children WHERE my_fk_id NOT IN (select id from parents);

赞(0）回复(0）举报 2023-03-11

gg58donl2#

WITH RECURSIVE foreigners as 
    (
    SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '<---->' and TABLE_NAME LIKE '<---->'
    )
SELECT 
   CONCAT('SELECT \'',TABLE_NAME,'.',COLUMN_NAME, '\' as broke, ', COLUMN_NAME,' FROM ',TABLE_NAME,' WHERE ',COLUMN_NAME, ' NOT IN (',' SELECT ',REFERENCED_COLUMN_NAME,' FROM ', REFERENCED_TABLE_NAME,') UNION' ) as x 
    from foreigners
    UNION SELECT 'SELECT null, null'

赞(0）回复(0）举报 2023-03-11

a6b3iqyw3#

这些答案对于小表来说都很好，但我认为它们的运行时间是O（n^2），这对于大数据库来说可能不太理想。

SELECT * FROM children c LEFT JOIN parents p ON p.id=c.parent_id WHERE p.id IS NULL AND c.parent_id IS NOT NULL;

注意，您可能不需要最后一个非空条件，我这样做是因为我想排除没有父母的孩子（在我的特定场景中是一个有效的情况）

赞(0）回复(0）举报 2023-03-11

我来回答

MySQL找到无效的外键

3条答案

相关问题

热门标签

最新问答