“Collation in MySQL refers to a set of rules that govern the comparison of characters using a specific character set. In MySQL, each character set is mapped to at least one default collation. You can also have one character set mapped to multiple collations as allowed by the MySQL server.”
NOTE: Keep in mind that more than one character set cannot use the same collation.
This tutorial will walk you through setting the character set and collation on the server level.
View Available Character SET and Collation
MySQL allows us to use the SHOW command to view the character sets and their associated collation.
The command syntax is as shown:
The command will show the table as shown:
The table shows the character set, description, default collation, and max length. Notice that the default collation for each character set begins with the name of the character set and the postfix, which defines whether the collation is case sensitive (cs), case insensitive (ci), and binary (bin).
You can also get all the collation for the UTF character set using the command as shown:
Where “utf%” is the name of the character set whose collation you wish to retrieve.
Example output for UTF character set is as shown:
NOTE: The table output provided in this article is truncated for visibility purposes. MySQL supports more character sets and collation than depicted above.
Configure the Character Set and Collation at MySQL Server
MySQL allows you to specify a character set and its corresponding collation at four main levels. These include:
- Server Level
- Database Level
- Table Level
- Column Level
This section will teach how to configure the character set and collation at the server level. Let’s discuss.
At Startup
One way to configure character set and collation at the server level is during server startup. Using the mysqld command, you can use the –character-set-server and –collation-server-server parameters.
Note: If you only specify the character set without the corresponding collation, MySQL will use the default collation for the specified character set.
The command to set the character set and collation at startup is as shown:
For example, to set the server level character set and collation to UTF and general_ci collation, run the command:
And you are done.
Conclusion
Success, in this article, you learned about MySQL character sets and collations. You also learned how to set the default character set and collation at the server level.
We hope you enjoyed it!!
