change single column collation in MySQL with ALTER TABLE

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!)

Advertisements

5 thoughts on “change single column collation in MySQL with ALTER TABLE

  1. ALTER TABLE ads MODIFY image BLOB CHARACTER SET utf8 COLLATE utf8_unicode_ci

    i m having error in this code of line,what is the reason?please help me

    Like

  2. I don’t think you can set a character set to a blob, as blobs are for binary data (and character sets only make sense for text-based columns)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s