A tip: Avoiding implicit charset matching errors
Posted by: Mike Jorgenstam
Date: April 03, 2010 06:50PM

A tip for beginners!

When designing a database with quite a large amount of columns, it may sometimes be difficult keeping track of the character set and collation definitions you specify for fields which are similar or the same in different tables.

Especially tricky is this when your database reaches near 1,000 columns in all your tables to keep track of this.

The problem you face is that calling a simple join on two tables, should you have defined a key or referenced column using only a slightly different charset/collation you will not receive a result but rather an error telling you, you cannot match for example utf8_general_ci with utf8_unicode_ci without specifying a CONVERT or CAST in the join ON clause.

To gain a bit overview over the content and definition of your database, you can take a quick glance on the charset and collation information about your database by going into the INFORMATION_SCHEMA database.

The following code has helped me track down occasional mismatched definitions:


SET @myDatabase = 'WhatIsYourDatabaseNamed';

SELECT `TABLE_NAME`, `COLUMN_NAME`,
 `CHARACTER_SET_NAME`, `COLLATION_NAME`, `COLUMN_TYPE`,
count(CONCAT(`COLUMN_NAME`,`CHARACTER_SET_NAME`,`COLLATION_NAME`)) as `cnt`
 FROM `COLUMNS`
 WHERE `TABLE_SCHEMA` = @myDatabase
 GROUP BY CONCAT(`COLUMN_NAME`,`CHARACTER_SET_NAME`,`COLLATION_NAME`)
 ORDER BY `COLUMN_NAME`;

I hope it becomes helpful to others as well.

Options: ReplyQuote


Subject
Views
Written By
Posted
A tip: Avoiding implicit charset matching errors
3124
April 03, 2010 06:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.