Collation Default In Phpmyadmin

[CarpeDiem]

When I was setting up my first SQL database test table with phpMyAdmin on my website, I was confronted with making choices I don't remember encountering in the tutorials. One choice in particular about collation stumped me.The default collation in phpMyAdmin for tables is automatically latin1_swedish_ci, and I accepted it since I didn't have a clue about what might be a better choice (and I looked collation up at dev.mysql.com, but it didn't help me understand what I should choose). I had hoped I'd see something like English as an option, but no such luck (or I missed it if it was there).QUESTIONS:

1. Is latin1_swedish_ci going to meet my needs when I'm mainly dealing with speakers of English?
2. Should I change the phpMyAdmin default latin1_swedish_ci collation of my tables to something else? If yes, any recommendations?

[justsomeguy]

I'm not sure what the specific encodings differ in, but I usually just use the utf8_unicode_ci encoding for all tables, it's usually the last one in the list. UTF is eventually going to be pretty standard worldwide, if it's not already.

[CarpeDiem]

JustSomeGuy,I did some research on the web before I got your response, and I found agreement with your recommendation. Before I got your response too, I tried changing all my fields to utf8_unicode_ci, but even after the change, the table is still listed in phpMyAdmin as latin1_swedish_ci and it seems unchangeable (alas!).Since I figured that a disagreement between what phpMyAdmin said the table collation was and what I had selected for each and every applicable field might cause problems, I'm either in the position to return all the fields to the latin1_swedish.ci that was the default so it will agree with what the table is collated as, or I need to set up everything again with utf8_unicode_ci for every applicable field from the get go so that the table will then be termed utf8_unicode_ci too (I assume this is the only way I can get an agreement between the fields and the table collation).QUESTIONS:

1. Should there be agreement between what myPhpAdmin identifies as the table collation and the applicable individual field collations (or does this not really matter)?
2. Should I, for long-term concerns, redo everything as utf8_unicode_ci (redo rather than change, since changing the fields does not update the table collation, so is a redo needed)?
3. I prefer case sensitivity, but only latin7 (well, in the latin group) shows a case sensitivity, such as in latin7_general_cs. I assume this would work for English speakers. Any feedback about why utf8_unicode_ci might be better?
4. Any idea why there is no English collation (it's the number one language on the planet I understand, so why not English collation)?
5. Last, utf8_unicode_cs seems missing (the "cs" does not appear in the utf8 group) and why? That's a question I don't expect you to answer, JustSomeGuy; it's just rhetorical since I would think case sensitivity sometimes matters to certain situations, so why not utf8_unicode_cs as an option? I assume the answer is that it is just not needed (though I'd use it).

[justsomeguy]

The table collation is just the default to use for each field. If you want to change that, click on the table so that you're viewing the structure, then the Operations tab on top. The only reason UTF is better than a Latin character set is because UTF includes support for things like Chinese, Kanji, Korean, Farsi, Cyrillic, etc. Latin just gives you the basic English alphabet plus a few accented characters. There's nothing specifically called English just because it's based on Latin, so Latin is the English character set. I'm not sure about case sensitivity, but I believe that when you're doing a full-text search, for example, you can specify whether or not it's case sensitive.

it's the number one language on the planet I understand

I thought that distinction belonged to Chinese. Oddly enough, China India is the number one English-speaking country.

[CarpeDiem]

JustSomeGuy,Thanks again! With your help, I now have everything in collation agreement (the database, the table, and the applicable fields). Using the operations tab was the key to helping me change not only the table collation, but the database too. Everything's quite symmetric now!Actually, the case sensitivity I needed was more for indexing, with the primary index being most important and the other indexed fields of secondary importance, and having the data "natively" organized by a case-sensitive indexing would make organizing and searching data somewhat simpler than always repetitively needing to write in the additional criteria into the select parameters, at least for what I'm doing (plus there's some organizational things case-sensitive indexing would allow that case-insensitive indexing does not, such as A-thru-Z plus a-thru-z as case sensitive structures equals 52 possibilities, whereas as case insensitive, it's only 26). I understand that most people will not need or relate to this organizational structure, but to me, it's a useful opportunity to have twice as many distinct entities. If I can't live without case sensitivity, I guess I can go the latin7_general_cs route, especially since characters in Chinese, Kanji, Korean, Farsi, Cyrillic, etc., that are part of utf8_unicode_ci are not languages I understand and I won't be able to read them if someone were to write them into my blog (and if I can't comprehend what they've written, it will not be published on my website -- I can't take such risks I feel).As usual, you've given me lots of good things to ponder which actually affect what I do! Thanks!