mysql where in (select ids_str from test)如何查询

Question

mysql> desc test1
    -> ;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+
mysql> desc test2;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| ids_str | varchar(32)      | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+

ids_str是一个字符串。
ids_str="1,2,3"

select * from test1 where id in (select ids_str from test2)
即：

select * from test1 where id in ("1,2,3")

Answer 1

select * from test1 where find_in_set(id,(select ids_str from test2));

Answer 2

你的id是int(11)，而ids_str是varchar(32)，当然就不行。除非把ids_str也改成int型

Answer 3

先考虑"1,2,3"转成int类型等于多少

Answer 4

其实问题的根源就在于怎么把字符串'1,2,3'，分隔成mysql的列，即：

ids
1
2
3

有两种方式

1.自定义函数（推荐）
2.用这个sql

SELECT substring_index(substring_index(t.ids,',', b.help_topic_id + 1), ',', -1) FROM t_ids t 
join mysql.help_topic b ON b.help_topic_id <  (LENGTH(t.ids) - LENGTH(REPLACE(t.ids, ',', '')) + 1);  

其中t_ids表，就是你问题中test2的设定

CREATE TABLE `t_ids` (
  `ids` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;