search
Search
Login
Map of Data Science
menu
menu search toc more_vert
Robocat
Guest 0reps
Sign up
Log in
account_circleMy Profile homeAbout paidPricing
emailContact us
exit_to_appLog out
Map of data science
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe:
"Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
brightness_medium
share
arrow_backShare
Twitter
Facebook
check_circle
Mark as learned
thumb_up
0
thumb_down
0
chat_bubble_outline
0
auto_stories new
settings

Checking character set in MySQL

Database
chevron_right
MySQL
chevron_right
Cookbooks
chevron_right
Character sets and Collations
schedule Jul 1, 2022
Last updated
local_offer MySQL
Tags
tocTable of Contents
expand_more
map
Check out the interactive map of data science

We can check the character set in MySQL on a database, table, column, string level using varying syntax.

Examples

Databases

To check the charset for our database (this works only for databases that you are currently using):

SHOW variables LIKE "character_set_database";
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+

To check the charset for all databases:

SELECT SCHEMA_NAME 'database', default_character_set_name FROM information_schema.SCHEMATA;
+--------------------+----------------------------+
| database | DEFAULT_CHARACTER_SET_NAME |
+--------------------+----------------------------+
| mysql | utf8mb4 |
| information_schema | utf8 |
| performance_schema | utf8mb4 |
| sys | utf8mb4 |
| people | utf8mb4 |
| tutorial | latin2 |
+--------------------+----------------------------+

For Tables

SELECT t.TABLE_NAME, ccsa.CHARACTER_SET_NAME
FROM information_schema.tables t, information_schema.collation_character_set_applicability ccsa
WHERE t.table_collation = ccsa.collation_name
AND t.TABLE_SCHEMA = 'database_name'
AND t.TABLE_NAME = 'table_name';

Consider the following information about some students:

student_id

fname

lname

day_enrolled

age

username

1

Sky

Towner

2015-12-03

17

stowner1

2

Ben

Davis

2016-04-20

19

bdavis2

3

Travis

Apple

2018-08-14

18

tapple3

4

Arthur

David

2016-04-01

16

adavid4

5

Benjamin

Town

2014-01-01

17

btown5

The above sample table can be created using the code here.

To check the character set and collation for the table students within the people database:

SELECT t.TABLE_NAME, ccsa.CHARACTER_SET_NAME, t.TABLE_COLLATION
FROM information_schema.tables t, information_schema.collation_character_set_applicability ccsa
WHERE t.table_collation = ccsa.collation_name
AND t.TABLE_SCHEMA = 'people'
AND t.TABLE_NAME = 'students';
+------------+--------------------+--------------------+
| TABLE_NAME | CHARACTER_SET_NAME | TABLE_COLLATION |
+------------+--------------------+--------------------+
| students | utf8mb4 | utf8mb4_0900_ai_ci |
+------------+--------------------+--------------------+

For Columns

SHOW FULL COLUMNS
FROM table_name;

Consider the following information about some students:

student_id

fname

lname

day_enrolled

age

username

1

Sky

Towner

2015-12-03

17

stowner1

2

Ben

Davis

2016-04-20

19

bdavis2

3

Travis

Apple

2018-08-14

18

tapple3

4

Arthur

David

2016-04-01

16

adavid4

5

Benjamin

Town

2014-01-01

17

btown5

The above sample table can be created using the code here.

To check the character set and collation for columns within the students table:

SHOW FULL COLUMNS
FROM students;
+--------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| student_id | int unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| fname | varchar(30) | utf8mb4_0900_ai_ci | YES | | NULL | | select,insert,update,references | |
| lname | varchar(30) | utf8mb4_0900_ai_ci | YES | | NULL | | select,insert,update,references | |
| day_enrolled | date | NULL | YES | | NULL | | select,insert,update,references | |
| age | int | NULL | YES | | NULL | | select,insert,update,references | |
| username | varchar(15) | utf8mb4_0900_ai_ci | YES | | NULL | | select,insert,update,references | |
+--------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+

Note that although the character set is not given directly as a column in the results, we can infer the character set from the Collation value.

For Strings

To check the character set for string 'apple':

SELECT CHARSET('apple');
+------------------+
| CHARSET('apple') |
+------------------+
| utf8mb4 |
+------------------+
robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Ask a question or leave a feedback...
thumb_up
0
thumb_down
0
chat_bubble_outline
0
settings
Enjoy our search
Hit / to insta-search docs and recipes!