change charset for mysql

Posted by gjergj.sheldija on September 18, 2013

a quick stored procedure to get rid of the infamous : Error Code: 1267. Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation ‘=’

first of all i had to find those tables and databases, and to do so i used the following sql code

SELECT 
	table_schema, 
	TABLE_NAME, 
	column_name, 
	character_set_name, 
	collation_name
FROM 
	information_schema.COLUMNS
WHERE 
	collation_name = ‘latin1_swedish_ci’
	AND 
	table_schema = ‘siis_qsut’
ORDER BY 
	table_schema, 
	TABLE_NAME,
	ordinal_position 

reaplce your_db_name with the name od the db you want to change the charset.

DELIMITER $$
 CREATE PROCEDURE change_charset_and_collation_for_tables()
     BEGIN
        DECLARE TABLE_NAME VARCHAR(255);
        DECLARE end_of_tables INT DEFAULT ;
        DECLARE num_tables INT DEFAULT ;
        DECLARE cur CURSOR FOR
            SELECT 
				t.TABLE_NAME
            FROM 
				information_schema.TABLES t
            WHERE 
				t.table_schema = ‘your_db_name’ 
				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 num_tables = num_tables + 1;
 
            SET @s = CONCAT(‘ALTER TABLE ’ , TABLE_NAME , ’ CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci’);
 
            PREPARE stmt FROM @s;
            EXECUTE stmt;
        END LOOP;

        CLOSE cur;
    END $$