MySQL Change Character Set
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.
- http://codex.wordpress.org/Converting_Database_Character_Sets
- http://www.mysqlperformanceblog.com/2009/03/17/converting-character-sets/
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 ;
