Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database which now needs to support 4 byte characters (Chinese). Luckily I already have MySQL 5.5 in production.

So I would just like to make all collations which are utf8_bin to utf8mb4_bin.

I believe there is no performance loss/gain with this change other than a bit of storage overhead.

share|improve this question
    
Use ALTER TABLE tbl_name [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name] –  Raihan Nov 22 '11 at 18:31
1  
I think he wants to make everything utf8mb4_bin, meaning tables and columns. –  Glen Solsberry Nov 22 '11 at 20:37

3 Answers 3

From How to support full Unicode in MySQL databases, here are the queries you can run to update the charset and collation of a database, a table, or a column:

# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a `VARCHAR` column.)

Note, however, that you cannot fully automate the conversion from utf8 to utf8mb4. As described in step 4 of the abovementioned guide, you’ll need to check the maximum length of columns and index keys, as the number you specify has a different meaning when utf8mb4 is used instead of utf8.

Section 10.1.11 of the MySQL 5.5 Reference Manual has some more information on this.

share|improve this answer
1  
According to the ALTER TABLE syntax (dev.mysql.com/doc/refman/5.5/en/alter-table.html) CONVERT TO handles conversion between the character sets for each column, modifying the column type where appropriate. I think that means ALTER-ing each column is redundant. Is there some reason each column must be ALTER-ed? –  CatShoes Aug 27 '13 at 19:23
    
@CatShoes: your point is supported by this discussion: dchua.com/2014/08/15/migrating-from-utf8-to-utf8mb4-in-mysql –  davide Mar 10 at 5:43

I would write a script (in Perl, or whatever) to use information_schema (TABLES and COLUMNS) to walk through all the tables, and do MODIFY COLUMN on every CHAR/VARCHAR/TEXT field. I would collect all the MODIFYs into a single ALTER for each table; this will be more efficient.

I think (but am not sure) that Raihan's suggestion only changes the default for the table.

share|improve this answer

I have a solution that will convert databases and tables by running a few commands. It also converts all columns of the type varchar, text, tinytext, mediumtext, longtext. You can add other types like char by simply extending the queries in the following code. You should also backup your database in case something breaks.

Copy the following code into a file called it preAlterTables.sql:

SELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql 
FROM `TABLES` where table_schema like "yourDbName" group by table_schema;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql  
FROM `TABLES` where table_schema like "yourDbName" group by table_schema, table_name;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql 
FROM `COLUMNS` where table_schema like "yourDbName" and data_type in ('varchar');
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql 
FROM `COLUMNS` where table_schema like "yourDbName" and data_type in ('text','tinytext','mediumtext','longtext');

Replace all occurences of "yourDbName" with the database you want to convert. Then run:

mysql -uroot < preAlterTables.sql | egrep '^ALTER' > alterTables.sql

This will generate a new file alterTables.sql, with all the queries you need to convert the database. Run the following command to start the conversion:

mysql -uroot < alterTables.sql

You can also adapt this to run through multiple databases, by changing the condition for the table_schema. For example table_schema like "wiki_%" will convert all databases with the name prefix wiki_. To convert all databases replace the condition with table_type!='SYSTEM VIEW'.

An issue that might arise. I had some varchar(255) columns in mysql keys. This causes an error:

ERROR 1071 (42000) at line 2229: Specified key was too long; max key length is 767 bytes

If that happens you can simply change the column to be smaller, like varchar(150), and rerun the command.

Please note: This answer converts the database to utf8mb4_unicode_ci instead of utf8mb4_bin, asked in the question. But you can simply replace this.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.