As per MySQL docs, MySQL Collation is a set of rules for comparing characters in a character set. MySQL collation affect how Strings are sorted and compared with each other. While creating a database table, you will have multiple collation options to choose from. Your choice of collation while creating a database mainly depends on your user base and/or their expectations. You can read more about all collation’s & their differences from the MySQL website.
Also Read : MySQL Scheduler Overview with Examples
The default MySQL Collation and charset is latin1. But, you may have a reason or a requirement to change the default collation, lets say to utf8_general_cli. There are multiple options to achieve this in MySQL. Below are the 3 options to update the MySQL Collation.
1. Configure MySQL Collation at the sever level
MySQL provides an option to configure the default collation at the MySQL server level. You can choose to use this option if you want to use the same collate for all the applications accessing your MySQL instance. You can use the below configuration in the MySQL configuration file,
[mysqld] character-set-server=utf8 collation-server=utf8_general_ci
2. Specify the MySQL Collation while creating the Schema
If you have multiple schemas in your database and if you want to update the collate only for a specific schema, then you can specify the collate while creating the schema itself. The collate used while creating the schema becomes the default collate for all the tables created in that schema. Below is the syntax to specify the collate while creating the schema.
CREATE DATABASE testdb DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
Also Read: How to configure JaCoCo for Code Coverage in Spring boot Applications
If you want to update the collate for an already existing schema, then you can consider exporting the complete schema & then importing it back with updated collation.
3. Use Alter statement on Tables
You can change the collate for a particulat table by using Alter statement as well. This approach would be useful if you want to update the collate for certain specific tables in the schema. Below is the syntax to update the collate for a particular table,
'ALTER TABLE `mydb`.`mytable` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;'
‘mydb’ & ‘mytable’ needs to be replaced with database and table names respectively.
With a bit of tweek you can also use this approach to update the collate for all the tables in a database as well. If you run the below command, it will give you a list of Alter statements for each of the tables in the database. You will have to replace ‘mydb’ in the last line with the relavant schema name.
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` = 'mydb'
Now, all you have to do is to copy the output from the above query and execute the alter statements for each of the tables.
Do let me know if you need any help in updating your MySQL collation.
