Removing per column and table collation and charset

Question

I recently completed a transition of a database from CHARSET=utf8 COLLATE=latin1 to CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci. During the process tables were migrated one at a time by altering their respective charsets and collation properties.

Now that the transition has been completed and all of the servers and the database itself have now been set to use utf8mb4 and utf8mb4_unicode_ci, how would I go about removing those properties from the columns and tables so they inherit the default value set on the server?

For instance presently if I execute a SHOW FULL COLUMNS FROM table; Collation for most columns is set to utf8mb4_unicode_ci rather than NULL.

Answer 1

As a programmer, it is easy to assume that there is "inheritance" in the database. If a value is null, look at the parent.

There is no inheritance

Text-type columns cannot have a null character set or collation. Each field will have a fixed character set and collation when created. Imagine changing to a completely different character set at the table or database level, and existing data in the existing columns automatically inherit the new character set. It would probably look corrupt.

Here is some documentation: https://dev.mysql.com/doc/refman/8.0/en/charset-table.html

MySQL chooses the table character set and collation in the following manner:

If both CHARACTER SET charset_name and COLLATE collation_name are specified, character set charset_name and collation collation_name are used.

If CHARACTER SET charset_name is specified without COLLATE, character set charset_name and its default collation are used. To see the default collation for each character set, use the SHOW CHARACTER SET statement or query the INFORMATION_SCHEMA CHARACTER_SETS table.

If COLLATE collation_name is specified without CHARACTER SET, the character set associated with collation_name and collation collation_name are used.

Otherwise (neither CHARACTER SET nor COLLATE is specified), the database character set and collation are used.

Just to be clear, the character set and collation are chosen when the column is created. If you change them, you should also convert the data at the same time.

Answer 2

To delete collation or charset just omit it:

alter table t
    modify c varchar(255)

Answer 3

Converting columns:

ALTER TABLE table_name MODIFY col_name VARCHAR(100) CHARACTER SET utf8;

Converting tables:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;

Answer 4

In my testing, it looks like SHOW FULL COLUMNS will always put the collation of the table into the results if one is not set on the column. Only for text-type columns. Numerics, dates, etc., do not have a collation.

The example table has 1 column with a collation.

CREATE TABLE `testCollationChanging` (
  `apiUserID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `email` varchar(128) COLLATE latin1_general_ci NULL,
  `apiKey` char(32) NOT NULL,
  `companyID` int(11) NOT NULL,
  `userID` int(11) NOT NULL,
  PRIMARY KEY (`apiUserID`),
  UNIQUE KEY `Duplicate name/company combo check` (`name`,`companyID`) USING BTREE,
  UNIQUE KEY `apiKey` (`apiKey`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

SHOW FULL COLUMNS FROM `testCollationChanging`;

ALTER TABLE `testCollationChanging` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

SHOW FULL COLUMNS FROM `testCollationChanging`;

DROP TABLE `testCollationChanging`;

This shows the following 2 result sets ...

apiUserID   int(11)     NO  PRI     auto_increment  select,insert,update,references 
name    varchar(128)    latin1_swedish_ci   NO  MUL         select,insert,update,references 
email   varchar(128)    latin1_general_ci   YES             select,insert,update,references 
apiKey  char(32)    latin1_swedish_ci   NO  UNI         select,insert,update,references 
companyID   int(11)     NO              select,insert,update,references 
userID  int(11)     NO              select,insert,update,references 

and

apiUserID   int(11)     NO  PRI     auto_increment  select,insert,update,references 
name    varchar(128)    utf8mb4_unicode_520_ci  NO  MUL         select,insert,update,references 
email   varchar(128)    utf8mb4_unicode_520_ci  YES             select,insert,update,references 
apiKey  char(32)    utf8mb4_unicode_520_ci  NO  UNI         select,insert,update,references 
companyID   int(11)     NO              select,insert,update,references 
userID  int(11)     NO              select,insert,update,references 

I think if the columns have inconsistent collations, you need to convert them also. In the above example, they seem to be converted appropriately.

Incorporating ...

SELECT * FROM information_schema.TABLES WHERE TABLE_NAME = 'testCollationChanging';
SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'testCollationChanging';

into the results shows that the information schema is holding the collation against each column.