不正确的字符串值:'\ xF0 \ x9F \ x8E \ xB6 \ xF0 \ x9F ...'MySQL [英] Incorrect string value: '\xF0\x9F\x8E\xB6\xF0\x9F...' MySQL

问题描述

我正在尝试在我的MYSQL表中存储一条推文. tweet 是:

I am trying to store a tweet in my MYSQL table. The tweet is:

quiero que me quecu，没有te burles no te rias，anoche tuve unsueñoque 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 🎶🎶

最后两个字符均为多种音符"(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:

第1行的'tweet_text'列的字符串值不正确:'\ xF0 \ x9F \ x8E \ xB6 \ xF0 \ x9F ...'

Incorrect string value: '\xF0\x9F\x8E\xB6\xF0\x9F...' 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%';

这篇关于不正确的字符串值:'\ xF0 \ x9F \ x8E \ xB6 \ xF0 \ x9F ...'MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！