欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-22 11:58:40
mysql 需要支持 emoji 表情符号版本需要大于5.5.3,且字符集需要设置为utf8mb4 字符集。 utf8mb4和utf8到底有什么区别呢?原来以往的my...

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

[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、检查字符集。

查看服务器字符集设置

mysql> show variables where variable_name like 'character%' or variable_name like '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 * from schemata where schema_name='ttlsa';
+--------------+-------------+----------------------------+------------------------+----------+
| catalog_name | schema_name | default_character_set_name | default_collation_name | sql_path |
+--------------+-------------+----------------------------+------------------------+----------+
| def     | ttlsa   | utf8mb4          | utf8mb4_unicode_ci   | null   |
+--------------+-------------+----------------------------+------------------------+----------+

查看表字符集

mysql> select table_schema,table_name,table_collation from information_schema.tables;
+--------------------+----------------------------------------------------+--------------------+
| table_schema    | table_name                     | table_collation  |
+--------------------+----------------------------------------------------+--------------------+

查看列字符集

mysql> select table_schema,table_name,column_name,collation_name from columns;
+--------------------+----------------------------------------------------+--------------------------------------------+--------------------+
| table_schema    | table_name                     | column_name                | collation_name   |
+--------------------+----------------------------------------------------+--------------------------------------------+--------------------+

转换字符集语句

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','tinytext','mediumtext','long