不正确的字符串值:'\xF0\x9F\ xB6\xF0\x9F…' MySQL。

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

我试图在我的MYSQL表中存储一条tweet。微博是:

quiero que me escuches, no te burles no te rias, anoche tuve un sueño que te fuiste de mi vida ????????

爱你是我escuches,没有te节te rias,anoche tuve联合国sueño,te fuiste de mi维达????????

The final two characters are both 'MULTIPLE MUSICAL NOTES' (U+1F3B6), for which the UTF-8 encoding is 0xf09f8eb6.

最后两个字符都是“多重音符”(U+1F3B6)，而UTF-8编码是0xf09f8eb6。

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:

我的表中的tweet_text字段是用utf8mb4编码的。但当我试图将tweet存储在该列时，我得到以下错误消息:

Incorrect string value: '\xF0\x9F\x8E\xB6\xF0\x9F...' for column 'tweet_text' at row 1.

不正确的字符串值:“\ xF0 \ x9F \ x8E \ xB6 \ xF0 \ x9F……“在第1行的列‘tweet_text’中”。

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.

什么错了吗?我怎么解决这个问题?我需要存储多种语言，这个字符集适用于所有语言，但不适用于表情符号和表情符号等特殊字符。

This is my create table statement:

这是我的create table语句:

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;

2 个解决方案

#1

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

我终于能够解决这个问题了。我必须修改mysql配置中的一些设置。这篇文章帮助了很多http://mathiasbynens.be/notes/mysql-utf8mb4#字符集。

First i changed the character set in my.ini to utf8mb4 Next i ran the following commands in mysql client

首先，我改变了我的字符集。接下来，我在mysql客户端运行以下命令。

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%';

#2

I had hit the same problem and learnt the following-

我遇到了同样的问题，并学会了下面这些。

Even though database has a default character set of utf-8, it's possible for database columns to have a different character set in MySQL. Modified dB and the problematic column to UTF-8:

尽管数据库具有默认的utf-8字符集，但数据库列可以在MySQL中设置不同的字符集。修改dB和问题列到UTF-8:

mysql> ALTER DATABASE MyDB CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'

mysql> ALTER TABLE database.table MODIFY COLUMN column_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

Now creating new tables with:

现在创建新的表:

> CREATE TABLE My_Table_Name (
    twitter_id_str VARCHAR(255) NOT NULL UNIQUE,
    twitter_screen_name VARCHAR(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    .....
  ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

#1