MySQL: Change collation for all tables in your database

Did you ever had a problem that the special characters are not correctly displayed on your front end?
Like you see M?nchen instead of München? This error can happen if your database or table or column has a different character-set as „utf8“. If you face with this kind of encoding problem in TYPO3 or in an other system the solution is kind of easy. You need to check the character set and collation of the column, and if it is „latin1“ for instance everything what you need to do is just let run an SQL command like this:

ALTER TABLE t1 MODIFY
col1 VARCHAR(5)
CHARACTER SET utf8
COLLATE utf8_general_ci;


MySQL Manual for Column Character Set and Collation

Then usually you ask yourself: „Ok, this one is done, but… could it happen in the future again? Do I have other columns with problems?“
This is always a good idea to take a look on your database and check the other tables if they have this issue too:

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND COLLATION_NAME NOT LIKE 'utf8_%';


This script will list every and each table and their columns with a different collation as utf8 in the current database.
Of course you can change these columns again with the ALTER TABLE statement above. However if you have multiple columns inside a table you can solve this easier.
In this case you need the following statement:

ALTER TABLE t1 CONVERT TO CHARACTER SET utf8;

This will change the character-set for each column in your table and the collation to the default collation of this character set. It is probably utf8_general_ci.

(Note: It is not written in stone which one is the default collation. You can read this small article in the MySQL documentation, it clarifies the different option to set or proof this settings : https://dev.mysql.com/doc/refman/5.6/en/charset-applications.html )

It is already a faster way, but it will be nice to check the whole database end solve the problems in one step. I have searched a lot to find a proper solution, which is reusable for different project, and at the end I decided to write my own stored procedure with some dynamic prepared statements to solve the task. A short explanation is added in the form of comments.

DELIMITER $$

DROP PROCEDURE IF EXISTS changeCollation$$

— character_set parameter could be ‚utf8‘
— or ‚latin1‘ or any other valid character set
CREATE PROCEDURE changeCollation(IN character_set VARCHAR(255))
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_table_name varchar(255) DEFAULT „“;
DECLARE v_message varchar(4000) DEFAULT „No records“;

— This will create a cursor that selects each table,
— where the character set is not the one
— that is defined in the parameter

DECLARE alter_cursor CURSOR FOR SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
AND COLLATION_NAME NOT LIKE CONCAT(character_set, ‚_%‘);

— This handler will set the value v_finished to 1
— if there are no more rows

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;

OPEN alter_cursor;

— Start a loop to fetch each rows from the cursor
get_table: LOOP

— Fetch the table names one by one
FETCH alter_cursor INTO v_table_name;

— If there is no more record, then we have to skip
— the commands inside the loop
IF v_finished = 1 THEN
LEAVE get_table;
END IF;

IF v_table_name != “ THEN

IF v_message = ‚No records‘ THEN
SET v_message = “;
END IF;

— This technic makes the trick, it prepares a statement
— that is based on the v_table_name parameter and it means
— that this one is different by each iteration inside the loop

SET @s = CONCAT(‚ALTER TABLE ‚,v_table_name,
‚ CONVERT TO CHARACTER SET ‚, character_set);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET v_message = CONCAT(‚The table ‚, v_table_name ,
‚ was changed to the default collation of ‚, character_set,
‚.\n‘, v_message);

SET v_table_name = “;

END IF;
— Close the loop and the cursor
END LOOP get_table;
CLOSE alter_cursor;

— Returns information about the altered tables or ‚No records‘
SELECT v_message;

END $$

DELIMITER ;

If you run this command successfully the last thing what you need to do is to call this procedure:

CALL changeCollation('utf8’);

I hope, that this script helps you to solve encoding problems not just in TYPO3 but generally with MySQL.
If you have any question or suggestion please do not hesitate to share them!