1

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?

1

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'
  • Both are at latin1. No distinction between them. – Valentin Despa Jan 16 '15 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.