php - 这可以在 MySQL 查询中完成，还是需要在 PHP 中完成？ (服务器端)

Question

我有一张 table :

id
first
last
institution
address
space1_1_value
space1_2_value
space2_1_value
space2_2_value
space3_1_value
space3_2_value
agreement

(为了举例，这被削减了字段数)。

我最初需要将每个“空间 x”分组(即:space1.x、space2.x、space3.x)分解为它自己的 ROWS(即使所有数据都在 1 条记录/行中)

id | first | last | institution | address | space1_1_value | space1_2_value | agreement

如果 space2_1_value 中有值则:

id | first | last | institution | address | space2_1_value | space2_2_value | agreement

如果 space3_1_value 中有值则:

id | first | last | institution | address | space3_1_value | space3_2_value | agreement

这是通过使用以下查询得出的:

SELECT first, last, space1_1_value as space_value FROM report
UNION ALL
SELECT first, last, space2_2_value as space_value FROM report WHERE space2_2_value <> ''
UNION ALL
SELECT first, last, space3_3_value as space_value FROM report WHERE space3_3_value <> ''
ORDER BY first

然而，我的任务是进一步分解它，但我不确定这是否可以通过查询实现，或者实际上是否需要在后端/PHP 方面完成？

因此请牢记上述指令(这是一项要求):

因此对于每个 SPACE1.x 组，都有一个包含逗号分隔值的列。

为此，我们假设所有 space1_2_value、space2_2_value 和 space3_3_value 列都包含如下内容:

March 3,March 5,March 6
March 1,March 2
Feb 27

例如。

因此每个列可以有 1 到多个逗号分隔值。

在 MySQL 中有没有办法分解这些值，并为每个值返回一个镜像行？或者需要在后端 (PHP) 端完成一些简单的事情？

我花了几分钟时间思考如何用 PHP 来完成。

但是我陷入了没有任何索引的 foreach() 循环？ (或者只是一个带索引的普通 for() 循环，但检查每个 $value 是否为空(手动)的开销并不大。

Answer 1

这可以在 sql 中完成。一种方法是使用仅包含整数的“辅助表”，您可以加入您的数据以多次获取您的行，然后仅提取第 n子元素。

试试这个:

-- helper table with a listof integers from 1 to 10
create table _int_1_10 (id int primary key);
insert into _int_1_10 (id) 
values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

-- some example data 
create table test_strexplode (
   id int primary key, 
   space_value_1 varchar(200),
   space_value_2 varchar(200)
);

insert into test_strexplode (id, space_value_1, space_value_2)
values (1, 'row 1', 'March 3,March 5,March 6 March 1,March 2 Feb 27'),
       (2, 'row 2', 'March 3,,March 5'),
       (3, 'row 3', '');

select space_value_1, 
  _int_1_10.id,
  -- extracts the "_int_1_10.id"th element
  SUBSTRING_INDEX(SUBSTRING_INDEX(
       space_value_2,',',_int_1_10.id),',',-1) as subentry
from test_strexplode
join _int_1_10 
on _int_1_10.id <= 
   -- number of elements in your string (= number of "," + 1)
   char_length(space_value_2) - char_length(replace(space_value_2, ',', '')) + 1
order by test_strexplode.id, _int_1_10.id;

这会给你:

+---------------+----+-----------------+
| space_value_1 | id | subentry        |
+---------------+----+-----------------+
| row 1         | 1  | March 3         | 
| row 1         | 2  | March 5         |
| row 1         | 3  | March 6 March 1 |
| row 1         | 4  | March 2 Feb 27  |
| row 2         | 1  | March 3         |
| row 2         | 2  |                 |
| row 2         | 3  | March 5         |
| row 3         | 1  |                 |
+---------------+----+-----------------+

我使用了缺少一些 , 的示例数据，这就是为什么结果包含例如3 月 2 日、2 月 27 日。还要注意一些子条目是空的(因为我的示例数据包含空条目)；您可能想也可能不想过滤掉它们。您的整数表显然必须至少包含您希望在任何行中包含的最大元素数的数字(如果它包含 0 或负数，请在on-子句)。

substring_index(str,delim,count)返回 count 分隔符 delim 出现之前的字符串 str 的子字符串。对于正数，subentry 的完整语句将返回第 _int_1_10.id 个元素，如果字符串的元素较少，则返回最后一个元素。

on 子句因此计算元素的数量(通过计算 , 的数量)以防止多次获取最后一个元素。如果您的字符串不包含任何空元素(如我的示例数据中的 ,,)，您不需要该部分，但可以添加一个空元素来标记列表的末尾。

您可以将此代码应用于整个结果集，例如通过使用

...
from (select ... 
         space1_1_value as space_value_1,
         space1_2_value as space_value_2
      ...
      union all ... union all ... ) as test_strexplode 
join _int_1_10 ...

它会工作，但可能会很慢。它不能在 space*_2_value 列上使用索引，并且必须进行大量连接和字符串评估。但是，除了规范化数据之外，您对此无能为力。

在 sql 中执行此操作是否有用可能取决于您对数据执行的操作。如果您只是打算在网页上的 html 表中显示它，则在 php 中循环遍历数组可能既简单又快捷。要对结果集进行排序、过滤或join，在sql 中实现起来可能更容易(而且可能更快)，即使您在框架中使用它也是如此。如果您要更新这些值，在 php 中会更容易，因为它很可能在 sql 中变得一团糟(在此结果集上) .