mysql - 如何在 SQL 中查找表中的唯一键

Question

我在 SQL 中有 100 多个表，这些表在列上没有主键或索引。

我正在手动检查每个表的每一列，以查看它们是否具有唯一键。

如果表中不存在唯一键列，如何查询它们？

标签： mysqlsqldatabaseunique

Answer 1

请尝试以下查询

select stat.table_schema as database_name,
       stat.table_name,
       stat.index_name,
       group_concat(stat.column_name
            order by stat.seq_in_index separator ', ') as columns,
       tco.constraint_type
from information_schema.statistics stat
join information_schema.table_constraints tco
     on stat.table_schema = tco.table_schema
     and stat.table_name = tco.table_name
     and stat.index_name = tco.constraint_name
where stat.non_unique = 0
      and stat.table_schema not in ('information_schema', 'sys',
                                    'performance_schema', 'mysql')
      and (tco.constraint_type !='UNIQUE' OR tco.constraint_type !='PRIMARY KEY') //You can made changes here if needed
group by stat.table_schema,
         stat.table_name,
         stat.index_name,
         tco.constraint_type
order by stat.table_schema,
         stat.table_name;