查询数据库表的唯一索引

需求

        根据库名,表名查询表的唯一索引

实现

        达梦数据库实现方式

select
        wm_concat(dcc.column_name) as indexNames
from
        all_ind_columns dcc
inner join all_indexes di
on
        dcc.index_name = di.index_name
    and dcc.index_owner = di.owner
where
        di.owner = '数据库名'
    and di.table_name = '数据表名'
    and di.uniqueness = 'UNIQUE'
--    如果想要排除主键ID字段,添加以下语句
--    and dcc.column_name != 'ID'

        MySQL数据库实现方式

SELECT
        GROUP_CONCAT(DISTINCT COLUMN_NAME
ORDER BY
        SEQ_IN_INDEX SEPARATOR ',' ) AS indexNames
FROM
        INFORMATION_SCHEMA.STATISTICS TABLE_SCHEMA = #{库名}
    and TABLE_NAME=#{表名}
    AND NON_UNIQUE = 0
    AND INDEX_NAME != 'PRIMARY'
GROUP BY
        INDEX_NAME