mysql like 查询排除数字 [英] mysql like query exclude numbers

问题描述

我在使用 php mysql 查询时遇到一个小问题，我正在寻求帮助.

I have a small problem with a php mysql query, I am looking for help.

我有一个家谱表，我在其中为每个人存储他/她的祖先 ID，用逗号分隔.像这样

I have a family tree table, where I am storing for each person his/her ancestors id separated by a comma. like so

 id ancestors
 10 1,3,4,5

所以id 10的人是id 5的父亲，id 4的父亲是3的父亲等等......

So the person of id 10 is fathered by id 5 who is fathered by id 4 who is fathered by 3 etc...

现在我希望选择所有在其祖先中具有 id x 的人，因此查询将类似于:从祖先喜欢%x%"的人中选择*

Now I wish to select all the people who have id x in their ancestors, so the query will be something like: select * from people where ancestors like '%x%'

现在这可以正常工作，除非 id x 是 2，并且记录的祖先 id 为 32，这样的查询将检索 32，因为 32 包含 2.如果我使用 '%,x,%' (包括逗号)查询将忽略其祖先 x 位于列的任一边缘(左侧或右侧)的记录.它还将忽略 x 是唯一祖先的记录，因为不存在逗号.

Now this would work fine except, if id x is lets say 2, and a record has an ancestor id 32, this like query will retrieve 32 because 32 contains 2. And if I use '%,x,%' (include commas) the query will ignore the records whose ancestor x is on either edge(left or right) of the column. It will also ignore the records whose x is the only ancestor since no commas are present.

简而言之，我需要一个类似的查询来查找被逗号包围或没有被任何东西包围的表达式.或者获取正则表达式的查询，前提是周围没有数字.我需要它尽可能高效(我很擅长写正则表达式)

So in short, I need a like query that looks up an expression that either is surrounded by commas or not surrounded by anything. Or a query that gets the regular expression provided that no numbers are around. And I need it as efficient as possible (I suck at writing regular expressions)

谢谢.

好的，帮我想出一个更好的架构.

Okay guys, help me come up with a better schema.

推荐答案

您没有以正确的方式存储数据.无论如何，如果你仍然想使用这个模式，你应该使用 FIND_IN_SET 而不是 LIKE，以避免出现不想要的结果.

You are not storing your data in a proper way. Anyway, if you still want to use this schema you should use FIND_IN_SET instead of LIKE to avoid undesired results.

SELECT *
  FROM mytable
 WHERE FIND_IN_SET(2, ancestors) <> 0

这篇关于mysql like 查询排除数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！