使用Perl/DBI/MySQL/InnoDB查找外键信息

Question

我想以编程方式查找 MySQL 数据库中特定 InnoDB 表的外键。

我正在使用 Perl，并且偶然发现了 $dbh->foreign_key_info。我刚刚尝试使用它，但似乎有点错误。

它不会返回 ON DELETE 和 ON UPDATE 信息，即使它暗示它可以。它还返回常规索引。

感谢您的任何帮助。

use strict;
use warnings;
use DBI;
use Data::Dumper;
my $dbh = DBI->connect("DBI:mysql:database=db;host=localhost", "user", "password");
my $sth = $dbh->foreign_key_info(undef, undef, undef, undef, undef, "table_name");
print Dumper $sth->fetchall_hashref("FK_NAME");

和输出：

$VAR1 = {
      'some_table_ibfk_3' => {
                                     'PK_NAME' => undef,
                                     'DEFERABILITY' => undef,
                                     'FKTABLE_CAT' => undef,
                                     'PKTABLE_SCHEM' => 'db',
                                     'UNIQUE_OR_PRIMARY' => undef,
                                     'PKTABLE_CAT' => undef,
                                     'FKTABLE_NAME' => 'some_table',
                                     'FKTABLE_SCHEM' => 'db',
                                     'PKTABLE_NAME' => 'some_other_table',
                                     'FKCOLUMN_NAME' => 'some_other_table_id',
                                     'FK_NAME' => 'some_table_ibfk_3',
                                     'DELETE_RULE' => undef,
                                     'PKCOLUMN_NAME' => 'id',
                                     'KEY_SEQ' => '1',
                                     'UPDATE_RULE' => undef
                                   },
      'user_id_2' => {
                       'PK_NAME' => undef,
                       'DEFERABILITY' => undef,
                       'FKTABLE_CAT' => undef,
                       'PKTABLE_SCHEM' => undef,
                       'UNIQUE_OR_PRIMARY' => undef,
                       'PKTABLE_CAT' => undef,
                       'FKTABLE_NAME' => 'some_table',
                       'FKTABLE_SCHEM' => 'db',
                       'PKTABLE_NAME' => undef,
                       'FKCOLUMN_NAME' => 'some_other_table_id',
                       'FK_NAME' => 'user_id_2',
                       'DELETE_RULE' => undef,
                       'PKCOLUMN_NAME' => undef,
                       'KEY_SEQ' => '2',
                       'UPDATE_RULE' => undef
                     },
      'PRIMARY' => {
                     'PK_NAME' => undef,
                     'DEFERABILITY' => undef,
                     'FKTABLE_CAT' => undef,
                     'PKTABLE_SCHEM' => undef,
                     'UNIQUE_OR_PRIMARY' => undef,
                     'PKTABLE_CAT' => undef,
                     'FKTABLE_NAME' => 'some_table',
                     'FKTABLE_SCHEM' => 'db',
                     'PKTABLE_NAME' => undef,
                     'FKCOLUMN_NAME' => 'id',
                     'FK_NAME' => 'PRIMARY',
                     'DELETE_RULE' => undef,
                     'PKCOLUMN_NAME' => undef,
                     'KEY_SEQ' => '1',
                     'UPDATE_RULE' => undef
                   },
      'some_table_ibfk_1' => {
                                     'PK_NAME' => undef,
                                     'DEFERABILITY' => undef,
                                     'FKTABLE_CAT' => undef,
                                     'PKTABLE_SCHEM' => 'db',
                                     'UNIQUE_OR_PRIMARY' => undef,
                                     'PKTABLE_CAT' => undef,
                                     'FKTABLE_NAME' => 'some_table',
                                     'FKTABLE_SCHEM' => 'db',
                                     'PKTABLE_NAME' => 'user_bk2',
                                     'FKCOLUMN_NAME' => 'user_id',
                                     'FK_NAME' => 'some_table_ibfk_1',
                                     'DELETE_RULE' => undef,
                                     'PKCOLUMN_NAME' => 'id',
                                     'KEY_SEQ' => '1',
                                     'UPDATE_RULE' => undef
                                   },
      'some_table_ibfk_2' => {
                                     'PK_NAME' => undef,
                                     'DEFERABILITY' => undef,
                                     'FKTABLE_CAT' => undef,
                                     'PKTABLE_SCHEM' => 'db',
                                     'UNIQUE_OR_PRIMARY' => undef,
                                     'PKTABLE_CAT' => undef,
                                     'FKTABLE_NAME' => 'some_table',
                                     'FKTABLE_SCHEM' => 'db',
                                     'PKTABLE_NAME' => 'user_bk2',
                                     'FKCOLUMN_NAME' => 'coach_id',
                                     'FK_NAME' => 'some_table_ibfk_2',
                                     'DELETE_RULE' => undef,
                                     'PKCOLUMN_NAME' => 'id',
                                     'KEY_SEQ' => '1',
                                     'UPDATE_RULE' => undef
                                   }
    };

Answer 1

看起来 mysql 驱动程序可能还不支持它。通过调试进行快速检查，看起来输入了以下 sql 语句：

SELECT NULL AS PKTABLE_CAT,
   A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM,
   A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,
   A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,
   A.TABLE_CATALOG AS FKTABLE_CAT,
   A.TABLE_SCHEMA AS FKTABLE_SCHEM,
   A.TABLE_NAME AS FKTABLE_NAME,
   A.COLUMN_NAME AS FKCOLUMN_NAME,
   A.ORDINAL_POSITION AS KEY_SEQ,
   NULL AS UPDATE_RULE,
   NULL AS DELETE_RULE,
   A.CONSTRAINT_NAME AS FK_NAME,
   NULL AS PK_NAME,
   NULL AS DEFERABILITY,
   NULL AS UNIQUE_OR_PRIMARY
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A,
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS NOT NULL
   AND A.TABLE_NAME = ? ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION

请注意，UPDATE_RULE 和 DELETE_RULE 列都设置为 NULL。