Oracle数据库字符串数组类型选型及Liquibase配置问题

Oracle不支持PostgreSQL风格的varchar(30)[]语法，要存储字符串数组，你可以选择以下几种方案：

方案1：使用自定义嵌套表类型 #

Oracle需要先定义集合类型，才能在表中使用数组列：

1. 创建自定义集合类型 #

添加changeSet创建嵌套表类型：

<changeSet id="1639718925269-0" author="bharatsuthar">
    <sql>
        CREATE TYPE permission_list AS TABLE OF VARCHAR2(30);
    </sql>
    <rollback>
        DROP TYPE permission_list;
    </rollback>
</changeSet>

2. 修改列类型为自定义集合 #

更新addColumn的changeSet：

<changeSet id="1639718925269-1" author="bharatsuthar">
    <addColumn tableName="users">
        <column name="permissions" type="permission_list"/>
    </addColumn>
</changeSet>

3. 插入数组数据 #

用valueComputed调用集合构造函数插入数据：

<changeSet id="1639718925270-1" author="sahilgoel">
    <insert tableName="users">
        <column name="user_id" value="hgw-rdk@telekom-digital.com"/>
        <column name="created_at" valueComputed="((SYSDATE - date '1970-01-01')*24*60*60*1000)"/>
        <column name="modified_at" valueComputed="((SYSDATE - date '1970-01-01')*24*60*60*1000)"/>
        <column name="permissions" valueComputed="permission_list('DOWNLOAD_PHASE_II_FILE','DOWNLOAD_PHASE_III_FILE','DOWNLOAD_MERGED_FILE','DOWNLOAD_ERROR_FILE')"/>
    </insert>
</changeSet>

方案2：使用JSON类型（Oracle 12c+） #

如果你的Oracle版本是12c及以上，直接用JSON类型更简单，无需自定义类型：

1. 添加JSON列 #

<changeSet id="1639718925269-1" author="bharatsuthar">
    <addColumn tableName="users">
        <column name="permissions" type="JSON"/>
    </addColumn>
</changeSet>

2. 插入JSON数组 #

<changeSet id="1639718925270-1" author="sahilgoel">
    <insert tableName="users">
        <column name="user_id" value="hgw-rdk@telekom-digital.com"/>
        <column name="created_at" valueComputed="((SYSDATE - date '1970-01-01')*24*60*60*1000)"/>
        <column name="modified_at" valueComputed="((SYSDATE - date '1970-01-01')*24*60*60*1000)"/>
        <column name="permissions" value='["DOWNLOAD_PHASE_II_FILE","DOWNLOAD_PHASE_III_FILE","DOWNLOAD_MERGED_FILE","DOWNLOAD_ERROR_FILE"]'/>
    </insert>
</changeSet>

方案3：使用关联表（符合数据库范式） #

如果不想用非标准类型，推荐用关联表存储权限，这是关系型数据库的标准设计方式：

1. 创建用户权限关联表 #

<changeSet id="1639718925269-0" author="bharatsuthar">
    <createTable tableName="user_permissions">
        <column name="user_id" type="VARCHAR2(100)">
            <constraints foreignKeyName="fk_user_permissions_user" references="users(user_id)" nullable="false"/>
        </column>
        <column name="permission" type="VARCHAR2(30)" nullable="false"/>
        <constraints primaryKeyName="pk_user_permissions" primaryKeyColumns="user_id, permission"/>
    </createTable>
</changeSet>

2. 插入用户及权限数据 #

<changeSet id="1639718925270-1" author="sahilgoel">
    <insert tableName="users">
        <column name="user_id" value="hgw-rdk@telekom-digital.com"/>
        <column name="created_at" valueComputed="((SYSDATE - date '1970-01-01')*24*60*60*1000)"/>
        <column name="modified_at" valueComputed="((SYSDATE - date '1970-01-01')*24*60*60*1000)"/>
    </insert>
    <insert tableName="user_permissions">
        <column name="user_id" value="hgw-rdk@telekom-digital.com"/>
        <column name="permission" value="DOWNLOAD_PHASE_II_FILE"/>
    </insert>
    <insert tableName="user_permissions">
        <column name="user_id" value="hgw-rdk@telekom-digital.com"/>
        <column name="permission" value="DOWNLOAD_PHASE_III_FILE"/>
    </insert>
    <insert tableName="user_permissions">
        <column name="user_id" value="hgw-rdk@telekom-digital.com"/>
        <column name="permission" value="DOWNLOAD_MERGED_FILE"/>
    </insert>
    <insert tableName="user_permissions">
        <column name="user_id" value="hgw-rdk@telekom-digital.com"/>
        <column name="permission" value="DOWNLOAD_ERROR_FILE"/>
    </insert>
</changeSet>

内容的提问来源于stack exchange，提问作者EV Experience