MySQL架构优化之字符集
发表于：2025-11-07 作者：千家信息网编辑
千家信息网最后更新 2025年11月07日，MySQL架构优化之字符集MySQL应用于大中小企业当中，字符集标准化也是MySQL架构优化中重要的一部分。通常建议中英文混合环境建议选择utf8字符集。1.操作系统Linux cat /etc/sy
千家信息网最后更新 2025年11月07日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安全错误
数据库的锁怎样保障安全
关于网络安全的知识题库
数据库表怎么添加一个容器
数据库的data文件在哪
代理服务器的优缺点
软件开发工程师的技能要求
衢州官方软件开发价格
奥西450 服务器装系统
DLL数据库中
crsed游戏服务器在哪里
数据库自动化运维场景
云服务器搭建手机版
数据库连接密钥生成工具
低功耗网吧服务器
吉林市公安局网络安全监察
常熟市网络安全技能大赛
上海市泡宝网络技术有限公司
传感器网络安全黑板报
虹口区咨询网络技术服务什么价格
服务器管理器功能
计算机视觉和网络安全选哪个
嵌入式软件开发资料
加强网络技术监控
vc 实用教程第七章数据库
网络安全工具箱收纳
哈理工软件工程是软件开发
数据库由于索引重复值
校园网络安全宣传周
计算机网络技术应聘职务
linux服务器日志管理
酷家乐网页版是用什么软件开发的
