SQL尝试使用LIKE返回字段的行

Question

I have a db I query to select the column devicetoken and return all rows that are like an string I have an array. The query happens in a loop for each value in the array. The SQL syntax is not doing what I want. I am not using a wildcard because I dont want it to return substrings of the strings I am looking for.

Here the SQL syntax:

$result = $mysqli->query("SELECT devicetoken FROM `department devices` WHERE unit LIKE $unitsarray[$i]");

The units array for my testing consists of:

--- Units row 0 = 121
--- Units row 1 = 125 
--- Units row 2 = BR171 
--- Units row 3 = C1 
--- Units row 4 = C120 
--- Units row 5 = D2INV 
--- Units row 6 = E03 
--- Units row 7 = E05 
--- Units row 8 = E17 
--- Units row 9 = E173 
--- Units row 10 = HFPIO 
--- Units row 11 = MW70 
--- Units row 12 = T05 
--- Units row 13 = TIMERW 
--- Units row 14 = WT01 
--- Units row 15 = WT13 
--- Units row 16 = WT17 

My db looks like this:

device_id    devicetoken                                           unit
T05 ipad     773f5436825a7115417d3d1e036da20e806efeef547b7c3fe4    E17, E03, E05
E05 ipad     773f5436825a7115417d3d1e036da20e806efeef547b7c3fe4    E01, E62, E67

So, my event in the loop should be called three times, once when it goes through and finds E17, once for E03, and once for E05. So how do I set up the LIKE to get these strings each time I cycle through the loop.

Answer 1

Do you have any flexibility on the database design? Normalization theory says you should be storing those values in a separate table (in case you are not familiar with this, you can find an example here) . Otherwise you are facing a text-search problem per row which may hinder performance significantly as your table grows.

Now, if you are stock with this design you may be able to expand your query in case you have a fixed number of items per row. For example this question shows a possible solution:

WHERE
      MyColumn LIKE '%,' + @search + ',%' --middle
      OR
      MyColumn LIKE @search + ',%' --start
      OR
      MyColumn LIKE '%,' + @search --end
      OR 
      MyColumn =  @search --single