软件开发培训班 >> 编程开发 >> 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

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