获取mysql数据库中某个表的主键和索引

使用mysql的主键和索引，并转化为oracle数据库的简表语句

获取主键

#mysql获取表主键
select
concat(‘alter table {} add constraint P{} primary key ‘,’(’,r1.primary_col,’)’)
from (
SELECT
TABLE_NAME
,COUNT(1) index_count
,GROUP_CONCAT(DISTINCT(column_name) SEPARATOR ‘,’) primary_col
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
table_name = ‘course’
AND constraint_name = ‘PRIMARY’
and constraint_schema = ‘ods’
GROUP BY TABLE_NAME
ORDER BY COUNT(1) DESC
) r1

获取索引

#mysql 获取表索引
SELECT
CONCAT(‘create un index ‘,’ {} on {} ‘,’(’,r1.indexes,’)’)
from (
SELECT
TABLE_NAME
,COUNT(1) index_count
,GROUP_CONCAT(DISTINCT(index_name) SEPARATOR ‘,’) indexes
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = ‘ods’
AND INDEX_NAME <> ‘primary’
and table_name = ‘score2’
GROUP BY TABLE_NAME
ORDER BY COUNT(1) DESC
) r1