像POSTGRESQL这样的MYSQL中的UNNEST函数 [英] UNNEST function in MYSQL like POSTGRESQL

问题描述

在MySQL上，是否存在POSTGRESQL中的"unnest"之类的功能?

Is there a function like "unnest" from POSTGRESQL on MYSQL?

查询(PSQL):

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

结果(如表所示):

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

推荐答案

简短答案

是的，有可能.从技术角度来看，您可以通过一个查询来实现.但问题是-最有可能的是，您正在尝试将一些逻辑从应用程序传递到数据存储.数据存储旨在存储数据，而不是表示/格式化数据，或者甚至对其应用一些逻辑.

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.

是的，MySQL没有数组数据类型，但是在大多数情况下这不是问题，可以创建体系结构以使其符合这些限制.而且无论如何，即使您能以某种方式实现它(例如-参见下文)，您也将无法再对该数据进行适当的处​​理，因为它只是结果集.当然，您可以存储它-以便以后再建立索引，但这又是应用程序的任务-创建该导入.

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.

此外，请确保它不是 Jaywalker 案例，所以不要存储分隔符分隔的值并稍后尝试提取它们.

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

好答案

从技术角度来看，您可以使用两个行集的笛卡尔积来实现.然后使用一个众所周知的公式:

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 ² + ...

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

因此，您将能够创建全数字"表，并在之后进行遍历.该迭代与MySQL 字符串函数一起，可能会导致您遇到类似这样的情况:

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:

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

相应的小提琴将在此处.

您还应该注意:这不是一个功能.以及函数(我的意思是，用户使用 CREATE FUNCTION 语句进行定义)由于MySQL中的函数无法按定义返回结果集，因此无法获得结果行集.但是，并不是说用MySQL执行请求的转换是完全不可能的.

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.

这篇关于像POSTGRESQL这样的MYSQL中的UNNEST函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！