"MYSQ" the character set adjustment raised by an extended platform

The company app (Android) app extended iOS platform (Android client has been running for one year), because iOS comes with emoji emoji character set, the API will have problems, MySQL database replacement utf8mb4, the original character set UTF8.

What's the difference between utf8mb4 and UTF8? The original MySQL UTF8 a character up to 3 bytes, while the UTF8MB4 expands to a character up to 4 bytes, so it can support more character sets.

Main ideas export data, re-build the library to insert data

1. Check whether the current database supports UTF8MB4 (it seems that the version below 5.5.3 is not available, not tested)

Mysql> show CharSet like ' utf8mb4 ', +---------+---------------+--------------------+--------+| Charset | Description | Default Collation | MaxLen |+---------+---------------+--------------------+--------+| UTF8MB4 | UTF-8 Unicode |      Utf8mb4_general_ci |  4 | #<--supports UTF8MB4 character set +---------+---------------+--------------------+--------+1 row in Set (0.00 sec)

2. Shutdown scenario (unable to stop library can lock library read-only)

PS: Unable to provide real data, the following only analog data

Current MySQL Character set

mysql> show variables like  ' character_set% '; +--------------------------+--------------- ----------------------------+| variable_name        | value                           |+--------------------------+--------------------------------------- ----+| character_set_client     | utf8                         |      #<--Client Character Set | character_set_connection  | utf8                          |     #<--Linked Character Set | character_set_database    | utf8                          |     #<--Database Character Set | character_set_ filesystem  | binary                        | |  character_set_results    | utf8                          |     #<--return Character Set | character_set_server     | utf8                           |    #<--Service-side character set | character_set_system      | utf8                          ||  character_sets_dir      | /application/mysql-5.5.32/share/charsets/  |+--------------------------+-------------------------------------------+8 rows in set   (0.00&NBSP;SEC)

Simulation Libraries and tables

Create a library

Mysql> CREATE database app character set UTF8 collate utf8_general_ci; Query OK, 1 row Affected (0.00 sec) mysql> show databases like ' app '; +----------------+| Database (APP) |+----------------+| App |+----------------+1 row in Set (0.00 sec) mysql> Show create DATABASE app\g*************************** 1 . Row *************************** database:appcreate database:create Database ' app '/*!40100 DEFAULT CHARACTER SET u Tf8 */1 Row in Set (0.00 sec)

Create a table

Randomly simulate two tables

mysql> show create table user\g*************************** 1. row ******        table: usercreate table: create  TABLE  ' user '   (   ' uid '  int (4)  NOT NULL AUTO_INCREMENT,    ' name '  char  NOT NULL,  PRIMARY KEY  (' uid ')  engine=innodb  DEFAULT CHARSET=utf81 row in set  (0.00 sec) mysql> show create  table userclient\g*************************** 1. row ***************************        table: userclientcreate table: create table   ' userclient '   (   ' uid '  int (4)  NOT NULL AUTO_INCREMENT,   ' Name '  char  NOT NULL,  PRIMARY KEY  (' uid ')  ENGINE=InnoDB  Default charset=utf81 row in set  (0.00 SEC) 

Analog data

mysql> INSERT into user (name) values (' Evan '), (' Cker '), (' Niuniu '); Query OK, 3 Rows Affected (0.00 sec) Records:3 duplicates:0 warnings:0mysql> insert into userclient (name) VALUES (' C _ Evan '), (' C_ '), (' c_ beef cattle '); Query OK, 3 rows affected (0.01 sec) Records:3 duplicates:0 warnings:0

3. Export the table structure and modify the character set

[Email protected] ~]# mysqldump-uroot-p--default-character-set=utf8-d app >/opt/app_utf8.sql

--dafault-character-set=utf8 export table structure in UTF8 character set to prevent garbled characters

Modifying the character set of a table structure

[Email protected] ~]# sed-i ' [email protected]@[email protected] '/opt/app_utf8.sql

4. Ensure data is not updated, export all data

[Email protected] ~]# mysqldump-uroot-p--quick--no-create-info--extended-insert--default-character-set=utf8 app &G T;/opt/appdata.sql

--quit used for switching large tables

--no-create-info CREATE TABLE statement is not created

--extended-insert uses multi-line insert syntax to reduce IO read and write, and speed

--default-character-set=utf8 Export in accordance with the original character set to prevent garbled characters

5. Re-building the library

mysql> drop database app; Query OK, 2 rows affected (0.05 sec) mysql> show databases like ' app '; Empty Set (0.00 sec) mysql> Create database app character Set=utf8mb4 Collate=utf8mb4_unicode_ci; Query OK, 1 row Affected (0.00 sec) mysql> Show create Database app\g*************************** 1. Row *************************** database:appcreate database:create Database ' app '/*!40100 DEFAULT CHARACTER SET u TF8MB4 COLLATE utf8mb4_unicode_ci */1 row in Set (0.00 sec)

6. Import table structure and data

Modify client character set and library consistency

Mysql> set names utf8mb4;       query ok, 0  rows affected  (0.00 sec) mysql> show variables like  ' character_set% '; +--------------------------+-------------------------------------------+| variable_name             | Value                                       |+--------------------------+--- ----------------------------------------+| character_set_client     |  utf8mb4                                    | |  character_set_connection | utf8mb4                                     | |  character_set_database   | utf8                                        | |  character_set_filesystem | binary                                      | |  character_set_results    | utf8mb4                                    | |  character_set_server     | utf8                                        | |  character_set_system     | utf8                                        | |  character_sets_dir       | /application/mysql-5.5.32/share/ charsets/ |+--------------------------+-------------------------------------------+8 rows in  set  (0.00 SEC)

Import Table Structure

mysql> use app;    #<--into the library mysql> source /opt/app_utf8.sql;   #<--sql file mysql> show tables;+---------------+| tables_in_app |+ to perform the backup---- -----------+| user          | |  userclient    |+---------------+2 rows in set  (0.00 sec) mysql> show create table user\g*************************** 1. row ******        table: usercreate table: create  TABLE  ' user '   (   ' uid '  int (4)  NOT NULL AUTO_INCREMENT,    ' name '  char  NOT NULL,  PRIMARY KEY  (' uid ')  engine=innodb  auto_increment=4 default charset=utf8mb4       #<-- The character set is utf8mb41 row in set  (0.00 SEC) Mysql> show create table userclient\g*************************** 1. row  ***************************       Table: userclientCreate  table: create table  ' userclient '   (   ' uid '  int (4)  NOT NULL  auto_increment,   ' name '  char  NOT NULL,  PRIMARY KEY  (' uid '))  ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb41 row in set  ( 0.00&NBSP;SEC)

Import data

Mysql> Source/opt/appdata.sql;

Query data is normal

Mysql> SELECT * FROM userclient;+-----+----------+| UID |   Name |+-----+----------+| 1 |   C_ Evan | | 2 |   C_ | | 3 | C_ Beef Cattle |+-----+----------+3 rows in Set (0.00 sec) mysql> SELECT * FROM user;+-----+--------+| UID |   Name |+-----+--------+| 1 |   Evan | | 2 |   Cker | | 3 | Niuniu |+-----+--------+3 rows in Set (0.00 sec)

Import data garbled Error method

mysql> set names GBK;  Query OK, 0 rows Affected (0.00 sec) mysql> Use app;database changedmysql> select * FROM userclient;+-----+--------+| UID |   Name |+-----+--------+| 1 |   c_°￡τ| | 2 |   C_ | | 3 | C_ |+-----+--------+3 rows in Set (0.00 sec)

The Client and library table character sets are different for the above reasons

mysql> show variables like  ' character_set% '; +--------------------------+--------------- ----------------------------+| variable_name             | Value                                       |+--------------------------+-------------------------------------------+|  character_set_client     | gbk                                         | |  character_set_connection | gbk                                         | |  character_set_database   | utf8mb4                                     | |  character_set_filesystem | binary                                      | |  character_set_results    | gbk                                         | |  character_set_server     | utf8                                        | |  character_set_system     | utf8                                        | |  character_sets_dir       | /application/mysql-5.5.32/share/ charsets/ |+--------------------------+-------------------------------------------+

7. Service-Side default character processing (if not necessary, as long as the "client, and library table" characters are guaranteed)

Client

Temporary effect (set names character set)

Permanent effect change the [client] module of the configuration file my.cnf

[Client]

default-character-set= Character Set #<--Add this statement without restarting the service

Service side

Change the [mysqld] module of the configuration file my.cnf

[Mysqld]

default-character-set= character set for 5.1 and previous versions

character-set-server= character set for 5.5

This article is from "Evan" blog, please make sure to keep this source http://ievan.blog.51cto.com/11177578/1765487

"MYSQ" the character set adjustment raised by an extended platform