mysql - 查询 SQL 中字符串的精确匹配

Question

我想从数据库表中查询字符串的精确匹配。

当我将查询写成

select description from tproduct where description like '%diamond%'

它运行并以通配符 查询的形式给出结果。比如我拿到了ringdiamond、diamondmaster等。

但我只想得到“钻石”。

为此我做了:

select description from tproduct where description = 'diamond'

但是报错:

Error: Unknown column 'diamond' in 'where clause'

描述栏包含:

This bracelet is composed of round diamond surrounded by milgrain detailing. Its secure setting prevents it from twisting and ensures it will sit impeccably on her wrist.


This stylish design is the perfect accessory for the day or evening. Akoya cultured pearls are lined up by a string of bezel-set round diamond.

Answer 1

如果我对问题的理解正确，那么当“diamond”是一个不同的词而不是另一个词(如“diamondville”)的一部分时，您希望匹配它。您可以执行类似 SELECT * FROM tproduct WHERE description like '% diamond %' 这样的操作，这将匹配所有“diamond”被空格包围的记录。

但这行不通。这不会找到描述以“Diamond”开头或在“Diamond”之后有逗号或句号的记录

您需要匹配正则表达式。您可以指定单词边界:

select * from t2 where description regexp '[[:<:]]diamond[[:>:]]';

有关 MySQL 正则表达式的更多信息，请参阅此页面:http://dev.mysql.com/doc/refman/5.1/en/regexp.html