How to change the default charset of a MySQL table?

102

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 ?

This question is tagged with mysql sqlyog

~ Asked on 2012-01-18 07:48:21

The Best Answer is


218

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;

~ Answered on 2012-01-18 08:04:24


19

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;

~ Answered on 2012-01-18 07:58:31


Most Viewed Questions: