使MySQL能够存储emoji表情字符的设置教程
MySQL需要支持emoji表情符号版本需要大于5.5.3，且字符集需要设置为utf8mb4字符集。
utf8mb4和utf8到底有什么区别呢？原来以往的mysql的utf8一个字符最多3字节，而utf8mb4则扩展到一个字符最多能有4字节，所以能支持更多的字符集。
将Mysql的编码从utf8转换成utf8mb4。
需要>=MySQL5.5.3版本、从库也必须是5.5的了、低版本不支持这个字符集、复制报错
停止MySQLServer服务
修改my.cnf或者mysql.ini
[client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] character-set-client-handshake=FALSE character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci init_connect='SETNAMESutf8mb4'
重启MySQLServer、检查字符集。
查看服务器字符集设置
mysql>SHOWVARIABLESWHEREVariable_nameLIKE'character%'ORVariable_nameLIKE'collation%';
+--------------------------+--------------------+ |Variable_name|Value| +--------------------------+--------------------+ |character_set_client|utf8mb4| |character_set_connection|utf8mb4| |character_set_database|utf8mb4| |character_set_filesystem|binary| |character_set_results|utf8mb4| |character_set_server|utf8mb4| |character_set_system|utf8| |collation_connection|utf8mb4_unicode_ci| |collation_database|utf8mb4_unicode_ci| |collation_server|utf8mb4_unicode_ci| +--------------------------+--------------------+
查看数据库字符集
mysql>select*fromSCHEMATAwhereSCHEMA_NAME='ttlsa';
+--------------+-------------+----------------------------+------------------------+----------+ |CATALOG_NAME|SCHEMA_NAME|DEFAULT_CHARACTER_SET_NAME|DEFAULT_COLLATION_NAME|SQL_PATH| +--------------+-------------+----------------------------+------------------------+----------+ |def|ttlsa|utf8mb4|utf8mb4_unicode_ci|NULL| +--------------+-------------+----------------------------+------------------------+----------+
查看表字符集
mysql>selectTABLE_SCHEMA,TABLE_NAME,TABLE_COLLATIONfrominformation_schema.TABLES;
+--------------------+----------------------------------------------------+--------------------+ |TABLE_SCHEMA|TABLE_NAME|TABLE_COLLATION| +--------------------+----------------------------------------------------+--------------------+
查看列字符集
mysql>selectTABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAMEfromCOLUMNS;
+--------------------+----------------------------------------------------+--------------------------------------------+--------------------+ |TABLE_SCHEMA|TABLE_NAME|COLUMN_NAME|COLLATION_NAME| +--------------------+----------------------------------------------------+--------------------------------------------+--------------------+
转换字符集语句
useinformation_schema;
SELECTconcat("ALTERDATABASE`",table_schema,"`CHARACTERSET=utf8mb4COLLATE=utf8mb4_unicode_ci;")as_sql
FROM`TABLES`wheretable_schemalike"DB_NAME"groupbytable_schema;
SELECTconcat("ALTERTABLE`",table_schema,"`.`",table_name,"`CONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;")as_sql
FROM`TABLES`wheretable_schemalike"DB_NAME"groupbytable_schema,table_name;
SELECTconcat("ALTERTABLE`",table_schema,"`.`",table_name,"`CHANGE`",column_name,"``",column_name,"`",data_type,"(",character_maximum_length,")CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;")as_sql
FROM`COLUMNS`wheretable_schemalike"DB_NAME"anddata_typein('varchar');
SELECTconcat("ALTERTABLE`",table_schema,"`.`",table_name,"`CHANGE`",column_name,"``",column_name,"`",data_type,"CHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;")as_sql
FROM`COLUMNS`wheretable_schemalike"DB_NAME"anddata_typein('text','tinytext','mediumtext','long