我需要一个查询(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;

最佳答案

这是我最后一个(透视的)查询:

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';

但我认为必须有更简单的解决方案。

关于mysql - 显示具有键类型和引用的 MYSQL 表列，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/30720609/