mysql5.7.x编码设置为utf8mb4 | xx's blog

1. 临时设置编码(重新进入mysql shell后失效)

mysql> show variables like '%char%';
+--------------------------+-----------------------------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.28-macos10.14-x86_64/share/charsets/ |
+--------------------------+-----------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> set character_set_client=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_connection=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_database=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> set character_set_results=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_server=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%char%';
+--------------------------+-----------------------------------------------------------+
| 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/local/mysql-5.7.28-macos10.14-x86_64/share/charsets/ |
+--------------------------+-----------------------------------------------------------+
8 rows in set (0.00 sec)

2. 永久设置编码

在/etc/my.cnf文件中修改：

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
[mysql]
default-character-set = utf8mb4
[client]
default-character-set = utf8mb4

重启mysql服务生效。

3. 查看编码及编码集

mysql> show variables where variable_name like '%char%' or variable_name like 'collation%';
+--------------------------+-----------------------------------------------------------+
| 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/local/mysql-5.7.28-macos10.14-x86_64/share/charsets/ |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+-----------------------------------------------------------+
11 rows in set (0.00 sec)
mysql>

4. 修改已有数据库或表的编码及编码集

# 修改数据库
mysql> ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# 修改表
mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 修改表字段
mysql> ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;