CREATE TABLE `child` ( `id` int(11) DEFAULT NULL, `parent_id` int(11) DEFAULT NULL, KEY `par_ind` (`parent_id`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ciCREATE TABLE `parent` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ciSELECT * FROM information_schema.KEY_COLUMN_USAGE a WHERE REFERENCED_TABLE_NAME='parent' mysql> SELECT * FROM information_schema.KEY_COLUMN_USAGE a WHERE REFERENCED_TABL E_NAME='parent' AND table_schema='ee' \G; *************************** 1. row *************************** CONSTRAINT_CATALOG: NULL CONSTRAINT_SCHEMA: ee CONSTRAINT_NAME: child_ibfk_1 TABLE_CATALOG: NULL TABLE_SCHEMA: ee TABLE_NAME: child COLUMN_NAME: parent_id ORDINAL_POSITION: 1 POSITION_IN_UNIQUE_CONSTRAINT: 1 REFERENCED_TABLE_SCHEMA: ee REFERENCED_TABLE_NAME: parent REFERENCED_COLUMN_NAME: id 1 row in set (0.01 sec)
怎样通过外键查询到主键的表名。 我通过外键查询到主键的表名, SELECT * FROM information_schema.key_column_usage WHERE table_name='外键表名' and column_name='外键列名'或者通过主键查询到外键的表名。 SELECT * FROM information_schema.key_column_usage WHERE referenced_table_name='主键表名' AND referenced_column_name='主键列名'
SELECT * FROM information_schema.KEY_COLUMN_USAGE a WHERE REFERENCED_TABLE_NAME='tt'
`id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
KEY `par_ind` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ciCREATE TABLE `parent` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ciSELECT * FROM information_schema.KEY_COLUMN_USAGE a WHERE REFERENCED_TABLE_NAME='parent'
mysql> SELECT * FROM information_schema.KEY_COLUMN_USAGE a WHERE REFERENCED_TABL
E_NAME='parent' AND table_schema='ee' \G;
*************************** 1. row ***************************
CONSTRAINT_CATALOG: NULL
CONSTRAINT_SCHEMA: ee
CONSTRAINT_NAME: child_ibfk_1
TABLE_CATALOG: NULL
TABLE_SCHEMA: ee
TABLE_NAME: child
COLUMN_NAME: parent_id
ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
REFERENCED_TABLE_SCHEMA: ee
REFERENCED_TABLE_NAME: parent
REFERENCED_COLUMN_NAME: id
1 row in set (0.01 sec)
SELECT * FROM information_schema.key_column_usage
WHERE table_name='外键表名'
and column_name='外键列名'或者通过主键查询到外键的表名。
SELECT * FROM information_schema.key_column_usage
WHERE referenced_table_name='主键表名'
AND referenced_column_name='主键列名'