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