Pages

Tuesday, January 5, 2010

MySQL: migrating to UTF-8

Queries to generate the proper SQL statements for changing each table's field from 'CHAR' to 'BINARY' and then back to 'CHAR' (UTF8) field type:

USE information_schema;
SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ',
    REPLACE(column_type, 'char', 'binary'), ';') 
FROM columns 
WHERE table_schema = [DBNAME] 
    AND data_type LIKE '%char%';

SELECT CONCAT(
    'ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', 
    REPLACE(column_type, 'text', 'blob'), ';') 
FROM columns 
WHERE table_schema = [DBNAME]
    AND data_type LIKE '%text%';

SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', 
    REPLACE(column_type, 'binary', 'char'), ' CHARACTER SET utf8;') 
FROM columns 
WHERE table_schema = [DBNAME] 
    AND data_type LIKE '%binary%';

SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', 
    REPLACE(column_type, 'blob', 'text'), ' CHARACTER SET utf8;') 
FROM columns 
WHERE table_schema = [DBNAME] 
    AND data_type LIKE '%blob%';
 

No comments:

Post a Comment