选择包含列中确切数字的行，并用逗号分隔数字集 [英] Select row which contains exact number in column with set of numbers separated by comma

问题描述

也许答案很简单，但是我找不到合适的MySQL查询来完成我想要的工作.

Maybe answer is very easy, but I can't find the right MySQL query which do what I want.

我有表 user :

| id_user |  name   | action_type |
+---------------------------------+
|    1    | joshua  |  1,13,12,40 |
|    2    | joshua  |  2,8        |

我只想选择action_type列中具有确切编号的行.

And I want to select only rows which have exact number in action_type column.

action_type作为TEXT存储在MySQL中.

action_type is stored in MySQL as TEXT.

我已经尝试过了:

SELECT * FROM user WHERE action_type LIKE '%2%'

但是它选择了12行，这不是我想要的:(

But it selected rows with 12 which is not what I want :(

也许可以使用IN运算符，但是我找不到正确的使用方式.

Maybe it's possible with IN operator, but I couldn't find a right way to use this.

推荐答案

您正在寻找FIND_IN_SET

SELECT *
FROM user
WHERE FIND_IN_SET( '2', action_type )

SQL小提琴DEMO

更新

只需提一下，这也是可能的

Just to mentioned it, this is also possible

SELECT *
FROM user
WHERE FIND_IN_SET( 2, action_type )

MySQL将自动转换为char

MySQL will do an automatic conversion to char

这篇关于选择包含列中确切数字的行，并用逗号分隔数字集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！