mysql统一字符集

### 1、检查字符集

```
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';
SHOW VARIABLES LIKE 'collation_connection';
```

结果应为

#### 修改配置文件

```
[client]
default-character-set=utf8mb4
 
[mysql]
default-character-set=utf8mb4
 
[mysqld]
default_authentication_plugin=mysql_native_password
# lower_case_table_names=1
ngram_token_size=1

# 统一字符集和排序规则
init_connect='SET collation_connection = utf8mb4_0900_ai_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci

skip-character-set-client-handshake
skip-name-resolve
max_connections=300
back_log=90 
innodb_thread_concurrency=1
wait-timeout=600 
innodb_buffer_pool_size=500M 
log_bin=mysql-bin 
binlog-format=ROW          #选择row模式
server_id=1

binlog-do-db=mrerp

replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

# 以上非必须，以下为必须
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
skip-name-resolve
```

#### 设置数据库

```
ALTER DATABASE `数据库名` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```

### 2、查找所有不一致的 Collation

```
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = '数据库名' 
AND COLLATION_NAME IS NOT NULL 
AND COLLATION_NAME != 'utf8mb4_0900_ai_ci';
```

#### 生成 ALTER TABLE 语句

```
SELECT 
    CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE, ' COLLATE utf8mb4_0900_ai_ci;') AS sql_stmt
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = '数据库名' 
AND COLLATION_NAME IS NOT NULL 
AND COLLATION_NAME != 'utf8mb4_0900_ai_ci';
```

#### 执行 ALTER TABLE 语句

这会返回所有的 `ALTER TABLE` 语句。你可以手动将其复制到 MySQL 客户端或管理工具（如 MySQL Workbench、Navicat 等）中执行。

#### 使用 PREPARE 执行批量修改（可选）

如果你希望自动执行所有修改，可以用以下 SQL 批量执行这些 `ALTER TABLE` 语句：

```
SET SESSION group_concat_max_len = 100000;  -- 增加SQL拼接长度

SELECT GROUP_CONCAT(
    CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE, ' COLLATE utf8mb4_0900_ai_ci;')
    SEPARATOR ' '
) INTO @sql_stmt
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = '数据库名' 
AND COLLATION_NAME IS NOT NULL 
AND COLLATION_NAME != 'utf8mb4_0900_ai_ci';

-- 预编译执行
PREPARE stmt FROM @sql_stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
```