[RESOLVED] DB Collation Type Help

Rodney_H_

Hi, all:

I have been googling and searching the forums, but have not been able to find a good explanation of what MySQL Collation Types are, and when to use a specific one over the others.

There are a ton of them, and my tables default to something like SWEDISH case InSensitive.... but I want to know:

Is that what I SHOULD use, or when should I use some of the other choices...???

Thanks if you can point me in the right direction.

Sxooter

You pick the collation based on what your needs are. Most language type collations allow for proper ordering in accordance with the rules of that language. for instance an n and an ena (that n with a curly thing on top, I don't have multiple keyboard maps setup on this laptop, so you'll just have to imagine it) should sort as the same character in spanish, and should probably allow matching too, in case you need to search and the user enters Pena and you really want it with the curly n character in it.

however, native language collation costs in terms of CPU and system overhead, so if you're not matching / sorting on such things, then straight ascii collation might be preferred.

Rodney_H_

Thanks, Sxooter, for the info.

Why does PhpMyAdmin default to: "latin1_swedish_ci"?

And is "UTF-8_bin" a viable option without too much overhead? Or is it silly to use this?

Can you suggest some reading where I may learn more about this? (I don't want to burden you with all my questions, LOL!)

thanks again...

Sxooter

Rodney H. wrote:
Thanks, Sxooter, for the info.

Why does PhpMyAdmin default to: "latin1_swedish_ci"?

And is "UTF-8_bin" a viable option without too much overhead? Or is it silly to use this?

Can you suggest some reading where I may learn more about this? (I don't want to burden you with all my questions, LOL!)

thanks again...

The most likely reason phpmyadmin is picking that locale is that the machine it is running on has it a it default locale

UTF8 is not actually a locale with collation per se, it's an encoding methodology. I.e. the character set / locale of latin1_swedish_ci could be stored with an encoding of UTF8

ASCII locale with UTF8 encoding allows basically any character that is properly UTF8 encoded to be inserted into the database.

Not sure where to go to learn about locale. Everything I've learned I've learned from reading the PostgreSQL mailing lists.

Rodney_H_

Thanks once again, Sxooter.

Much obliged.

PS: When you have finished coding the apocolypse, give me a day or two warning before launch. I would like to look up some old friends, run up my credit cards without a care, and eat that pint of Ben and Jerry's in the ice box, etc... , etc..., etc...

Sxooter

And I apologize, I was a bit incorrect in that. Here's the real definitions:

Encoding: Method for storing references to character sets. A binary format. ASCII is an encoding, as is UTF-8 and UTF-16
Character sets: The actual characters stored in a character set. like the letter o with an umlaut over it, things like that. The define the character and it's canonical position in the set.
Locales; These are the rules for how to treat individual characters in a character set. i.e. how do you sort and compare letters in a given locale. A simple example would be something like:

Adams
deSilva
Gilbert

as last names. In ASCII locale (i.e. the C locale) you'd sort those names in this order:

deSilva
Adams
Gilbert

(at least I think lower case comes first... If not, put it at the end, but you get the idea) in the C locale characters are sorted in the same order that they occur in their character sets.

In the English locale, you'd sort them the way I had them to start with. Also, things like spaces may not count, so that "de Silva" and "deSilva" would sort the same.

I know there are some web resources out there, but I've not had to use them in a while.

Oh, and the apocalypse has been put on indefinite hold until the current run of Dr. Who finishes. So, keep track of it. No more Dr., no more world...

Rodney_H_

Shut up!

You are a Doctor WHO fan, too?

AH, yes, the good old days. I remember reading something like the SCI-FI channel was bringing him back. Thank God, I mean your father.

Is it on now? Don't answer that. I have to finish a couple of projects before I can enjoy them, but that definitely gives me something to look forward to.

I spoke to Tom Baker on the phone the other day. I was so excited, but it turned out not to be THAT Tom Baker...

Alas, I have gone off topic again. shame on me</off topic>

Thanks for the info ON and Off topic. Have a great weekend...

Sxooter

It was a new series started on the BBC a year ago, and it's just now showing in America. Of course, those wise in the ways of mysticism (i.e. bittorrent) aren't affected by such arbitrary boundaries...

The new series isn't half bad. It's kind of a weird fusion of the older, cheesier version, and modern CGI effects. I like it. If you get a chance to catch it on cable, do so. Some episodes are much better than others. I really liked the one about the future earth exploding when the sun goes supernova.

Talk about your apocalypses...