MySQL
keyboard_arrow_down 295 guides
chevron_leftCharacter sets and Collations
check_circle
Mark as learned thumb_up
0
thumb_down
0
chat_bubble_outline
0
Comment auto_stories Bi-column layout
settings
Setting the character set in MySQL
schedule Aug 12, 2023
Last updated local_offer
Tags MySQL
Master the mathematics behind data science with 100+ top-tier guides
Start your free 7-days trial now!
Start your free 7-days trial now!
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.SCHEMATAWHERE 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 | |+--------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Comment
Citation
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!
