{"id":105570,"date":"2025-03-17T16:51:26","date_gmt":"2025-03-17T16:51:26","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=105570"},"modified":"2025-03-17T16:51:28","modified_gmt":"2025-03-17T16:51:28","slug":"mysql-vs-postgresql-character-sets-and-collations","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/mysql-vs-postgresql-character-sets-and-collations\/","title":{"rendered":"MySQL vs. PostgreSQL: Character Sets and Collations"},"content":{"rendered":"\n

This is part of a series of posts from Aisha Bukar comparing MySQL and PostgreSQL. You can see the entire series here<\/a>.<\/strong><\/p>\n\n\n\n\n

When working with strings in a database system, you need to ensure that the data is stored, sorted, and compared accurately. That\u2019s where character sets and collations come in. Character sets determine how text is saved while collations define how the database sorts and compares text.<\/p>\n\n\n\n

For example, if you\u2019re storing text in different languages (like English, Chinese, or Arabic), choosing the right character set ensures the text is saved properly. Picking the right collation ensures sorting is correct\u2014like ordering names alphabetically while ignoring case differences or accents.<\/p>\n\n\n\n

Usually, the default that is chosen at installation is all that is worried about, setting the values to whatever the data for a given region where you are storing data. However, it is important to understand what your initial choices mean, and how to handle any special cases. If you don\u2019t set your values correctly, your database might store or sort text incorrectly, leading to errors, weird behavior, or frustrated users.<\/p>\n\n\n\n

 <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/a>What are Character Sets and Collations?<\/h2>\n\n\n\n

Character Sets<\/strong>: A character set or CHARSET<\/code> defines how a string is stored in a database. These characters can be symbols, letters, or numbers.<\/p>\n\n\n\n

Imagine you have a database for storing users’ names from different countries, and you want to ensure that names in any language (English, Chinese, Russian, etc.) can be saved correctly. This can be done using a character set to display these characters correctly.<\/p>\n\n\n\n

MySQL and PostgreSQL both support multiple character sets but there are of course some key differences to explore in both databases, so stick around!<\/p>\n\n\n\n

Collations<\/strong>: <\/a>A collation is a set of rules for comparing and sorting text in a specific character set. Collations help ensure your database sorts text correctly based on your needs.<\/p>\n\n\n\n

For example: Does “Apple” come before “Banana”? Or is “\u00e9” treated the same as “e”? In some collations, “\u00e9” and “e” might be considered equal, even though they look different. Some collations can also be case insensitive where “Apple” = “apple” as well as \u201cApple\u201d = \u201caPPl\u00e9\u201d.<\/p>\n\n\n\n

Every character set has at least one collation, and some have multiple options depending on the use case. Both MySQL and PostgreSQL support various collations, but they handle them in slightly different ways. Let\u2019s dive into how this works!<\/p>\n\n\n\n

Character Sets<\/h2>\n\n\n\n

In this section I will compare how character sets are implemented in PostgreSQL and MySQL.<\/p>\n\n\n\n

<\/a>Character Sets in MySQL<\/h3>\n\n\n\n

Character sets (or CHARSET<\/code>) define how sets of characters can be stored in a database.<\/p>\n\n\n\n

MySQL uses a default character set for storing and handling text. Right now, that default is utf8mb4, which is great because it supports all kinds of characters, including emojis and symbols from many languages. Think of utf8mb4 as the “universal” option for storing text.<\/p>\n\n\n\n

If you want to check the available character sets in your MySQL schema, you can do this by running the following command:<\/p>\n\n\n\n

SHOW CHARACTER SET;<\/pre>\n\n\n\n
This returns all the available character set in your MySQL schema<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Here is an example of a character set being used in MySQL:<\/p>\n\n\n\n
CREATE TABLE users (\n    id INT PRIMARY KEY,\n    name VARCHAR(255) CHARACTER SET utf8mb4\n);<\/pre>\n\n\n\n
The name<\/code> column can store text in any language using the utf8mb4<\/code> character set to support all Unicode characters, including emojis.<\/p>\n\n\n\n
Now, let\u2019s insert a statement into the users table using characters from English, French, Arabic, and Chinese language.<\/p>\n\n\n\n
INSERT INTO users (id, name) VALUES\n(1, 'My name is Aisha'),\n(2, '\u0627\u0633\u0645\u064a \u0639\u0627\u0626\u0634\u0629'),\n(3, 'Je m\\'appelle Aisha'),\n(4, '\u6211\u7684\u540d\u5b57\u662f\u827e\u838e');<\/pre>\n\n\n\n
Let’s take a look at the data we just inserted:<\/p>\n\n\n\n
SELECT * FROM users;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The above image is the output of the query we just ran. You might be wondering how the utf8mb4 character set can store so many different characters. It works by using something called the Basic Multilingual Plane (BMP). <\/a>The Basic Multilingual Plane (BMP) <\/a> is the main group of Unicode characters, including letters, symbols, and punctuation used in most modern languages. It covers the basic characters you see in everyday text.<\/p>\n\n\n\n
With utf8mb4<\/a>, these BMP characters usually take 1-3 bytes to store. However, utf8mb4 can also handle characters outside this group, like emojis and rare symbols, by using up to 4 bytes. This makes it capable of storing all Unicode characters including various emojis.<\/p>\n\n\n\n
\"Basic<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Roadmap to Unicode BMP multilingual<\/em> by Drmccreedy<\/em>, used under CC BY-SA 4.0 License<\/em> \/cropped from original. Available at: https:\/\/en.wikipedia.org\/wiki\/Plane_(Unicode)#\/media\/File:Roadmap_to_Unicode_BMP_multilingual.svg<\/a><\/p>\n\n\n\n
<\/a>Character Set repertoire?<\/h4>\n\n\n\n
A character set repertoire in MySQL simply refers to the set of characters that a particular character set can store and handle. Think of it as the “alphabet” for a given character set. Each character set has a specific range of letters, symbols, and other characters it supports. Here are some common MySQL character sets and their repertoires:<\/p>\n\n\n
\n