Mobile device emoji database storage with UTF8 and utf8mb4 character set

  Some of our business systems have recently had a situation, especially the new version of the iOS device, when the message was published, the use of emoji when the,  to the GBK character set database, the data written to the database, in the echo, the "mouth" can not echo,  to UTF8 Character Set database, you cannot write to the database at all, the client program directly reported Java.io.exception xxxxxxxx.      why?     various mobile devices, a variety of different input methods, will bring some enhanced version of the ' BMP ' emoji.    These emoticons are encoded as 4-byte characters, and the  utf8 character set currently supports only 1-3-byte characters, which results in data not being put into storage. There are two types of    solutions  :   1.   Iterates through the input text, replacing four-byte-length characters with custom characters, with a certain amount of work.   2.   Modify the database character set, modify the database character set from UTF8 to support the 1-4-byte character of utf8mb4       mysql starting from 5.5.3 version supports the UTF8MB4 character set, from my After SQL 5.5.3, the version basically can be upgraded seamlessly to the UTF8MB4 character set.   3. Upgrade scenario:   1)   Modify database character set CHARACTER-SET-SERVER=UTF8MB4   Restart database is in effect.         [mysqld]       character-set-server=utf8mb4      & NBSP; 2)   Modify database   's character set to utf8mb4       alter  database  dbname   Character  set=utf8mb4  3)     Modify the character set of the table   for UTF8MB4        alter  table tablename Character set = Utf8mb4   4. Some points to be aware of.    1)  innodb Index can only support 767 of bytes, UTF8 about 255 characters can be indexed,   to UTF8MB4 is only 191 characters, your index may have to be redefined.   2) your char  varchar field definition may be redefined or modified to the text type.   3)  UTF8MB4 is a superset of UTF8, utf8 character encoding, location, storage   in UTF8MB4 and UTF8 character set, no damage to existing data.   4) be cautious, test your app's compatibility issues with this character set in detail.      Character Set: 

Mysql>SHOW VARIABLESWHEREVariable_name like 'character\_set\_%' ORVariable_name like 'collation%';+--------------------------+-----------------+|Variable_name|Value|+--------------------------+-----------------+|Character_set_client|Utf8||Character_set_connection|Utf8||Character_set_database|Utf8||Character_set_filesystem| binary          ||Character_set_results|Utf8||Character_set_server|Utf8||Character_set_system|Utf8||Collation_connection|Utf8_general_ci||Collation_database|Utf8_general_ci||Collation_server|Utf8_general_ci|+--------------------------+-----------------+TenRowsinch Set(0.20Sec

MySQL> SETCharacter_set_client=GBK; Query OK,0Rows Affected (0.31sec) MySQL> SETCharacter_set_connection=GBK; Query OK,0Rows Affected (0.00sec) MySQL> SetCharacter_set_database=utf8mb4; Query OK,0Rows Affected (0.00sec) MySQL> SetCharacter_set_filesystem=binary; Query OK,0Rows Affected (0.00sec)MySQL> SetCharacter_set_results=GBK; Query OK,0Rows Affected (0.00Sec

Mysql> set Character_set_system=utf8;
ERROR 1238 (HY000): Variable ' Character_set_system ' is a read only Variable

MySQL>set  names GBK; it is equivalent to the following three-sentence instruction: SET = GBK; SET = GBK; SET = GBK;

SET NAMES charset_name ' COLLATE ' collation_name 

[Client]     Connection via connector eg:php


[MySQL]      //mysql The character set when the program landed

Default-character-set = GBK
Equivalent
--set character_set_client = GBK;
--set character_set_results = GBK;
--set character_set_connection = GBK;

#character_set_system = utf8mb4#character_set_client = utf8mb4#character_set_connection = Utf8mb4#character_set_database = Utf8mb4#character_set_results = utf8mb4      //#代表相应变量不能设置在这个区中, error, MySQL login error

[mysqld]   setting character set when//mysqld start

Character_set_filesystem=binary
character - Set -Server=utf8mb4

Collation-server = Utf8mb4_unicode_ci
Init_connect= ' Set autocommit=0; Set names GBK; '   The root user does not perform

#character_set_system=utf8mb4
#character_set_client=
#character_set_connection=utf8mb4//#代表相应变量不能设置在这个区中, mysqld start error
#character_set_database=
#character_set_results =UTF8MB4

[]

Mysql> Select *  fromT1;+------------+-----------------+---------------------+|Cur_user|N_user|In_time|+------------+-----------------+---------------------+| @localhost |Mysql@localhost |  .- -- to xx: -: Geneva |+------------+-----------------+---------------------+1Rowinch Set(0.19sec)MySQL> ALTER TABLET1 MODIFY Cur_userCHAR( -)CHARACTER SETSjis; MySQL>ShowCreate TableT1;   CREATE TABLE' T1 ' (' Cur_user ' )Char( -)CHARACTER SETSjisDEFAULT NULL, ' N_user 'varchar( -)CHARACTER SETUtf8DEFAULT NULL, ' In_time 'timestamp NULL DEFAULT Current_timestamp) ENGINE=InnoDBDEFAULTCHARSET=Utf8mb4

Mobile device emoji database storage with UTF8 and utf8mb4 character set