Mysql – How to determine column character set used on creation

character-setMySQL

I have this SQL creation statement:

CREATE TABLE `testdb`.`new_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `text_default` VARCHAR(45) NULL,
  `text_latin` VARCHAR(45) CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci' NULL,
  PRIMARY KEY (`id`));

where testdb has the default charset latin1.

Later, without knowing the create statement, I want to figure out if a column was defined to have latin1 as character set or it just inherited it from the database.

What I have tried

Export from the database using MySQLWorkbench

The CHARACTER SET instruction is not there.

Change the default character set of the table

ALTER TABLE `testdb`.`new_table` CHARACTER SET utf8;

Both columns text_default and text_latin preserve the latin1 as charset.

Convert the whole table to another charset

ALTER TABLE testdb.new_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

All columns are now utf8.

So basically I found no solution to track back to creation if the column was specifically as latin1 created. Any ideas?

Best Answer

This shows the collation and character set for one table:

SELECT column_name, 
       character_set_name, 
       collation_name 
FROM information_schema.columns 
WHERE table_name = 'your_table_name'
Related Question