search
Search
Login
Math ML Join our weekly DS/ML newsletter
menu
menu search toc more_vert
Robocat
Guest 0reps
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

Setting the 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

We can set the character set at a server, database, table or column level in MySQL.

Syntax

To map all strings sent between the MySQL server and the current client with a particular character set:

SET CHARSET 'charset_name';

To set the character set back to the default:

SET CHARSET DEFAULT;

To set the database character set and collation:

ALTER DATABASE database_name CHARACTER SET 'charset_name' COLLATE 'collation_name';

To set the table character set and collation:

ALTER TABLE table_name CONVERT TO CHARACTER SET 'charset_name' COLLATE 'collation_name';

To set the column character set and collation:

ALTER TABLE database_name.table_name
MODIFY column_name datatype CHARACTER SET 'charset_name' COLLATE 'collation_name';

Examples

Server

To set the character set of all strings sent to MySQL server as latin2:

SET CHARSET 'latin2';
Query OK, 0 rows affected (0.00 sec)

Database

To set the character set to latin2 for a database called tutorial:

ALTER DATABASE tutorial CHARACTER SET 'latin2' COLLATE 'latin2_general_ci';
Query OK, 1 row affected (0.01 sec)

To check the character set has been changed for tutorial:

SELECT SCHEMA_NAME 'database', default_character_set_name
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'tutorial';
+----------+----------------------------+
| database | DEFAULT_CHARACTER_SET_NAME |
+----------+----------------------------+
| tutorial | latin2 |
+----------+----------------------------+

Table

To set the character set to latin2 for a table called students:

ALTER TABLE students CONVERT TO CHARACTER SET 'latin2' COLLATE 'latin2_general_ci';
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0

To check the character set for students table has indeed been changed:

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 | latin2 | latin2_general_ci |
+------------+--------------------+-------------------+

Column

To set the character set to latin1 for column fname within table students:

ALTER TABLE people.students
MODIFY fname VARCHAR(50) CHARACTER SET 'latin1' COLLATE 'latin1_general_ci';
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0

To check the column character set and collation has now been updated:

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(50) | latin1_general_ci | YES | | NULL | | select,insert,update,references | |
| lname | varchar(30) | latin2_general_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) | latin2_general_ci | YES | | NULL | | select,insert,update,references | |
+--------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
mail
Join our newsletter for updates on new DS/ML comprehensive guides (spam-free)
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!