使MySQL能够存储emoji表情字符的设置教程_MySQL
May 27, 2016 pm 01:46 PMMySQL 需要支持 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
以上就是使MySQL能够存储emoji表情字符的设置教程_MySQL的内容,更多相关内容请关注PHP中文网(www.php.cn)!

Article chaud

Outils chauds Tags

Article chaud

Tags d'article chaud

Bloc-notes++7.3.1
Éditeur de code facile à utiliser et gratuit

SublimeText3 version chinoise
Version chinoise, très simple à utiliser

Envoyer Studio 13.0.1
Puissant environnement de développement intégré PHP

Dreamweaver CS6
Outils de développement Web visuel

SublimeText3 version Mac
Logiciel d'édition de code au niveau de Dieu (SublimeText3)

Sujets chauds

Compétences de traitement de structures de données volumineuses de PHP

Comment optimiser les performances des requêtes MySQL en PHP ?

Comment utiliser la sauvegarde et la restauration MySQL en PHP ?

Comment insérer des données dans une table MySQL en utilisant PHP ?

Quels sont les scénarios d'application des types d'énumération Java dans les bases de données ?

Comment corriger les erreurs mysql_native_password non chargé sur MySQL 8.4

Comment utiliser les procédures stockées MySQL en PHP ?

Stratégies d'optimisation des performances pour la pagination des tableaux PHP
