Collation refers to the rules governing how the database sorts and compares characters. It affects the way data is stored and retrieved, particularly when it comes to string values.
Different languages and alphabets may have different ways of ordering and comparing characters. For example, in some languages, accented letters are considered distinct from their unaccented counterparts, while in others they are not. Similarly, some languages may have case-sensitive or case-insensitive rules for comparing strings.
To handle these variations, databases support different character sets and collations. A character set is a collection of symbols and encodings, such as ASCII or UTF-8. A collation is a set of rules for how a character set is ordered and compared.
In the past, RunCloud used to set the collation to latin1_swedish_ci by default. This meant that the database would save strings using the Latin character set.
However, this could cause problems for users who wanted to work with different languages and alphabets. For instance, if a user created a WordPress website in Hebrew, the website wouldn’t be properly read or displayed. This could be resolved by changing the collation.
To prevent such issues from happening again, RunCloud has made collation optional. Users now have the freedom to choose the collation that aligns with their content and language preferences. This ensures that their data is stored and retrieved accurately, without any issues related to character set compatibility.
MariaDB and MySQL Character Sets
MariaDB and MySQL support many character sets and collations, which can be specified at different levels: server, database, table, or column level. The default character set for MariaDB and MySQL is latin1, and the default collation is latin1_swedish_ci. However, these defaults may not be suitable for all applications and languages.
For example, if you want to store and manipulate data in Korean, Arabic, or Chinese characters, you need to use a character set that supports these characters, such as utf8mb4. Similarly, if you want to sort and compare strings according to a specific language or culture, you need to use a collation that matches your expectations, such as utf8mb4_turkish_ci or utf8mb4_german2_ci.
MariaDB and MySQL provide a comprehensive overview of supported character sets and collations, enabling users to make informed choices.
View MariaDB Collations and Character sets (Optional)
RunCloud provides you with complete access to your server – you can log in via SSH and access the database directly as the root user. If you are not sure how to connect to your server, refer to our tutorial on connecting to server via SSH.
Logging In To MariaDB
After you have connected to your RunCloud server, you can easily log in to your database using the following command:
mysql -u root
After executing the above command, you will be greeted with a message saying “Welcome to the MariaDB monitor,” indicating that you have successfully established a connection with your database.
List Supported Character Sets
A character set is a collection of symbols that you can use to write things. For example, some text might use English letters, some may have letters from other languages, like Spanish or Arabic, and some may have symbols such as emojis or numbers. After logging in, you can view all the supported character sets by executing the following command:
SHOW CHARACTER SET;
List Supported Collations
Collation is a set of rules that tell you how to sort and compare symbols in your character set. There are different kinds of collations for different languages and cultures. To view a list of all supported collations, you can execute the following command:
show collation;
Although the above command lists all collations, it is not very useful as the output is too big to comprehend. In most cases, users will be interested just in collations that support a particular character set. To list all the collations that use hebrew character set, execute the following command.
SHOW COLLATION where charset = 'hebrew';
View the Collation of Your Database
If you are not sure what collation is being used by your database, you can check that easily by executing the following command:
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION, Engine FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like 'wp_%';
The above command shows the details of all the tables whose names start with “wp_”. If you are using a different prefix for your tables, replace “wp_” with that.
Setting Collation on RunCloud
RunCloud provides a quick and easy way to set the collation while creating the database. To set a collation, you can follow these steps:
- Go to your server dashboard and click on Database – this page will show existing databases on your server.
- Click on Add New Database to create a fresh database. Under Collation, select the desired option from the drop-down menu. If you only plan to use English characters, you can leave this option blank.
If you do plan to use special characters, but aren’t sure which collation is right for you, select the default collation for your desired charset.
- Fill in other details such as database name and authorized users.
- Finally, click on Save Database to create this database. Now you can start using it as you normally would.