Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
-- MySQL procedure for converting all MyISAM tables in a database to InnoDB.
--
-- Example usage:
--
-- $ mysql -N example_database < convert_innodb.sql
--
-- @author Jonathan Robson <jnrbsn@gmail.com>
-- @copyright 2011 Jonathan Robson
-- @license http://gist.github.com/802399 MIT License
-- @link http://gist.github.com/1144336
DROP PROCEDURE IF EXISTS convert_innodb;
DELIMITER //
CREATE PROCEDURE convert_innodb ()
BEGIN
DECLARE var_table CHAR(255);
DECLARE cur CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_schema = DATABASE() AND engine = 'MyISAM';
SELECT 'Getting list of tables...';
OPEN cur;
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
LOOP
FETCH cur INTO var_table;
SELECT CONCAT(' Converting table ', var_table);
SET @var_sql := CONCAT('ALTER TABLE `', var_table, '` ENGINE InnoDB');
PREPARE stmt FROM @var_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
END;
CLOSE cur;
SELECT 'Completed successfully!';
END;
//
DELIMITER ;
CALL convert_innodb();
DROP PROCEDURE convert_innodb;
-- MySQL procedure for converting all tables in a database to utf8_general_ci.
--
-- Example usage:
--
-- $ mysql -N example_database < convert_utf8.sql
--
-- @author Jonathan Robson <jnrbsn@gmail.com>
-- @copyright 2011 Jonathan Robson
-- @license http://gist.github.com/802399 MIT License
-- @link http://gist.github.com/1144336
DROP PROCEDURE IF EXISTS convert_utf8;
DELIMITER //
CREATE PROCEDURE convert_utf8 ()
BEGIN
DECLARE var_table CHAR(255);
DECLARE cur CURSOR FOR
SELECT DISTINCT table_name FROM information_schema.columns
WHERE table_schema = DATABASE()
AND collation_name IS NOT NULL
AND collation_name != 'utf8_general_ci';
SELECT 'Altering database...';
ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci;
SELECT 'Temporarily disabling foreign key checks...';
SET FOREIGN_KEY_CHECKS = 0;
SELECT 'Getting list of tables...';
OPEN cur;
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
LOOP
FETCH cur INTO var_table;
SELECT CONCAT(' Converting table ', var_table);
SET @var_sql := CONCAT(
'ALTER TABLE `', var_table,
'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci'
);
PREPARE stmt FROM @var_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
END;
CLOSE cur;
SELECT 'Re-enabling foreign key checks...';
SET FOREIGN_KEY_CHECKS = 1;
SELECT 'Completed successfully!';
END;
//
DELIMITER ;
CALL convert_utf8();
DROP PROCEDURE convert_utf8;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment