Different Database collations allow you to store different types of characters in a database. By default, MySQL has latin1 character set with latin1_swedish_ci collation. However, you can easily change MySQL collation of specific tables, all tables or even an entire database. Here’s how to change collation in MySQL.
How to Change Collation of All Tables in MySQL
We will look at the steps to change collation in MySQL.
Here’s the SQL query to change collation of one MySQL table
ALTER TABLE tbl_name[[DEFAULT] CHARACTER SET charset_name][COLLATE collation_name]
In the above query, you need to specify table name tbl_name. You can also optionally specify character set charset_name and collation collation_name.
Bonus Read : Top Database blogs to follow
Here’s an example to change collation of all tables of a database to utf8_general_ci. Replace database_name and table_name below with database and field names respectively.
alter table database_name.table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;If you want to change collation of all tables in your database, you need to run the above query for each table separately.
Since this can be very tedious, we create a SELECT query that generates the ALTER table statements for each table in our database. First, here’s the SELECT query that returns all table-related details in your database.
SELECT * FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA="database_name"AND TABLE_TYPE="BASE TABLE";
Bonus Read : Top MySQL Workbench Alternatives
We modify the above SELECT query to create ALTER TABLE statement for each table in your database.
Here’s the select query that will generate SQL queries for all tables in your database. Replace database_name and collation_name below with your database and collation names and run it to generate SQL queries to change collation of all tables in your database.
SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE collation_name;") AS ExecuteTheString FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="database_name" AND TABLE_TYPE="BASE TABLE";
Bonus Read : How to Enable Slow Query Log in MySQL
Here’s another SQL query to change collation of all tables to utf8.
SELECT CONCAT('ALTER TABLE ', tbl.TABLE_SCHEMA, '.', tbl.TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.TABLES
tbl WHERE tbl.TABLE_SCHEMA = 'database_name'
Once you have the list of ALTER TABLE queries for each table, you can copy-paste and run them to change collation of all tables.
Hopefully, the above article will help you change collation of all tables in database in MySQL.
