列出MySQL表外键的有效方式? [英] Performant Way to List Foreign Keys for a MySQL Table?

问题描述

是否有 performant 方法来获取分配给MySQL表的外键列表?

Is there a performant way to fetch the list of foreign keys assigned to a MySQL table?

使用

SELECT
    `column_name`, 
    `referenced_table_schema` AS foreign_db, 
    `referenced_table_name` AS foreign_table, 
    `referenced_column_name`  AS foreign_column 
FROM
    `information_schema`.`KEY_COLUMN_USAGE`
WHERE
    `constraint_schema` = SCHEMA()
AND
    `table_name` = 'your-table-name-here'
AND
    `referenced_column_name` IS NOT NULL
ORDER BY
    `column_name`;

可以工作，但是在我尝试过的MySQL版本上却很慢.进行了一些研究此错误，这似乎表明这是一个持续存在的问题没有明确的解决方案.提示的解决方案需要使用补丁程序重新配置或重新编译mysql，这不适用于我正在处理的项目.

works, but is painfully slow on the versions of MySQL I've tried it with. A bit of research turned up this bug, which seems to indicate it's an ongoing issue without a clear solution. The solutions which are hinted at require reconfiguring or recompiling mysql with a patch, which doesn't work for the project I'm working on.

我知道可以发布以下内容

I realize it's possible to issue the following

SHOW CREATE TABLE table_name;

并获得CREATE TABLE语句的字符串表示形式，其中将包括外键约束.但是，解析此字符串似乎很脆弱，而且我没有大量的CREATE TABLE语句可用于测试. (如果这里有标准的解析代码，我会喜欢一些链接)

and get a string representation of a CREATE TABLE statement, which will include the foreign key constraints. However, parsing this string seems like it would be fragile, and I don't have a large corpus of CREATE TABLE statements to test against. (if there's a standard bit of parsing code for this out there, I'd love some links)

我还意识到我可以用以下内容列出索引

I also realize I can list the indexes with the following

SHOW CREATE TABLE table_name;

索引列表将包括外键，但是似乎没有一种方法可以确定哪些索引是外键，哪些是常规" MySQL索引.同样，在这里使用SHOW CREATE表信息进行一些交叉引用可能会有所帮助，但这使我们回到了脆弱的字符串分析中.

The list of indexes will include the foreign keys, but there doesn't appear to be a way to determine which of the indexes are foreign keys, and which are "regular" MySQL indexes. Again, some cross referencing with the SHOW CREATE table information could help here, but that brings us back to fragile string parsing.

任何帮助，甚至链接到有关此问题的其他精巧讨论，都将不胜感激.

Any help, or even links to other smart discussions on the issue, would be appreciated.

推荐答案

SequelPro和Magento都利用SHOW CREATE TABLE查询来加载外键信息.我将参考Magento的实现，因为它既是一个基于PHP的系统，也是我们俩都非常熟悉的系统.但是，以下代码段可以应用于任何基于PHP的系统.

SequelPro and Magento both utilize the SHOW CREATE TABLE query to load the foreign key information. Magento's implementation is the one I am going to reference since it's both a PHP based system and one that both of us are very familiar with. However, the following code snippets can be applied to any PHP based system.

解析是通过Varien_Db_Adapter_Pdo_Mysql::getForeignKeys()方法完成的(此类的代码可以在此处找到)，使用相对简单的RegEx:

The parsing is done in the Varien_Db_Adapter_Pdo_Mysql::getForeignKeys() method (the code for this class can be found here) using a relatively simple RegEx:

    $createSql = $this->getCreateTable($tableName, $schemaName);

    // collect CONSTRAINT
    $regExp  = '#,\s+CONSTRAINT `([^`]*)` FOREIGN KEY \(`([^`]*)`\) '
        . 'REFERENCES (`[^`]*\.)?`([^`]*)` \(`([^`]*)`\)'
        . '( ON DELETE (RESTRICT|CASCADE|SET NULL|NO ACTION))?'
        . '( ON UPDATE (RESTRICT|CASCADE|SET NULL|NO ACTION))?#';
    $matches = array();
    preg_match_all($regExp, $createSql, $matches, PREG_SET_ORDER);
    foreach ($matches as $match) {
        $ddl[strtoupper($match[1])] = array(
            'FK_NAME'           => $match[1],
            'SCHEMA_NAME'       => $schemaName,
            'TABLE_NAME'        => $tableName,
            'COLUMN_NAME'       => $match[2],
            'REF_SHEMA_NAME'    => isset($match[3]) ? $match[3] : $schemaName,
            'REF_TABLE_NAME'    => $match[4],
            'REF_COLUMN_NAME'   => $match[5],
            'ON_DELETE'         => isset($match[6]) ? $match[7] : '',
            'ON_UPDATE'         => isset($match[8]) ? $match[9] : ''
        );
    }

在doc块中，它对结果数组的描述如下:

In the doc block it describes the resulting array as follows:

    /**
     * The return value is an associative array keyed by the UPPERCASE foreign key,
     * as returned by the RDBMS.
     *
     * The value of each array element is an associative array
     * with the following keys:
     *
     * FK_NAME          => string; original foreign key name
     * SCHEMA_NAME      => string; name of database or schema
     * TABLE_NAME       => string;
     * COLUMN_NAME      => string; column name
     * REF_SCHEMA_NAME  => string; name of reference database or schema
     * REF_TABLE_NAME   => string; reference table name
     * REF_COLUMN_NAME  => string; reference column name
     * ON_DELETE        => string; action type on delete row
     * ON_UPDATE        => string; action type on update row
     */

我知道这不是恰好，因为它使用的是SHOW CREATE TABLE输出，但是根据我的发现，这似乎是人们普遍接受的处理方式.

I know it's not exactly what you were asking for since it's using the SHOW CREATE TABLE output, but based on my findings, it seems to be the generally accepted way of doing things.

这篇关于列出MySQL表外键的有效方式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！