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