怎么将emoji表情存入mysql

首先需要知道在UTF8编码方式有可能是2，3或4个字节。而Emoji表情的UTF8是4个字节，但是在mysql中设置utf8编码最多是3个字节，所以emoji的数据是无法存入。

解决方案就是：将Mysql的编码从utf8转换成utf8mb4，是数据库支持4个字节的utf8编码，有下边三种方法：

1. 修改my.cnf

[mysqld]

character-set-server=utf8mb4

[mysql]

default-character-set=utf8mb4

修改后重启Mysql

2. 以root身份登录Mysql，修改环境变量，将character_set_client，character_set_connection，character_set_database，character_set_results，character_set_server 都修改成utf8mb4

如果表已经存在，将已经建好的表也转换成utf8mb4

alter table tblName convert to character set utf8mb4 collate utf8mb4_bin

MySQL 需要支持 emoji 表情符号版本需要大于5.5.3，且字符集需要设置为utf8mb4 字符集。

utf8mb4和utf8到底有什么区别呢？原来以往的mysql的utf8一个字符最多3字节，而utf8mb4则扩展到一个字符最多能有4字节，所以能支持更多的字符集。

将Mysql的编码从utf8转换成utf8mb4。

需要 >= MySQL 5.5.3版本、从库也必须是5.5的了、低版本不支持这个字符集、复制报错

停止MySQL Server服务

修改 my.cnf或者mysql.ini

?1234567891011 [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='SET NAMES utf8mb4'

重启 MySQL Server、检查字符集。

查看服务器字符集设置

?1 mysql>SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%'

?1234567891011121314 +--------------------------+--------------------+ | 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 | +--------------------------+--------------------+

查看数据库字符集

?1 mysql>select * from SCHEMATA where SCHEMA_NAME='ttlsa'

?12345 +--------------+-------------+----------------------------+------------------------+----------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | +--------------+-------------+----------------------------+------------------------+----------+ | def | ttlsa | utf8mb4 | utf8mb4_unicode_ci | NULL | +--------------+-------------+----------------------------+------------------------+----------+

查看表字符集

?1 mysql>select TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION from information_schema.TABLES

?123 +--------------------+----------------------------------------------------+--------------------+ | TABLE_SCHEMA| TABLE_NAME | TABLE_COLLATION | +--------------------+----------------------------------------------------+--------------------+

查看列字符集

?1 mysql>select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from COLUMNS

?123 +--------------------+----------------------------------------------------+--------------------------------------------+--------------------+ | TABLE_SCHEMA| TABLE_NAME | COLUMN_NAME| COLLATION_NAME | +--------------------+----------------------------------------------------+--------------------------------------------+--------------------+

转换字符集语句

?123456789 use information_schemaSELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci") as _sql FROM `TABLES` where table_schema like "DB_NAME" group by table_schemaSELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci") as _sql FROM `TABLES` where table_schema like "DB_NAME" group by table_schema, table_nameSELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci") as _sql FROM `COLUMNS` where table_schema like "DB_NAME" and data_type in ('varchar')SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci") as _sql FROM `COLUMNS` where table_schema like "DB_NAME" and data_type in ('text','tinytext','mediumtext'

---