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&NBSP;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&NBSP;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