Leif の 坊

己亥中原兴似火,九月金陵凉至秋。
琴声已散弦犹振,雁飞九天声已留。

目录
MySQL设置全局数据库编码为UTF8
/  

MySQL设置全局数据库编码为UTF8

一般mysql安装完毕后,数据库信息如下:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

Connection id:		6917
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.26 MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			80 days 22 hours 29 sec

Threads: 41  Questions: 13193212  Slow queries: 1149493  Opens: 30923  Flush tables: 161  Open tables: 476  Queries per second avg: 1.887

--------------

默认字符集为lantin1,下面介绍修改全局字符编码的方法:

1.修改mysql的配置文件,添加如下内容:

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysql.server]
default-character-set=utf8

[mysqld_safe]
default-character-set=utf8

docker容器中的mysql我采用挂载配置文件的方式让配置生效,路径为:/etc/mysql/mysql.conf.d/mysqld.cnf
centos下mysql配置文件路径:/etc/my.cnf
ubuntu下mysql配置文件路径:/etc/mysql/mysql.conf.d/mysqld.cnf

2.重启mysql服务或docker容器
效果:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper

Connection id:		39
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.27 MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			3 hours 10 min 24 sec

Threads: 6  Questions: 5520  Slow queries: 0  Opens: 172  Flush tables: 1  Open tables: 165  Queries per second avg: 0.483
--------------
mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.01 sec)
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

“The first 90% of the code accounts for the first 90% of the development time. The remaining 10% of the code accounts for the other 90% of the development time.” – Tom Cargill

标  题MySQL设置全局数据库编码为UTF8
作  者Leif160519
出  处https://leif.fun/articles/2019/11/18/1574059502176.html
关于博主:坐标金陵古城,运维工程师,如有问题探讨可以直接下方留言。
声援博主:如果您觉得文章对您有帮助,可以评论、订阅、收藏。您的鼓励是博主的最大动力!

评论