我需要一个查询(INFORMATION_SCHEMA),它将针对给定的模式和表名称向我显示具有以下属性的所有表列(它是什么键类型:PK=>主键,UQ=>唯一键,FK=>外键,什么是键名称,如果是外键,则引用 schema.table.column):
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;