Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MySQL Collation

Posted on 2007-08-01
8
Medium Priority
?
2,015 Views
Last Modified: 2008-01-09
Hi! Just wondering what's the best collation to use. Prior to MySQL 5, I don't think I had this choice...

I would put my money on utf8_general_ci, but I also see ascii_general_ci in use, and the default seems to be latin1_swedish_ci ......
0
Comment
Question by:Julian Matz
  • 3
  • 3
  • 2
8 Comments
 
LVL 24

Accepted Solution

by:
mankowitz earned 960 total points
ID: 19610434
It really depends what languages you are going to use. If your text is mostly english and european languages, I would stick with the latin collation -- because it is the default and everyone else is using it. If you need kanji,chinese or another kind of pictogram language, you should use that one.
0
 
LVL 15

Expert Comment

by:ncoo
ID: 19610967
latin1_swedish or latin1_general are both good, I've not had a problem with either for Europe, the Americas(N&S) and India.

They do say if you're page is going to be of content type UTF8 you should use a UTF8 collation.

But what ever you do make sure any key/foreign fields are all of the same type, otherwilse you will be in for some real trouble. Trust me on that one!
0
 
LVL 21

Author Comment

by:Julian Matz
ID: 19611194
Thanks!

I'm using mainly English, sometimes French, German, Italian, etc. for content management systems.

My websites are all UTF8 (my Apache server forces this content-type) and the MySQL data is usually inserted through HTML input fields and PHP, and I also sometimes put HTML into the database. That wouldn't make a difference, no?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 24

Expert Comment

by:mankowitz
ID: 19611351
No, you should be fine with that.
0
 
LVL 15

Assisted Solution

by:ncoo
ncoo earned 1040 total points
ID: 19618328
Either will do perfectly ok, I would probably opt for UTF8 it will give you more scope should you want to expand the management system away from Europe and in to Asia for example.


 latin1_bin           West European (multilingual), Binary
 latin1_danish_ci         Danish, case-insensitive
 latin1_general_ci         West European (multilingual), case-insensitive
 latin1_general_cs         West European (multilingual), case-sensitive
 latin1_german1_ci         German (dictionary), case-insensitive
 latin1_german2_ci         German (phone book), case-insensitive
 latin1_spanish_ci         Spanish, case-insensitive
 latin1_swedish_ci         Swedish, case-insensitive


  utf8_bin           Unicode (multilingual), Binary
 utf8_czech_ci         Czech, case-insensitive
 utf8_danish_ci         Danish, case-insensitive
 utf8_estonian_ci         Estonian, case-insensitive

>>>>>> utf8_general_ci         Unicode (multilingual), case-insensitive

 utf8_icelandic_ci         Icelandic, case-insensitive
 utf8_latvian_ci         Latvian, case-insensitive
 utf8_lithuanian_ci         Lithuanian, case-insensitive
 utf8_persian_ci         Persian, case-insensitive
 utf8_polish_ci         Polish, case-insensitive
 utf8_roman_ci         West European, case-insensitive
 utf8_romanian_ci         Romanian, case-insensitive
 utf8_slovak_ci         Slovak, case-insensitive
 utf8_slovenian_ci         Slovenian, case-insensitive
 utf8_spanish2_ci         Traditional Spanish, case-insensitive
 utf8_spanish_ci         Spanish, case-insensitive
 utf8_swedish_ci         Swedish, case-insensitive
 utf8_turkish_ci         Turkish, case-insensitive
 utf8_unicode_ci         Unicode (multilingual), case-insensitive
0
 
LVL 21

Author Comment

by:Julian Matz
ID: 19632670
Thanks for your comments!

latin1_swedish - is this what most people use because it seems to be the default or is it because it supports the most European/Latin language characters??

Are there any disadvantages to using UTF8?

0
 
LVL 15

Assisted Solution

by:ncoo
ncoo earned 1040 total points
ID: 19634547
By default, MySQL uses the latin1 (cp1252 West European) character set and the latin1_swedish_ci collation that sorts according to Swedish/Finnish rules. These defaults are suitable for the United States and most of Western Europe.

The only problem I could seem to find was to do with the creation of a UTF8 database. Some hosts will have to be contacted to create a correct UTF8 database.

CREATE DATABASE `name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

You also will not be able to use ISO with a UTF database.

For simplicity the default may be best (latin1_swedish_ci).
0
 
LVL 21

Author Comment

by:Julian Matz
ID: 19852305
Thanks!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question