FhT5SwVR

1-- Example of how to fix double encoded UTF-8 in mysql with a single mysql query.
2-- This example assumes that the data has been inserted with NAMES=latin1.
3-- 
4-- XXX: Do not rely on this to work without testing it. XXX
5-- 
6-- XXX: If this is run on a column with a UNIQUE index and the COLLATE is 
7-- case-insensitive this might fail. The reason for this is that double-encoded 
8-- upper and lower case chars are considered different but when encoded correctly 
9-- they will be considered the same.
10
11DROP TABLE IF EXISTS double_encoded_utf8;
12CREATE TABLE double_encoded_utf8(
13     `text` varchar(500) COLLATE utf8_swedish_ci
14) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
15
16-- Insert text with NAMES=latin1
17SET NAMES latin1;
18INSERT INTO double_encoded_utf8 VALUES("!\"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ");
19
20SELECT `text` AS '=== Test selected with NAMES=latin1' FROM double_encoded_utf8;
21SELECT count(*) as '=== Number of matches before encoding' FROM double_encoded_utf8 WHERE `text`="!\"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ";
22
23-- Switch to UTF8
24SET NAMES utf8;
25
26SELECT `text` AS '=== Test selected with NAMES=utf8 before converting' FROM double_encoded_utf8;
27SELECT count(*) as '=== Number of matches before encoding' FROM double_encoded_utf8 WHERE `text`="!\"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ";
28
29-- Convert
30UPDATE double_encoded_utf8  SET `text`=convert(cast(cast(`text` AS CHAR CHARACTER SET latin1) AS BINARY) USING utf8);
31
32SELECT `text` AS '=== Test selected with NAMES=utf8 after converting' FROM double_encoded_utf8;
33SELECT count(*) as '=== Number of matches after encoding' FROM double_encoded_utf8 WHERE `text`="!\"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ";