无法在数据库中存储表情符号 [英] Cannot store emoji in database
问题描述
状况：
如果此问题已被提出，请提前抱歉，但解决方案不适合我。 / p>
无论我尝试什么，我都不能在我的数据库中存储表情符号。它们保存为 ???? 。
正确保存的唯一emojis是那些只需要3个字节，如害羞的脸或太阳。
实际的utf8mb4无效。
它已经在Android和Ios上测试。具有相同结果。
VERSIONS ：
Mysql：5.5.49
CodeIgniter：3.0.0
步骤：
-
我修改了数据库字符集和排序规则属性。
ALTER DATABASE my_database CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci -
我修改了表格字符集和排序规则属性。
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci -
UTF-8（ut8mb4）和排序规则：
utf8mb4_unicode_ci -
我已在CodeIgniter应用程式中修改资料库连结。
-
$ c> SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci
-
最后，我也尝试过：
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;
它不工作。
DATABASE SETTINGS ：
运行以下命令：
`SHOW VARIABLES WHERE Variable_name LIKE'character\_set\_％'OR Variable_name LIKE'collation ％'，`
表设置：
表结构的快照：
DATABASE CONNECTION ：
这些是database.php中的数据库连接设置（注意这不是唯一的数据库，还有其他使用utf8连接的数据库）
$ db ['my_database'] = array（
'dsn'=> ''，
'hostname'=> PROJECT_DATABASE_HOSTNAME，
'username'=> PROJECT_DATABASE_USERNAME，
'password'=> PROJECT_DATABASE_PASSWORD，
'database'=> PROJECT_DATABASE_NAME，
'dbdriver'=> 'mysqli'，
'dbprefix'=> ''，
'pconnect'=> FALSE，
'db_debug'=> TRUE，
'cache_on'=> FALSE，
'cachedir'=> ''，
'char_set'=> 'utf8mb4'，
'dbcollat'=> 'utf8mb4_unicode_ci'，
'swap_pre'=> ''，
'encrypt'=> FALSE，
'compress'=> FALSE，
'stricton'=> FALSE，
'failover'=> array（），
'save_queries'=> TRUE
）;
MY.CNF设置：
这是文件my.cnf的全部内容：
[mysqld]
-storage-engine = MyISAM
innodb_file_per_table = 1
max_allowed_packet = 268435456
open_files_limit = 10000
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[client]
默认字符集= utf8mb4
[mysql]
默认字符-set = utf8mb4
问题：
你知道为什么不工作吗？我错过了什么？
HYPHOTESIS 1 ：
但是问题的原因可能是：
正如你可以在my.cnf character-set-server 清楚地设置为 utf8mb4 ：
但在数据库中运行查询后：
SHOW VARIABLES WHERE Variable_name LIKE'character\_set\_％'OR Variable_name LIKE'collation％';
结果是 character-set-server = latin1
你知道为什么吗？为什么实际上无法更新？
HYPHOTESIS 2 ：
不同的数据库。
这一个设置为utf8mb4，但所有其他设置为utf8。
谢谢！
编辑：
这是 SHOW CREATE TABLE的结果app_messages;
CREATE TABLE`app_messages`（
`message_id` bigint（20）unsigned NOT NULL AUTO_INCREMENT，
`project_id`bigint NULL，
`sender_id` bigint（20）NOT NULL，
`receiver_id` bigint（20）NOT NULL，
`message` text COLLATE utf8mb4_unicode_ci，
`timestamp` bigint 20）DEFAULT NULL，
`is_read` enum（'x'，''）COLLATE utf8mb4_unicode_ci DEFAULT NULL，
PRIMARY KEY（`message_id`）
）ENGINE = InnoDB AUTO_INCREMENT = 496 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci
编辑2：
$ b
我运行了以下命令：
INSERT INTO app_messages（message_id，project_id，sender_id，
VALUES（'496'，'322'，'77'，'188'，'THE SITUATION:
Sorry in advance if this question has already been asked, but the solutions aren't working for me.
No matter what I try, I cannot store emoji in my database. They are saved as ????.
The only emojis that are properly saved are the ones that require only 3 bytes, like the shy face or the sun.
The actual utf8mb4 is not working.
It has been tested on both Android and Ios. With same results.
VERSIONS:
Mysql: 5.5.49
CodeIgniter: 3.0.0
THE STEPS:
I have modified database character set and collation properties.
ALTER DATABASE my_database CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci
I have modified table character set and collation properties.
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
I have set each field of the table, where possible, as Encoding: UTF-8(ut8mb4) and Collation: utf8mb4_unicode_ci
I have modified the database connection in the CodeIgniter app.
I have run the following: SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci
Lastly I have also tried this:
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;
Everything should have been setup properly but yet it doesn't work.
DATABASE SETTINGS:
This is the outcome running the following command:
`SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';`
TABLE SETTINGS:
A screeshot of the table structure:
DATABASE CONNECTION:
These are the database connection settings inside database.php (note this is not the only database, there are also others that connect using utf8)
$db['my_database'] = array(
'dsn' => '',
'hostname' => PROJECT_DATABASE_HOSTNAME,
'username' => PROJECT_DATABASE_USERNAME,
'password' => PROJECT_DATABASE_PASSWORD,
'database' => PROJECT_DATABASE_NAME,
'dbdriver' => 'mysqli',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => TRUE,
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8mb4',
'dbcollat' => 'utf8mb4_unicode_ci',
'swap_pre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);
MY.CNF SETTINGS:
This is the whole content of the file my.cnf:
[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
THE QUESTION:
Do you know why is not working? Am I missing something?
HYPHOTESIS 1:
I am not sure, but the cause of the problem may be this:
As you can see in my.cnf character-set-server is clearly set as utf8mb4:
But after running the query in the database:
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
The outcome is that character-set-server = latin1
Do you know why is that? Why is not actually updating?
HYPHOTESIS 2:
The application use several different databases.
This one is set to utf8mb4 but all the others are set to utf8. It may be a problem even if they are separated databases?
Thank you!
EDIT:
This is the outcome of SHOW CREATE TABLE app_messages;
CREATE TABLE `app_messages` (
`message_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`project_id` bigint(20) NOT NULL,
`sender_id` bigint(20) NOT NULL,
`receiver_id` bigint(20) NOT NULL,
`message` text COLLATE utf8mb4_unicode_ci,
`timestamp` bigint(20) DEFAULT NULL,
`is_read` enum('x','') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`message_id`)
) ENGINE=InnoDB AUTO_INCREMENT=496 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
EDIT 2:
I have run the following command:
INSERT INTO app_messages (message_id, project_id, sender_id, receiver_id, message, timestamp, is_read)
VALUES ('496','322','77','188', '
这篇关于无法在数据库中存储表情符号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
