使用Perl / DBI / MySQL / InnoDB查找外键信息 [英] Find foreign key information using Perl/DBI/MySQL/InnoDB
问题描述
我正在使用Perl，而且偶然发现了 $ dbh-> foreign_key_info 。我刚刚尝试过使用它，但似乎有点错误。
它不返回ON DELETE和ON UPDATE信息，即使它意味着它可以。
感谢您的帮助。
严格使用;
使用警告;
使用DBI;
使用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）;
并输出：
$ b $
$ 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
}
};
它看起来可能不支持mysql司机呢。通过调试快速检查，它看起来像下面的SQL语句进入：
pre $选择空作为PKTABLE_CAT
A.REFERENCED_TABLE_SCHEMA作为PKTABLE_SCHEM，
A.REFERENCED_TABLE_NAME作为PKTABLE_NAME，
A.REFERENCED_COLUMN_NAME作为PKCOLUMN_NAME，
A.TABLE_CATALOG作为FKTABLE_CAT，
A.TABLE_SCHEMA作为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作为PK_NAME，
NULL作为DEFERABILITY，
NULL作为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。
I want to programmatically find the the foreign keys on a particular InnoDB table in my MySQL database.
I'm using Perl, and I stumbled across $dbh->foreign_key_info. I've just tried using it but it seems a bit faulty.
It doesn't return the ON DELETE, and ON UPDATE information, even though it implies it can. And it's also returning regular indexes.
Thanks for any help.
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");
And the output:
$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
}
};
It looks like it may not be supported by the mysql driver yet. Doing a quick check through debug, it looks like the following sql statement gets entered:
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
Notice that the UPDATE_RULE and DELETE_RULE columns are both set to NULL.
这篇关于使用Perl / DBI / MySQL / InnoDB查找外键信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
