im试图用PDO在mysql中获得一个外键assoc的表名。例如,我有:
用户表
+------------+----------+------+-----+-----+----------------+
| Field | Type | Null | Key | AI | Foreign Keys |
+------------+----------+------+-----+-----+----------------+
| id | int(255) | NO | PRI | yes | |
| username | char(16) | NO | | | |
| password | char(32) | NO | UNI | | |
| group | int(255) | NO | MUL | | mytable.group |
| created | datetime | NO | | | |
+------------+----------+------+-----+-----+----------------+
分组表
+------------+----------+------+-----+-----+----------------+
| Field | Type | Null | Key | AI | Foreign Keys |
+------------+----------+------+-----+-----+----------------+
| id | int(255) | NO | PRI | YES | |
| name | char(16) | NO | UNI | | |
| description| TEXT | YES | | | |
+------------+----------+------+-----+-----+----------------+
我想以普通方式获得外部表assoc(users.group=>group)的名称。SHOW COLUMNS和DESCRIBE命令不显示关联
如果有人知道怎么做,我将不胜感激。谢谢
SHOW CREATE TABLE用户,显示表的所有信息,但不显示在有组织的数组中。我发现了一种使用SQL动态获取所有assoc数据的方法:
SELECT
ke.column_name col,
ke.referenced_table_schema assoc_db,
ke.referenced_table_name assoc_table,
ke.referenced_column_name assoc_col
FROM
information_schema.KEY_COLUMN_USAGE ke
WHERE
ke.referenced_table_name IS NOT NULL
AND ke.table_schema='YOUR_DATABASE_NAME'
AND ke.table_name='YOUR_TABLE_NAME'
我的例子将返回:
array(
public 'col' => string 'group' (length=5) //field with foreign data
public 'assoc_db' => string 'mytable' (length=6) //foreign db
public 'assoc_table' => string 'group' (length=5) //foreign table
public 'assoc_col' => string 'id' (length=2) //foreign field
);
试试这个:
SHOW CREATE TABLE users