MySQL 忽略空格的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2709929/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query that ignore the spaces
提问by xRobot
What's the best way to run a query so that spaces in the fields are ignored? For example, the following queries:
运行查询以便忽略字段中的空格的最佳方法是什么？例如，以下查询：
SELECT * FROM mytable WHERE username = "JohnBobJones"
SELECT * FROM mytable WHERE username = "John Bob Jones"
would find the following entries:
会找到以下条目：
John Bob Jones
JohnBob Jones
JohnBobJones
I am using php or python but I think this doesn't matter.
我正在使用 php 或 python，但我认为这无关紧要。
回答by SLaks
SELECT * FROM mytable
WHERE REPLACE(username, ' ', '') = REPLACE("John Bob Jones", ' ', '')
回答by Mark Byers
It depends. If you don't care about good performance then there are lots of things you could do but most of them will be slow. Maybe that's OK for you, but I will leave this answer here in case others reading do want a fast solution.
这取决于。如果您不关心良好的性能，那么您可以做很多事情，但其中大多数会很慢。也许这对你来说没问题，但我会在这里留下这个答案，以防其他阅读的人确实想要一个快速的解决方案。
If you want very fast performance you should index the string without spaces in the database. In PostgreSQL you can create an index on a function. You can use this to create an index on the column with spaces replaced with the empty string. The advantage of this method is that it requires no maintenance apart from creating the index.
如果你想要非常快的性能，你应该在数据库中索引没有空格的字符串。在 PostgreSQL 中，您可以在函数上创建索引。您可以使用它在列上创建一个索引，其中空格替换为空字符串。这种方法的优点是除了创建索引不需要维护。
In MySQL you cannot do this so the simplest way would be to duplicate the data in the database - once with spaces and once without. Use the column without spaces in your WHERE clause, but the original column in your SELECT column list. This requires more maintenance as the columns must be kept in sync. You can do this with application logic or database triggers.
在 MySQL 中你不能这样做，所以最简单的方法是复制数据库中的数据 - 一次有空格，一次没有。在 WHERE 子句中使用没有空格的列，但在 SELECT 列列表中使用原始列。这需要更多的维护，因为列必须保持同步。您可以使用应用程序逻辑或数据库触发器执行此操作。
回答by Mquinteiro
The proposed solution look very well but is horrible for performance, if it is possible restrict the query with something like
建议的解决方案看起来很好，但对性能来说很糟糕，如果可能的话，可以用类似的东西来限制查询
SELECT * FROM mytable WHERE username like 'John%' and REPLACE(username, ' ', '') = REPLACE("John Bob Jones", ' ', '')
SELECT * FROM mytable WHERE username like 'John%' and REPLACE(username, ' ', '') = REPLACE("John Bob Jones", ' ', '')
Also you can use REGEXP.
您也可以使用 REGEXP。
SELECT * FROM mytable WHERE username REGEXP '^John *Bob *Jones'
SELECT * FROM mytable WHERE username REGEXP '^John *Bob *Jones'
And remember the performance, operation in the where are in general bad idea.
并记住性能，操作在哪里一般都不好。
Take a look to http://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html
看看http://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html
回答by Mathusuthanan
TRY THIS:
尝试这个：
SELECT * FROM mytable WHERE username =REPLACE("John Bob Jones", ' ', '')
回答by Adam111p
We often want to search for text, regardless of the number of spaces, white space and letters.
我们经常想搜索文本，而不管空格、空格和字母的数量。
Just trim , lower case , and replace all multipe Non-word characters for one space.
只需修剪，小写，并将所有多个非单词字符替换为一个空格。
SELECT regexp_replace(trim(lower('Here is a long text , with many white spaces AND different character sensitive')),'\W+',' ','g') t
return :here is a long text with many white spaces and different character sensitive
返回：这是一个包含许多空格和不同字符敏感的长文本
Here is the use for search. Only the order of words is important, nothing more.And this is beautiful.
这是搜索的用途。只有单词的顺序很重要，仅此而已。这很漂亮。
select * from (
SELECT regexp_replace(trim(lower('Here is a long text , with many white spaces AND different character sensitive')),'\W+',' ','g') t
) as o
where t= regexp_replace(trim(lower('Here is a LonG TEXT , with mANY white ^ spaces AND different character sensiTive')),'\W+',' ','g')
return:here is a long text with many white spaces and different character sensitive
返回：这是一个包含许多空格和不同字符敏感的长文本
Garbage in data and junk in the query, but it still finds it right.
数据中的垃圾和查询中的垃圾，但它仍然认为它是正确的。
回答by QB.
One way would be to use LIKE and WildCards to build your query citeria. Something like:
一种方法是使用 LIKE 和 WildCards 来构建您的查询 citeria。就像是：
SELECT * FROM mytable WHERE username LIKE 'JohnBobJones';
SELECT * FROM mytable WHERE username LIKE 'John BobJones';
