导出数据库所有索引

  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 = 'xiadmin' AND EXTRA = UPPER('AUTO_INCREMENT') ORDER BY TABLE_NAME ASC
  1. 导出所有索引:
    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 = 'xiadmin' GROUP BY TABLE_NAME, INDEX_NAME ORDER BY TABLE_NAME ASC, INDEX_NAME ASC

     

  2. 创建删除所有自增字段:
    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 = 'xiadmin' AND EXTRA = UPPER('AUTO_INCREMENT') ORDER BY TABLE_NAME ASC
  3. 删除库所有索引:
    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 = 'xiadmin' GROUP BY TABLE_NAME ORDER BY TABLE_NAME ASC

     

说明 TABLE_SCHEMA 为数据库名只要把 xiadmin改为对应的数据库即可

测试结果如下

 

运维mysql

数据库单库备份脚本

2019-7-9 10:13:49

随笔

wordpress使用QQ邮箱为网站发送邮件

2020-8-31 1:34:20

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
今日签到
有新私信 私信列表
搜索