How to change the default charset of a MySQL table?

Question

There is a MySQL table which has this definition taken from SQLYog Enterprise :

Table              Create Table                                             
-----------------  ---------------------------------------------------------
etape_prospection  CREATE TABLE `etape_prospection` (                       
                     `etape_prosp_id` int(10) NOT NULL AUTO_INCREMENT,      
                     `type_prosp_id` int(10) NOT NULL DEFAULT '0',          
                     `prosp_id` int(10) NOT NULL DEFAULT '0',               
                     `etape_prosp_date` datetime DEFAULT NULL,              
                     `etape_prosp_comment` text,                            
                     PRIMARY KEY (`etape_prosp_id`),                        
                     KEY `concerne_fk` (`prosp_id`),                        
                     KEY `de_type_fk` (`type_prosp_id`)                     
                   ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1  

I want to change the default charset of this table from latin1 to utf8. How to do that ?

Answer 1

If you want to change the table default character set and all character columns to a new character set, use a statement like this:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

So query will be:

ALTER TABLE etape_prospection CONVERT TO CHARACTER SET utf8;

Answer 2

The ALTER TABLE MySQL command should do the trick. The following command will change the default character set of your table and the character set of all its columns to UTF8.

ALTER TABLE etape_prospection CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

This command will convert all text-like columns in the table to the new character set. Character sets use different amounts of data per character, so MySQL will convert the type of some columns to ensure there's enough room to fit the same number of characters as the old column type.

I recommend you read the ALTER TABLE MySQL documentation before modifying any live data.

Answer 3

Change table's default charset:

ALTER TABLE etape_prospection
  CHARACTER SET utf8,
  COLLATE utf8_general_ci;

To change string column charset exceute this query:

ALTER TABLE etape_prospection
  CHANGE COLUMN etape_prosp_comment etape_prosp_comment TEXT CHARACTER SET utf8 COLLATE utf8_general_ci;

Answer 4

You can change the default with an alter table set default charset but that won't change the charset of the existing columns. To change that you need to use a alter table modify column.

Changing the charset of a column only means that it will be able to store a wider range of characters. Your application talks to the db using the mysql client so you may need to change the client encoding as well.

Answer 5

If someone is searching for a complete solution for changing default charset for all database tables and converting the data, this could be one:

DELIMITER $$

CREATE PROCEDURE `exec_query`(IN sql_text VARCHAR(255))
BEGIN
  SET @tquery = `sql_text`;
  PREPARE `stmt` FROM @tquery;
  EXECUTE `stmt`;
  DEALLOCATE PREPARE `stmt`;
END$$

CREATE PROCEDURE `change_character_set`(IN `charset` VARCHAR(64), IN `collation` VARCHAR(64))
BEGIN
DECLARE `done` BOOLEAN DEFAULT FALSE;
DECLARE `tab_name` VARCHAR(64);
DECLARE `charset_cursor` CURSOR FOR 
    SELECT `table_name` FROM `information_schema`.`tables`
    WHERE `table_schema` = DATABASE() AND `table_type` = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` = TRUE;

SET foreign_key_checks = 0;
OPEN `charset_cursor`;
`change_loop`: LOOP
FETCH `charset_cursor` INTO `tab_name`;
IF `done` THEN
    LEAVE `change_loop`;
END IF;
CALL `exec_query`(CONCAT(
  'ALTER TABLE `',
  tab_name,
  '` CONVERT TO CHARACTER SET ',
  QUOTE(charset),
  ' COLLATE ',
  QUOTE(collation),
  ';'
));
CALL `exec_query`(CONCAT('REPAIR TABLE `', tab_name, '`;'));
CALL `exec_query`(CONCAT('OPTIMIZE TABLE `', tab_name, '`;'));
END LOOP `change_loop`;
CLOSE `charset_cursor`;
SET foreign_key_checks = 1;
END$$

DELIMITER ;

You can place this code inside the file e.g. chg_char_set.sql and execute it e.g. by calling it from MySQL terminal:

SOURCE ~/path-to-the-file/chg_char_set.sql

Then call defined procedure with desired input parameters e.g.

CALL change_character_set('utf8mb4', 'utf8mb4_bin');

Once you've tested the results, you can drop those stored procedures:

DROP PROCEDURE `change_character_set`;
DROP PROCEDURE `exec_query`;