MySQL 批量修改表和字段

谁借莪１个温暖的怀抱￠ 2023-07-05 11:40 8阅读 0赞

MySQL 的 information\_schema 数据库中包含了大量的元数据，通过这些数据可以方便的获取表和字段的信息，通过组合这些信息可以生成操作表和字段的 SQL。

本文只是为了方便自己的记录。

由于发现系统多个数据库使用的字符集和排序方式有误，因此要修改错误的这部分表，由于修改表无法直接影响 char, varchar, text 等字符串类型的值，因此这部分字段还需要特殊修改。又因为数据库开启了主从复制，因此生成的 SQL 必须是 `use 数据库` 形式，不能使用 `数据库.表名` 形式。

## 修改库 ##

select concat('ALTER database `', SCHEMA_NAME, '` '
                  , 'DEFAULT CHARACTER SET=utf8 default COLLATE=utf8_general_ci;')
    from SCHEMATA
    where SCHEMA_NAME not in (
    'information_schema',
    'mysql',
    'sys',
    'performance_schema'
    )
    and DEFAULT_COLLATION_NAME != 'utf8_general_ci';

生成的 SQL 示例：

ALTER database `oa_wbs` DEFAULT CHARACTER SET=utf8  default COLLATE=utf8_general_ci;

## 修改表 ##

SELECT concat('use `', TABLE_SCHEMA, '` ;', 
                  'ALTER TABLE `', table_name, '` ',
                  'DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;')
    FROM `TABLES` 
    -- 排除系统表
    where TABLE_SCHEMA not in (
    'information_schema',
    'mysql',
    'sys',
    'performance_schema'
    )
    and 
    TABLE_COLLATION != 'utf8_general_ci';

生成的 SQL 示例：

use `test` ;ALTER TABLE `author` DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;
    use `test` ;ALTER TABLE `city` DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;
    use `test` ;ALTER TABLE `country_i` DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

## 修改字段 ##

SELECT concat('use `', TABLE_SCHEMA, '`; ',
                  ' ALTER TABLE `', TABLE_NAME, 
                  '` MODIFY COLUMN `', COLUMN_NAME, '` ' , 
                  DATA_TYPE , 
                  -- varchar, char, 其他不需要指定长度
                  if(data_type like '%text', ' ', 
                               concat('(', CHARACTER_MAXIMUM_LENGTH, ') ')),
                  ' CHARACTER SET utf8 COLLATE utf8_general_ci ', 
                  if(IS_NULLABLE = 'YES', 'NULL DEFAULT NULL', 'NOT NULL') ,
                  ' COMMENT ''', COLUMN_COMMENT, ''';') sqlstr
    FROM `COLUMNS` 
    -- 排除系统表
    where TABLE_SCHEMA not in (
    'information_schema',
    'mysql',
    'sys',
    'performance_schema'
    )
    -- 排除视图
    and not exists (
        select 1 from tables 
        where tables.table_type = 'VIEW' and tables.table_name = columns.table_name)
    -- 所有不是 utf8 的表
    and (CHARACTER_SET_NAME != 'utf8' or COLLATION_NAME != 'utf8_general_ci');

生成的 SQL 示例：

use `datasync`;  ALTER TABLE `sync_record_detail` MODIFY COLUMN `RESOURCE_FORM_VALUE` text  CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '资源配置';
    use `datasync`;  ALTER TABLE `sync_resource_config` MODIFY COLUMN `FORM_TEMPLATE` text  CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '表单';
    use `datasync`;  ALTER TABLE `sync_resource_config` MODIFY COLUMN `FORM_DEFAULT_VALUE` text  CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '表单默认值';
    use `files`;  ALTER TABLE `sys_file` MODIFY COLUMN `md5` char(32)  CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'md5';

选取一个格式化:

use `files`;  
    ALTER TABLE `sys_file` 
    MODIFY COLUMN `md5` char(32)
    CHARACTER SET utf8 COLLATE utf8_general_ci 
    NULL DEFAULT NULL COMMENT 'md5';