Choosing Character Sets in MySQL: A Short Guide

In this blog, we will look a little closer into the character sets available in MySQL – we will tell you what they are, and how best to work with them.

What are Character Sets and Why Are They Important?

Before we dive deeper into character sets in MySQL, we should probably start by explaining a couple of the core concepts related to them. Essentially, a character set is a set of characters that are allowed to be used as part of a value of a column. While collations refer to rules that are used to compare given characters, character sets are sets of symbols and encodings.

Character sets are generally important because when combined with collations, they make a really powerful combination – for example, the big5 character set comes with the default collation of big5_chinese_ci that can make the usage of Chinese characters possible in MySQL and MariaDB, the latin1 character set comes with the latin1_swedish_ci collation that can be used to support characters relevant to the Swedish language, we can use a character set known as “sjis” to support Japanese characters (such character set comes with the collation of ujis_japanese_ci), etc.

How to Choose a Proper Character Set?

Contrary to a popular belief, choosing to use a proper character set in MySQL is rather simple. You can simply run a query like SHOW CHARACTER SET \G; and you should see something like this:

************************** 1. row ***************************Charset: big5Description: Big5 Traditional ChineseDefault collation: big5_chinese_ciMaxlen: 2*************************** 2. row ***************************Charset: dec8Description: DEC West EuropeanDefault collation: dec8_swedish_ciMaxlen: 1

However, there is another approach. Run a query like so:

SELECT * FROM information_schema.CHARACTER_SETS ORDER BY CHARACTER_SET_NAME and you will be able to observe all of the character sets ordered by their name from A to Z as well. Here is what you will see:

In this case, we think that it should be a little easier to choose character sets than collations in MySQL because you are already able to see that MySQL combines both character sets and collations and also gives you a short description of what a certain collation is (in other words, what kinds of languages it might be relevant to, etc.)

However, in most cases, to choose a proper character set you would need to evaluate the requirements of your project upfront. First off, you would need to think about your database schemas – are they optimized? Once you have taken a deeper look into your database schemas, think about the data you are about to store – are you storing big data sets in a certain storage engine? What data does your data consist of? Are you storing usernames? geographical locations? names? surnames? If you are storing names or surnames, in what country does the people reside (that might be a decisive factor as well – remember, certain languages (for example, Swedish, Russian, etc.) have characters unique to them)? – make sure to consider all factors that might play a decisive role as far as languages are related, then issue a query like the one above and choose your character sets wisely. Of course, you can always change them if you don’t like something, but choosing something upfront is always better than changing something as you go, right?

However, if you find that you don’t like the character sets you elected to use, you can always change them by issuing a query like so where utf8mb4 is the name of any character set you elect to use:

ALTER TABLE demo_table CONVERT TO CHARACTER SET utf8mb4;

That’s it – once you know both how to choose character sets and how to convert your table to a given character set, you should be ready to get rolling on the high-performance road! If you find that the information we have provided is incomplete, though, feel free to refer to the MySQL documentation or head back to the BreachDirectory blog – we cover a bunch of interesting topics related to information security, data breaches, development, and other things as well, so you will certainly find something of interest to you! Be well, and until next time.