MySQL架構(gòu)優(yōu)化之字符集

發(fā)布時間：2020-08-06 23:26:11 來源：ITPUB博客閱讀：117 作者：lovehewenyu 欄目：MySQL數(shù)據(jù)庫

MySQL架構(gòu)優(yōu)化之字符集

MySQL應(yīng)用于大中小企業(yè)當(dāng)中，字符集標(biāo)準(zhǔn)化也是MySQL架構(gòu)優(yōu)化中重要的一部分。通常建議中英文混合環(huán)境建議選擇utf8字符集。
1.操作系統(tǒng)Linux cat /etc/sysconfig/i18n => LANG="en_US.UTF-8"
2.MySQL客戶端 cat /etc/my.cnf => [client] default-character-set=utf8
3.MySQL服務(wù)端 cat /etc/my.cnf => [mysqld] character-set-server=utf8 collation-server=utf8_bin
4.庫、表字符集一致默認(rèn)庫、表字符集與MySQL服務(wù)端保持一致；所以，默認(rèn)庫、表字符集均為utf8
5.程序選擇統(tǒng)一的utf8程序安裝包

1.操作系統(tǒng)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服務(wù)端字符集
[root@db12cvm1 ~]# cat /etc/my.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_bin

4.庫、表字符集一致默認(rèn)庫、表字符集與MySQL服務(wù)端保持一致
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亂碼
上面講到字符集要標(biāo)準(zhǔn)化保持一致。如果出現(xiàn)不一致，怎么解決亂碼問題呢？
臨時解決:
set names utf8;
永久解決：
vi /etc/my.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_bin

向AI問一下細(xì)節(jié)

MySQL架構(gòu)優(yōu)化之字符集

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽