… have begun to migrate their databases to utf8. Googling for “mysql convert charset to utf8” results in a plethora of sites, each… 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
… issued. This means I can get the correct (utf8) data when MySQL performs a “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
… `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
… then do charset conversion: UPDATE table SET column=CONVERT(CONVERT(CONVERT(column USING binary) USING utf8) USING cp1251) WHERE id=123; This can be… automatically, and the 3rd explicit CONVERT can be omitted. UPDATE table SET column=CONVERT(CONVERT(column USING binary) USING utf8) WHERE id=123; The…
Comment: Fixing column encoding mess in MySQL
September 1, 2008 By 32 Comments
… issue: The table is created with charset utf8 – correct. A data is exported with charset utf8 – also correct. But thing goes wrong… 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 same wrong…
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… table is utf8, I try the nr2 way mentioned in this blog: UPDATE table SET column=CONVERT(CONVERT(column USING binary) USING utf8) WHERE…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
…), foreign key (fkto3) references dos3(fkfrom1) ) engine=innodb character set=utf8; create table dos3 ( fkfrom1 char(32) not null, fkfrom2 char… UTF8 in this database at all, and that we want to achieve the best performance possible, so we’re going to convert… way we were expecting. Whatever the reason, we decide to convert these columns to be ASCII. We might assume that since…
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… any simple encoding) is significantly faster for joins compared to UTF8 These tests were preformed for in memory tables, for IO…
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
…`.`sbtest`.`id` > 6) and (`test`.`sbtest`.`c` = _utf8‘a’) and (`test`.`sbtest`.`pad` = _utf8‘a’)) 1 row in set (0.00 sec… column list, each column was fully quantified plus MySQL optimizer converted c=”a” and pad=c; to c=”a” and pad… the message. We can see query is using , it is converted to using . And there is some in being done. Unfortnuately…
Post: Percona Server 5.1.58-12.9
August 17, 2011 By 2 Comments
… / #61341 in MySQL (Yasufumi Kinoshita). Compatibility Collations Two new collations, utf8_general50_ci and ucs2_general50_ci, have been to improve… 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…
