如何在MySQL中将逗号分隔的值分成几行? [英] How can I split comma separated value in MySQL into rows?

问题描述

我在MySQL中有一个函数，该函数返回由逗号分隔的整数值列表，从而形成一个字符串.

I have a function in MySQL that returns a list of integer values separated by a comma, thus forming a string.

例如

MyFunction(23);

返回

----------------------
|  MyFunction(23)    |
|--------------------|
|  34,45,87,23,1     |
----------------------

这是后端(Java)所需的简单功能-但现在，随着新功能的提出，我需要这些结果用于另一个查询，该查询可能正在使用 IN 关键字对它们进行迭代以下:

This was a simple functionality required by the backend (Java) - but now, as new features are being suggested, I need these results for another query which might be iterating over these using IN keyword like the following:

SELECT id, myItemId, myItemValue, myDateTime 
FROM 
items where id 
IN (select MAX(id) from items where myItemId=1  
    and myItemValue 
    IN (select MyFunction(123)) 
group by myItemId);

作为单个csv元素，MyFunction(23)的结果不可迭代.如果我可以使用Split(MyFunction(23))之类的函数来生成类似以下内容的结果，则无需过多更改即可完全适合我的查询.

Being a single csv element, the result of MyFunction(23) is not iterable. If I could use some function like Split(MyFunction(23)) which would generate results like the following will perfectly suit my queries without too much change.

这是我期望的结果:

-----------------------------
|  Split(MyFunction(23))    |
|---------------------------|
|  34                       |
|  45                       |
|  87                       |
|  23                       |
|  1                        |
-----------------------------

要注意的另一件事是MyFunction()可能返回空结果或具有单个元素(且没有逗号)的结果.

Another thing to take care is that MyFunction() might return empty result or a result with a single element (and no comma).

是否可以创建内置函数或简单函数来获得相同的功能?

Is there an inbuilt function or a simple function I can create to get the same?

这与关于stackoverflow的其他问题不同，因为我的要求非常简单，而派生其他解决方案将是一个过大的杀伤力.看起来类似于split()函数或非常简单的实现.

This is different than the other questions on stackoverflow as my requirement is very simple and derivations of other solutions will be an overkill. Looking something similar to split() function or a very simple implementation.

推荐答案

您可以使用FIND_IN_SET函数.例如:

You can use FIND_IN_SET function. For example:

SELECT FIND_IN_SET('34', '34,45,87,23,1'); -- return true

SELECT FIND_IN_SET('34', '34'); -- return true    

SELECT FIND_IN_SET('34', ''); -- return false

在您的情况下，SQL查询将如下所示:

In your case, the SQL query will look like:

SELECT id,
       myItemId,
       myItemValue,
       myDateTime
FROM items
WHERE id IN
    (SELECT MAX(id)
     FROM items
     WHERE myItemId=1
       AND FIND_IN_SET(myItemValue, MyFunction(123))
     GROUP BY myItemId);

这篇关于如何在MySQL中将逗号分隔的值分成几行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！