Ransford Okpoti's Blog

4 October, 2011

How To Solve MySQL Character Sets and Collations Mixing Problems

Filed under: MySQL — Tags: , — ranskills @ 11:20 am

Character sets and collations enable MySQL to perform comparisons on strings, so if have two or more columns with different character sets and/or collations are used in a way that performs some comparison of some sort on them (either using =, >, <, , a JOIN ON, etc), MySQL will be a little confused and display the error message:

[Err] 1267 – Illegal mix of collations (latin1_danish_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation ‘=’

Unfortunately, there is no single command at the database level that could convert the character sets and collations of all tables within a database. In fact, the only command which appears to get the job done only changes the default values of the character set and/or collation of a database, and does not affect existing tables, but only has effect on new tables to be created.

ALTER DATABASE {database_name} CHARACTER SET {character_set} COLLATE {collation};

Likely for us, there is just a command/syntax for changing these values at the table level, syntax shown below.

ALTER TABLE {table_name} CONVERT TO CHARACTER SET {character_set} COLLATE {collation};

Example:

ALTER TABLE employees CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

This problem can be resolved by ensuring that all the tables and their corresponding columns have the same character set and collation. If multiple databases are employed, then they should all have the same character set and collation.
Using the above commands would be a very boring and painful process if you have, say, anything above 20 tables in your database. You will realize that is no fun at all, so we will develop a stored procedure that will automatically iterate through all the tables in the database and sets the character set and collations to our desired values.

The codes for the stored procedure is show below, I presume you can create this in MySQL (If not, jump to the screencast below to see it).

DROP PROCEDURE IF EXISTS procChangeCharSetandCollationForAllTables $$

CREATE PROCEDURE procChangeCharSetandCollationForAllTables()

	BEGIN

		DECLARE table_name VARCHAR(255);
		DECLARE end_of_tables INT DEFAULT 0;
		DECLARE num_tables INT DEFAULT 0;

		DECLARE cur CURSOR FOR 
			SELECT t.table_name 
			FROM information_schema.tables t 
			WHERE t.table_schema = DATABASE() 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 $$

Once you have the above stored procedure created in the database, just run it as shown below to have all the tables use the same character set and collation which in this case happens to be
CHARACTER SET = latin1
COLLATE = latin1_general_ci

CALL procChangeCharSetandCollationForAllTables();

If you prefer to use a different character set and collation, say utf8 and utf8_general_ci respectively, just change the codes in line 23 to

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

[SCREENCAST COMING SOON]

Advertisements

6 Comments »

  1. http://www.eguide.com.sg/companies/soubix Thanks for that awesome posting. It saved MUCH time 🙂

    Comment by singapore seo — 10 October, 2011 @ 7:54 am

    • It is nice to know it helped someone out there, thanks.

      Comment by ranskills — 13 October, 2011 @ 8:09 am

  2. Thanks for your post Ransford.
    I encountered a similar charset problem in MySQL when working on a bilingual website.

    My approach was using a similar script in PHP where I get all the tables excluding the information schema table
    and altering the charset. The script below demonstrate what I did;

    $db = mysql_connect('localhost','myuser_mydbuser','mypassword');
    
    if(!$db) echo "Cannot connect to the database - incorrect details";
    mysql_select_db('myuser_mydbname'); $result=mysql_query('show tables');
    
    while($tables = mysql_fetch_array($result)) {
        foreach ($tables as $key => $value) {
            mysql_query("ALTER TABLE $value COLLATE utf8_general_ci");
        }
    }
    echo "The collation of your database has been successfully changed!";
    

    Comment by amazingsey — 13 October, 2011 @ 8:01 am

    • It is a nice alternative for those who want to do it in PHP. By the way, I have wrapped your codes to be syntax-highlighted.

      Comment by ranskills — 13 October, 2011 @ 8:16 am

  3. Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your
    point. You obviously know what youre talking about, why
    throw away your intelligence on just posting videos
    to your site when you could be giving us something enlightening to read?

    Comment by Coworking space — 25 July, 2013 @ 4:08 am

    • I wanted this blog to be a unique one where each blog post will have a video accompanying it for those who may not have the time to read and for those who may be searching for articles on video sharing sites like YouTube, etc. But the videos are not coming because I haven’t had the time to work on those.
      Anyway, your point is well taken.
      Thanks.

      Comment by ranskills — 11 August, 2013 @ 9:15 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: