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

前端技术 2023/09/08 MYSQL

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

本文地址：https://www.stayed.cn/item/22858

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案