MySQL架构优化之字符集
发表于：2026-11-01 作者：千家信息网编辑
千家信息网最后更新 2026年11月01日，MySQL架构优化之字符集MySQL应用于大中小企业当中，字符集标准化也是MySQL架构优化中重要的一部分。通常建议中英文混合环境建议选择utf8字符集。1.操作系统Linux cat /etc/sy
千家信息网最后更新 2026年11月01日MySQL架构优化之字符集MySQL架构优化之字符集
MySQL应用于大中小企业当中，字符集标准化也是MySQL架构优化中重要的一部分。通常建议中英文混合环境建议选择utf8字符集。
1.操作系统Linux cat /etc/sysconfig/i18n => LANG="en_US.UTF-8"
2.MySQL客户端 cat /etc/my.cnf => [client] default-character-set=utf8
3.MySQL服务端 cat /etc/my.cnf => [mysqld] character-set-server=utf8 collation-server=utf8_bin
4.库、表字符集一致 默认库、表字符集与MySQL服务端保持一致；所以，默认库、表字符集均为utf8
5.程序 选择统一的utf8程序安装包
1.操作系统Linux字符集
[root@db12cvm1 ~]# cat /etc/sysconfig/i18n
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"
2.MySQL客户端字符集
[root@db12cvm1 ~]# cat /etc/my.cnf
[client]
default-character-set=utf8
3.MySQL服务端字符集
[root@db12cvm1 ~]# cat /etc/my.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_bin
4.库、表字符集一致 默认库、表字符集与MySQL服务端保持一致
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database dbadoudou;
Query OK, 1 row affected (0.00 sec)
mysql> show create database dbadoudou\G;
*************************** 1. row ***************************
Database: dbadoudou
Create Database: CREATE DATABASE `dbadoudou` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */
1 row in set (0.00 sec)
ERROR:
No query specified
## DATABASE DEFAULT CHARACTER SET utf8 COLLATE utf8_bin and [mysqld] character-set-server=utf8
## collation-server=utf8_bin the same
mysql> use dbadoudou;
Database changed
mysql> create table dbadoudou
-> (
-> id int(4) not null auto_increment,
-> name char(20) not null,
-> primary key (id)
-> );
Query OK, 0 rows affected (4.36 sec)
mysql> show create table dbadoudou;
+-----------
+------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+
| Table | Create Table
|
+-----------
+------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+
| dbadoudou | CREATE TABLE `dbadoudou` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-----------
+------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
## TABLE DEFAULT CHARSET=utf8 COLLATE=utf8_bin AND [mysqld] character-set-server=utf8
## collation-server=utf8_bin the same
mysql> insert into dbadoudou (name) values('dbadoudou'),('ocmxiaoyu'),('ocmdream');
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT =
STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited
to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
## CAUSE: ERROR 1665 (HY000) SOLUTION: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
mysql> show global variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> SELECT @@session.tx_isolation, @@global.tx_isolation;
+------------------------+-----------------------+
| @@session.tx_isolation | @@global.tx_isolation |
+------------------------+-----------------------+
| READ-COMMITTED | READ-COMMITTED |
+------------------------+-----------------------+
1 row in set (0.00 sec)
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@session.tx_isolation, @@global.tx_isolation;
+------------------------+-----------------------+
| @@session.tx_isolation | @@global.tx_isolation |
+------------------------+-----------------------+
| REPEATABLE-READ | READ-COMMITTED |
+------------------------+-----------------------+
1 row in set (0.00 sec)
mysql> insert into dbadoudou (name) values('dbadoudou'),('ocmxiaoyu'),('ocmdream');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from dbadoudou;
+----+-----------+
| id | name |
+----+-----------+
| 1 | dbadoudou |
| 2 | ocmxiaoyu |
| 3 | ocmdream |
+----+-----------+
3 rows in set (0.00 sec)
## reference:MOS (文档 ID 1433907.1)
5.处理MySQL乱码
上面讲到字符集要标准化保持一致。如果出现不一致，怎么解决乱码问题呢？
临时解决:
set names utf8;
永久解决：
vi /etc/my.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_bin
MySQL应用于大中小企业当中，字符集标准化也是MySQL架构优化中重要的一部分。通常建议中英文混合环境建议选择utf8字符集。
1.操作系统Linux cat /etc/sysconfig/i18n => LANG="en_US.UTF-8"
2.MySQL客户端 cat /etc/my.cnf => [client] default-character-set=utf8
3.MySQL服务端 cat /etc/my.cnf => [mysqld] character-set-server=utf8 collation-server=utf8_bin
4.库、表字符集一致 默认库、表字符集与MySQL服务端保持一致；所以，默认库、表字符集均为utf8
5.程序 选择统一的utf8程序安装包
1.操作系统Linux字符集
[root@db12cvm1 ~]# cat /etc/sysconfig/i18n
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"
2.MySQL客户端字符集
[root@db12cvm1 ~]# cat /etc/my.cnf
[client]
default-character-set=utf8
3.MySQL服务端字符集
[root@db12cvm1 ~]# cat /etc/my.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_bin
4.库、表字符集一致 默认库、表字符集与MySQL服务端保持一致
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database dbadoudou;
Query OK, 1 row affected (0.00 sec)
mysql> show create database dbadoudou\G;
*************************** 1. row ***************************
Database: dbadoudou
Create Database: CREATE DATABASE `dbadoudou` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */
1 row in set (0.00 sec)
ERROR:
No query specified
## DATABASE DEFAULT CHARACTER SET utf8 COLLATE utf8_bin and [mysqld] character-set-server=utf8
## collation-server=utf8_bin the same
mysql> use dbadoudou;
Database changed
mysql> create table dbadoudou
-> (
-> id int(4) not null auto_increment,
-> name char(20) not null,
-> primary key (id)
-> );
Query OK, 0 rows affected (4.36 sec)
mysql> show create table dbadoudou;
+-----------
+------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+
| Table | Create Table
|
+-----------
+------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+
| dbadoudou | CREATE TABLE `dbadoudou` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-----------
+------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
## TABLE DEFAULT CHARSET=utf8 COLLATE=utf8_bin AND [mysqld] character-set-server=utf8
## collation-server=utf8_bin the same
mysql> insert into dbadoudou (name) values('dbadoudou'),('ocmxiaoyu'),('ocmdream');
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT =
STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited
to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
## CAUSE: ERROR 1665 (HY000) SOLUTION: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
mysql> show global variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> SELECT @@session.tx_isolation, @@global.tx_isolation;
+------------------------+-----------------------+
| @@session.tx_isolation | @@global.tx_isolation |
+------------------------+-----------------------+
| READ-COMMITTED | READ-COMMITTED |
+------------------------+-----------------------+
1 row in set (0.00 sec)
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@session.tx_isolation, @@global.tx_isolation;
+------------------------+-----------------------+
| @@session.tx_isolation | @@global.tx_isolation |
+------------------------+-----------------------+
| REPEATABLE-READ | READ-COMMITTED |
+------------------------+-----------------------+
1 row in set (0.00 sec)
mysql> insert into dbadoudou (name) values('dbadoudou'),('ocmxiaoyu'),('ocmdream');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from dbadoudou;
+----+-----------+
| id | name |
+----+-----------+
| 1 | dbadoudou |
| 2 | ocmxiaoyu |
| 3 | ocmdream |
+----+-----------+
3 rows in set (0.00 sec)
## reference:MOS (文档 ID 1433907.1)
5.处理MySQL乱码
上面讲到字符集要标准化保持一致。如果出现不一致，怎么解决乱码问题呢？
临时解决:
set names utf8;
永久解决：
vi /etc/my.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_bin
字符
字符集
一致
服务
架构
操作系统
乱码
客户
客户端
建议
标准
程序
系统
标准化
选择
重要
中英
大中小企业
小企业
文档
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
互联网科技公司起名
国智互联网科技
科技与互联网
美国互联网科技巨头监管
期待互联网科技
杭州互联网科技公司
广州凡科互联网科技
新浪科技互联网
掌上梦互联网科技
互联网科技网站
天扬科技互联网广告
互联网企业和科技型企业
上海宁丞科技互联网
伊宁互联网科技做什么的
众安科技工业互联网合作
锡山区互联网智能科技产品供应
皮皮虾互联网科技有限公司
互联网科技股为什么大涨
济南小医互联网科技有限公司招聘
绵阳真晨互联网科技有限公司
互联网科技网
中关村互联网科技集团
互联网科技官网
圆梦互联网科技
网络科技有限互联网
卫星互联网科技股票
小米互联网科技园入驻
半导体行业属于互联网高科技类吗
第三届互联网大会安防黑科技
互联网科技创新创业项目
