I have an existing table and I want to convert the charset only for one specific column to utf-8.
I know that this command ALTER TABLE table_name CONVERT TO CHARACTER SET utf8
does it for the whole table but I'm looking for a column-specific command.
Is there a command for that?
The MySQL server has a compiled-in default character set and collation. To change these defaults, use the --character-set-server and --collation-server options when you start the server.
To change the character set encoding to UTF-8 for the database itself, type the following command at the mysql> prompt. Replace dbname with the database name: Copy ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci; To exit the mysql program, type \q at the mysql> prompt.
Similarly, here's the command to change character set of MySQL table from latin1 to UTF8. Replace table_name with your database table name. mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; Hopefully, the above tutorial will help you change database character set to utf8mb4 (UTF-8).
Try this:
ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET utf8mb4;
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With