迁移脚本中字符串比较如何使用相同排序规则？

问题场景 #

应用部署在多数据库环境，使用Liquibase编写迁移脚本。近期的迁移逻辑是将大表ExampleTable的varchar列exampleName迁移至独立表NameTable，并改用引用字段exampleName_id替代。本地测试（包含MySQL环境）均正常，但在某台MySQL机器上执行迁移时触发排序规则不兼容错误：

Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
[Failed SQL: (1267) UPDATE myschema.ExampleTable SET exampleName_id = (SELECT n.id FROM NameTable n WHERE n.name = ExampleTable.exampleName)]

问题原因 #

新表NameTable.name的排序规则与原表ExampleTable.exampleName不一致，推测是MySQL版本升级导致默认排序规则变化。虽然显式指定排序规则能临时解决问题，但存在两个弊端：无法提前知晓各机器的列排序规则，且脚本无法跨SQL方言通用。

现有迁移脚本 #

<createTable tableName="NameTable">
    <column name="id" type="${type.bigint}" autoIncrement="true">
        <constraints primaryKey="true" nullable="false" />
    </column>
    <column name="name" type="varchar(255)">
        <constraints nullable="false" />
    </column>
</createTable>

<addUniqueConstraint tableName="NameTable" constraintName="unique_name" columnNames="name" />

<sql>INSERT INTO NameTable (name) SELECT DISTINCT exampleName FROM ExampleTable</sql>

<addColumn tableName="ExampleTable">
    <column name="exampleName_id" type="${type.bigint}"
            valueComputed="(SELECT n.id FROM NameTable n WHERE n.name = ExampleTable.exampleName)" />
</addColumn>

<addNotNullConstraint tableName="ExampleTable" columnName="exampleName_id" columnDataType="${type.bigint}" />

<addForeignKeyConstraint constraintName="FK_ExampleTable_exampleName_id" 
                         baseTableName="ExampleTable" baseColumnNames="exampleName_id"
                         referencedTableName="NameTable" referencedColumnNames="id" />

<dropColumn tableName="ExampleTable" columnName="exampleName" />

尝试的临时方案 #

通过显式指定排序规则规避错误，但无法适配多环境：

<addColumn tableName="ExampleTable">
    <column name="exampleName_id" type="${type.bigint}"
            valueComputed="(SELECT n.id FROM NameTable n WHERE n.name = ExampleTable.exampleName COLLATE utf8mb4_unicode_ci)" />
</addColumn>

可行解决方案 #

1. 创建新表时继承原列的排序规则（推荐） #

通过查询information_schema动态获取原列的排序规则，创建NameTable时直接沿用，从根源避免不兼容问题：

<!-- 替换原createTable逻辑 -->
<sql>
CREATE TABLE NameTable (
    id ${type.bigint} AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name varchar(255) NOT NULL COLLATE (SELECT collation_name FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'ExampleTable' AND column_name = 'exampleName')
);
</sql>

<addUniqueConstraint tableName="NameTable" constraintName="unique_name" columnNames="name" />

该方案仅针对MySQL生效，其他数据库会自动忽略COLLATE子句（或无需处理），不影响跨方言兼容性。

2. 查询时动态匹配原列排序规则 #

如果不想修改表结构，可在关联查询的SQL中动态获取原列排序规则，确保比较时使用一致规则：

<addColumn tableName="ExampleTable">
    <column name="exampleName_id" type="${type.bigint}"
            valueComputed="(SELECT n.id FROM NameTable n WHERE n.name = ExampleTable.exampleName COLLATE (SELECT collation_name FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'ExampleTable' AND column_name = 'exampleName'))" />
</addColumn>

3. Liquibase属性适配（可选） #

通过Liquibase的数据库专属属性，针对MySQL动态设置排序规则，兼顾脚本可读性和兼容性：

<!-- 定义MySQL专属的排序规则属性 -->
<property name="mysql.name.collation" 
          value="COLLATE (SELECT collation_name FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'ExampleTable' AND column_name = 'exampleName')" 
          dbms="mysql"/>

<createTable tableName="NameTable">
    <column name="id" type="${type.bigint}" autoIncrement="true">
        <constraints primaryKey="true" nullable="false" />
    </column>
    <!-- 仅MySQL生效，其他数据库忽略该属性 -->
    <column name="name" type="varchar(255)" ${mysql.name.collation:}>
        <constraints nullable="false" />
    </column>
</createTable>

内容的提问来源于stack exchange，提问作者Tobias Liefke