Shulou(Shulou.com)06/01 Report--

Character set optimized by MySQL architecture

MySQL is used in large, small and medium-sized enterprises, and character set standardization is also an important part of MySQL architecture optimization. It is generally recommended that you choose the utf8 character set in a mixed Chinese and English environment.

1. Operating system Linux cat / etc/sysconfig/i18n = > LANG= "en_US.UTF-8"

2.MySQL client cat / etc/my.cnf = > [client] default-character-set=utf8

3.MySQL server cat / etc/my.cnf = > [mysqld] character-set-server=utf8 collation-server=utf8_bin

4. The default library and table character set are consistent with the MySQL server; therefore, the default library and table character set are utf8

5. The program chooses a unified utf8 program installation package

1. Operating system Linux character set

[root@db12cvm1 ~] # cat / etc/sysconfig/i18n

LANG= "en_US.UTF-8"

SYSFONT= "latarcyrheb-sun16"

2.MySQL client character set

[root@db12cvm1 ~] # cat / etc/my.cnf

[client]

Default-character-set=utf8

3.MySQL server character set

[root@db12cvm1 ~] # cat / etc/my.cnf

[mysqld]

Character-set-server=utf8

Collation-server=utf8_bin

4. Library, table character set consistent default library, table character set is consistent with the MySQL server

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.01sec)

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 (document ID 1433907.1)

5. Deal with MySQL garbled

It is mentioned above that the character set should be standardized and consistent. If there is inconsistency, how to solve the garbled problem?

Temporary solution:

Set names utf8

Permanent solution:

Vi / etc/my.cnf

[mysqld]

Character-set-server=utf8

Collation-server=utf8_bin

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.