In MySQL, you can specify the character set and collation at various levels. You can specify them at the connection level, the server level, the database level, the table level, and the column level. You can also specify a collation in your queries so that it overrides any collation that has been previously specified at the aforementioned levels.

To set the character set and collation at the column level, you can use the CREATE TABLE statement or ALTER TABLE statement (depending on whether you’re creating the table or modifying it), and specify the character set and collation within the column’s definition (the column that you want to set the character set/collation on).

Set the Character Set and Collation

To set the character set and collation of a specific column when you first create a table, use the CHARACTER SET and COLLATE clauses within the CREATE TABLE statement:

CREATE TABLE Tasks (
    TaskId INT NOT NULL AUTO_INCREMENT,
    TaskName VARCHAR(255) NOT NULL
        CHARACTER SET big5 
        COLLATE big5_chinese_ci,
    PRIMARY KEY (TaskId));

Note that, instead of specifying the character set and collation for the whole table, we’re only specifying it for only a specific column (in this case, the TaskName column).

This example sets the column’s character set to big5 and the collation to big5_chinese_ci. This overrides whatever default collation has been set at the database and server levels.

Change the Character Set and Collation

To change the character set and collation of an existing column, use the CHARACTER SET and COLLATE clauses within the ALTER TABLE statement:

ALTER TABLE Tasks MODIFY
    TaskName VARCHAR(255)
        CHARACTER SET hebrew
        COLLATE hebrew_general_ci
        NOT NULL;

This changes the column’s character set to hebrew and the collation to hebrew_general_ci.

Check the Collation

In MySQL Workbench, you can check a column’s collation by right-clicking its table and selecting Alter Table..., then selecting the applicable column in the top pane. The collation is shown in the Column details pane.

There are also several ways to check a column’s collation using SQL.