Join David Powers for an in-depth discussion in this video Setting the server connection collation, part of Up and Running with phpMyAdmin.
- Before starting work with phpMyAdmin, it's important to make sure the correct character set is being used to communicate with MySQL. Launch your local installation of phpMyAdmin and log in if necessary as the root superuser. In General Settings at the top center, if your local version of MySQL is 5.5.3 or later, Server connection collation will be set by default to utf8mb4_unicode_ci.
Strictly speaking, collation affects only the sort order and string comparisons according to various language conventions, but phpMyAdmin uses collation to control both sort order and character set. For example, if we open this drop-down menu, we can see many languages listed there: Estonian, German2, Hungarian, Icelandic, and so on. But as far as the server connection is concerned, the sort order of a language is unimportant.
What this setting does is it determines the character set or encoding that will be used to send data to and from MySQL. The character set is the first part before the underscore, so the character set here is utf8mb4. So why is phpMyAdmin using utf8mb4 rather than plain UTF-8? To understand, we need to take a brief detour into MySQL support for unicode.
UTF-8 uses variable length encoding, storing characters using 1, 2, 3, or 4 bytes. When MySQL 4.1 first added support for UTF-8 in 2004, it included only characters up to 3 bytes in length. That was sufficient to cover most of the world's languages, but it excluded all 4-byte characters. These supplementary characters include most emoji, which have gained increasing popularity recently.
Support for 4-byte UTF-8 characters was added in MySQL 5.5.3 and MariaDB10. But to maintain backward compatibility, MySQL kept the name UTF-8 as the character set for the limited range of characters encoded using 1 to 3 bytes, and it called the expanded character set that includes the full range utf8mb4. However, this is purely an internal MySQL convention.
utf8mb4 can be regarded as MySQL's name for the real UTF-8 encoding. So what this default setting of Server connection collation means is the communication between phpMyAdmin and MySQL will always use the full range of UTF-8 characters, but this poses a question as to what you should do if your remote server is running an older version of MySQL that doesn't support utf8mb4. The previous default in phpMyAdmin was utf8_general_ci, so let's try to change it to utf8_general_ci.
We need to scroll up a little bit to find UTF-8. There we are, utf8_general_ci. I'll select that, and the middle part of the collation has changed to general, but the character set part has been forced to utf8mb4, and this is a deliberate decision. It doesn't matter which version of UTF-8 that you choose. phpMyAdmin will substitute the utf8mb4 version, and that's to prevent 4-byte characters from being silently truncated, but this applies only to UTF-8 character sets.
For example, let's say you're working with Japanese, and you want to use sjis. Let's try to change that, so we need to scroll up a lot further. And there it is, sjis_bin, Japanese Binary. If we select that, it will change it to sjis binary. However, you should do this only if you want phpMyAdmin to use a specific encoding. In most cases, you should choose the phpMyAdmin default of utf8mb4_unicode_ci, so I'm going to change that back.
It's right down at the bottom, utf8mb4_unicode_ci. This will ensure that communication between phpMyAdmin and MySQL uses the full range of UTF-8 characters. And this setting affects only communication to and from MySQL. It doesn't have any effect on the character set or sort order of individual databases, tables, and columns. You do that separately. Unless you know that you need to use a different encoding to communicate with MySQL, make sure that the Server connection collation is set to utf8mb4_unicode_ci before continuing, but if you're using an older version of MySQL locally, if it's older than 5.5.3, utf8mb4 won't be supported, so you'll need to use utf8_unicode_ci instead.
Updated for phpMyAdmin 4.5, this introductory course shows designers and developers how to set up local user accounts, create a database, and design tables. Author David Powers also explains how to import data from CSV files, spreadsheets, and SQL dump files, and how to export data for backup or to transfer to another database. You'll also learn how to run database queries within its intuitive form-based interface, and monitor server performance.
- Why use phpMyAdmin?
- Setting up a user account
- Creating a database
- Choosing the right column types for tables
- Inserting data manually
- Importing and exporting data
- Dealing with foreign keys
- Building and running queries
- Creating views
- Creating stored procedures and functions
- Checking performance