Home  >  Article  >  Database  >  MySQL导出所有Index和约束的方法

MySQL导出所有Index和约束的方法

WBOY
WBOYOriginal
2016-06-07 16:28:321113browse

本文汇总了MySQL导出所有Index 和 约束的方法,提供给大家以方便大家查询使用。具体如下: 1. 导出创建自增字段语句: SELECTCONCAT('ALTER TABLE `',TABLE_NAME,'` ','MODIFY COLUMN `',COLUMN_NAME,'` ',IF(UPPER(DATA_TYPE) = 'INT',REPLACE(SUBSTRING_IN

本文汇总了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

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

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn