Month: October 2014

change single column collation in MySQL with ALTER TABLE

mb7 Comments

Ok, I may be a little rusty with SQL, but this took me surprisingly long to figure out.

If you want to change the collation for a single column in one of your tables, the correct syntax is

ALTER TABLE myTable 
MODIFY myColumn VARCHAR(width) CHARACTER SET charset COLLATE collation;

so for example, if you want to set your ‘id’ column in your ‘users’ table to accept case-sensitive ascii strings of length <= 10 characters, you would execute:

ALTER TABLE users 
MODIFY id VARCHAR(10) CHARACTER SET ascii COLLATE ascii_bin;

Yes, it's easy, but I guess I was relying on google a bit too much this time (I could only find how to modify tables and databases, and many answers about columns in stackoverflow are just.. wrong!)