mysql导出全部索引_MySQL导出所有Index和约束的方法

本文汇总了MySQL导出所有Index 和 约束的方法，提供给大家以方便大家查询使用。具体如下：

1. 导出创建自增字段语句：

SELECT

CONCAT(

‘ALTER TABLE `‘,

TABLE_NAME,

‘` ‘,

‘MODIFY COLUMN `‘,

COLUMN_NAME,

‘` ‘,

IF(UPPER(DATA_TYPE) = ‘INT‘,

REPLACE(

SUBSTRING_INDEX(

UPPER(COLUMN_TYPE),

‘)‘,

1

),

‘INT‘,

‘INTEGER‘

),

UPPER(COLUMN_TYPE)

),

‘) UNSIGNED NOT NULL AUTO_INCREMENT;‘

)

FROM information_schema.COLUMNS

WHERE TABLE_SCHEMA = ‘source_database_name‘ AND

EXTRA = UPPER(‘AUTO_INCREMENT‘)

ORDER BY TABLE_NAME ASC

2. 导出所有索引：

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 = ‘pbq‘

GROUP BY TABLE_NAME, INDEX_NAME

ORDER BY TABLE_NAME ASC, INDEX_NAME ASC

3. 创建删除所有自增字段：

SELECT

CONCAT(

‘ALTER TABLE `‘,

TABLE_NAME,

‘` ‘,

‘MODIFY COLUMN `‘,

COLUMN_NAME,

‘` ‘,

IF(UPPER(DATA_TYPE) = ‘INT‘,

REPLACE(

SUBSTRING_INDEX(

UPPER(COLUMN_TYPE),

‘)‘,

1

),

‘INT‘,

‘INTEGER‘

),

UPPER(COLUMN_TYPE)

),

‘) UNSIGNED NOT NULL;‘

)

FROM information_schema.COLUMNS

WHERE TABLE_SCHEMA = ‘destination_database_name‘ AND

EXTRA = UPPER(‘AUTO_INCREMENT‘)

ORDER BY TABLE_NAME ASC

4. 删除库所有索引：

SELECT

CONCAT(

‘ALTER TABLE `‘,

TABLE_NAME,

‘` ‘,

GROUP_CONCAT(

DISTINCT

CONCAT(

‘DROP ‘,

IF(UPPER(INDEX_NAME) = ‘PRIMARY‘,

‘PRIMARY KEY‘,

CONCAT(‘INDEX `‘, INDEX_NAME, ‘`‘)

)

)

SEPARATOR ‘, ‘

),

‘;‘

)

FROM information_schema.STATISTICS

WHERE TABLE_SCHEMA = ‘destination_database_name‘

GROUP BY TABLE_NAME

ORDER BY TABLE_NAME ASC

希望本文所述示例能够对大家有所帮助。

原文：http://www.jb51.net/article/53595.htm