Horribly confused by mySQL

julianps

Whilst I have had a root around on Google and here I cannot get my head around character encoding, collation, encoding, tables and columns, etc.

I do sort of get it that certain non-standard letters (ie those that appear in non-English alphabets) occur in various different character sets; basically documents that seek to map characters to underlying ascii values?

And whilst mySQL will always store this ascii data and charset on an HTML page will display this ascii data correctly, any mySQL operations performed on that data require that it be meaningful to mySQL (ie the data must correspond to a known character set)?

Therefore it seems to be a "good thing" to have the same character set for both table collation, column collation and character set information in HTML.

But how does one determine from the myriad sets which to choose and how to apply it to a database?

What is the difference between table and column collation (or is that so specialised that village-idiots like me can live with the rule that they should always be the same?)?

Is encoding a configurable, or a consequence of collation choices and what is it overall relevance?

Finally, does anyone know of a Collation 101 primer (mySQL's page on collation and character-sets is a bit headache inducing..:queasy:

I know that this is touched on a thousand places but as I'm having so much trouble pulling it altogether I'd really appreciate a helping hand.

Thanks,

jiF

leatherback

Hi Jif,

My knowledge is bare-basics, and there are bound to be some errors in it. Buit I am pretty sure the gist of it is correct..

Hm.. Character encodings.. Basically.. Any character on screen has a digital representation (00110011001010). How that digital representation is shown to you on screen, depends on the character set you use. As English has other characters than say Swedish or chinese, different languages require different character sets. When first developed, the need for cross-language was less important than now, and all sort of languages had their own ideal translation tables.

To overcome this, UTF-8 was defined. It works in many, many languages and is safe to use over the internet across languages. All you need to do is make sure that your complete flow of information is in the same characterset. So.. Your pages (Use an editor that can safe as UTF-8 encoding), the forms (Set the accept-type tag) and the database itself (Look at the unicode set. I use the unicode_CI, which is case insensitive). Collations are -afaik- used when combining or comparing datasets; basically they affect the way one field is compared to another.

In short: If you have an international database, and you have the potential of having contributions in other languages than just English, be sure, and use UTF-8 for your character encoding.

NogDog

Character set: how the characters are stored.
Collation: how the character fields are sorted or compared.

All character type fields (CHAR, VARCHAR, TEXT, etc.) will use the character set and collation of their table unless you override it with values specific for that column. Generally the only time I do that is for something like when I'm using a case-insensitive collation for the table, but want a specific field to use case-sensitive comparisons, such as with a login name field, perhaps. So I might define the table as using utf8_general_ci but then specify that the login_name field be utf8_bin.