# 添加以下配置
[mysqld]
# 服务器默认字符集
character-set-server=utf8mb4
# 服务器默认排序规则
collation-server=utf8mb4_unicode_ci
# 连接字符集
default_authentication_plugin=mysql_native_password
init_connect=’SET NAMES utf8mb4′

# 保存并重启MySQL
systemctl restart mysqld

# 验证服务器字符集配置
mysql -u root -p -e “SHOW VARIABLES LIKE ‘character_set_server’; SHOW VARIABLES LIKE ‘collation_server’;”
Enter password: Fgedu123!
+———————-+———+
| Variable_name | Value |
+———————-+———+
| character_set_server | utf8mb4 |
+———————-+———+
+——————+——————–+
| Variable_name | Value |
+——————+——————–+
| collation_server | utf8mb4_unicode_ci |
+——————+——————–+

# 查看数据库字符集配置
mysql -u root -p -e “SELECT schema_name, default_character_set_name, default_collation_name FROM information_schema.schemata WHERE schema_name = ‘mydb’;”
Enter password: Fgedu123!
+————-+—————————-+————————+
| schema_name | default_character_set_name | default_collation_name |
+————-+—————————-+————————+
| mydb | utf8mb4 | utf8mb4_unicode_ci |
+————-+—————————-+————————+

# 修改数据库字符集
mysql -u root -p -e “ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;”
Enter password: Fgedu123!

# 查看表字符集配置
mysql -u root -p mydb -e “SHOW CREATE TABLE users;”
Enter password: Fgedu123!
+——-+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| Table | Create Table |
+——-+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| users | CREATE TABLE `users` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+——-+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+

# 创建表时指定列级字符集
mysql -u root -p mydb -e “CREATE TABLE products (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, sku VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;”
Enter password: Fgedu123!

# 查看当前连接字符集
mysql -u root -p mydb -e “SHOW VARIABLES LIKE ‘character_set%’;”
Enter password: Fgedu123!
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+

# 查看连接排序规则
mysql -u root -p mydb -e “SHOW VARIABLES LIKE ‘collation%’;”
Enter password: Fgedu123!
+———————-+——————–+
| Variable_name | Value |
+———————-+——————–+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+———————-+——————–+

# 转换单个列的字符集
mysql -u root -p mydb -e “ALTER TABLE users MODIFY name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;”
Enter password: Fgedu123!

# 生成转换数据库所有表的SQL语句
mysql -u root -p -e “SELECT CONCAT(‘ALTER TABLE ‘, table_schema, ‘.’, table_name, ‘ CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;’) FROM information_schema.tables WHERE table_schema = ‘mydb’;”
Enter password: Fgedu123!
+———————————————————————————————————-+
| CONCAT(‘ALTER TABLE ‘, table_schema, ‘.’, table_name, ‘ CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;’) |
+———————————————————————————————————-+
| ALTER TABLE mydb.users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
| ALTER TABLE mydb.products CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
+———————————————————————————————————-+

# 检查数据库字符集配置
mysql -u root -p -e “SELECT schema_name, default_character_set_name, default_collation_name FROM information_schema.schemata WHERE schema_name = ‘mydb’;”

# 检查表字符集配置
mysql -u root -p -e “SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema = ‘mydb’;”

# 检查列字符集配置
mysql -u root -p -e “SELECT column_name, character_set_name, collation_name FROM information_schema.columns WHERE table_schema = ‘mydb’ AND table_name = ‘users’;”

# 测试字符存储
mysql -u root -p mydb -e “INSERT INTO users (name, email) VALUES (‘测试用户’, ‘test@example.com’), (‘John Doe’, ‘john@example.com’), (‘用户表情😊’, ’emoji@example.com’);”
mysql -u root -p mydb -e “SELECT * FROM users;”
Enter password: Fgedu123!
+—-+————–+——————+——-+———————+
| id | name | email | phone | created_at |
+—-+————–+——————+——-+———————+
| 1 | 测试用户 | test@example.com | NULL | 2024-01-01 00:00:00 |
| 2 | John Doe | john@example.com | NULL | 2024-01-01 00:00:00 |
| 3 | 用户表情😊 | emoji@example.com | NULL | 2024-01-01 00:00:00 |
+—-+————–+——————+——-+———————+

# 查看导出文件的字符集信息
file -i mydb_utf8mb4.sql
mydb_utf8mb4.sql: text/plain; charset=utf-8

# 导入数据时指定字符集
mysql –default-character-set=utf8mb4 -u root -p mydb < mydb_utf8mb4.sql Enter password: Fgedu123! # 使用mysqlimport导入时指定字符集 mysqlimport --default-character-set=utf8mb4 -u root -p mydb data.csv Enter password: Fgedu123!