cnail | 十字螺丝钉的DBA日记

MySQL 导出表上所有索引的创建语句

获取单表上所有索引的建表语句

SELECT 
    CONCAT('ALTER TABLE `',
            TABLE_NAME,
            '` ',
            'ADD ',
            IF(NON_UNIQUE = 1,
                CASE UPPER(INDEX_TYPE)
                    WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
                    WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
                    ELSE CONCAT('INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE)
                END,
                IF(UPPER(INDEX_NAME) = 'PRIMARY',
                    CONCAT('PRIMARY KEY USING ', INDEX_TYPE),
                    CONCAT('UNIQUE INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE))),
            '(',
            GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`')
                ORDER BY SEQ_IN_INDEX ASC
                SEPARATOR ', '),
            ');') AS 'Show_Add_Indexes'
FROM
    information_schema.STATISTICS
WHERE
    TABLE_SCHEMA = '库名'
        AND TABLE_NAME = '表明'
GROUP BY TABLE_NAME , INDEX_NAME
ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

SELECT

CONCAT('ALTER TABLE `',

TABLE_NAME,

'` ',

'ADD ',

IF(NON_UNIQUE = 1,

CASE UPPER(INDEX_TYPE)

WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'

WHEN 'SPATIAL' THEN 'SPATIAL INDEX'

ELSE CONCAT('INDEX `',

INDEX_NAME,

'` USING ',

INDEX_TYPE)

END,

IF(UPPER(INDEX_NAME) = 'PRIMARY',

CONCAT('PRIMARY KEY USING ', INDEX_TYPE),

CONCAT('UNIQUE INDEX `',

INDEX_NAME,

'` USING ',

INDEX_TYPE))),

'(',

GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`')

ORDER BY SEQ_IN_INDEX ASC

SEPARATOR ', '),

');') AS 'Show_Add_Indexes'

FROM

information_schema.STATISTICS

WHERE

TABLE_SCHEMA = '库名'

AND TABLE_NAME = '表明'

GROUP BY TABLE_NAME , INDEX_NAME

ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

获取非主键索引的建表语句

SELECT 
    CONCAT('ALTER TABLE `',
            TABLE_NAME,
            '` ',
            'ADD ',
            IF(NON_UNIQUE = 1,
                CASE UPPER(INDEX_TYPE)
                    WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
                    WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
                    ELSE CONCAT('INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE)
                END,
                IF(UPPER(INDEX_NAME) = 'PRIMARY',
                    CONCAT('PRIMARY KEY USING ', INDEX_TYPE),
                    CONCAT('UNIQUE INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE))),
            '(',
            GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`')
                ORDER BY SEQ_IN_INDEX ASC
                SEPARATOR ', '),
            ');') AS 'Show_Add_Indexes'
FROM
    information_schema.STATISTICS
WHERE
    UPPER(INDEX_NAME) <> 'PRIMARY'
        AND TABLE_SCHEMA = '库名'
        AND TABLE_NAME = '表名'
GROUP BY TABLE_NAME , INDEX_NAME
ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

SELECT

CONCAT('ALTER TABLE `',

TABLE_NAME,

'` ',

'ADD ',

IF(NON_UNIQUE = 1,

CASE UPPER(INDEX_TYPE)

WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'

WHEN 'SPATIAL' THEN 'SPATIAL INDEX'

ELSE CONCAT('INDEX `',

INDEX_NAME,

'` USING ',

INDEX_TYPE)

END,

IF(UPPER(INDEX_NAME) = 'PRIMARY',

CONCAT('PRIMARY KEY USING ', INDEX_TYPE),

CONCAT('UNIQUE INDEX `',

INDEX_NAME,

'` USING ',

INDEX_TYPE))),

'(',

GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`')

ORDER BY SEQ_IN_INDEX ASC

SEPARATOR ', '),

');') AS 'Show_Add_Indexes'

FROM

information_schema.STATISTICS

WHERE

UPPER(INDEX_NAME) <> 'PRIMARY'

AND TABLE_SCHEMA = '库名'

AND TABLE_NAME = '表名'

GROUP BY TABLE_NAME , INDEX_NAME

ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

获取主键索引的建表语句

SELECT 
    CONCAT('ALTER TABLE `',
            TABLE_NAME,
            '` ',
            'ADD ',
            IF(NON_UNIQUE = 1,
                CASE UPPER(INDEX_TYPE)
                    WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
                    WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
                    ELSE CONCAT('INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE)
                END,
                IF(UPPER(INDEX_NAME) = 'PRIMARY',
                    CONCAT('PRIMARY KEY USING ', INDEX_TYPE),
                    CONCAT('UNIQUE INDEX `',
                            INDEX_NAME,
                            '` USING ',
                            INDEX_TYPE))),
            '(',
            GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`')
                ORDER BY SEQ_IN_INDEX ASC
                SEPARATOR ', '),
            ');') AS 'Show_Add_Indexes'
FROM
    information_schema.STATISTICS
WHERE
    UPPER(INDEX_NAME) = 'PRIMARY'
        AND TABLE_SCHEMA = '库名'
        AND TABLE_NAME = '表名'
GROUP BY TABLE_NAME , INDEX_NAME
ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

SELECT

CONCAT('ALTER TABLE `',

TABLE_NAME,

'` ',

'ADD ',

IF(NON_UNIQUE = 1,

CASE UPPER(INDEX_TYPE)

WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'

WHEN 'SPATIAL' THEN 'SPATIAL INDEX'

ELSE CONCAT('INDEX `',

INDEX_NAME,

'` USING ',

INDEX_TYPE)

END,

IF(UPPER(INDEX_NAME) = 'PRIMARY',

CONCAT('PRIMARY KEY USING ', INDEX_TYPE),

CONCAT('UNIQUE INDEX `',

INDEX_NAME,

'` USING ',

INDEX_TYPE))),

'(',

GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`')

ORDER BY SEQ_IN_INDEX ASC

SEPARATOR ', '),

');') AS 'Show_Add_Indexes'

FROM

information_schema.STATISTICS

WHERE

UPPER(INDEX_NAME) = 'PRIMARY'

AND TABLE_SCHEMA = '库名'

AND TABLE_NAME = '表名'

GROUP BY TABLE_NAME , INDEX_NAME

ORDER BY TABLE_NAME ASC , INDEX_NAME ASC;

如果想获取整个库中所有表的索引创建语句，可以写存储过程循环执行就行了。 &nbs

一	二	三	四	五	六	日
« 9月
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

十字螺丝钉的DBA日记

作者：cnail

MySQL 导出表上所有索引的创建语句

MySQL binlog_row_image参数详解

MySQL 5.7 误删sys schema 如何恢复？

通宵加班跨越儿童节看到了美丽的晨辉

MySQL Router 8.0 安装及说明

MySQL 5.6 5.7半同步的区别

profiling – MySQL资源使用情况剖析工具

MySQL truncate table操作都干了啥，以及注意事项