DB script常用操作

1. 检查编码和排序规则：

show variables like '%character%';
show variables like '%collation%';

2. 检查不是utf8mb4_0900_ai_ci的表：

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLLATION_NAME IS NOT NULL and COLLATION_NAME <> 'utf8mb4_0900_ai_ci'
ORDER BY
    COLLATION_NAME,
    TABLE_NAME,
    ORDINAL_POSITION;

3. 改变库编码：

ALTER DATABASE DATABASE()
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

4. 生成修改表（表改了，列自动改）的编码的sql：

SELECT CONCAT(
               'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME,
               '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;'
       ) AS sql_text
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_COLLATION <> 'utf8mb4_0900_ai_ci';

5. 当sql脚本需要执行，在最前方配置当前session使用的@定义的变量使用统一编码和排序规则，但是表自己本身字段的编码依然不会改变，后续我们会统一所有库、表、字段的编码和排序规则：

-- ==========================================
-- Charset / Collation
-- ==========================================
SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;