How to convert your latin1 mysql table to utf8

Convert the existing columns content if there are unicode characters saved in non utf8 column:

UPDATE `databasename`.`prescription_template_billing_item` SET description = @txt WHERE char_length(description) =  LENGTH(@txt := CONVERT(BINARY CONVERT(description USING latin1) USING utf8));

Have MYSQL loop through . all of your tables and columns finding all the necessary columns which have types needing to be converted:

SELECT CONCAT('UPDATE `', TABLE_SCHEMA, '`.`', TABLE_NAME,'` SET `', COLUMN_NAME, '` = @txt WHERE char_length(`', COLUMN_NAME, '`) =  LENGTH(@txt := CONVERT(BINARY CONVERT(`', COLUMN_NAME, '` USING latin1) USING utf8)); ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME,'` MODIFY `', COLUMN_NAME, '` ', COLUMN_TYPE, ' CHARACTER SET utf8;') AS mysql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN('varchar', 'mediumtext', 'text')
AND TABLE_SCHEMA = 'databasename';

You will get queries like these which can be ran:

UPDATE `databasename`.`addcolleague_email_history` SET `email_address` = @txt WHERE char_length(`email_address`) =  LENGTH(@txt := CONVERT(BINARY CONVERT(`email_address` USING latin1) USING utf8)); ALTER TABLE `databasename`.`addcolleague_email_history` MODIFY `email_address` varchar(255) CHARACTER SET utf8;
UPDATE `databasename`.`addcolleague_email_history` SET `created_at` = @txt WHERE char_length(`created_at`) =  LENGTH(@txt := CONVERT(BINARY CONVERT(`created_at` USING latin1) USING utf8)); ALTER TABLE `databasename`.`addcolleague_email_history` MODIFY `created_at` varchar(255) CHARACTER SET utf8;
UPDATE `databasename`.`address` SET `line_1` = @txt WHERE char_length(`line_1`) =  LENGTH(@txt := CONVERT(BINARY CONVERT(`line_1` USING latin1) USING utf8)); ALTER TABLE `databasename`.`address` MODIFY `line_1` varchar(255) CHARACTER SET utf8;

Then we update the each tables overarching settings. We run this to get the queries for the tables:

SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;") AS mysqlyo
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='databasename'
AND TABLE_TYPE='BASE TABLE';

That will give us something like this to run (I added the SET foreign_key_checks around the ALTER TABLE queries:

SET foreign_key_checks = 0;
ALTER TABLE `sandbox-08-local`.`addcolleague_email_history` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE `sandbox-08-local`.`address` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE `sandbox-08-local`.`administrator` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE `sandbox-08-local`.`alert_configuration` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
SET foreign_key_checks = 1;

Now we can set the encoding and collation default for the database as a whole:

ALTER DATABASE sandbox_08_easyrxortho_com CHARACTER SET utf8 COLLATE utf8_bin;

Leave a Reply

Your email address will not be published. Required fields are marked *