问题描述
我正在尝试在我的 MYSQL 表中存储一条推文.推文是:
I am trying to store a tweet in my MYSQL table. The tweet is:
quiero que me escuches, no te burles no te rias, anoche tuve un sueño que te fuiste de mi vida 🎶🎶
quiero que me escuches, no te burles no te rias, anoche tuve un sueño que te fuiste de mi vida 🎶🎶
最后两个字符都是 'MULTIPLE MUSICAL NOTES' (U+1F3B6)，对于其中 UTF-8 编码为 0xf09f8eb6.
The final two characters are both 'MULTIPLE MUSICAL NOTES' (U+1F3B6), for which the UTF-8 encoding is 0xf09f8eb6.
我表中的 tweet_text 字段以 utf8mb4 编码.但是当我尝试将推文存储在该列中时，我收到以下错误消息:
The tweet_text field in my table is encoded in utf8mb4. But when I try to store the tweet in that column I get the following error message:
不正确的字符串值:'xF0x9Fx8ExB6xF0x9F...' 列 'tweet_text' 在第 1 行.
Incorrect string value: 'xF0x9Fx8ExB6xF0x9F...' for column 'tweet_text' at row 1.
怎么了?我怎样才能解决这个问题?我还需要存储多种语言，这个字符集适用于所有语言，但不适用于表情符号和表情符号等特殊字符.
What is going wrong? How can I fix this? I need to store multiple languages as well and this character set works for all languages but not for the special characters like emoticons and emojis.
这是我的创建表语句:
CREATE TABLE `twitter_status_data` (
`unique_status_id` bigint(20) NOT NULL AUTO_INCREMENT,
`metadata_result_type` text CHARACTER SET utf8,
`created_at` text CHARACTER SET utf8 NOT NULL COMMENT 'UTC time when this Tweet was created.',
`id` bigint(20) unsigned NOT NULL COMMENT 'Unique tweet identifier',
`id_str` text CHARACTER SET utf8 NOT NULL,
`tweet_text` text COMMENT 'Actual UTF-8 text',
`user_id_str` text CHARACTER SET utf8,
`user_name` text COMMENT 'User''s name',
`user_screen_name` text COMMENT 'Twitter handle',
`coordinates` text CHARACTER SET utf8,
PRIMARY KEY (`unique_status_id`),
KEY `user_id_index` (`user_id`),
FULLTEXT KEY `tweet_text_index` (`tweet_text`)
) ENGINE=InnoDB AUTO_INCREMENT=82451 DEFAULT CHARSET=utf8mb4;
推荐答案
我终于找到了问题所在.我不得不在 mysql 配置 my.ini 中更改一些设置这篇文章很有帮助http://mathiasbynens.be/notes/mysql-utf8mb4#character-sets
I was finally able to figure out the issue. I had to change some settings in mysql configuration my.ini This article helped a lot http://mathiasbynens.be/notes/mysql-utf8mb4#character-sets
首先我将 my.ini 中的字符集更改为 utf8mb4接下来我在 mysql 客户端运行以下命令
First i changed the character set in my.ini to utf8mb4 Next i ran the following commands in mysql client
SET NAMES utf8mb4;
ALTER DATABASE dreams_twitter CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
使用以下命令检查是否进行了更改
Use the following command to check that the changes are made
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';