Difference between utf8_general_ci and utf8_bin

deezerd

Hi there,

does anyone know the difference between these two types of mysql table collations:

utf8_general_ci and utf8_bin

Is one better than the other and if yes, can you explain why?

thanks

NogDog

utf8_bin: compare strings by the binary value of each character in the string

utf8_general_ci: compare strings using general language rules and using case-insensitive comparisons

utf8_general_cs: compare strings using general language rules and using case-sensitive comparisons

For example, the following will evaluate at true with either of the UTF8_general collations, but not with the utf8_bin collation:

Ä = A
Ö = O
Ü = U

With the utf8_general_ci collation, they would also return true even if not the same case.

None of these is inherently "better"; they simply have different functionalities. You need to choose which one best suits your particular needs.

deezerd

thanks for your very clear answer 🙂

which makes me think to another question:

what are the consequences if utf8_bin collation doesn't evaluate at true Ä = A

I mean, will it change the way my php script work if I use some sort or str_replace functions?
For example, does it mean these two tests below will return the same result?

$string = 'Ä'; // result that would come from my table

str_replace("Ä","foo",$string); 

str_replace("A","foo",$string);

thanks

NogDog

It only changes how MySQL will process queries; it has no effect on what PHP does. So if the WHERE clause of a query says "WHERE first_name = 'Bob'", the different collations would return matches for first_name values of:

'Bob' : utf8_bin, utf8_general_ci and utf8_general_cs
'Böb' : utf8_general_ci and utf8_general_cs
'BÖB' : utf8_general_ci

Again, this is tranparent to PHP. It only affect how MySQL performs string comparisons while processing queries.

deezerd

May I ask your opinion on something? I'm currently developping a blog application in which users could perform a search in blogs posts or post titles. In this case, it would then make sense to use utf8_general_ci, don't you think?
I'm looking for kind of a best practice for this thing, so you opinion is highly welcome 🙂

NogDog

deezerd wrote:
May I ask your opinion on something? I'm currently developping a blog application in which users could perform a search in blogs posts or post titles. In this case, it would then make sense to use utf8_general_ci, don't you think?
I'm looking for kind of a best practice for this thing, so you opinion is highly welcome 🙂

Sounds good to me.

oVerboosT

I'd just like to say thanks as well to NogDog for that explaination too. 🙂
Cheers!

deezerd

Many thanks, it's all clear for me now