1

I'm curious what is considered the standard today for use as the Collation of MySQL Tables?

I was told that Latin-1 was the best choice when I was beginning with MySQL, but came across this post from 2009.

It states that US-ASCII and LATIN-1 Character Sets are on the way out, and that UTF-8 is on the way in and should be made the default by MSQ version 6.

Does this gel with what you know, or with what you think, or with what you do?

For this question, let's assume there is no specific need for non-english characters.

Improve this question
7
  • 1
    Doesn't it depend on whether you plan to use english-only text or not?
    – c0da
    Sep 22, 2011 at 5:54
  • @c0da - I added a caveat to the question. Good point.
    – stefmikhail
    Sep 22, 2011 at 6:04
  • Check out asciitable.com If you are satisfied by the 127 ASCII chars, then use it :) Otherwise you can go ahead with UTF-8...
    – c0da
    Sep 22, 2011 at 6:12
  • 1
    Migrations to UTF-8 are painful. Unless you know you'll never, ever have a requirement to do it, I'd simply go for UTF-8 now and have one less problem to worry about - assuming your language of preference is fully compliant, of course.
    – Bill Michell
    Sep 22, 2011 at 12:12
  • 1
    @stefmikhail - Backwards compatibility. html can explicitly convey the encoding. But on a computer system, many programmers (especially the ones who were writing the code 10 years ago) simply accept the system default encoding, and there are as many of those (incompatible from one another) as stars in the sky. Treating LATIN-1 as UTF-8 will get you in trouble as soon as you stray outside the US-ASCII character range. Here in the UK, for example, £ is the character most people will trip over first.
    – Bill Michell
    Sep 23, 2011 at 8:31

2 Answers 2

Reset to default
6

UTF-8 is becoming more and more common virtually everywhere (W3Techs in June 2015 puts UTF-8 at 84.3% on the web), has no storage penalty over US-ASCII for the US-ASCII range (U+0000 through U+007F) (depending on the implementation the BOM may carry a three-byte penalty, but a BOM is only needed if the encoding and/or byte order is not otherwise known, which it would be in this case), and it can represent the full range of Unicode so it will future-proof your application character-set-wise at no extra cost if you don't use that capability. In summary, I see no reason not to use UTF-8 encoding these days, particularly if your choices are between UTF-8 and US-ASCII. And even in a US-only world, I would be very wary of saying that there will be "no need" to encode any letters outside of the English alphabet.

I'm pretty sure I saw a RFC from a few years back that stated that UTF-8 is the new Internet standard character set (replacing US-ASCII), but then I couldn't find it again. However, BCP 18 (RFC 2277) section 3.1, What charset to use, comes close in stating that, in part:

All protocols MUST identify, for all character data, which charset is in use.

Protocols MUST be able to use the UTF-8 charset, which consists of the ISO 10646 coded character set combined with the UTF-8 character encoding scheme, as defined in [10646] Annex R (published in Amendment 2), for all text.

Protocols MAY specify, in addition, how to use other charsets or other character encoding schemes for ISO 10646, such as UTF-16, but lack of an ability to use UTF-8 is a violation of this policy; such a violation would need a variance procedure ([BCP9] section 9) with clear and solid justification in the protocol specification document before being entered into or advanced upon the standards track.

Improve this answer
1
  • The article I linked to in my question also states that UTF-8 is the new Internet standard, and the article is from 2009. I asked this of Bill Michell as he commented on my question, but why is it that the default for html is UTF-8, while in MySQL it is not?
    – stefmikhail
    Sep 22, 2011 at 19:37
2

utf8mb4

utf8mb4_bin

If your PHP script accesses MySQL, there’s a chance your strings could be stored as non-UTF-8 strings in the database even if you follow all of the precautions above.

To make sure your strings go from PHP to MySQL as UTF-8, make sure your database and tables are all set to the utf8mb4 character set and collation, and that you use the utf8mb4 character set in the PDO connection string. See example code below. This is critically important.

Note that you must use the utf8mb4 character set for complete UTF-8 support, not the utf8 character set! See Further Reading for why.

The following web site links to the aforementioned web site and may be easier to read regarding the subject of PHP and databases:

You can also search the documentation of the database software you are using for suggestions and ideas for your own database software.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.