Convert MySQL tables to use UTF8 character set and collation

I moved a Gerrit installation to use MySQL. The tables turned out to be using latin1 encoding.

Here’s a vile concoction, composed of many StackOverflow answers and swamp creatures:

-- update database and each table to use utf8 according to http://stackoverflow.com/a/6115705/39974.
-- to apply to each table, used http://stackoverflow.com/a/18294520/39974
-- use utf8_unicode_ci due to: http://stackoverflow.com/a/766996/39974

DELIMITER $$

DROP PROCEDURE IF EXISTS utfAllTables $$

CREATE PROCEDURE utfAllTables()

BEGIN
        DECLARE table_name VARCHAR(256);
        DECLARE end_of_tables INT DEFAULT 0;

        DECLARE cur CURSOR FOR
            SELECT t.table_name
            FROM information_schema.tables t
            WHERE t.table_schema = DATABASE() AND t.table_type='BASE TABLE';
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_tables = 1;

        OPEN cur;

        tables_loop: LOOP
            FETCH cur INTO table_name;

            IF end_of_tables = 1 THEN
                LEAVE tables_loop;
            END IF;

            SET @s = CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;');
            PREPARE stmt FROM @s;
            EXECUTE stmt;

        END LOOP;

        CLOSE cur;
    END $$

DELIMITER ;

Let’s use this stored procedure, and then drop it.

ALTER DATABASE reviewdb CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE reviewdb;
CALL utfAllTables;
DROP PROCEDURE utfAllTables;

Sadly, it looks like ALTER DATABASE is not PREPAREable, nor does it accept a variable or a function
call as its database name. Therefore, I could not pass DATABASE() here, nor could I move the ALTER DATABASE
statement inside the stored procedure.


via blog.vucica.net

Leave a Reply

Your email address will not be published.

 

What is 15 + 3 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)

This site uses Akismet to reduce spam. Learn how your comment data is processed.