mysql split in查询,MySQL Split在“SELECT WHERE IN”語句中使用

I've been scouring various MySQL sites in the hope of finding something that will allow me to turn this:

我一直在搜索各種MySQL網站,希望找到一些可以讓我改變它的東西:

var parameters = "a,b,c,d"; // (Could be any number of comma-delimited values)

Into this (assuming that my parameters are somehow becoming the values you see in the IN block):

進入此(假設我的參數以某種方式成為您在IN塊中看到的值):

SELECT * FROM mytable WHERE parametertype IN('a', 'b', 'c', 'd');

But I'm not having a great deal of success! The best site I've found has been: dev.mysql.com, which discusses the splitting of strings based on a delimiter (',' in my case) although it hasn't turned up any answers...

但我沒有取得很大的成功!我找到的最好的網站是:dev.mysql.com,它討論了基於分隔符的字符串拆分(在我的情況下是','),盡管它沒有找到任何答案......

Does anyone know of a good way of splitting a comma-delimited string into a group of strings that can be used in this context?

有沒有人知道將逗號分隔的字符串拆分成可在此上下文中使用的字符串組的好方法?

2 个解决方案

#1

6

It may not have all the flexibility you need, but the MySQL FIND_IN_SET function might be sufficient. There's a hard limit of a maximum of 64 values in the set to compare with though.

它可能沒有您需要的所有靈活性,但MySQL FIND_IN_SET函數可能就足夠了。盡管如此,該組中最多有64個值的硬限制。

For example:

SELECT *

FROM mytable

WHERE FIND_IN_SET( parametertype, 'a,b,c,d' ) != 0

This is an example of the use of an in-line MySQL SET ('a,b,c,d') - its sort-of an enum. It may be a sign that something is awry with the normalisation of the data model if these are being used. But, they can be handy to eliminate a join, or (as in this case) to associate with complex information that resides outside the database.

這是使用內聯MySQL SET('a,b,c,d')的一個例子 - 它是一種枚舉。如果正在使用數據模型的規范化,這可能表明某些事情是錯誤的。但是,它們可以方便地消除連接,或者(如本例所示)與位於數據庫外部的復雜信息相關聯。

#2

0

you can use dynamic sql

你可以使用動態的SQL

delimiter //;

create procedure tst()

begin

prepare stmp from 'INSERT INTO LOGG SELECT PFILE_Filename FROM PFILE_FILE';

execute stmp;

deallocate prepare stmp;

end//

delimiter ;//

you have to do something like this

你必須做這樣的事情

you have a part of sql query

你有一個SQL查詢的一部分

SELECT * FROM mytable WHERE parametertype IN( then you join it with a string passed as a parameter "'a', 'b', 'c', 'd'" using CONCAT function for example into stmp as above and then execute stmp