MySQL SELECT语句，值在数组中 [英] MySQL SELECT statement where value is in array

问题描述

我需要一些我无法真正使用的MySQL语句的帮助.

I need some help with an MySQL statement that I cannot really make to work.

我有一个这样的表:

+---+-------------------------------+
|id | fruit                         |
+---+-------------------------------+
| 1 | apple,orange,pear,grape,lemon |
| 2 | pear,melon,apple,kiwi,lemon   |
| 3 | orange,kiwi,pear,apple,cherry |
| 4 | grape,lemon,cherry,apple,melon|
+---+-------------------------------+

我需要做的是选择fruit列包含单词melon的所有行.有问题的单词可能在数组中的任何位置.

What I need to do is to SELECT all rows where the column fruit contains the word melon. The word in question might be at any position in the array.

我对以下查询感到厌倦，但由于某种原因，我只得到3-4行，绝对不是全部:

I tired with the below query but for some reason I only get 3-4 rows, definitely not all of them:

$fruit = $_GET['fruit'];
$query1= "SELECT * FROM tbl_fruits WHERE ".$fruit." IN (fruit)";

任何评论将不胜感激.

推荐答案

您可以使用FIND_IN_SET

SELECT * FROM tbl_fruits 
WHERE find_in_set('$fruit', fruit)

但是您实际上应该更改表设计.

永远不要在一个列中存储多个值！

But you actually should rather change your table design.

Never store multiple values in a single column!

一个更好的表设计将会是

A better table design would be

fruits table
------------
id    name
1     melon
2     orange
3     apple
...


products table
-------------------
id    name   price
1     P1     1.50
2     P2     2.99
3     P3     0.99


product_fruits table
--------------------
product_id   fruit_id
1            1
1            2
2            2
3            1

这是经典的多对多关系(从m到n).

That is a classic many to many relation (m to n).

这篇关于MySQL SELECT语句，值在数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！