表情符号显示为?在MySQL数据库中 [英] emoji display as ? in MySQL database
问题描述
表情符号存储为?在我的数据库中(当我使用phpMyAdmin可视化它们时),但是当我使用一个简单的请求(从php)检索它们时,我得到了真正的价值.
Emoji are stored as ? in my database (when i'm visualizing them using phpMyAdmin), however when i retrieve them using a simple request (from php) i get the real value.
数据库行(使用phpMyAdmin)
来自php的请求
$query = "SELECT id,com
FROM coms_table
WHERE id = 627";
结果
id com
627 \ud83d\ude0e
使用此命令
Using this command
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
我明白了:
i get this :
my.cnf文件:
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
performance-schema=0
innodb_buffer_pool_size=134217728
max_allowed_packet=268435456
open_files_limit=10000
local-infile=0
character-set-client-handshake = TRUE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
总而言之,仅当我使用phpMyAdmin连接到数据库时,才会出现此问题.最糟糕的是,当我尝试使用phpMyAdmin导入数据库时,所有问号都替换为真实的?",并且数据最终丢失.
To summary, the issue only occurs when i'm connecting to database using phpMyAdmin. Worst, when i try to import database using phpMyAdmin, all question marks are replaced by true "?", and datas are definitively lost.
编辑1
phpMyAdmin>变量
会话值(utf8)与期望值(utf8mb4)
EDIT 1
phpMyAdmin>Variables
Session values(utf8) are different to expected values(utf8mb4)
推荐答案
似乎是phpMyAdmin错误(在4.6.4版中已更正).
当您使用phpMyAdmin连接到数据库时,不会使用默认值初始化会话的变量,例如character_set_client
和character_set_results
.
参见此提交.
It's appear that's a phpMyAdmin bug (corrected at version 4.6.4).
When you use phpMyAdmin to connect to your database, the session's variables like character_set_client
and character_set_results
aren't initialized with the default values.
See this commit.
如果您有此错误并想要导入/导出数据库,请不要使用phpMyAdmin. 解决方法是,使用此链接执行导入操作,并使用
If you have this bug and want import/export your database, don't use phpMyAdmin. As a workaround, perform the import action using this link, and the export action using the documentation at this link.
这篇关于表情符号显示为?在MySQL数据库中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!