Mysql 搜索将句号视为空格 [英] Mysql Search Treat Periods Like Spaces

问题描述

现在我有一堆数据以这种格式存储在我的数据库中.

well right now I have a bunch of data stored in my db in the format.

this.is.the.name

不是空格，而是句号.

我只是想知道如何进行 mysql 搜索以将句点视为空格?或者就这样，如果用户搜索this is the"，它将返回条目.

I'm just wondering how I could do my mysql search to treat the periods like spaces? Or just have it so if a user searches "this is the" it'll return the entry.

推荐答案

不像其他答案建议的那样在字段上进行替换，您可以对搜索词进行替换.这样 MySQL 仍然可以使用 field1 上的索引.假设句号总是在那里而不是空格

Instead of doing the replace on the field, like other answers suggest, you could do the replace on the search term. In this way MySQL could still use an index on field1. Supposing that the periods are always there instead of spaces

SELECT field1, field2 FROM table WHERE field1 = REPLACE('user input', ' ', '.')

如果你想在没有点的情况下进行可视化，你也可以在 SELECT 部分进行反向替换:

If you want to visualise without dots you could do the reverse replace in the SELECT part too:

SELECT REPLACE(field1, '.', ' '), field2 FROM table 
WHERE field1 = REPLACE('user input', ' ', '.')

或者您甚至可以考虑更新您的数据库，而不是与之抗争:

Or you can even consider to update your DB, instead of fighting with it:

UPDATE table SET field1 = REPLACE(field1, '.', ' ')

这篇关于Mysql 搜索将句号视为空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！