MySQL生成批量操作数据库表脚本语句

-- 3.1 生成批量操作数据库表脚本语句

-- 1. 生成删除数据库表脚本语句
SELECT
    CONCAT( 'DROP TABLE IF EXISTS ', t.TABLE_SCHEMA,'.`', t.TABLE_NAME, '` ;' ) 
FROM
    information_schema.`TABLES` t 
WHERE t.TABLE_SCHEMA NOT IN ('information_schema','performance_schema','sys','mysql')
    AND t.TABLE_COMMENT <> 'VIEW' 
ORDER BY
    t.TABLE_SCHEMA
    ;

    
-- 2. SHOW CREATE TABLE  -
SHOW CREATE TABLE zhxx_six_mes.agv_dispatch_deploy;    
    
    
-- 2.没有注释的表添加注释为：【未知，待验证】
--  ALTER TABLE `hh_log`.`employees` COMMENT = '【未启用】'; 
    SELECT
    CONCAT( 'ALTER TABLE  ', t.TABLE_SCHEMA,'.`',t.TABLE_NAME, '`', ' COMMENT=\'【未知，待验证】',''' ;' ) 
FROM
    information_schema.`TABLES` t 
WHERE t.TABLE_SCHEMA NOT IN ('information_schema','performance_schema','sys','mysql')
    AND t.TABLE_COMMENT <> 'VIEW' 
    AND t.TABLE_COMMENT = ''
ORDER BY
    t.TABLE_SCHEMA
    ;
    
    -- 3. 通过优化后，给表记录为0的添加注释 【未启用】 + ；
SELECT
    CONCAT( 'ALTER TABLE  `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`', ' COMMENT=\'【未启用】', t.TABLE_COMMENT,''' ;' ) 
FROM
    information_schema.`TABLES` t 
WHERE t.TABLE_SCHEMA NOT IN ('information_schema','performance_schema','sys','mysql')
    AND t.TABLE_COMMENT <> 'VIEW' 
    AND t.TABLE_ROWS = 0 
ORDER BY
    t.TABLE_SCHEMA
    ;

-- 3.1 生产库添加注释：
SELECT
    CONCAT( 'ALTER TABLE  `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`', ' COMMENT=\'', t.TABLE_COMMENT,''' ;' ) 
FROM
    information_schema.`TABLES` t 
WHERE t.TABLE_SCHEMA  IN ('mes')
    AND t.TABLE_COMMENT <> 'VIEW' 
  AND t.TABLE_COMMENT > ''
ORDER BY
    t.TABLE_SCHEMA
    ;
    
-- 4. 字符字段修改字符集
-- ALTER TABLE `zhxx_six_mes`.`auth_role` MODIFY COLUMN `name` varchar(20) NULL DEFAULT NULL COMMENT '角色名称' ;
SELECT
CONCAT( 'ALTER TABLE  `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`', '  MODIFY COLUMN `', c.COLUMN_NAME,'` ',c.column_type,' NULL DEFAULT NULL COMMENT ','\'',c.column_comment,'\' ;' ) 
FROM
    information_schema.`COLUMNS` c,
    information_schema.`TABLES` t
WHERE
    c.TABLE_SCHEMA  IN ('mes')
AND t.TABLE_SCHEMA  IN ('mes')
AND c.TABLE_NAME = t.TABLE_NAME
AND t.TABLE_COMMENT <> 'VIEW' 
-- AND c.character_set_name = 'utf8'
AND c.COLLATION_NAME IS NOT NULL
AND c.IS_NULLABLE = 'YES'
;

-- 5.修改表为统一 InnDB ,如果报1031的错误，需要先修改row_format的参数；
-- 错误：1031  - Table storage engine for '#sql-195_30' doesn't have this option
-- ALTER TABLE `zhxx_fb_mes1`.hibernate_sequence ROW_FORMAT = DEFAULT ;
SELECT 
CONCAT( 'ALTER TABLE  `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`', ' ROW_FORMAT = DEFAULT ;')  
FROM 
    information_schema.TABLES t
WHERE
    t.table_schema = 'mes'
AND t.TABLE_COMMENT <> 'VIEW'
AND t.ENGINE <> 'InnoDB'
;
-- ALTER TABLE  `zhxx_six_mes`.`auth_menu` ENGINE = InnoDB;
SELECT 
CONCAT( 'ALTER TABLE  `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`', ' ENGINE = InnoDB; ')  
FROM 
    information_schema.TABLES t
WHERE
    t.table_schema = 'mes'
AND t.TABLE_COMMENT <> 'VIEW'
AND t.ENGINE <> 'InnoDB'
;
--  修改表的编码格式：
-- ALTER TABLE `zhxx_mes`.`agv_request` CHARACTER SET = utf8mb4, COLLATE = utf8mb4_general_ci;
SELECT 
CONCAT( 'ALTER TABLE  `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`', ' CHARACTER SET = utf8mb4, COLLATE = utf8mb4_general_ci; ')  
FROM 
    information_schema.TABLES t
WHERE
    t.table_schema = 'mes'
AND t.TABLE_COMMENT <> 'VIEW'
-- MySQL 8.0
-- AND t.TABLE_TYPE <> 'VIEW'
;

-- 6. 添加索引 ：
-- ALTER TABLE `zhxx_six_mes`.`produce_process_log` ADD INDEX `idx_create_time`(`create_time`);
 -- 编码类字段添加索引
 /*
AND c.column_name  LIKE '%code'
AND c.column_name  LIKE '%_no'
AND c.column_name  LIKE '%name'
AND c.column_name  LIKE '%_number'
*/

-- 父类ID,类型等枚举字段
/*
AND c.column_name  LIKE '%_id'
AND c.column_name  LIKE '%_type'
AND c.column_name  LIKE 'is_%'
AND c.column_name  LIKE '%type'
AND c.column_name  LIKE '%status'
AND c.column_name  LIKE '%state'
*/

-- 时间类字段
/*
AND c.column_name NOT LIKE 'gmt_%'
AND c.column_name NOT LIKE '%time'
AND c.column_name NOT LIKE '%_create'
AND c.column_name NOT LIKE '%_update'
*/

-- 用户，评级类字段
/*
AND c.column_name NOT LIKE '%user'
AND c.column_name NOT LIKE '%_level'
*/
SELECT
 CONCAT( 'ALTER TABLE  `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`', ' ADD INDEX ', ' `idx_',c.COLUMN_NAME,'`  ( `',c.COLUMN_NAME,'` )',' , ALGORITHM=INPLACE, LOCK=NONE;' ) 
FROM
    information_schema.`COLUMNS` c,
    information_schema.`TABLES` t
WHERE
       c.TABLE_SCHEMA = 'eap'
AND t.TABLE_SCHEMA = 'eap'
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND t.TABLE_COMMENT <> 'VIEW'
AND (
c.column_name  LIKE '%code'
OR c.column_name  LIKE '%_no'
OR c.column_name  LIKE '%name'
OR c.column_name  LIKE '%_number'
OR c.column_name  LIKE '%_id'
OR c.column_name  LIKE '%_type'
OR c.column_name  LIKE 'is_%'
OR c.column_name  LIKE '%type'
OR c.column_name  LIKE '%status'
OR c.column_name  LIKE '%state'
OR c.column_name  LIKE 'gmt_%'
OR c.column_name  LIKE '%time'
OR c.column_name  LIKE '%_create'
OR c.column_name  LIKE '%_update'
OR c.column_name  LIKE '%user'
OR c.column_name  LIKE '%_level'
)
ORDER BY
    t.TABLE_COMMENT
    ;

-- 结尾开头的枚举字段
-- _type%
-- %_id
-- is_%
-- %status
SELECT
 CONCAT( 'ALTER TABLE  `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`', ' ADD INDEX ', ' `idx_',c.COLUMN_NAME,'`  ( `',c.COLUMN_NAME,'` )',' , ALGORITHM=INPLACE, LOCK=NONE;' ) 
FROM
    information_schema.`COLUMNS` c,
    information_schema.`TABLES` t
WHERE
       c.TABLE_SCHEMA = 'mes'
AND t.TABLE_SCHEMA = 'mes'
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND t.TABLE_COMMENT <> 'VIEW'
AND c.COLUMN_NAME  LIKE  'code'
ORDER BY
    t.TABLE_COMMENT
    ;
    

-- %_number(18)
-- %_no(18)
-- %_code(18)
SELECT
-- CONCAT( 'ALTER TABLE  `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`', ' ADD INDEX ', ' `idx_',c.COLUMN_NAME,'`  ( `',c.COLUMN_NAME,'` )',' ;' ) 
CONCAT( 'ALTER TABLE  `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`', ' ADD INDEX ', ' `idx_',c.COLUMN_NAME,'`  ( `',c.COLUMN_NAME,'`(18) )',' , ALGORITHM=INPLACE, LOCK=NONE;' ) 
FROM
    information_schema.`COLUMNS` c,
    information_schema.`TABLES` t
WHERE
       c.TABLE_SCHEMA = 'mes'
AND t.TABLE_SCHEMA = 'mes'
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND t.TABLE_COMMENT <> 'VIEW'
AND c.COLUMN_NAME  = 'code'
ORDER BY
    t.TABLE_COMMENT
    ;
    

-- 检查那些字段确实索引（反之，这些字段是要添加索引的）
SELECT DISTINCT c.column_name
FROM
    information_schema.`COLUMNS` c,
    information_schema.`TABLES` t
WHERE
       c.TABLE_SCHEMA = 'mes'
AND t.TABLE_SCHEMA = 'mes'
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND t.TABLE_COMMENT <> 'VIEW'
AND c.column_name NOT LIKE '%code'
AND c.column_name NOT LIKE '%_no'
AND c.column_name NOT LIKE '%name'
AND c.column_name NOT LIKE '%_number'

AND c.column_name NOT LIKE '%_id'
AND c.column_name NOT LIKE '%_type'
AND c.column_name NOT LIKE 'is_%'
AND c.column_name NOT LIKE '%type'
AND c.column_name NOT LIKE '%status'
AND c.column_name NOT LIKE '%state'

AND c.column_name NOT LIKE 'gmt_%'
AND c.column_name NOT LIKE '%time'
AND c.column_name NOT LIKE '%_create'
AND c.column_name NOT LIKE '%_update'

AND c.column_name NOT LIKE '%user'
AND c.column_name NOT LIKE '%_level'
;
    
-- 7.删除索引 
-- ALTER TABLE `zhxx_six_mes`.`agv_dispatch_deploy` DROP INDEX `idx_crystal_no`;
SELECT
    CONCAT( 'ALTER TABLE  `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`', ' DROP INDEX ', lower(index_name),' ;' )
    FROM
    information_schema.statistics t
WHERE
    table_schema = 'mes'
AND lower(index_name) <> 'primary' 
AND lower(index_name) LIKE 'idx_%'
;

/*******************************************************************************
 * 标题：1.删除现有索引; 2.添加新索引;
 * 作者：久久 
 * 日期： 
 * 实例环境：【10.10.10.10 / mes 】
 * 版本信息：1.0
 * 内容描述：
 *     按照命名规范修改现有的索引名称
 *   索引命名规则：
 *   1.单列索引命名：idx_表名_字段名;
 *   2.索引长度不超过64个字符，原则上不超过30个字符;
 *   3.联系索引命名：joint_字段名1_字段名2...;
 *   4.唯一索引：unique_idx_字段名;
 *
 * 
 * 
 *********************************************************************************/
-- 查看数据库所有索引
SELECT
    table_name,
    lower(column_name) column_name,
    lower(index_name) index_name,
  non_unique,
    seq_in_index 
FROM
    information_schema.statistics
WHERE
    table_schema = 'mes'
AND lower(index_name) <> 'primary' 
;

-- 只有主键索引的表：
SELECT
  a.TABLE_SCHEMA,
    a.table_name,
    COUNT(*) AS num 
FROM
    information_schema.statistics a 
WHERE
    a.TABLE_SCHEMA NOT IN ( 'information_schema','performance_schema','sys' ,'mysql')
GROUP BY
    a.table_name
HAVING num <= 1    
;

-- 只有主键的表批量生产索引：
SELECT
 CONCAT( 'ALTER TABLE  `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`', ' ADD INDEX ', ' `idx_',c.COLUMN_NAME,'`  ( `',c.COLUMN_NAME,'` )',' , ALGORITHM=INPLACE, LOCK=NONE;' ) 
FROM
    information_schema.`COLUMNS` c,
    information_schema.`TABLES` t,
 (
SELECT
  a.TABLE_SCHEMA,
    a.table_name,
    COUNT(*) AS num 
FROM
    information_schema.statistics a 
WHERE
    a.TABLE_SCHEMA NOT IN ( 'information_schema','performance_schema','sys' ,'mysql')
GROUP BY
    a.table_name
HAVING num <= 1    
) b
WHERE
       c.TABLE_SCHEMA NOT IN ( 'information_schema','performance_schema','sys' ,'mysql')
AND t.TABLE_SCHEMA NOT IN ( 'information_schema','performance_schema','sys' ,'mysql')
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND b.TABLE_SCHEMA = t.TABLE_SCHEMA
AND b.TABLE_NAME = t.TABLE_NAME
AND t.TABLE_COMMENT <> 'VIEW'
AND (
c.column_name  LIKE '%code'
OR c.column_name  LIKE '%_no'
OR c.column_name  LIKE '%name'
OR c.column_name  LIKE '%_number'
OR c.column_name  LIKE '%_id'
OR c.column_name  LIKE '%_type'
OR c.column_name  LIKE 'is_%'
OR c.column_name  LIKE '%type'
OR c.column_name  LIKE '%status'
OR c.column_name  LIKE '%state'
OR c.column_name  LIKE 'gmt_%'
OR c.column_name  LIKE '%time'
OR c.column_name  LIKE '%_create'
OR c.column_name  LIKE '%_update'
OR c.column_name  LIKE '%user'
OR c.column_name  LIKE '%_level'
)
ORDER BY
    t.TABLE_COMMENT
    ;
    

/**
 *1. 删除现有索引;
 */
-- ALTER TABLE  dw_account DROP INDEX user_id;
-- 
-- ALTER TABLE  dw_account_bank DROP INDEX user_id;
-- 

/**
 * 2.添加新索引;
 */
ALTER TABLE dw_borrow ADD INDEX idx_dw_borrow_user_id (user_id) USING BTREE;

ALTER TABLE dw_borrow ADD INDEX idx_dw_borrow_status (STATUS) USING BTREE;

/*******************************************************************************
 * 标题：1.删除现有索引; 2.添加新索引;
 * 作者：永久 
 * 日期：2015-12-08 
 * 实例环境：【生产环境】
 * 版本信息：1.0
 * 内容描述：
 *     按照命名规范修改现有的索引名称
 *   索引命名规则：
 *   1.idx_表名_字段名;
 *   2.索引长度不超过64个字符，原则上不超过30个字符;
 * 
 * 
 *********************************************************************************/
SELECT
    table_name,
    column_name,
    lower(column_name) column_name,
    lower(index_name) index_name,
    lower(index_type) type,
    seq_in_index column_position
FROM
    information_schema.statistics
WHERE
    table_schema = 'mes'
AND lower(index_name) <> 'primary';

-- 1.删除原索引
ALTER TABLE del_qifu_001 DROP INDEX idx_id;

-- 批量修改数据库表的自增ID：
-- ALTER TABLE `zhxx_fb_mes1_four`.`interface_eap_grinding_rack` AUTO_INCREMENT = 10;
SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.AUTO_INCREMENT 
FROM
    information_schema.TABLES t ;
-- 生产语句：
SELECT
CONCAT('ALTER TABLE  `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`' ,' AUTO_INCREMENT = ',t.AUTO_INCREMENT+100000,' ;' )     
FROM
    information_schema.TABLES t 
WHERE t.TABLE_SCHEMA = 'mes'    
AND t.TABLE_COMMENT <> 'VIEW'
;

-- 表优化
-- OPTIMIZE TABLE `dw_account_log`;
-- 批量生产语句：
SELECT
CONCAT('OPTIMIZE TABLE   `', t.TABLE_SCHEMA,'`.`',t.TABLE_NAME, '`' ,'  ;' )     
FROM
    information_schema.TABLES t 
WHERE t.TABLE_SCHEMA = 'mes'    
AND t.TABLE_COMMENT <> 'VIEW'
;