mysql - 从 MySql DB 中选择所有列名称

Question

我有一个 MySql select 查询用于选择所有列名称:

SELECT info.COLUMN_NAME AS `NAME`, info.TABLE_SCHEMA AS `TABLESCHEMA`,
    info.TABLE_NAME AS `TABLENAME`, info.ORDINAL_POSITION AS `POSITION`,
    info.IS_NULLABLE AS `ISNULLABLE`, info.DATA_TYPE AS `DATATYPE`,
    tc.CONSTRAINT_TYPE AS `CONSTRAINTTYPE`, 
    kcufk.TABLE_SCHEMA AS `REFRENCESCHEMA`,
    kcufk.TABLE_NAME AS `REFRENCETABLE`,
    kcufk.COLUMN_NAME AS `REFRENCECOLUMN` FROM 
    INFORMATION_SCHEMA.`COLUMNS` info LEFT OUTER JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` kcu
    ON kcu.COLUMN_NAME = info.COLUMN_NAME AND kcu.TABLE_NAME = info.TABLE_NAME LEFT OUTER JOIN 
    INFORMATION_SCHEMA.`TABLE_CONSTRAINTS` tc ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    AND tc.TABLE_NAME = kcu.TABLE_NAME LEFT OUTER JOIN INFORMATION_SCHEMA.`REFERENTIAL_CONSTRAINTS`
    rk ON rk.CONSTRAINT_NAME = tc.CONSTRAINT_NAME LEFT OUTER JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` 
    kcufk ON kcufk.CONSTRAINT_NAME = rk.UNIQUE_CONSTRAINT_NAME WHERE info.TABLE_NAME = 'reportsetting' AND 
    info.TABLE_SCHEMA = 'core' ORDER BY info.ORDINAL_POSITION ASC

问题是执行此查询时，外键多次出现。

我在这个查询中做错了什么？

Answer 1

使用连接和条件更新了您的查询，这应该给出您想要的内容

    SELECT info.COLUMN_NAME AS `NAME`, info.TABLE_SCHEMA AS `TABLESCHEMA`,    info.TABLE_NAME AS `TABLENAME`, info.ORDINAL_POSITION AS `POSITION`,    info.IS_NULLABLE AS `ISNULLABLE`, info.DATA_TYPE AS `DATATYPE`,    tc.CONSTRAINT_TYPE AS `CONSTRAINTTYPE`,     kcufk.TABLE_SCHEMA AS `REFRENCESCHEMA`,    kcufk.TABLE_NAME AS `REFRENCETABLE`,    kcufk.COLUMN_NAME AS `REFRENCECOLUMN`     FROM INFORMATION_SCHEMA.`COLUMNS` info     JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` kcu        ON kcu.COLUMN_NAME = info.COLUMN_NAME         AND kcu.TABLE_NAME = info.TABLE_NAME    JOIN INFORMATION_SCHEMA.`TABLE_CONSTRAINTS` tc         ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME        AND tc.TABLE_NAME = kcu.TABLE_NAME    JOIN INFORMATION_SCHEMA.`REFERENTIAL_CONSTRAINTS` rk         ON rk.CONSTRAINT_NAME = tc.CONSTRAINT_NAME        AND rk.TABLE_NAME = kcu.TABLE_NAME    JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` kcufk        ON kcufk.CONSTRAINT_NAME = rk.UNIQUE_CONSTRAINT_NAME        AND kcufk.TABLE_NAME = rk.TABLE_NAME    WHERE info.TABLE_NAME = 'reportsetting'        AND info.TABLE_SCHEMA = 'core'     ORDER BY info.ORDINAL_POSITION ASC