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 ?

The correct name for "UTF8" in MySQL is "utf8mb4" the charset "utf8" is broken, it supports only up to 3 byte characters. See the mysql manual for details, or google "mysql" and "utf8"... https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html — Samuel Åslund, Dec 17 '18 at 12:53

score 229 · Accepted Answer · answered Jan 18 '12 at 08:04

229

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 Jan 18 '12 at 08:04

25

By contrast, if you just use the `ALTER TABLE tbl CHARACTER SET utf8` syntax as suggested by others, you will change only the default encoding for table; existing columns will not be converted as they will if you use this answer. – eaj Jan 27 '14 at 13:57
8

If you want to apply this change for all tables that are not encoded in ut8 in a database, you can use this query and execute the resulted queries : `SELECT concat('alter table ', table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.tables WHERE table_schema='' and table_collation != 'utf8_general_ci' GROUP BY table_name;` – Maxooo Oct 09 '14 at 08:11
3

this is not changing the **default** charset. to change the default do as eak said `ALTER TABLE tbl CHARACTER SET utf8` – Accountant م Sep 22 '16 at 20:18
If you get a `Duplicate entry` error when you try to do this, you can do `ALTER IGNORE TABLE ...` to ignore and drop duplicate entries. – gitaarik Feb 02 '17 at 16:01
...CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci to make all your fields in the table uft8 also. – darkgaze Apr 12 '17 at 09:02
8

I would like to add that usually you don't want to use utf8 but utf8mb4 instead to get what you might expect utf8 to be. To explain: in MySQL utf8 is really just a subset of utf8, which would be better named utf8mb3. It can only encode up to 3 bytes of utf8 characters instead of the up to 4 bytes that are specified. This means that many emoji will not be encodable and will just be lost if you try to write them into the DB. See eg. https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434 for details. – dwt Feb 19 '18 at 13:58
8

for multibyte you can use `ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;` – RN Kushwaha Sep 04 '18 at 07:07
Is it expected that this query take a very long (~5 minutes) to run? – Raj Nov 14 '18 at 22:34
This is actually not an answer to the question, it's good information, but the question was changing the default charset. There are many good reasons to not want to take the risk of changing the data when trying to stop digging a deeper hole. – Samuel Åslund Dec 17 '18 at 13:06

score 19 · Answer 2 · answered Jan 18 '12 at 07:58

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 Jan 18 '12 at 07:58

Devart

110,991
22
156
173

14

You should never, ever use utf8_general_ci: it simply doesn’t work. It’s a throwback to the bad old days of ASCII stooopeeedity from fifty years ago. Unicode case-insensitive matching cannot be done without the foldcase map from the UCD. For example, “Σίσυφος” has three different sigmas in it; or how the lowercase of “TSCHüẞ” is “tschüβ”, but the uppercase of “tschüβ” is “TSCHÜSS”. You can be right, or you can be fast. Therefore you must use utf8_unicode_ci, because if you don’t care about correctness, then it’s trivial to make it infinitely fast. – Yohanes AI Apr 01 '17 at 15:41
3

MySQL UTF8 charset is broken, you need to use utf8mb4 ! – Samuel Åslund Dec 17 '18 at 13:07

piersadrian · Answer 3 · 2018-01-06T15:14:26.630

7

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.

edited Jan 06 '18 at 15:14

answered Jan 18 '12 at 07:57

piersadrian

1,398
10
11

1

Yes, it does do the trick, but one thing is different with the **convert to** way: it can not remove the old charset option from columns automatically. – tech_me Jul 12 '14 at 09:10

score 4 · Answer 4 · edited Sep 09 '19 at 11:45

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`;

There is a missing condition which leads us to select views too. It's WHERE table_schema = DATABASE(); => WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE'; But thanks!! This is what I need! — nguyenhoai890, Jun 11 '19 at 12:20

score 2 · Answer 5 · edited Jan 18 '12 at 09:01

2

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.

edited Jan 18 '12 at 09:01

Aufwind

22,034
33
94
149

answered Jan 18 '12 at 08:02

Joni

101,441
12
123
178

I would if it wasn't for the limited keyboard on the iPod touch :-) – Joni Jan 18 '12 at 08:15
well the iPod Touch/iPhone *must* have *some* drawback. :-P Never realised that they are missing until you mentioned it. ;-) – Aufwind Jan 18 '12 at 08:33
Hold down the button for the quote character to get to see the backquote character. – Chloe Mar 18 '19 at 20:23

How to change the default charset of a MySQL table?

5 Answers5

Linked

Related