How to modify mariadb chartset at once?

Question

I am developing with mariadb and Spring, JdbcTemplate.

At first, we made DB charset as utf8, but now we have to change it into utf8mb4 because of emojis.

Till now I update individual charset with query something like below.

ALTER TABLE WT_WORKS CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE WT_WORKS CHANGE WORKS_TITLE WORKS_TITLE VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE WT_WORKS CHANGE WORKS_DESC WORKS_DESC VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

However It is now efficient because of table relations. For example when I insert into WT_WORKS, also need to into WT_WRITERS. It looks impossible to find every tables and columns.

So I want to know change these at once.(Including Procedures and Functions). -- something like (v_name VARCHAR(10)) into (v_name VARCHAR(10) CHARSET utf8mb4).

Thanks for answer.

FYI. my my.cnf got follow settings

[client]
default-character-set=utf8mb4

[mysqld]
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4

[client]
default-character-set = utf8mb4

score 0 · Accepted Answer · answered Apr 17 '17 at 03:45

SELECT  DISTINCT TABLE_SCHEMA, TABLE_NAME
    FROM  information_schema.COLUMNS
    WHERE  CHARACTER_SET_NAME = 'utf8'

will list all the tables that still have some column set to utf8. When them, do ALTER TABLE .. CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

This should generate all the ALTERs you need:

SELECT  DISTINCT
        CONCAT(
            "ALTER TABLE ", TABLE_SCHEMA, ".", TABLE_NAME,
            "  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;"
              )
    FROM  information_schema.COLUMNS
    WHERE  CHARACTER_SET_NAME = 'utf8'

Then copy and paste them into the mysql command line tool.

Caveat: If you have some columns with charsets other than utf8, they will be blindly converted to utf8mb4. This would be bad for hex, ascii, etc., columns, such as country_code, uuid, md5, etc.

You could do something similar to change individual columns instead.

You do not need to do both.

It looks like can not fix with just one query. Thanks though. — Juneyoung Oh, Apr 25 '17 at 08:37
True. One query would generate a list of many `ALTERs`, which would then need to be run. — Rick James, Apr 26 '17 at 06:25

How to modify mariadb chartset at once?

1 Answers1