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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] How to change the default charset of a MySQL table?

Examples related to mysql

Examples related to sqlyog