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?

Solution 1

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>

Solution 2

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.

Solution 3

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>

Comments

[jso]

Which Liquibase version did you tested this with? Does not seem to work in 3.4.1

[Pavel S.]

@jso It was tested with version 3.5.3.

[psv]

does not work when you have foreign key(s) in the table

[Jatin Jha]

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.?

[cristy]

or this </createTable> <modifySql> <append value=" ENGINE=InnoDB DEFAULT CHARSET=utf8"/> </modifySql> </changeSet

[Emanuel George Hategan]

Am I right that these changes will need to be added manually? I'm hoping liquibase would detect this as part of liquibase:diff

[whistling_marmot]

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.