Change MySQL Collation

Sometimes, the MySQL database collation could make your web content become question mark(?) and you might not know what is happening and what wrong from your scripting.

To understand what is the server default collation, there are two ways to done it as below.

[email protected] [~]# mysqladmin variables | grep collation
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |

OR

Login to MySQL from server.

[email protected] [~]# mysql -uroot -p
mysql> SHOW VARIABLES LIKE ‘collation%’;
+———————-+—————–+
| Variable_name | Value |
+———————-+—————–+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+———————-+—————–+
3 rows in set (0.00 sec)

With the above command, you will notice that the collation set from your server is utf8_general_ci. Assume that you wish to change them to latin1_general_ci, simply edit your /etc/my.cnf (Linux) or C:\MySQL5\my.ini file record from

collation-server=utf8_general_ci

to

collation-server=latin1_general_ci

But please aware that you should not simply change the collation as it might affected some of your database data become question mark(?).

Then you shall restart the MySQL services.

Published by

Mick Genie

Mick Genie is the founder of MickGenie.com and working at ExaBytes Network Sdn Bhd and WPWebHost web host company. He is expertise in Windows and Linux environment especially web hosting related information, tips and trick as well as the IT Information.

3 thoughts on “Change MySQL Collation”

  1. Pingback: Change collation for sql database (CRITICAL)? | BingSite

Comments are closed.