UNNEST function in MYSQL such as POSTGRESQL

Question

Is there a function that "disconnects" from POSTGRESQL in MYSQL?

Query (PSQL):

select unnest('{1,2,3,4}'::int[]) 

Result (in the form of a table):

  int | _____| 1 | _____| 2 | _____| 3 | _____| 4 | _____| 

Answer 1

Short answer

Yes it is possible. From a technical point of view, you can achieve this with a single request. But the fact is that, most likely, you are trying to transfer some logic from the application to the data warehouse. The data warehouse is intended for data storage, but not for presentation / formatting or, more importantly, apply some logic to it.

Yes, MySQL does not have a data type of arrays, but in most cases this is not a problem, and an architecture can be created to meet these restrictions. And in any case, even if you somehow achieve it (for example, see below), you will not be able to work correctly later with this data, as this will simply be the result. You can save it, of course - so, let's say, index it later, but then again the task for the application is to create this import.

Also, make sure that this is not the case of Jaywalker, so it’s not about storing values ​​separated by delimiters, and then trying to extract them.

Long answer

From a technical point of view, you can do this using the Cartesian product of two sets of strings. Then use the famous formula:

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

Thus, you can create a table "all numbers", and then repeat it. This iteration along with MySQL string functions can lead you to something like this:

 SELECT data FROM ( 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 ) AS joins WHERE marker IS NOT NULL 

The corresponding script will be here .

You should also note: this is not a function. And with functions (I mean, user-defined using CREATE FUNCTION ), it is impossible to get a set of result rows, since a function in MySQL cannot return a result set by definition. However, it cannot be said that it is impossible to fully complete the requested conversion with MySQL.

But remember: if you can do something, this does not mean that you should do it.