Liquibase: How to set Charset UTF-8 on MySQL database tables?

Question

My Liquibase changeset looks like

<changeSet id="05192014.1525" author="h2">
        <createTable tableName="network">
            <column name="network_id" type="BIGINT(19) UNSIGNED">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="name" type="VARCHAR(300)">
                <constraints nullable="false"/>
            </column>
            <column name="active" type="TINYINT(1)" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="created_at" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP">
                <constraints nullable="false"/>
            </column>
            <column name="created_by" type="VARCHAR(100)"/>
            <column name="updated_at" type="TIMESTAMP"/>
            <column name="updated_by" type="VARCHAR(100)"/>
        </createTable>
    </changeSet>

I have integrated liquibase with Maven using plugin
When I run mvn clean install, it creates MySQL table like

CREATE TABLE network ( network_id bigint(19) unsigned NOT NULL, name varchar(300) NOT NULL, active tinyint(1) NOT NULL DEFAULT '1', created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by varchar(100) DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL, updated_by varchar(100) DEFAULT NULL, PRIMARY KEY (network_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Everything looks good except CHARSET=latin1

Question

How can I make CHARSET=UTF-8?

Have you tried to [force-replace](http://www.liquibase.org/documentation/modify_sql.html) it to the required value? — Oleg Estekhin, May 20 '14 at 04:05

score 15 · Answer 1 · edited Aug 23 '16 at 10:53

15

You can use the modifySql element after your create table definition:

</createTable>
<modifySql dbms="mysql">
    <append value="ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_unicode_ci"/>
</modifySql>

edited Aug 23 '16 at 10:53

Riad

3,822
5
28
39

answered Apr 17 '15 at 13:57

tugelblend

197
3
11

Is it possible to write inside a separate as on starting the db migration process, it will fire an error if the database is already deployed elsewhere.? – Jatin Jha Mar 29 '18 at 06:55
or this ` – cristy Jun 29 '18 at 02:31
Am I right that these changes will need to be added manually? I'm hoping liquibase would detect this as part of `liquibase:diff` – Emanuel George Hategan Dec 05 '18 at 16:01

Pavel S. · Answer 2 · 2017-12-20T10:18:22.753

10

If needing per-column specification of charset/collate (as you may want different charsets for your columns), the following has worked for me (just appending CHARACTER SET and COLLATE clauses to the type attr value of column):

    <createTable tableName="my_table">
        <column name="some_column" type="VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" >
            <constraints nullable="false"/>
        </column>
    </createTable>

Alternatively, you can use:

    <createTable tableName="my_table">
        <column name="some_column" type="VARCHAR(20)" >
            <constraints nullable="false"/>
        </column>
    </createTable>
    <modifySql dbms="mysql">
        <replace replace="VARCHAR(20)" with="VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" />
    </modifySql>

You can omit CHARACTER SET but not COLLATE.

edited Dec 20 '17 at 10:18

answered Apr 28 '17 at 15:53

Pavel S.

1,202
1
13
29

Which Liquibase version did you tested this with? Does not seem to work in 3.4.1 – jso Aug 02 '17 at 16:44
@jso It was tested with version 3.5.3. – Pavel S. Aug 03 '17 at 10:49
The second worked for me. The modifySql must come after all the createTable elements, and you may need to move other elements into another changeSet. – whistling_marmot Feb 13 '20 at 13:39

score 2 · Answer 3 · edited Aug 15 '14 at 15:05

2

From looking at the documenation, Charset is database dependent, and if that is the case, judging from the documentation you can use

http://www.liquibase.org/documentation/changes/sql.html

Looking at the MySql documentation you could probably just plug this line in:

<sql>ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;</sql>

edited Aug 15 '14 at 15:05

Matt Ball

354,903
100
647
710

answered May 20 '14 at 04:06

MatthewJ

3,127
2
27
34

does not work when you have foreign key(s) in the table – psv Jan 15 '18 at 12:57

score 0 · Answer 4 · edited May 23 '17 at 10:31

Usually the configuration of the charset is done on database creation time, and you should configure it on your liquibase config file like this:

url: jdbc:mysql://localhost:3306/yourdatabasename?useUnicode=true&characterEncoding=utf8

for more info see: https://dev.mysql.com/doc/refman/5.0/en/charset-applications.html

or if you already have a database created with a different charset you could try to convert it see:

How to convert an entire MySQL database characterset and collation to UTF-8?

Liquibase: How to set Charset UTF-8 on MySQL database tables?

4 Answers4