这可以在 MySQL 查询中完成，还是需要在 PHP 中完成?(服务器端) [英] Can this be done in a MySQL query, or does it need to be done in PHP? (serverside)
问题描述
我有一张桌子:
id第一的最后的机构地址space1_1_valuespace1_2_valuespace2_1_valuespace2_2_valuespace3_1_valuespace3_2_value协议(例如为了减少字段数).
我最初需要将每个 'space x' 分组(即:space1.x、space2.x、space3.x)分解为它自己的 ROWS(即使所有数据都在 1 条记录/行中)
<块引用>id |第一 |最后 |机构|地址 |space1_1_value |space1_2_value |协议
如果 space2_1_value 中有值，则:
<块引用>id |第一 |最后 |机构|地址 |space2_1_value |space2_2_value |协议
如果 space3_1_value 中有值，则:
<块引用>id |第一 |最后 |机构|地址 |space3_1_value |space3_2_value |协议
这是通过使用以下查询得出的:
SELECT first, last, space1_1_value as space_value FROM report联合所有SELECT first, last, space2_2_value as space_value FROM report WHERE space2_2_value <>''联合所有SELECT first, last, space3_3_value as space_value FROM report WHERE space3_3_value <>''先订购然而，我的任务是进一步分解，但我不确定这是否可以通过查询实现，或者实际上是否需要在后端/PHP 方面完成?
因此请记住上述指令(这是一项要求):
因此，对于每个 SPACE1.x 组，都有一个包含逗号分隔值的列.
为此，我们会说所有的 space1_2_value、space2_2_value &space3_3_value cols 包含以下内容:
3月3日、3月5日、3月6日3月1日、3月2日2月27日例如.
所以每个列可以有 1 到多个逗号分隔值.
MySQL 中有没有办法分解这些值，并为每个值返回一个镜像行?或者需要在后端 (PHP) 方面完成一些简单的事情?
我花了几分钟时间尝试思考如何使用 PHP 来实现.
但是我是否陷入了没有任何索引的 foreach() 循环?(或者只是一个带有索引的普通 for() 循环，但检查每个 $value 是否为空的开销并不大(手动).
这可以在 sql 中完成.一种方法是使用只有整数的帮助表"，您可以join您的数据以多次获取您的行，然后仅提取 n-th子元素.
试试这个:
-- 包含从 1 到 10 的整数列表的辅助表创建表_int_1_10(id int主键)；插入 _int_1_10 (id)值 (1)、(2)、(3)、(4)、(5)、(6)、(7)、(8)、(9)、(10)；-- 一些示例数据创建表 test_strexplode (id int 主键，space_value_1 varchar(200),space_value_2 varchar(200));插入 test_strexplode (id, space_value_1, space_value_2)值 (1, 'row 1', 'March 3,March 5,March 6 March 1,March 2 Feb 27'),(2, 'row 2', 'March 3,,March 5'),(3, '第 3 行', '');选择 space_value_1，_int_1_10.id，-- 提取第 "_int_1_10.id" 个元素SUBSTRING_INDEX(SUBSTRING_INDEX(space_value_2,',',_int_1_10.id),',',-1) 作为子条目来自 test_strexplode加入_int_1_10在 _int_1_10.id <=-- 字符串中的元素数(= "," 的数量 + 1)char_length(space_value_2) - char_length(replace(space_value_2, ',', '')) + 1按 test_strexplode.id, _int_1_10.id 排序；这会给你:
+---------------+----+-----------------+|space_value_1 |身份证 |子条目 |+--------------+----+-------+|第 1 行 |1 |3 月 3 日 ||第 1 行 |2 |3 月 5 日 ||第 1 行 |3 |3 月 6 日 3 月 1 日 ||第 1 行 |4 |3 月 2 日 2 月 27 日 ||第 2 行 |1 |3 月 3 日 ||第 2 行 |2 |||第 2 行 |3 |3 月 5 日 ||第 3 行 |1 ||+--------------+----+-------+我使用了您的示例数据，但缺少一些 ,，这就是结果包含例如3 月 2 日 2 月 27 日.另请注意，某些子条目是空的(因为我的示例数据包括空条目)；您可能想也可能不想过滤掉它们.您的整数表显然必须至少包含您希望在任何行中包含的最大元素数(如果它包含 0 或负数，请在on-子句).
substring_index(str,delim,count) 返回字符串 str 中出现 count 次分隔符 delim 之前的子串.subentry 的完整语句将为正数返回第 _int_1_10.id 个元素，或者如果字符串的元素较少，则返回最后一个元素.
on 子句因此计算元素的数量(通过计算 , 的数量)以防止多次获取最后一个元素.如果您的字符串不包含任何空元素(如我的示例数据中的 ,,)，则您不需要该部分，但可以添加一个空元素来标记列表的末尾.>
您可以将此代码应用于整个结果集，例如通过使用
...从(选择...space1_1_value 作为 space_value_1，space1_2_value 作为 space_value_2...union all ... union all ... ) as test_strexplode加入_int_1_10 ...它会起作用，但可能会很慢.它不能在 space*_2_value 列上使用索引，并且必须进行大量连接和字符串评估.但是，除了规范化数据之外，您对此无能为力.
如果在 sql 中执行此操作很有用，可能取决于您对数据的处理方式.如果您只是打算在网页上的 html 表中显示它，那么在 php 中循环遍历数组可能会更容易和更快.要对结果集进行排序、过滤或join，在sql 中实现可能要容易得多(并且可能更快)，即使您在框架中使用它也是如此.如果您要更新值，在 php 中会更容易，因为它很可能在 sql 中变得一团糟(在这个结果集上).
I have a 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
(This is trimmed down field count for example sake).
I initially needed to break down each 'space x' grouping (ie: space1.x, space2.x, space3.x) to its own ROWS (even though all data was in 1 record/row)
id | first | last | institution | address | space1_1_value | space1_2_value | agreement
If there are value in space2_1_value then:
id | first | last | institution | address | space2_1_value | space2_2_value | agreement
If there are value in space3_1_value then:
id | first | last | institution | address | space3_1_value | space3_2_value | agreement
This was worked out by using the following query:
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
However I have been tasked to break this down even further, but I am not sure if this is even possible with a query, or if in fact would need to be done on the backend/PHP side of things?
So keeping the above directive in mind (which is a requirement):
So for each SPACE1.x group, there is a column with a comma delimited values in it.
For this, we'll say all space1_2_value, space2_2_value & space3_3_value cols hold something like:
March 3,March 5,March 6
March 1,March 2
Feb 27
for example.
So each col could have 1 to many comma delimited values.
Is there a way in MySQL to break up these values, and return a mirroring row for each one? Or something that simple needs to be done on the backend (PHP) side of thing?
I gave a few minutes to try and think how'd I'd do it in PHP.
But am getting caught up foreach() loops without any index? (or just a plain for() loop WITH index, but not a lot of overhead to check if each $value is blank (manually).
This can be done in sql. One way would be to use a "helper table" with just integers that you can join your data against to get your row several times and then to extract just the n-th subelement.
Try this:
-- 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;
This will give you:
+---------------+----+-----------------+
| 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 | |
+---------------+----+-----------------+
I used your sample data that is missing some ,, which is why the result contains e.g. March 2 Feb 27. Also note that some subentries are empty (as my sample data includes empty entries); you may or may not want to filter them out. Your integer table will obviously have to contain numbers at least up to the maximum number of elements you expect to have in any of your rows (and if it contains a 0 or negative numbers, filter them out in the on-clause).
substring_index(str,delim,count) returns the substring from string str before count occurrences of the delimiter delim. The full statement for subentry will, for a positive number, return either the _int_1_10.id-th element or, if the string has less elements, the last element.
The on-clause thus calculates the number of elements (by counting the number of ,) to prevent getting the last element several times. If your string does not contain any empty elements (like the ,, in my sample data), you don't need that part but could add an empty element to mark the end of the list.
You can apply this code to your whole resultset e.g. by using
...
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 ...
It will work, but it might be slow. It cannot use an index on the space*_2_value-columns and will have to do a lot of joining and string-evaluation. You cannot do much about it though, except normalizing your data.
If it is useful to do this in sql will probably depend on what you are doing with the data. If you are just going to display it in an html-table on a webpage, it might be both easier and faster to just loop through the array in php. To sort, filter or join your resultset, it's probably much easier to implement (and likely faster) in sql, maybe even if you are using it in a framework. If you are going to update the values, it will be much easier to do in php, as it is most likely going to be a mess in sql (on this resultset).
这篇关于这可以在 MySQL 查询中完成，还是需要在 PHP 中完成?(服务器端)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
