相当于postgresql的mysql FIND_IN_SET

时间:2023-01-31 14:25:04
select * 
from folder f,uploads u 
where u.id=f.folderId 
and FIND_IN_SET('8', '15,9,13,27')

Please tell to me equivalent to predefind or userdefined postgresql function

请告诉我等价于predefind或userdefined postgresql函数。

3 个解决方案

#1


5  

You shouldn't be storing comma separated values in the first place, but you can do something like this:

您不应该首先存储逗号分隔的值,但是您可以这样做:

select * 
from folder f
  join uploads u ON u.id = f.folderId 
where '8' = ANY (string_to_array(some_column,','))

string_to_array() converts a string into a real array based on the passed delimiter

string_to_array()基于传递的分隔符将字符串转换为真实的数组

#2


0  

    select * 
       from folder f,uploads u 
          where u.id=f.folderId and '8' in('15','9','13','27')

#3


0  

The FIND_IN_SET() function in MySQL applies - not surprisingly - to sets. The equivalent of a MySQL SET in PostgreSQL is the enum type, with some minor differences in implementation.

MySQL中的FIND_IN_SET()函数应用于集合——这并不奇怪。与PostgreSQL中的MySQL集等价的是enum类型,在实现上有一些细微的差异。

The FIND_IN_SET() function returns the index of an item in the set, or 0 if not present in the set. That is logically non-sensical: "a set is an abstract data type that can store certain values, without any particular order, and no repeated values". PostgreSQL has no built-in way to find the order of an item in an enum type, it doesn't even have a way to find out if a string is also an item in an enum type. And that is just how it should be.

函数的作用是:返回集合中项的索引,如果集合中没有项,返回0。PostgreSQL没有找到enum类型中项的顺序的内置方法,它甚至没有找到字符串是否也是enum类型中的项的方法。这就是它应该的样子。

If you are working with "sets" of strings in a less restricted sense, you probably want to use a text[] data type for your column. Your query then becomes, assuming you test just for the presence of a value in the array:

如果您使用的是不受限制的字符串“集合”,那么您可能需要为列使用文本[]数据类型。然后您的查询变成,假设您测试的只是数组中是否存在一个值:

SELECT * 
FROM folder f
JOIN uploads u ON u.id = f.folderId 
WHERE '8' = ANY (text_array_column);

If you want the specific index of '8' in the text array column you should specify in your question what you want to do with it; with the current information a better answer is impossible.

如果你想要文本数组中“8”的特定索引,你应该在你的问题中指定你想用它做什么;根据目前的信息,不可能有更好的答案。

#1


5  

You shouldn't be storing comma separated values in the first place, but you can do something like this:

您不应该首先存储逗号分隔的值,但是您可以这样做:

select * 
from folder f
  join uploads u ON u.id = f.folderId 
where '8' = ANY (string_to_array(some_column,','))

string_to_array() converts a string into a real array based on the passed delimiter

string_to_array()基于传递的分隔符将字符串转换为真实的数组

#2


0  

    select * 
       from folder f,uploads u 
          where u.id=f.folderId and '8' in('15','9','13','27')

#3


0  

The FIND_IN_SET() function in MySQL applies - not surprisingly - to sets. The equivalent of a MySQL SET in PostgreSQL is the enum type, with some minor differences in implementation.

MySQL中的FIND_IN_SET()函数应用于集合——这并不奇怪。与PostgreSQL中的MySQL集等价的是enum类型,在实现上有一些细微的差异。

The FIND_IN_SET() function returns the index of an item in the set, or 0 if not present in the set. That is logically non-sensical: "a set is an abstract data type that can store certain values, without any particular order, and no repeated values". PostgreSQL has no built-in way to find the order of an item in an enum type, it doesn't even have a way to find out if a string is also an item in an enum type. And that is just how it should be.

函数的作用是:返回集合中项的索引,如果集合中没有项,返回0。PostgreSQL没有找到enum类型中项的顺序的内置方法,它甚至没有找到字符串是否也是enum类型中的项的方法。这就是它应该的样子。

If you are working with "sets" of strings in a less restricted sense, you probably want to use a text[] data type for your column. Your query then becomes, assuming you test just for the presence of a value in the array:

如果您使用的是不受限制的字符串“集合”,那么您可能需要为列使用文本[]数据类型。然后您的查询变成,假设您测试的只是数组中是否存在一个值:

SELECT * 
FROM folder f
JOIN uploads u ON u.id = f.folderId 
WHERE '8' = ANY (text_array_column);

If you want the specific index of '8' in the text array column you should specify in your question what you want to do with it; with the current information a better answer is impossible.

如果你想要文本数组中“8”的特定索引,你应该在你的问题中指定你想用它做什么;根据目前的信息,不可能有更好的答案。