Java Liquibase：如何在 MySQL 数据库表上设置字符集 UTF-8？

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

提问by daydreamer

My Liquibasechangeset looks like

我的Liquibase变更集看起来像

<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 Mavenusing plugin
• When I run mvn clean install, it creates MySQLtable like

• 我已经Maven使用插件集成了 liquibase
• 当我运行时mvn clean install，它会创建MySQL类似于

CREATE TABLE network( network_idbigint(19) unsigned NOT NULL, namevarchar(300) NOT NULL, activetinyint(1) NOT NULL DEFAULT '1', created_attimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_byvarchar(100) DEFAULT NULL, updated_attimestamp NULL DEFAULT NULL, updated_byvarchar(100) DEFAULT NULL, PRIMARY KEY (network_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

创建表network( network_idbigint(19) unsigned NOT NULL, namevarchar(300) NOT NULL, activetinyint(1) NOT NULL DEFAULT '1', created_attimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_byvarchar(100) DEFAULT NULL, updated_attimestamp NULL DEFAULT NULL, updated_byvarchar(100) ) DEFAULT NULL, PRIMARY KEY ( network_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Everything looks good except CHARSET=latin1

一切看起来都不错，除了 CHARSET=latin1

Question

题

How can I make CHARSET=UTF-8?

我该怎么做CHARSET=UTF-8？

回答by MatthewJ

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

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

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

查看 MySql 文档，您可能只需插入以下行：

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

回答by tugelblend

You can use the modifySql elementafter your create table definition:

您可以在创建表定义后使用modifySql 元素：

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

回答by Ardi Goxhaj

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

通常字符集的配置是在数据库创建时完成的，你应该在你的 liquibase 配置文件中像这样配置它：

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

有关更多信息，请参阅：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?

如何将整个 MySQL 数据库字符集和排序规则转换为 UTF-8？

回答by Pavel S.

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 typeattr value of column):

如果需要按列指定字符集/排序规则（因为您可能需要为列设置不同的字符集），以下对我有用（只需将 CHARACTER SET 和 COLLATE 子句附加到typeattr 值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 SETbut not COLLATE.

您可以省略，CHARACTER SET但不能省略COLLATE。