Mysql: Set column charset

Solution 1:

Try this:

ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET utf8mb4;

Solution 2:

I share that, it can always help... I modified a database recently; moving from utf8 to utf8mb4; here is the script that allowed me to generate the alters...

Generate SQL commands to alter the tables:

SELECT CONCAT("ALTER TABLE `",`TABLE_NAME`,"` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") 
FROM `information_schema`.`TABLES` 
WHERE `TABLE_SCHEMA` = 'xxxx';

Generate SQL commands to alter each column:

SELECT CONCAT("ALTER TABLE `",`TABLE_NAME`,"` MODIFY `",`COLUMN_NAME`,"` ",COLUMN_TYPE," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ",IF(`IS_NULLABLE`='YES', 'NULL', 'NOT NULL')," ",IF(`COLUMN_DEFAULT` IS NOT NULL, CONCAT(" DEFAULT '", `COLUMN_DEFAULT`, "'"), ''),";") 
FROM `information_schema`.`COLUMNS` 
WHERE `TABLE_SCHEMA` = 'xxx' AND `TABLE_NAME` = 'xxxx' AND (`CHARACTER_SET_NAME` IS NOT NULL OR `COLLATION_NAME` IS NOT NULL);

Note that for foreign keys and primary keys that make a relationship, you will need to disable foregin key checks before modifying the column

SET FOREIGN_KEY_CHECKS=0;

and enable afterwards.

SET FOREIGN_KEY_CHECKS=1;

Solution 3:

Below one worked for me.

ALTER TABLE table_name   
MODIFY column_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Solution 4:

This one worked for me.

ALTER TABLE `table_name` CHANGE `column_name` `column_name` TEXT CHARACTER SET utf8 COLLATE utf8mb4_unicode_ci;