… who are still using latin1 have begun to migrate their databases to utf8. Googling for “mysql convert charset to utf8” results in a… set and collation. Approach #1: ALTER TABLE `t1` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; Take the following table as an… affected (0.02 sec) mysql> ALTER TABLE `t1` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 0 rows affected (0…
Comment: Fixing column encoding mess in MySQL
September 1, 2008 By 32 Comments
… “utf8 -> latin1″ conversion. In order to fix the encoding I need to treat this “latin1″ data as binary, then convert it to utf8. And so here is the statement I need: UPDATE table SET col = convert(CONVERT(CONVERT(col USING latin1) USING binary) using utf8);
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
…` join `test`.`t2` where (`test`.`t1`.`char_id` = convert(`test`.`t2`.`char_id` using utf8)) 1 row in set (0.00 sec) Notice the differences in key_len and the explicit call to CONVERT() in….33 seconds to execute with t1 as utf8 and t2 as latin1. Converting both tables to utf8 resulted in an average execution time…
Post: Fixing column encoding mess in MySQL
December 18, 2007 By 32 Comments
…, and then do charset conversion: UPDATE table SET column=CONVERT(CONVERT(CONVERT(column USING binary) USING utf8) USING cp1251) WHERE id=123; This can be further simplified… charset automatically, and the 3rd explicit CONVERT can be omitted. UPDATE table SET column=CONVERT(CONVERT(column USING binary) USING utf8) WHERE id=123; The same…
Comment: Fixing column encoding mess in MySQL
September 1, 2008 By 32 Comments
… NAMES utf8“, and MySQL sees all data as latin1. Now, the script can use “set names latin1″ to get back utf8 data. However, “CONVERT(CONVERT(offending_col USING binary) USING utf8)” doesn’t work – the…
Comment: Fixing column encoding mess in MySQL
March 14, 2012 By 32 Comments
…. MyISAM tables with latin1 encoding. Tried: ALTER TABLE CONVERT TO CHARACTER SET utf8; Does nothing except changes table/column encodings, but no… my table is utf8, I try the nr2 way mentioned in this blog: UPDATE table SET column=CONVERT(CONVERT(column USING binary) USING utf8) WHERE id…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… Cache. So what if we convert i and j columns to varchar while sticking to utf8 character set which we had as… more. The next test I decided to do is to convert Innodb table to latin1 character set. I was expected this… based join for Innodb. Finally I decided to check if using longer strings slows down things significantly and so I replaced…
Post: Percona Server 5.1.58-12.9
August 17, 2011 By 2 Comments
… in MySQL 5.1.24. If the following collations were used: utf8_general_ci ucs2_general_ci and any of the indexes… by converting the affected tables or columns to the collations introduced: utf8_general_ci to utf8_general50_ci, and ucs2_general_ci to utf8_general50_ci. Blueprint:utf8-general50-ci-5.1…
Post: Percona Server 5.5.14-20.5
August 17, 2011 By 4 Comments
… in MySQL 5.1.24. If the following collations were used: utf8_general_ci ucs2_general_ci and any of the indexes… to Percona Server by converting the affected tables or columns to the collations introduced: utf8_general_ci to utf8_general50_ci, and ucs2_general_ci to ucs2_general50_ci. Blueprint:utf8-general50-ci-5…
Comment: MySQL Upgrade Webinar Questions Followup
June 28, 2012 By 7 Comments
… to disk earlier. Now I am not suggesting NOT to use utf8 when it is needed. Of course when multiple languages is… a way to go. At the same time simply converting database to utf8 without application support on upgrade is a waste and… if application is storing latin1 and database interprets it as utf8 bad things can happen. The question is whenever moving to…
