使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
下一篇: java验证电话号码的方法