MySQL allows you to store different kinds of strings and texts in your database columns. There are different data types available for this purpose. But it is equally important to use the right character set for a column. Otherwise, it can lead to errors and slow performance. Character set can be set for a column or table or database. In this article, we will learn how to get character set of table or column or database.
What is Character Set
Character set is the set of characters that are allowed for a given column or table or database. This makes it easy for MySQL database to know beforehand the kind of characters to expect during selection, insertion and update statements, improving performance and avoiding errors. While inserting or modifying data, if one or more characters do not conform to this character set, then MySQL will try to force fit them into the existing character set, or throw an error.
For example, if the character set of a column is set to ASCII, then it will only numerals 0-9, uppercase and lowercase letters, basic punctuation marks and some non-printable control characters. If you try to save a Unicode string in this column, then MySQL will convert it into ASCII format and save it, or give an error. Even if it is saved in ASCII format, it will be unreadable and totally different from your actual data causing confusion. Therefore, it is important to use the right character set for your database, tables and columns.
You will also come across the term ‘collation’ while dealing with charsets. Collation is a set of rules to compare a given character with a character set. A character can have multiple collations but a collation will belong to only one charset.
Default Character Set in MySQL
Every database has a character set by default. It is stored in the database variable character_set_database. Each database has a separate value for this variable. You need to switch to your database and access its value to find out its charset. You can find it out by logging into MySQL and running the following queries. Replace <database_name> with your database name.
mysql> use <database_name>
mysql> select @@character_set_database;
How to Get Character Set of MySQL database or table or column
There are several ways to get the character set of MySQL database or table or column. It is important to remember that if the character set for a column is specified, then it will override the character set of the table. If character set of table is specified, then it will override the character set of the database. If the character set of database is specified, then it will override the character set of server.
Character Sets in MySQL
Each MySQL server supports a number of character sets. You can get this list of available charset using the following command.
SELECT * FROM information_schema.character_sets ORDER BY character_set_name
You will see the following output.
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
|--------------------|----------------------|--------------------|--------|
| armscii8 | armscii8_general_ci | ARMSCII-8 Armenian | 1 |
| ascii | ascii_general_ci | US ASCII | 1 |
...
...
| utf32 | utf32_general_ci | UTF-32 Unicode | 4 |
| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
| utf8mb4 | utf8mb4_0900_ai_ci | UTF-8 Unicode | 4 |
For example, in the above result, utf8mb4 has a MAXLEN of 4, which means each character is represented with 4 bytes.
Every MySQL database has information_schema database that contains all the key information about all databases, tables and columns on your server. You can query this database to obtain the character set of a database, table or column.
In fact, this information is present at different locations of your MySQL server so there are different ways to retrieve it. We have covered a couple of popular ways to get chartset information. You may come across many more.
Get Character Set of Database or Schema
Here is the SQL query to obtain the character set of database or schema. Replace <database_name> with the name of your database.
SELECT default_character_set_name FROM information_schema.SCHEMATA
WHERE schema_name = "<database_name>";
The information_schema.SCHEMATA table contains information about all databases on your server, including their charset. This information is present in default_character_set_name column.
Alternatively, you can switch to your database and fetch value of MySQL variable character_set_database.
mysql> USE <database_name>;
mysql> show variables like "character_set_database";
Get Character Set of Table
If you want to get the character set of table, then use the following SQL query. Replace <database_name> and <table_name> with the names of your database and tables.
SELECT C.character_set_name FROM information_schema.TABLES T,
information_schema.COLLATION_CHARACTER_SET_APPLICABILITY C
WHERE C.collation_name = T.table_collation
AND T.table_schema = "<database_name>"
AND T.table_name = "<table_name>";
In the above query, we merge information_schema.TABLES and information_schema.COLLATION_CHARACTER_SET_APPLICABILITY tables to obtain a list of database tables along with their key meta information such as charsets. Then we use WHERE clause to filter the charset information about our required database and tables.
Get Character Set of Column
You can use the following SQL query to get character set of column. Replace <database_name>, <table_name>, <column_name> with the names of your database, table and column respectively.
SELECT character_set_name FROM information_schema.COLUMNS
WHERE table_schema = "<database_name>"
AND table_name = "<table_name>"
AND column_name = "<column_name>";
You can query information_schema.COLUMNS table to obtain the character set information about a specific column.
Conclusion
In this article, we have learnt what character sets are, how to get charset for columns, tables, databases and servers. We have also learnt how to alter these character sets. If you are seeing unknown characters in your database column or web pages, then it means you need to change the charset of your data. You can use this information to fix performance issues and errors related to character sets in MySQL. It is important that your database charset is able to handle all characters used by your website/app. Also, as far as possible, keep it consistent with the charset of your site. Otherwise, you may end up seeing unknown characters on your web pages and/or your database.
Also read:
How to Set Checked for Checkbox in jQuery
How to Check if File Exists in Python
How to Merge Two Dictionaries in Python
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.