• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Strange strings that cannot be inserted into a table

I'm collecting some random tweets.  For some of the tweets, I failed to insert their body into a MySQL database.  Following is the error message I received.

Incorrect string value: '\xF0\x9F\x91\xAF\xF0\x9F...' for column 'tweetBody' at row 1

Open in new window

Here is the tweet body.

Tell Tyga look for this chick called black china sill a nigga bitch ???? In a hot flash ?? menopause hot flash

Open in new window

I believe it's because of the squares.  The collation of the database is utf8 - default collation.

2 Solutions
How are you escaping the text upon inserting?  Are you using mysql_real_escape_string() or similar?
What version of MySQL are you on?  What you are dealing with seem to be 4-byte UTF-8 characters.  Basically any version of MySQL before 5.5.3 cannot handle 4-byte utf-8 yet.  Possible solutions:

1.  Upgrade MySQL to 5.5.3
2.  Convert the fields to varbinary (http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html)
thomaszhwangAuthor Commented:

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now