批量生成所有表的修改脚本

1、-- 请将 'your_database_name' 替换为你的真实数据库名
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_TYPE = 'BASE TABLE';

2、-- 请将 'your_database_name' 替换为你的真实数据库名，过滤表名

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name' 
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_NAME LIKE 'dry\_%' ESCAPE '\';   -- 匹配以 dry_ 开头的表

3、修改字段

-- 请将 'your_database_name' 替换为你的真实数据库名
SELECT CONCAT(
    'ALTER TABLE `', TABLE_NAME, '` MODIFY `', COLUMN_NAME, '` ',
    COLUMN_TYPE,
    ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci',
    IF(IS_NULLABLE = 'NO', ' NOT NULL', ''),
    IF(COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ''', COLUMN_DEFAULT, ''''), ''),
    ';'
) AS alter_sql
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND DATA_TYPE IN ('varchar', 'char', 'text', 'tinytext', 'mediumtext', 'longtext');