SequelPro 和 Magento 都使用 SHOW CREATE TABLE 查询来加载外键信息。 Magento 的实现是我要参考的,因为它既是一个基于 PHP 的系统,也是我们都非常熟悉的系统。但是,以下代码片段可以应用于任何基于 PHP 的系统。
解析是在Varien_Db_Adapter_Pdo_Mysql::getForeignKeys()
方法 (这个类的代码可以在这里找到 http://svn.magentocommerce.com/source/branches/1.6/lib/Varien/Db/Adapter/Pdo/Mysql.php)使用相对简单的正则表达式:
$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] : ''
);
}
在文档块中,它描述了结果数组,如下所示:
/**
* 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
*/
我知道这不是exactly您所要求的,因为它使用 SHOW CREATE TABLE 输出,但根据我的发现,这似乎是普遍接受的做事方式。