MySQL导出所有Index和约束的方法
本文汇总了MySQL导出所有Index和约束的方法，提供给大家以方便大家查询使用。具体如下：
1.导出创建自增字段语句：
SELECT
CONCAT(
'ALTERTABLE`',
TABLE_NAME,
'`',
'MODIFYCOLUMN`',
COLUMN_NAME,
'`',
IF(UPPER(DATA_TYPE)='INT',
REPLACE(
SUBSTRING_INDEX(
UPPER(COLUMN_TYPE),
')',
1
),
'INT',
'INTEGER'
),
UPPER(COLUMN_TYPE)
),
')UNSIGNEDNOTNULLAUTO_INCREMENT;'
)
FROMinformation_schema.COLUMNS
WHERETABLE_SCHEMA='source_database_name'AND
EXTRA=UPPER('AUTO_INCREMENT')
ORDERBYTABLE_NAMEASC
2.导出所有索引：
SELECT
CONCAT('ALTERTABLE`',TABLE_NAME,'`','ADD',
IF(NON_UNIQUE=1,
CASEUPPER(INDEX_TYPE)
WHEN'FULLTEXT'THEN'FULLTEXTINDEX'
WHEN'SPATIAL'THEN'SPATIALINDEX'
ELSECONCAT('INDEX`',
INDEX_NAME,
'`USING',
INDEX_TYPE
)
END,
IF(UPPER(INDEX_NAME)='PRIMARY',
CONCAT('PRIMARYKEYUSING',
INDEX_TYPE
),
CONCAT('UNIQUEINDEX`',
INDEX_NAME,
'`USING',
INDEX_TYPE
)
)
),'(',GROUP_CONCAT(DISTINCTCONCAT('`',COLUMN_NAME,'`')ORDERBYSEQ_IN_INDEXASCSEPARATOR','),');')AS'Show_Add_Indexes'
FROMinformation_schema.STATISTICS
WHERETABLE_SCHEMA='pbq'
GROUPBYTABLE_NAME,INDEX_NAME
ORDERBYTABLE_NAMEASC,INDEX_NAMEASC
3.创建删除所有自增字段：
SELECT
CONCAT(
'ALTERTABLE`',
TABLE_NAME,
'`',
'MODIFYCOLUMN`',
COLUMN_NAME,
'`',
IF(UPPER(DATA_TYPE)='INT',
REPLACE(
SUBSTRING_INDEX(
UPPER(COLUMN_TYPE),
')',
1
),
'INT',
'INTEGER'
),
UPPER(COLUMN_TYPE)
),
')UNSIGNEDNOTNULL;'
)
FROMinformation_schema.COLUMNS
WHERETABLE_SCHEMA='destination_database_name'AND
EXTRA=UPPER('AUTO_INCREMENT')
ORDERBYTABLE_NAMEASC
4.删除库所有索引：
SELECT
CONCAT(
'ALTERTABLE`',
TABLE_NAME,
'`',
GROUP_CONCAT(
DISTINCT
CONCAT(
'DROP',
IF(UPPER(INDEX_NAME)='PRIMARY',
'PRIMARYKEY',
CONCAT('INDEX`',INDEX_NAME,'`')
)
)
SEPARATOR','
),
';'
)
FROMinformation_schema.STATISTICS
WHERETABLE_SCHEMA='destination_database_name'
GROUPBYTABLE_NAME
ORDERBYTABLE_NAMEASC
希望本文所述示例能够对大家有所帮助。