显示具有键类型和参考的MYSQL表列 [英] Showing MYSQL table columns with key types and reference
本文介绍了显示具有键类型和参考的MYSQL表列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要一个查询(INFORMATION_SCHEMA),该查询将针对给定的模式和表名向我显示具有以下属性的所有表列(键类型是什么:PK => Primary Key,UQ => Unique Key,FK => Foreign Key ,什么是键名,如果是外键,则引用什么schema.table.column):
I need a query (INFORMATION_SCHEMA) which will for given schema and table name show me all table columns with following attributes (what key type it is: PK=>Primary Key, UQ=>Unique Key, FK=>Foreign Key, what is key name, and if it is Foreign Key what schema.table.column is referenced):
COLUMN_NAME | DATA_TYPE | KEY_TYPE | KEY_NAME | REFERENCED
============+===========+==========+=============+========================
empid | int | PK | PRIMARY |
empname | varchar | UQ | uq_empname |
empactive | enum | | |
empcatid | int | FK | fk_emp_cat | schema.categories.catid
这是我的SQL:
SELECT c.COLUMN_NAME,
c.COLUMN_KEY,
c.DATA_TYPE,
k.REFERENCED_TABLE_SCHEMA,
k.REFERENCED_TABLE_NAME,
k.REFERENCED_COLUMN_NAME
FROM information_schema.COLUMNS c
LEFT JOIN information_schema.KEY_COLUMN_USAGE k
ON (k.TABLE_SCHEMA=c.TABLE_SCHEMA
AND k.TABLE_NAME=c.TABLE_NAME
AND k.COLUMN_NAME=c.COLUMN_NAME
AND k.POSITION_IN_UNIQUE_CONSTRAINT IS NOT NULL)
WHERE c.TABLE_SCHEMA='PHPDAO'
AND c.TABLE_NAME='employees';
这是我的桌子:
-- -----------------------------------------------------
-- Table `PHPDAO`.`categories`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PHPDAO`.`categories` (
`catid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`catname` VARCHAR(50) NOT NULL,
`catgroup` ENUM('A', 'B', 'C') NOT NULL DEFAULT 'A',
PRIMARY KEY (`catid`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `PHPDAO`.`employees`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `PHPDAO`.`employees` (
`empid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`empname` VARCHAR(100) NOT NULL,
`empactive` ENUM('Y', 'N') NOT NULL DEFAULT 'Y',
`empcatid` INT UNSIGNED NOT NULL,
PRIMARY KEY (`empid`),
INDEX `fk_emp_cat_idx` (`empcatid` ASC),
UNIQUE INDEX `uq_empname` (`empname` ASC),
UNIQUE INDEX `uq_empid` (`empid` ASC),
CONSTRAINT `fk_emp_cat`
FOREIGN KEY (`empcatid`)
REFERENCES `PHPDAO`.`categories` (`catid`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
推荐答案
这是我最后一个(枢轴化的)查询:
This is my last (pivotized) query:
SELECT c.COLUMN_NAME,
--c.COLUMN_KEY,
IF(EXISTS(select *
FROM information_schema.KEY_COLUMN_USAGE k
JOIN information_schema.TABLE_CONSTRAINTS tc
ON (k.TABLE_SCHEMA=tc.TABLE_SCHEMA
AND k.TABLE_NAME=tc.TABLE_NAME
AND k.CONSTRAINT_NAME=tc.CONSTRAINT_NAME)
WHERE k.TABLE_SCHEMA=c.TABLE_SCHEMA
AND k.TABLE_NAME=c.TABLE_NAME
AND tc.CONSTRAINT_TYPE='PRIMARY KEY'
AND c.COLUMN_NAME=k.COLUMN_NAME),'PK',null) AS PK,
IF(EXISTS(select *
FROM information_schema.KEY_COLUMN_USAGE k
JOIN information_schema.TABLE_CONSTRAINTS tc
ON (k.TABLE_SCHEMA=tc.TABLE_SCHEMA
AND k.TABLE_NAME=tc.TABLE_NAME
AND k.CONSTRAINT_NAME=tc.CONSTRAINT_NAME)
WHERE k.TABLE_SCHEMA=c.TABLE_SCHEMA
AND k.TABLE_NAME=c.TABLE_NAME
AND tc.CONSTRAINT_TYPE='UNIQUE'
AND c.COLUMN_NAME=k.COLUMN_NAME),'UQ',null) AS UQ,
IF(EXISTS(select *
FROM information_schema.KEY_COLUMN_USAGE k
JOIN information_schema.TABLE_CONSTRAINTS tc
ON (k.TABLE_SCHEMA=tc.TABLE_SCHEMA
AND k.TABLE_NAME=tc.TABLE_NAME
AND k.CONSTRAINT_NAME=tc.CONSTRAINT_NAME)
WHERE k.TABLE_SCHEMA=c.TABLE_SCHEMA
AND k.TABLE_NAME=c.TABLE_NAME
AND tc.CONSTRAINT_TYPE='FOREIGN KEY'
AND c.COLUMN_NAME=k.COLUMN_NAME),'FK',null) AS FK,
c.EXTRA,
c.IS_NULLABLE,
c.DATA_TYPE,
c.COLUMN_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
c.COLUMN_COMMENT,
k.REFERENCED_TABLE_SCHEMA,
k.REFERENCED_TABLE_NAME,
k.REFERENCED_COLUMN_NAME
FROM information_schema.COLUMNS c
LEFT JOIN information_schema.KEY_COLUMN_USAGE k
ON (k.TABLE_SCHEMA=c.TABLE_SCHEMA
AND k.TABLE_NAME=c.TABLE_NAME
AND k.COLUMN_NAME=c.COLUMN_NAME
AND k.POSITION_IN_UNIQUE_CONSTRAINT IS NOT NULL)
WHERE c.TABLE_SCHEMA='PHPDAO'
AND c.TABLE_NAME='employees';
但是我认为必须有一个更简单的解决方案.
But I think there has to be simpler solution.
这篇关于显示具有键类型和参考的MYSQL表列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文