MySQL架构优化之字符集
发表于：2025-01-27 作者：千家信息网编辑
千家信息网最后更新 2025年01月27日，MySQL架构优化之字符集MySQL应用于大中小企业当中，字符集标准化也是MySQL架构优化中重要的一部分。通常建议中英文混合环境建议选择utf8字符集。1.操作系统Linux cat /etc/sy
千家信息网最后更新 2025年01月27日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安全错误
数据库的锁怎样保障安全
报表数据库设计
嵌入式软件开发电子书
东南大学916网络安全
软件开发初级教程都有哪些
方舟生存进化付费服务器
hap数据库
数据库什么是元祖
云南上门软件开发市场价
计算机网络技术可以做什么专业
怎么维护网络服务器的安全
对网络安全与生活的认识
网络安全实施办法 党委
dell 最新服务器
数据库设计外卖订单管理系统
软件开发税费如何计算
软件开发最起码学几年
当前数据库技术分为
plex本地服务器
小型服装店用什么数据库
服务器怎么管理链接的客户
金蝶数据库恢复实体
网络安全PDRR模型工作模式
电脑数据库的字怎么调大小
amax服务器bios风扇调速
软件开发企业宣传资料
账户没登录数据库可以查数据吗
年度网络安全检查计划
怎么租云服务器跑代码
dedecms 数据库表
mc还原原神地图是哪个服务器
