MySql数据库表和字段排序规则和字符集批量修改、批量删除废弃表

偏执的太偏执、 2021-11-27 05:52 527阅读 0赞

### 删除某个数据库的全部表 ###

> 在做项目的过程中，开始进行技术探索过程中，数据库经常发生大的变动，可能原来的表都废弃了，要全部drop掉，所以有了下面这个脚本，其实很简单的

-- 批量删除的脚本
    SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
    FROM information_schema.tables
    WHERE table_schema = 'dbName';

dbName设置成要删除所有表的数据库名

### 批量修改MySql数据库表和字段排序规则和字符集 ###

> 和很多开发人员一样，我们经常需要从本地的Mysql上的数据库数据更新到服务器端的Mysql上，但问题来了，本地的Mysql经常是Mysql8.0以上版本，服务器端的一般是Mysql5.x，由于Mysql8.0版本的字符集默认设置为utf8mb4，字符排序规则为utf8mb4\_0900\_ai\_ci，Mysql5.x则为utf8,utf8\_general\_ci  
> 解决的办法：1.将服务器端的字符集和排序规则改成Mysql8.x的 2.手动将导出sql建表语句用替换功能改 3.将本地数据库的字符集和排序规则  
> 我们这里用的就是第三种，为了便于以后操作，所以根据网上有些博客，写了下面的简单脚本

-- 批量修改字符编码和排序编码
    select concat('ALTER TABLE `', table_name, '` MODIFY `', column_name, '`', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_general_ci',';') 
    FROM information_schema.COLUMNS
    WHERE table_schema = 'dbName'
    AND DATA_TYPE = 'varchar'
    AND
    (
        CHARACTER_SET_NAME != 'utf8'
        OR
        COLLATION_NAME != 'utf8_general_ci'
    );
    
    -- 设置每张表的字符集和排序规则为为utf-8、utf8_general_ci
    SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;')
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'dbName'

使用方法：

1.  dbName替换成你的数据名
2.  先执行批量修改字符编码和排序编码的脚本，将生成的结果复制出来，执行它们
3.  执行设置每张表的字符集和排序规则为为utf-8、utf8\_general\_ci的脚本，将生成的结果复制出来，执行它们