MySQL 遍历拆分字符串的元素并使用它们执行操作 [英] MySQL Iterate through elements of a split string and do things with them

问题描述

我的 MySQL 社区版 5.1 表之一中有一个单元格.内容总是特定的数字.如果有多个数字，则由 ; 分号分隔.

I have a cell in one of my MySQL community edition 5.1 tables. The contents are always certain number(s). If there is more than one number, then it is delimited by an ; semi-colon.

例如:

| Column |
1ar8fj
99dkek;adjidk3;dajdid
divdae;dadjid;
NULL
dkadjcud;dasd;adfkvdo
dkjfakj
...

我需要编写一些代码来获取每个列的值，通过 ; 将其拆分，然后使用拆分后的每个值进行另一个查询，并输出结果.

I need make some code that takes each column value, splits it up by the ; and then uses each value after it was split up to do another query, and output the results.

我知道我可以用 PHP 做到这一点，但我不需要把它做成网页，所以我想知道这是否可以用 MySQL 语法编写?PHP 代码如下所示:

I know I can do this with PHP but I don't need to make this into a webpage, so I was wondering if this is possible write in MySQL syntax? The PHP code would look something like this:

<?php
    $result = $mysqli->query('select column from table;');
    while ($row = $result->fetch_array($result)){
        $id_numbers = explode($row[0],';');
        foreach($id_numbers as $key => $val){
            // do another query
            $result2 = $mysqli->query('select * from table2 where col_val = "'.$val.'"');
            while ($row2 = $result2->fetch_array($result2){
                echo $row2[0].'<br>';
            }
        }
    }
?>

这可以直接在 MySQL 语法中实现吗?

Is this possible directly in MySQL syntax?

谢谢！！！

推荐答案

PHEW.好的.我终于让它工作了，但这里有一个作为存储过程的解决方案，它将字符串作为分隔符的输入，并在名为 testtable

PHEW. Okay. I finally got it working, but here's a solution as a stored procedure that takes a string as an input for the delimiter and is runs on the given table called testtable

--Procedure: sprecursplit

--DROP PROCEDURE IF EXISTS sprecursplit;

DELIMITER |

CREATE PROCEDURE sprecursplit 
(
  IN delim nvarchar(255)
)
BEGIN
  declare tdone tinyint unsigned default(0);
  declare depth int unsigned default(1);
  declare datas nvarchar(255) default('');
  declare done tinyint unsigned default(0);
  declare dlength int unsigned default(1);
  declare hlength int unsigned default(0);
  declare pos int unsigned default(1);
  declare runpos int unsigned default(1);
  declare slice nvarchar(255) default('');

  drop table if exists allsubs;
  create temporary table allsubs(id int unsigned auto_increment, val nvarchar(255), primary key (id))engine = memory;

  while tdone <> 1 do
        if depth <= (select count(*) from testtable) then
           select t.datastring into datas from testtable t where t.id = depth limit 1;
           if length(datas) > 0 then
                set dlength = length(delim);
                set hlength = length(datas);
                set pos = 1;
                set runpos = 1;
                set slice = '';
                set done = 0;
                if hlength > 0 then
                   while done <> 1 do
                         if runpos > hlength then
                            set done = 1;
                         else
                             set pos = locate(delim, substring(datas from runpos));
                             if pos <> 1 then
                                if pos > 1 then
                                   set slice = substring(datas from runpos for (pos - 1));
                                else
                                    set slice = substring(datas from runpos);    
                                end if;
                                insert into allsubs (val) values (slice);
                             end if;
                             if pos = 0 then
                                 set runpos = runpos + hlength;
                             else
                                 set runpos = runpos + pos + dlength - 1;
                             end if;
                         end if;
                   end while;
                end if;
           end if;
           set depth = depth + 1;
        else
            set tdone = 1;
        end if;
  end while;
  select * from allsubs;
  drop table allsubs;
END|

DELIMITER ;

这篇关于MySQL 遍历拆分字符串的元素并使用它们执行操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！