MySQL将逗号分隔的字段与单个字符串进行比较 [英] Mysql compare comma-separated field with single string

问题描述

因此数据库中称为学校的字段的值可能为：

So a field called schools in the database might have a value of:

'13,121,112,1212'

我正在使用它来显示潜在的错误。

I'm using that to show the potential for a mistake.

假设我正在该字段中寻找 12 的值。逗号表示整数，我不想匹配112或1212

Suppose I'm looking for a value of 12 in that field. The commas denote a "whole number" and I don't want to match 112 or 1212

是否有比这更优雅的匹配？

Is there a more elegant match than this?

@compare = 12;
WHERE CONCAT(schools,',') LIKE CONCAT('%',compare,',%)

我最近对GROUP_CONCAT函数印象深刻，但这与之相反。谢谢！

I was recently impressed by the GROUP_CONCAT function but this is kind of in reverse of that. Thanks!

推荐答案

对于这种简单的情况，您可以使用 FIND_IN_SET（） ;

For this simple case you can use FIND_IN_SET();

WHERE FIND_IN_SET('13', schools);

请注意，对于用逗号分隔的文本列没有很好的索引编制，因此查询将是比规范化数据库慢很多。

Note though that there is no good indexing for columns with comma separated text, so the queries will be much slower than a normalized database.

这篇关于MySQL将逗号分隔的字段与单个字符串进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！