SQL select命令使用带逗号分隔ID的字符串

Question

I have a string in PHP like:

"1234,2345,4567,5676,234,12,78957".....

I want to extract these numbers in varchar(30) format and use them on a command like

SELECT * FROM TABLE_NUM WHERE ID LIKE '%NUM';

where NUM will have above mentioned 7 strings.

And if possible i would also like to restrict '%' in '%NUM' to 1-5 characters only i.e. the prefix should not be greater than 5 characters. Example NUM = 1234 and ID has (31234,5678956781234) it should only provide first one as result and not the other one.

Accordingly I will get a merged result of all matching rows.

How can I achieve this ?

Thank You!

Answer 1

If that string is coming from a column somewhere in the database, you should fix the schema. It's almost always a bad idea to design a schema where you have to process sub-columnar data.

If it's a string from outside the database and you just want to run queries based on the individual parts, you're probably better off using facilities outside of your DBMS to construct the queries.

For example, using bash under Linux:

pax> list="1234,2345,4567,5676,234,12,78957"
pax> for i in $(echo $list | sed 's/,/ /g'); do
...>     echo mysql "\"SELECT * FROM TABLE_NUM WHERE ID LIKE '%$i'\""
...> done
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%1234'"
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%2345'"
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%4567'"
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%5676'"
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%234'"
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%12'"
mysql "SELECT * FROM TABLE_NUM WHERE ID LIKE '%78957'"

That script will echo the commands to do what you want (assuming mysql is the correct CLI interface to your DBMS) - simply remove the echo at the start to actually execute the commands.

---

For PHP (as per your question edit), you can use the explode function to split the string, something like:

$list = "1234,2345,4567,5676,234,12,78957";
$numbers = explode (",", $list);

then execute a query for each element of $numbers.

---

If what you're after is a single result set formed from all of those values, there are other ways to do it. One involves using the list to construct an "uber-query" string that will do all the work for you, then you execute it once.

Simply use an or clause to join the different "sub" queries into one (pseudo-code):

$query = "select * from table_num"
$joiner = " where"
for each $element in $list:
    $query = $query + $joiner + "id like '%" + $element + "'"
    $joiner = " or"
execute_sql $query

That ends up giving you the query string:

SELECT * FROM TABLE_NUM
    WHERE ID LIKE '%1234'
    OR    ID LIKE '%2345'
    :
    OR    ID LIKE '%78957'