MySQL存储过程：搜索可变数量的字符串

I need a some stored procedure that called like:

我需要一个调用的存储过程:

search('foo bar')

makes a search similar to:

进行类似的搜索:

SELECT FROM A, B
WHERE A.B_ID = B.ID
AND (A.f1 LIKE '%foo%' OR A.f2 LIKE '%foo%' OR B.f3 LIKE '%foo%')
AND (A.f1 LIKE '%bar%' OR A.f2 LIKE '%bar%' OR B.f3 LIKE '%bar%')

And I have some doubts and questions:

我有一些疑问和疑问:

I can't pass an array to the procedure, so my only option is to pass the string directly as in the example ('foo bar')?

我无法将数组传递给过程,所以我唯一的选择是直接传递字符串,如示例('foo bar')中所示?
So I assume I have to do the split in SP code. I didn't know how, so I searched and found this solution. Is using temporary tables and what I think a lot of clumsy code. Is really so complicated? How about the performance?

所以我假设我必须在SP代码中进行拆分。我不知道怎么做,所以我搜索并找到了这个解决方案。是使用临时表和我认为很多笨拙的代码。真的很复杂吗?表现怎么样?
I don't know how to create such dynamic query. I suppose I have to loop over the tokens to create a new block of the WHERE clause for everyone, but I'm not sure how to do this or if it's the best solution. Maybe concatenating strings and then making a prepared statement is better?

我不知道如何创建这样的动态查询。我想我必须遍历标记为每个人创建一个WHERE子句的新块,但我不知道如何做到这一点,或者它是否是最好的解决方案。也许连接字符串然后做一个准备好的声明更好?

Thanks.

Note: I use iBATIS (Java) for calling this routine.

注意:我使用iBATIS(Java)来调用此例程。

1 个解决方案

#1

What you want is to use Full Text Searching (FTS) - there's MySQL's native FTS functionality which can only be used on MyISAM tables, and 3rd party FTS like Sphinx, to choose from. Here's an online slideshow that's a decent intro & howto.

你想要的是使用全文搜索(FTS) - MySQL的本机FTS功能只能用于MyISAM表,以及像Sphinx这样的第三方FTS可供选择。这是一个在线幻灯片,是一个体面的介绍和如何。

Using MySQL native FTS, your query would resemble:

使用MySQL本机FTS,您的查询将类似于:

SELECT *
  FROM A AS a
  JOIN B AS b ON b.id = a.b_id
 WHERE MATCH (a.f1, a.f2, b.f3) AGAINST ('foo bar');

Dynamic SQL is still an option, depending on how strong you want/need to make the query. I'd look into the FTS offerings before I'd consider dynamic SQL...

动态SQL仍然是一个选项,具体取决于您想要/需要进行查询的强度。在考虑动态SQL之前,我会研究FTS产品......

#1