How to change collation of a table after creating the table?

Question

So I have a table that I want to change the collation of it to none, I tried in Operations > Table options but it gives me an error that says "No collation provided" is there any other ways to change it ?

Answer 1

The CHARACTER SET and COLLATION of a table are simply defaults for any columns that get added.

To change either of them, use

ALTER TABLE tbl CONVERT TO CHARACTER SET ... COLLATION ...;

But be very careful about changing the character set -- If Selects are displaying gibberish, using CONVERT TO is likely to make it impossible fix. (Cf: Trouble with UTF-8 characters; what I see is not what I stored ).

Caution: CONVERT TO changes all the string columns. If you have a column that is deliberately some other charset, it will get changed, too.

If CONVERT TO is not a good idea (for above reasons), then do multiple MODIFY COLUMNs, one per column that needs changing:

ALTER TABLE tbl
   MODIFY COLUMN string1 <<column_definition>>
   MODIFY COLUMN string2 <<column_definition>>
   ...;

Note: the column_definition must include all the other stuff (NULL/NOT NULL, etc.). Suggest you run SHOW CREATE TABLE, then copy paste.

Note upon note: If the SHOW CREATE TABLE does not provide the charset/collation on the individual columns, then you should still include such in the MODIFY clause(s).

And... If you might later add more string columns, ALTER the table as a whole with yet another syntax:

ALTER TABLE tbl
    DEFAULT CHARACTER SET <<charset_name>>
    DEFAULT COLLATE <<collation_name>>;

I agree with Hartmut's Comment on "none". However, there is something to add:

When specifying a CHARACTER SET without also specifying a COLLATION, there is a specific default collation for each character set. To see the defaults:

SHOW CHARACTER SET;

or

SHOW CHARACTER SET LIKE 'utf%';