How to clean UTF-8 data for MySQL

Question

I have UTF-8 text data from Twitter (so it my be very dirty). When input into mysql (database char set is utf8) some text get garbaged. I would like a way to clean data before putting it in.

Insert ignore search_tweets set id_str = 'pass1',text = 'RT @youpon_info: Youponです！この度はキャンペーン参加ありがとうございました。たくさんの方々にキャンペーンに参加して頂きました。'  ;
Insert ignore search_tweets set id_str = 'fail',text = 'RT @youpon_info: Youponです！この度はキャンペーン参加ありがとうございました。たくさんの方々にキャンペーンに参加して頂きました。また次のキャンペーンをすぐに予定しております！もう少'  ;
Insert ignore search_tweets set id_str = 'pass2',text = 'また次のキャンペーンをすぐに予定しております！もう少'  ;

fail.text = pass1.text + pass2.text and they both go in and come out of mysql fine. fail comes out as

RT @youpon_info: Youponã§ãï¼ãã®åº¦ã¯ãã£ã³ãã¼ã³åå ãããã¨ããããã¾ãããããããã®æ¹ã

I have done this with direct MySQL calls, although originally it was all done in Ruby datamapper and direct calls.

I would like to know how to clean the data so it goes in/comes out of MySQL the same. If possible a ruby solution would be nice but just knowing how to clean it would great.

Jan Hudec · Accepted Answer · 2011-08-01 12:20:28Z

3

It looks like the data being truncated. Do you have enough room in the text column for the data you are inserting?

I suspect varchar(n) will only accept n bytes, not n characters and the Japanese characters take at 3 bytes each. Mysql is known for silently truncating data that don't fit in and if it happens to be truncated in the middle of UTF-8 character, the reader may decide it's not correct UTF-8 and interpret it as ISO8859-1, which would result in what you are seeing.

Note, that in UTF-8, all characters of living languages fit in 3 bytes (with Chinese, Japanese and Korean all being in those that always need all 3) and the extended symbols and historical scripts need 4 bytes. So to stay on the safe side, the database must be willing to accept 4 times as many bytes as there are characters allowed.

edited Aug 1, 2011 at 12:20

answered Aug 1, 2011 at 12:14

Jan Hudec

76.8k13 gold badges138 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Grant M Over a year ago

I had varchar(255) so upped it to 511 enough for 3 bytes no luck but at 1023 - 4 bytes per possible char (tweets are max 140 chars) it worked thanks.

Jan Hudec Over a year ago

@Grant M: I am sure I've seen some recipes on the 'net how to get MySQL to fail instead of silently mangling the data. Might be worth looking for and including one in your projects to avoid this kind of surprises in future (there are many cases where default behaviour is to truncate/clip/convert the data).

samyok nepal Over a year ago

Note that tweets are now up to 280 chars.

Collectives™ on Stack Overflow

How to clean UTF-8 data for MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related