在逗号分隔字段上使用WHERE子句选择记录

Question

I have a table myTable with 2 fields (4 example):

ID   Zip_Codes
===  =========
1    60148,60123,61034,61234
2    60122,61034

I want to use:

$zip = 61034;
$qry = "Select ID from myTable Where $zip in Zip_Codes";

Is that possible? What makes it fast? Other better solution?

Answer 1

You can't use IN here. x IN (1,2) is shorthand for x = 1 OR x = 2, it doesn't let you search inside a comma separated list.

You can use FIND_IN_SET:

SELECT ID FROM myTable WHERE FIND_IN_SET(61034, Zip_Codes);

Though, you shouldn't have multiple values in one row. You should have one row per zip code.

RowID   ID   Zip_Code
=====   ===  =========
1       1    60148
2       1    60123
3       1    61034
4       1    61234
5       2    60122
6       2    61034

(RowID would the primary key. You could add an INDEX to ID.)

Then you can simply do:

SELECT ID FROM myTable WHERE Zip_Code = 61034