MySQL字符集设置

MySQL字符集设置

一、查看当前配置的字符集

\s;

• 示例

MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1Connection id:          11
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         5.5.68-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1  # sql服务端的字符集
Db     characterset:    latin1  # 数据库的字符集
Client characterset:    utf8    # 客户端的字符集
Conn.  characterset:    utf8    # 链接的字符集
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 11 min 15 secThreads: 1  Questions: 7  Slow queries: 0  Opens: 0  Flush tables: 2  Open tables: 26  Queries per second avg: 0.010
--------------

二、修改配置文件，统一字符集

• 文件位置/etc/my.cnf

vim /etc/my.cnf
# 修改或添加以下配置[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci[client]
default-character-set=utf8[mysql]
default-character-set=utf8

• 保存后重启MySQL服务

systemctl restart mariadb

• 进入MySQL客户端验证

[root@yun01 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1Connection id:          3
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         5.5.68-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 17 secThreads: 1  Questions: 5  Slow queries: 0  Opens: 0  Flush tables: 2  Open tables: 26  Queries per second avg: 0.294
--------------

三、注意事项

• 在MySQL、MariaDB5.5版本之前，不支持使用character-set-server字段来设置mysqld服务的字符集；5.5版本之前通常使用default-character-set 或 default-collation 来进行设置。
• 从 MySQL 5.5 开始，加入了 character-set-server 和 collation-server 选项，用于明确设置数据库服务器的默认字符集和排序规则。