2

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.

1

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;
  • These commands set the character set to UTF8. The questioner asks how to set the character set to NULL. – David42 Jul 19 '18 at 21:38
0

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.

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.