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

发布于 2024-09-08 16:52:31 字数 727 浏览 3 评论 0原文

我需要一个名为 like: 的存储过程，

search('foo bar')

进行类似于以下的搜索：

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%')

我有一些疑问和问题：

我无法将数组传递给该过程，所以我唯一的选择是直接传递字符串，如下所示示例 ('foo bar')？
所以我假设我必须在 SP 代码中进行拆分。我不知道怎么做，所以我搜索并找到了这个解决方案。就是使用临时表以及我认为很多笨拙的代码。真的有这么复杂吗？性能怎么样？
我不知道如何创建这样的动态查询。我想我必须循环标记才能为每个人创建一个新的 WHERE 子句块，但我不确定如何执行此操作或者这是否是最佳解决方案。也许连接字符串然后制作准备好的语句更好？

谢谢。

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

原文

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')?
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?
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?

Thanks.

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

善良天后 2024-09-15 16:52:31

您想要的是使用全文搜索 (FTS) - 有 MySQL 的本机 FTS 功能（只能在 MyISAM 表上使用）和第 3 方 FTS（如 Sphinx）可供选择。这是一个在线幻灯片，这是一个不错的介绍和内容；如何操作。

使用 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');

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

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.

Using MySQL native FTS, your query would resemble:

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...

回复收藏 0 原文

~没有更多了~