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”的特定索引,你应该在你的问题中指定你想用它做什么;根据目前的信息,不可能有更好的答案。