Tag: character

MySQL Change Character Set

Posted on August 11th, 2009 by tom

I needed to change the character set for a MySQL database with which I'm working. It was created with latin1 and I needed it to be in utf-8. Here are some relevant links.

My need was pretty simple, convert the database without to much concern for the data itself. So my main challenge was how to loop over the tables in my database and alter the tables. I decided to use the mysql command line. Here is what I came up with... 

USE databasename;

DELIMITER //

DROP PROCEDURE IF EXISTS test//

CREATE PROCEDURE test()

    BEGIN

      DECLARE done INT DEFAULT 0;
      DECLARE a CHAR(64);
      DECLARE cur1 CURSOR FOR SHOW TABLES;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

      OPEN cur1;

      LOOP

        FETCH cur1 INTO a;

        IF NOT done THEN

          SET @s = CONCAT('ALTER TABLE ',a,' CONVERT TO CHARACTER SET utf8');

          PREPARE stmt FROM @s;

          EXECUTE stmt;

          DEALLOCATE PREPARE stmt;

        END IF;

      END LOOP;

      CLOSE cur1;

    END//

DELIMITER ;

Comments (0)       
Tags: change, character, latin1, mysql, set, utf-8