Unnest Function In Mysql Like Postgresql

Is there a function like 'unnest' from POSTGRESQL on MYSQL? Query (PSQL): select unnest('{1,2,3,4}'::int[]) Result (as table): int | _____| 1 | _____| 2 | _____| 3 | ___

Solution 1:

Short answer

Yes, it is possible. From technical viewpoint, you can achieve that with one query. But the thing is - most probably, you are trying to pass some logic from application to data storage. Data storage is intended to store data, not to represent/format it or, even more, apply some logic to it.

Yes, MySQL doesn't have arrays data type, but in most cases it won't be a problem and architecture can be created so it will fit those limitations. And in any case, even if you'll achieve it somehow (like - see below) - you won't be possible to properly work later with that data, since it will be just result set. You may store it, of course - so to, let's say, index later, but then it's again a task for an application - so to create that import.

Also, make sure that it is not a Jaywalker case, so not about storing delimiter-separated values and later trying to extract them.

Long answer

From technical viewpoint, you can do it with Cartesian product of the two row sets. Then use a well known formula:

N = d₁x10 + d₂x10 + ...

Thus, you'll be able to create a "all-numbers" table and later iterate through it. That iteration, together with MySQL string functions, may lead you to something like this:

SELECTdataFROM (
  SELECT 
    @next:=LOCATE(@separator,@search, @current+1) AS next, 
    SUBSTR(SUBSTR(@search, @current, @next-@current), @length+1) AS data, 
    @next:=IF(@next, @next, NULL) AS marker, 
    @current:=@next AS current 
  FROM 
    (SELECT 0 as i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as n1    
    CROSS JOIN 
    (SELECT 0 as i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as n2 
    CROSS JOIN 
    (SELECT 
       -- set your separator here:
       @separator := ',', 
       -- set your string here:
       @data      := '1,25,42,71',
       -- and do not touch here:
       @current   := 1,
       @search    := CONCAT(@separator, @data, @separator), 
       @length    := CHAR_LENGTH(@separator)) AS init
    ) ASjoinsWHEREmarkerISNOTNULL

The corresponding fiddle would be here.

You should also notice: this is not a function. And with functions (I mean, user-defined with CREATE FUNCTION statement) it's impossible to get result row set since function in MySQL can not return result set by definition. However, it's not true to say that it's completely impossible to perform requested transformation with MySQL.

But remember: if you are able to do something, that doesn't mean you should do it.

Solution 2:

This sample fetchs all "catchwords" from Table data, wich are seperated by "," Maximum values in the commaseparated list is 100

WITHRECURSIVE num (n) AS (
      SELECT1UNIONALLSELECT n+1FROM num WHERE n<100-- change this, if more than 100 elements
    )
    SELECTDISTINCT substring_index(substring_index(catchwords, ',', n), ',', -1) asvalueFROM data
    JOIN num
      ONchar_length(catchwords) -char_length(replace(catchwords, ',', '')) >= n -1