MySQL根据另一个表中的id数组从一个表中选择值

I found many PHP examples which use map and implode to structure their string...I'm using Node and Javascript and I think that might be the problem. I have an array of ids and I'm saving it like this:

我发现很多PHP示例使用map和implode来构造他们的字符串...我正在使用Node和Javascript,我认为这可能是问题所在。我有一系列的ID,我就像这样保存它:

[1, 2, 3, 4].join();

That's being stored and I'm trying to run a query:

那是存储的,我正在尝试运行查询:

SELECT sr.id, sr.name, sr.city FROM table1 AS sr 
JOIN table2 AS st WHERE sr.city = st.city AND sr.id IN (st.ids);

So both tables have a column city, table2 having multiple records, table1 only has one. The 'ids' column is of type TEXT in table2. Problem is it's only returning the row with id 1. If I do this:

因此,两个表都有一个列城市,table2有多个记录,table1只有一个。 'ids'列在表2中是TEXT类型。问题是它只返回id为1的行。如果我这样做:

SELECT sr.id, sr.name, sr.city FROM table1 AS sr 
JOIN table2 AS st WHERE sr.city = st.city AND sr.id IN (1,2);

I will get rows 1 and 2. What's causing the problem? I tried [1, 2, 3, 4].join(', '); and that didn't help. Any suggestions?

我会得到第1行和第2行。是什么导致了这个问题?我试过[1,2,3,4] .join(',');那并没有帮助。有什么建议?

1 个解决方案

#1

I'm using MySQL v5.7 so I have access to JSON functions...I should've used this in the first place. The solution which works as I wanted is:

我正在使用MySQL v5.7,因此我可以访问JSON函数......我应该首先使用它。我想要的解决方案是:

SELECT sr.id, sr.name, sr.city FROM table1 AS sr 
JOIN table2 AS st WHERE sr.city = st.city AND 
JSON_SEARCH(st.ids, 'one', sr.id) IS NOT NULL;

Love the new JSON support feature...altho it comes with its caveats. For example, the ids in the array have to be strings, the ids cannot be int's. JSON_SEARCH kept on returning NULL for paths to the ids until I converted them to strings. Trial and error...

喜欢新的JSON支持功能......但它附带了它的警告。例如,数组中的id必须是字符串,id不能是int。 JSON_SEARCH继续为id的路径返回NULL,直到我将它们转换为字符串。试错了......