MySQL架构优化之字符集
发布时间：2022-06-28 02:30:04 所属栏目：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=ut
|
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 dbadoudouG; *************************** 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_bi （编辑：西双版纳站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
