将MySQL SET数据类型转换为Postgres [英] convert MySQL SET data type to Postgres

问题描述

我正在尝试将MySQL数据库转换为Postgres.这很令人沮丧，但一直在稳步进行.我遇到的一个问题是将MySQL SET数据类型转换为Postgres. MySQL中的SET数据类型与普通的ENUM类型不同，并且不能使用CHECK约束进行仿真.

I am trying to convert a MySQL database to Postgres. It is frustrating, but proceeding along steadily. One issue that has me stumped is to convert the MySQL SET data type to Postgres. A SET data type in MySQL is not the same as the plain ENUM type, and can't be emulated with a CHECK constraint.

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

As far as I understand, a SET type allows storing zero or more values from the set in the column. So, something like the following in MySQL

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

将允许以下任意值作为有效值

will allow any of the following as valid values

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

，依此类推. Postgres的近似值是

and so on. A close approximation in Postgres is

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

但以上不允许红色，蓝色"或绿色，红色"等等.

but the above doesn't allow 'red,blue' or 'green,red' and so on.

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

Of course, the above is just a simplification. The actual database is fairly more complicated with about half a dozen columns defined as SET.

建议?

推荐答案

您可以为该列使用数组，并为运算符包含array.html#ARRAY-OPERATORS-TABLE"rel =" noreferrer>":

You could use an array for the column and an "is contained by" operator for the CHECK constraint:

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约束:

This will allow {red,red} in the column though; if disallowing {red,red} is important, then you could add a function to check for unique color values in the array and adjust the CHECK constraint:

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版本的函数:

Another option would be a pile of association tables with simple scalar values in the columns. However, this might be cumbersome if you have six of these columns. You could also use Erwin's version of the function if you needed to worry about NULLs in the "sets":

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;

这篇关于将MySQL SET数据类型转换为Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！