mysql - 将 MySQL SET 数据类型转换为 Postgres

Question

我正在尝试将 MySQL 数据库转换为 Postgres。这是令人沮丧的，但稳步前进。一个让我难过的问题是将 MySQL SET 数据类型转换为 Postgres。 MySQL 中的 SET 数据类型与普通的 ENUM 类型不同，不能用 CHECK 约束模拟。

据我了解，SET 类型允许在列中存储零个或多个集合中的值。所以，在 MySQL 中类似下面的内容

CREATE TABLE foo (color SET('red','green','blue'));

将允许以下任何一个作为有效值

''
'red'
'red,blue'
'green,red'

等等。 Postgres 中的近似值是

CREATE TABLE foo (
    color VARCHAR(10) NOT NULL, 
    CHECK (color IN ('red','green','blue'))
);

但上面不允许 'red,blue' 或 'green,red' 等等。

当然，以上只是一种简化。实际的数据库相当复杂，大约有六个列定义为 SET。

建议？

Answer 1

您可以为列使用数组，并使用 "is contained by" CHECK 约束的运算符:

create table pancakes (
    color varchar(10)[] not null,
    check (color <@ ARRAY['red', 'green', 'blue']::varchar[])
);

然后像这样的事情发生了:

=> insert into pancakes values (ARRAY['red']);
INSERT 0 1
=> insert into pancakes values (ARRAY['red','green','blue']);
INSERT 0 1
=> insert into pancakes values (ARRAY['red','green','blue','black']);
ERROR:  new row for relation "pancakes" violates check constraint "pancakes_color_check"
=> select * from pancakes;
      color       
------------------
 {red}
 {red,green,blue}
(2 rows)

虽然这将允许列中的 {red,red}；如果不允许 {red,red} 很重要，那么您可以添加一个函数来检查数组中的唯一颜色值并调整 CHECK 约束:

create function has_unique_colors(varchar[]) returns boolean as $$
    select (select count(distinct c) from unnest($1) as dt(c)) = array_length($1, 1);
$$ language sql;

create table pancakes (
    color varchar(10)[] not null,
    check (color <@ ARRAY['red', 'green', 'blue']::varchar[] and has_unique_colors(color))
);

另一种选择是一堆在列中具有简单标量值的关联表。但是，如果您有六个这样的列，这可能会很麻烦。如果您需要担心“集合”中的 NULL，您也可以使用 Erwin 版本的函数:

create function has_unique_colors(varchar[]) returns boolean as $$
    select not exists(select c from unnest($1) dt(c) group by 1 having count(*) > 1);
$$ language sql;