使MySQL能够存储emoji表情字符的设置教程

这篇文章主要介绍了使MySQL能够存储emoji表情字符的设置教程,关键在于utf8mb4字符集的设置,需要的朋友可以参考下

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

?

1 2 3 4 5 6 7 8 9 10 11

[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%' ;

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

+--------------------------+--------------------+ | 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' ;

?

1 2 3 4 5

+--------------+-------------+----------------------------+------------------------+----------+ | 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;

?

1 2 3

+--------------------+----------------------------------------------------+--------------------+ | TABLE_SCHEMA    | TABLE_NAME                     | TABLE_COLLATION  | +--------------------+----------------------------------------------------+--------------------+

查看列字符集

?

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

?

1 2 3

+--------------------+----------------------------------------------------+--------------------------------------------+--------------------+ | TABLE_SCHEMA    | TABLE_NAME                     | COLUMN_NAME                | COLLATION_NAME   | +--------------------+----------------------------------------------------+--------------------------------------------+--------------------+

转换字符集语句

最后，还是看看我开发的“短信定时提醒”小程序，你也可以做的更好，扫码试一下，

你也可以做的更好！

?

1 2 3 4 5 6 7 8 9

use information_schema; SELECT 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_schema; SELECT 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_name; SELECT 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' , 'tinyte xt' , 'mediumtext' ,'long