I’m the kind of DBA that prefers to keep everything simple, BUT, sometimes it’s not possible. Few days ago I’ve faced an issue where none of the collations shipped by default with MySQL would guarantee integrity of my database, and to avoid a massive re-write of application code, we have explored an option that up to the day, I didn’t know about.
Add your own collation to MySQL.

This option is described on this section of MySQL documentation . On this post I will show how to make MySQL identify volves with acute accent (fada) as a different letter:

First, let’s find out where is our character-set folder:

mysql [localhost] {msandbox} ((none)) > SHOW VARIABLES LIKE 'character_sets_dir';
+--------------------+---------------------------------------+
| Variable_name | Value |
…

Every once in a while questions like the one in MySQL Bug #60843 or Bug #19567 come up:

What collation should i use if i want case insensitive behavior but also want all accented letter to be treated as distinct to their base letters?

or shorter, as the reporter of bug #60843 put it:

I need something like utf8_bin + ci

utf8_general_ci and utf8_unicode_ci unfortunately do not provide this behavior and utf8_bin is obviously not case insensitive.

read more

I've table contains text column and its charset is latin1, and i can store Arabic text ( and non English character) in this column and retrieve it, i don't know how is it?

So how is that? and why I need utf8?

CREATE TABLE `post` ( `postid` int(10) unsigned NOT NULL AUTO_INCREMENT, `threadid` int(10) unsigned NOT NULL DEFAULT '0', `parentid` int(10) unsigned NOT NULL DEFAULT '0', `

The headline is flame-bait, don’t take it. I just wanted to point something out about character sets and collations in MySQL.

To the uninitiated, it may seem overwhelming. Everything has a character set! Everything has a collation! And they act weirdly! The server has one. The database has one (oh, and it changes magically as I USE different databases.) Every table has one, and columns too. Is that all? NO! My connection has one! Kill me now!

Relax. In truth, only one kind of thing actually has a charset/collation. That is values. And values are stored in columns. The only thing that really has a charset/collation is a column.[1]

What about all the rest of those things — connection, database, server, table? Those are just defaults, which determine what charset/collation a value gets if it isn’t overridden. So if the table’s default charset is utf8, and you add a column without saying what …

The move from PHP 5 to PHP 6 will be a painful one. But once it’s done, I hope that it will be easier to handle safe web development for a global, multi-language internet. After all these years, we still … Continue reading →

Recently I needed to quickly change charset on all tables and change each field’s chanset in each table in a specific database to latin1 and collation to latin1_swedish_ci. I googled a little and found this solution by shimon doodkin. I used it and it did work, but also it attempted to change charset at MySQL […]

Background Knowledge

The MySQL database v4.0.23 is using the default character set of “Latin1″. When the database was created I had no knowledge of character sets other wise it would have been “UTF-8″.
The web pages are using a character set of “UTF-8″.

Problem

Data being queried from a MySQL database that contains French accent characters will not render correctly in the browser even after applying PHP htmlentities().

Example code: $string = htmlentities($string , ENT_QUOTES, “UTF-8″);

Solution

The queried data from the database was inputted using the character set “ISO-8859-1″. I …

I pulled the latest 5.1BK sources today and built mysqld. I must admit, it fared pretty well against my little charset tester. 0 failures and over 120 tests done which took >5 hours.

To give a clue to the score last time I run the same tests, here are the bugs I opened!

• 
  
• load data infile into table with big5 chinese fulltext index hangs 100% cpu
  
• corruption with character set macce collate macce_bin
  
• myisam corruption with character set cp932 collate cp932_japanese_ci
  
• repeatable myisam fulltext index corruption
  
…