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

我在PHP中有一个字符串,如:</ p>

 “1234  ,2345,4567,5676,234,12,78957“..... 
</ code> </ pre>

我想以varchar(30)格式提取这些数字并使用它们 在像</ p>

  SELECT * FROM TABLE_NUM WHERE ID LIKE'%NUM'的命令上; 
</ code> </ pre>

其中NUM将 有上面提到的7个字符串。 </ p>

如果可能的话,我还想将'%NUM'中的'%'限制为仅1-5个字符,即前缀不应超过5个字符。 示例NUM = 1234且ID具有(31234,5678956781234)它应该仅提供第一个而不是另一个。</ p>

因此,我将得到所有匹配行的合并结果。< / p>

我如何实现这一目标?</ p>

谢谢!</ p>
</ div>

展开原文

原文

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!

dqroktbn005028
dqroktbn005028 那么为什么不在PHP中爆炸字符串并构建更好的查询呢?
大约 6 年之前 回复
dongxu1668
dongxu1668 它将以php变量的形式出现,说$ids
大约 6 年之前 回复
douweng3564
douweng3564 这串数字来自哪里?
大约 6 年之前 回复
duanna5749
duanna5749 这不是子列数据...这是他想要搜索的ID列表
大约 6 年之前 回复
dousuie2222
dousuie2222 如果您发现自己必须处理子列数据(提取列的某些部分),那么您做错了:-)修复架构,您的查询将会尖叫。
大约 6 年之前 回复
dongzhi6905
dongzhi6905 我正在使用mysql
大约 6 年之前 回复
dsk88199
dsk88199 您使用的是什么RDBMS?
大约 6 年之前 回复

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'
dongqingchan2385
dongqingchan2385 “你不能得到任何不如”错误“的优化”〜那是一张可行的办公室动机海报
大约 6 年之前 回复
drpiqlzrh62917192
drpiqlzrh62917192 那么什么会更好的PHP功能或SQL程序?
大约 6 年之前 回复
dousao6313
dousao6313 因为你使用%xxx作为参数,所以它几乎肯定会变慢。 一些DBMS可以通过使用反向索引来缓解这种情况。 首先担心功能,然后质疑性能。 你不能比“错误”更优化:-)
大约 6 年之前 回复
dongpu1879
dongpu1879 这会减慢因为我将在一个字符串中有超过100个ID。 我在SQL中做这样的事情会更快。 你还可以查看我对有关'%'限制的编辑。
大约 6 年之前 回复
dqwn64004
dqwn64004 好的,@ Gleon,我添加了一个构造单个查询的方法,它将提取所有需要的行。 我的PHP知识达不到我有信心提供无错代码的水平,所以我只给出了伪代码。
大约 6 年之前 回复
drix47193
drix47193 由于php查询结果将被进一步处理。 我猜一个正确的SQL存储过程会更有用。
大约 6 年之前 回复
douquan2023
douquan2023 我希望所有这些没有被挑选出来的合并结果。
大约 6 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问