mysql - 如何提取 MySQL 表中包含特定字符的 csv 列的成员？

Question

我的 MySQL 表中有一个列是格式为 csv 的中等文本。我有兴趣提取这些 csv 列表中包含字符“*”的成员。最简单的方法是什么？

Answer 1

我从来没有做过类似的事情。正如你所看到的，我做了一个简单的测试。我不知道大型记录集的性能。

create table mytest (
id int not null auto_increment primary key,
content varchar(100)
) engine = myisam;

insert into mytest (content)
values 
('one,two*,three*text'),
('four,five'),
('six*,seven*,eight*');



delimiter //
drop procedure if exists split_found //
create procedure split_found()
begin
declare str varchar(200);
declare ssql varchar(200);
declare finito int default 0;
declare cursore cursor for select content from mytest;
declare continue handler for not found set finito = 1;
drop temporary table if exists tmp;
create temporary table tmp (cont varchar(50) );
open cursore;
mio_loop:loop
fetch cursore into str;
if finito = 1 then
leave mio_loop;
end if;
 set @ssql = concat("insert into tmp (cont) values ('", replace(str, ',', "'),('"), "')");
 prepare stmt from @ssql;
 execute stmt;
 deallocate prepare stmt;
end loop;
close cursore;
select * from tmp where cont like '%*%';
end; //
delimiter ;

mysql> call split_found();
+------------+
| cont       |
+------------+
| two*       |
| three*text |
| six*       |
| seven*     |
| eight*     |
+------------+
5 rows in set (0.01 sec)