SQL IN 절 매개 변수화
IN이와 같이 가변 개수의 인수가 있는 절을 포함하는 쿼리를 어떻게 매개 변수화 합니까?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
이 쿼리에서 인수의 수는 1에서 5까지입니다.
나는 이것 (또는 XML)에 전용 저장 프로 시저를 사용하지 않는 것을 선호하지만 SQL Server 2008에 특정한 우아한 방법이 있다면 그것에 대해 열려 있습니다.
내가 사용한 빠르고 더러운 기술은 다음과 같습니다.
SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'
그래서 다음은 C # 코드입니다.
string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";
using (SqlCommand cmd = new SqlCommand(cmdText)) {
cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}
두 가지주의 사항 :
- 성능이 끔찍합니다.
LIKE "%...%"쿼리는 인덱싱되지 않습니다. |, 공백 또는 null 태그 가 없는지 확인하십시오. 그렇지 않으면 작동하지 않습니다.
이 작업을 수행하는 다른 방법은 일부 사람들이 더 깨끗하다고 생각할 수 있으므로 계속 읽으십시오.
다음과 같이 각 값을 매개 변수화 할 수 있습니다 .
string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";
string[] paramNames = tags.Select(
(s, i) => "@tag" + i.ToString()
).ToArray();
string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
for(int i = 0; i < paramNames.Length; i++) {
cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
}
}
당신에게 줄 것 :
cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"
아니요, 이것은 SQL 주입에 개방되지 않습니다 . CommandText에 삽입 된 유일한 텍스트는 사용자 입력을 기반으로하지 않습니다. 하드 코딩 된 "@tag"접두사와 배열의 인덱스만을 기반으로합니다. 인덱스는 항상 정수이며 사용자가 생성하지 않으며 안전합니다.
사용자가 입력 한 값은 여전히 매개 변수에 채워져 있으므로 취약점이 없습니다.
편집하다:
주입 문제는 제쳐두고, 다양한 수의 매개 변수 (위와 같이)를 수용하도록 명령 텍스트를 구성하면 캐시 된 쿼리를 활용하는 SQL 서버의 기능이 방해된다는 점에 유의하십시오. 결과적으로 SQL 자체에 술어 문자열을 삽입하는 것과는 반대로 처음에 매개 변수를 사용하는 값을 거의 확실히 잃게됩니다.
캐시 된 쿼리 계획이 가치가 없다는 것은 아니지만 IMO이 쿼리는 많은 이점을 볼 수있을만큼 충분히 복잡하지 않습니다. 컴파일 비용은 실행 비용에 근접하거나 초과 할 수 있지만 여전히 밀리 초입니다.
RAM이 충분하다면 SQL Server가 일반적인 매개 변수 수에 대한 계획도 캐시 할 것으로 예상합니다. 항상 5 개의 매개 변수를 추가 할 수 있고 지정되지 않은 태그를 NULL로 둘 수 있다고 가정합니다. 쿼리 계획은 동일해야하지만 나에게는 매우 추악 해 보이며 마이크로 최적화의 가치가 있는지 확실하지 않습니다. 스택 오버플로-그만한 가치가 있습니다).
또한 SQL Server 7 이상은 쿼리 를 자동 매개 변수화 하므로 성능 측면에서 매개 변수를 사용할 필요가 없습니다. 그러나 보안 측면에서는 특히 이와 같이 사용자가 입력 한 데이터의 경우 중요 합니다.
SQL Server 2008의 경우 테이블 값 매개 변수를 사용할 수 있습니다 . 약간의 작업이지만 다른 방법 보다 깨끗 합니다.
먼저 유형을 만들어야합니다.
CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )
그러면 ADO.NET 코드는 다음과 같습니다.
string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";
// value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";
// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {
if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows
var firstRecord = values.First();
var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);
return values.Select(v =>
{
var r = new SqlDataRecord(metadata);
r.SetValues(v);
return r;
});
}
원래 질문은 "어떻게 쿼리를 매개 변수화합니까?" 였습니다.
이것은 원래 질문에 대한 답 이 아니라는 것을 바로 여기에서 말씀 드리겠습니다 . 다른 좋은 답변에서 이미 몇 가지 데모가 있습니다.
그 말로, 계속해서이 답변에 플래그를 지정하고, 반대 투표를하고, 답변이 아닌 것으로 표시하십시오 ... 옳다고 생각하는 것은 무엇이든하십시오.
내가 (외 231 명) 찬성 한 선호 답변은 Mark Brackett의 답변을 참조하세요. 그의 대답에 주어진 접근 방식은 1) 바인드 변수를 효과적으로 사용하고 2) sargable 술어를 허용합니다.
선택한 답변
내가 여기서 다루고 싶은 것은 Joel Spolsky의 대답에 주어진 접근 방식이며, 정답으로 "선택된"대답입니다.
Joel Spolsky의 접근 방식은 영리합니다. 그리고 그것은 합리적으로 작동하며, "정상"값이 주어지고 NULL 및 빈 문자열과 같은 규범적인 에지 케이스에서 예측 가능한 동작과 예측 가능한 성능을 보여줄 것입니다. 그리고 특정 응용 프로그램에 충분할 수 있습니다.
그러나이 접근 방식을 일반화하는 측면에서 Name열에 와일드 카드 문자가 포함 된 경우 (LIKE 조건 자에 의해 인식됨) 와 같이 더 모호한 모서리 사례도 고려해 보겠습니다 . 가장 일반적으로 사용되는 와일드 카드 문자는 %(퍼센트 기호)입니다. 이제 여기서 처리하고 나중에 다른 사례로 넘어가겠습니다.
% 문자와 관련된 몇 가지 문제
의 이름 값을 고려하십시오 'pe%ter'. (여기의 예에서는 열 이름 대신 리터럴 문자열 값을 사용합니다.) 이름 값이` 'pe % ter'인 행은 다음 형식의 쿼리에 의해 반환됩니다.
select ...
where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'
그러나 검색어의 순서가 반대로되면 동일한 행이 반환 되지 않습니다 .
select ...
where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'
우리가 관찰하는 행동은 다소 이상합니다. 목록에서 검색어 순서를 변경하면 결과 집합이 변경됩니다.
pe%ter그가 아무리 좋아하든 땅콩 버터를 맞추고 싶지 않을 수도 있다는 것은 말할 필요 도 없습니다.
모호한 코너 케이스
(예, 모호한 경우라는 데 동의합니다. 테스트 할 가능성이 거의없는 경우 일 것입니다. 열 값에 와일드 카드를 사용하지 않을 것입니다. 응용 프로그램이 이러한 값이 저장되는 것을 방지한다고 가정 할 수 있습니다. 그러나 내 경험상 LIKE비교 연산자 의 오른쪽에 와일드 카드로 간주되는 문자 나 패턴을 특별히 허용하지 않는 데이터베이스 제약 조건을 거의 본 적이 없습니다 .
구멍 패치
이 구멍을 패치하는 한 가지 방법은 %와일드 카드 문자 를 이스케이프하는 것 입니다. (연산자의 escape 절에 익숙하지 않은 사용자를 위해 여기에 SQL Server 문서 링크가 있습니다 .
select ...
where '|peanut|butter|'
like '%|' + 'pe\%ter' + '|%' escape '\'
이제 리터럴 %를 일치시킬 수 있습니다. 물론 열 이름이 있으면 와일드 카드를 동적으로 이스케이프해야합니다. 다음 과 REPLACE같이이 함수를 사용하여 %문자의 발생을 찾고 각 문자 앞에 백 슬래시 문자를 삽입 할 수 있습니다 .
select ...
where '|pe%ter|'
like '%|' + REPLACE( 'pe%ter' ,'%','\%') + '|%' escape '\'
따라서 % 와일드 카드로 문제가 해결됩니다. 거의.
탈출 탈출
우리의 솔루션이 또 다른 문제를 도입했음을 알고 있습니다. 이스케이프 문자입니다. 또한 이스케이프 문자 자체의 발생을 이스케이프해야한다는 것을 알 수 있습니다. 이번에는! 이스케이프 문자로 :
select ...
where '|pe%t!r|'
like '%|' + REPLACE(REPLACE( 'pe%t!r' ,'!','!!'),'%','!%') + '|%' escape '!'
밑줄도
이제 우리는 REPLACE밑줄 와일드 카드에 다른 핸들을 추가 할 수 있습니다 . 이번에는 재미로 $를 이스케이프 문자로 사용하겠습니다.
select ...
where '|p_%t!r|'
like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'
이 접근 방식은 Oracle과 MySQL 및 SQL Server에서 작동하기 때문에 이스케이프 방식을 선호합니다. (정규 표현식에서 사용하는 문자이기 때문에 일반적으로 \ 백 슬래시를 이스케이프 문자로 사용합니다. 그러나 왜 관례에 의해 제한되어야합니다!
그 성가신 브래킷
SQL Server에서는 와일드 카드 문자를 대괄호로 묶어 리터럴로 처리 할 수도 있습니다 []. 따라서 우리는 적어도 SQL Server에 대해서는 아직 수정하지 않았습니다. 괄호 쌍은 특별한 의미를 가지므로 이들도 이스케이프해야합니다. 괄호를 적절하게 이스케이프 처리하면 적어도 괄호 안의 하이픈 -과 캐럿 을 신경 쓰지 않아도 ^됩니다. 그리고 우리는 대괄호의 특별한 의미를 기본적으로 비활성화 할 것이기 때문에 대괄호 안에 모든 %및 _문자를 이스케이프 처리 한 채로 둘 수 있습니다 .
일치하는 괄호 쌍을 찾는 것은 그렇게 어렵지 않습니다. 싱글 톤 % 및 _의 발생을 처리하는 것보다 조금 더 어렵습니다. (단순 대괄호는 리터럴로 간주되고 이스케이프 할 필요가 없기 때문에 모든 대괄호를 이스케이프하는 것만으로는 충분하지 않습니다. 더 많은 테스트 케이스를 실행하지 않고도 처리 할 수있는 것보다 논리가 약간 모호해집니다. .)
인라인 표현이 지저분해진다
SQL의 인라인 표현식이 점점 더 길어지고 있습니다. 우리는 아마 그것을 작동시킬 수 있지만 천국은 뒤에 와서 그것을 해독해야하는 불쌍한 영혼을 돕습니다. 나는 인라인 표현을 좋아하는 팬이기 때문에 여기서는 사용하지 않는 경향이있다. 엉망이 된 이유를 설명하고 사과하는 것을 댓글로 남기고 싶지 않기 때문이다.
어디 기능?
좋습니다. SQL에서 인라인 표현식으로 처리하지 않으면 가장 가까운 대안은 사용자 정의 함수입니다. (오라클에서 할 수있는 것처럼 인덱스를 정의 할 수 없다면) 속도가 빨라지지는 않습니다. 함수를 만들어야한다면 SQL을 호출하는 코드에서 더 잘 할 수 있습니다. 성명서.
그리고 그 기능은 DBMS 및 버전에 따라 동작에 약간의 차이가있을 수 있습니다. (모든 데이터베이스 엔진을 상호 교환 적으로 사용할 수 있기를 바라는 모든 Java 개발자에게 한마디 부탁드립니다.)
도메인 지식
열의 도메인 (즉, 열에 적용되는 허용 가능한 값 집합)에 대한 전문 지식이있을 수 있습니다. 열에 저장된 값에는 퍼센트 기호, 밑줄 또는 대괄호가 포함되지 않는다는 선험적으로 알 수 있습니다. 이 경우 해당 사례를 다룬다는 간단한 설명 만 포함됩니다.
열에 저장된 값은 % 또는 _ 문자를 허용 할 수 있지만 제약 조건에서는 값이 LIKE 비교 "안전"하도록 정의 된 문자를 사용하여 해당 값을 이스케이프해야 할 수 있습니다. 다시 말하지만, 허용되는 값 집합, 특히 어떤 문자가 이스케이프 문자로 사용되는지에 대한 간단한 설명과 Joel Spolsky의 접근 방식을 따릅니다.
그러나 전문 지식과 보증이 없으면 최소한 이러한 모호한 코너 케이스를 처리하는 것을 고려하고 동작이 합리적이고 "사양에 따라"있는지 고려하는 것이 중요합니다.
요약 된 기타 문제
나는 다른 사람들이 일반적으로 고려되는 다른 관심 분야 중 일부를 이미 충분히 지적했다고 생각합니다.
SQL 인젝션 (사용자가 제공 한 정보로 보이는 것을 취하고이를 바인드 변수를 통해 제공하지 않고 SQL 텍스트에 포함합니다. 바인드 변수를 사용할 필요는 없으며 SQL 인젝션을 방해하는 편리한 방법 중 하나 일뿐입니다. 그것을 다루는 방법 :
인덱스 탐색 대신 인덱스 스캔을 사용하는 최적화 프로그램 계획, 와일드 카드를 이스케이프하기위한 표현식 또는 함수 필요 (표현식 또는 함수에 대한 가능한 인덱스)
바인드 변수 대신 리터럴 값을 사용하면 확장성에 영향을줍니다.
결론
나는 Joel Spolsky의 접근 방식을 좋아합니다. 영리합니다. 그리고 작동합니다.
그러나 그것을 보자 마자 나는 그것에 잠재적 인 문제를 즉시 보았습니다. 그리고 그것을 미끄러지게하는 것은 내 본성이 아닙니다. 나는 다른 사람들의 노력을 비판하는 것을 의미하지 않습니다. 많은 개발자들이 작업을 매우 개인적으로 받아들이는 것을 알고 있습니다. 왜냐하면 그들은 작업에 너무 많은 투자를하고 너무 많은 관심을 갖고 있기 때문입니다. 그러니 이해해주십시오. 이것은 개인적인 공격이 아닙니다. 여기서 제가 확인하는 것은 테스트가 아닌 프로덕션에서 발생하는 문제 유형입니다.
예, 원래 질문과는 거리가 멀었습니다. 그러나 질문에 대한 "선택된"답변과 관련하여 내가 중요한 문제로 간주하는 것에 대해이 메모를 남길 다른 곳은 어디입니까?
매개 변수를 문자열로 전달할 수 있습니다.
그래서 당신은 문자열이
DECLARE @tags
SET @tags = ‘ruby|rails|scruffy|rubyonrails’
select * from Tags
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc
그런 다음 문자열을 1 개의 매개 변수로 전달하기 만하면됩니다.
내가 사용하는 분할 기능은 다음과 같습니다.
CREATE FUNCTION [dbo].[fnSplit](
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
Jeff / Joel이 오늘 팟 캐스트에서 이것에 대해 이야기하는 것을 들었습니다 ( 에피소드 34 , 2008-12-16 (MP3, 31MB), 1 시간 03 분 38 초-1 시간 06 분 45 초). 사용 된 SQL에 LINQ를 ,하지만 어쩌면 그것은 도랑에 빠지게되었다. 다음은 LINQ to SQL에서도 마찬가지입니다.
var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };
var results = from tag in Tags
where inValues.Contains(tag.Name)
select tag;
그게 다야. 그리고 예, LINQ는 이미 충분히 Contains거꾸로 보이지만 절은 나에게 더 거꾸로 보입니다. 직장에서 프로젝트에 대해 유사한 쿼리를 수행해야했을 때 로컬 배열과 SQL Server 테이블 사이에 조인을 수행하여 자연스럽게 잘못된 방식으로이 작업을 수행하려고했습니다. LINQ to SQL 변환기가 어떻게 든 번역. 그렇지 않았지만 설명적인 오류 메시지를 제공하고 Contains 사용하도록 지시했습니다 .
어쨌든 강력하게 권장되는 LINQPad 에서 실행하고이 쿼리를 실행하면 SQL LINQ 공급자가 생성 한 실제 SQL을 볼 수 있습니다. IN절로 매개 변수화되는 각 값을 보여줍니다 .
.NET에서 호출하는 경우 Dapper dot net을 사용할 수 있습니다 .
string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = dataContext.Query<Tags>(@"
select * from Tags
where Name in @names
order by Count desc", new {names});
여기서 Dapper는 생각을 수행하므로 그럴 필요가 없습니다. 물론 LINQ to SQL 에서도 비슷한 일이 가능합니다 .
string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = from tag in dataContext.Tags
where names.Contains(tag.Name)
orderby tag.Count descending
select tag;
이것은 아마도 그것을하는 절반의 불쾌한 방법 일 것입니다. 한 번 사용했는데 오히려 효과적이었습니다.
목표에 따라 유용 할 수 있습니다.
- 하나의 열이 있는 임시 테이블 을 만듭니다 .
INSERT각 조회 값을 해당 열에 입력합니다.- 를 사용하는 대신
IN표준JOIN규칙을 사용할 수 있습니다 . (유연성 ++)
이렇게하면 수행 할 수있는 작업에 약간의 유연성이 추가되지만 쿼리 할 테이블이 크고 인덱싱이 좋으며 매개 변수화 된 목록을 두 번 이상 사용하려는 상황에 더 적합합니다. 두 번 실행하고 모든 위생을 수동으로 수행해야하는 시간을 절약합니다.
얼마나 빠른지 정확히 프로파일 링 하지는 못했지만 제 상황에서는 필요했습니다.
조인 할 수있는 테이블 변수를 생성하는 함수가 있습니다.
ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list AS VARCHAR(8000),
@delim AS VARCHAR(10))
RETURNS @listTable TABLE(
Position INT,
Value VARCHAR(8000))
AS
BEGIN
DECLARE @myPos INT
SET @myPos = 1
WHILE Charindex(@delim, @list) > 0
BEGIN
INSERT INTO @listTable
(Position,Value)
VALUES (@myPos,LEFT(@list, Charindex(@delim, @list) - 1))
SET @myPos = @myPos + 1
IF Charindex(@delim, @list) = Len(@list)
INSERT INTO @listTable
(Position,Value)
VALUES (@myPos,'')
SET @list = RIGHT(@list, Len(@list) - Charindex(@delim, @list))
END
IF Len(@list) > 0
INSERT INTO @listTable
(Position,Value)
VALUES (@myPos,@list)
RETURN
END
그래서:
@Name varchar(8000) = null // parameter for search values
select * from Tags
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc
이것은 총체적이지만 적어도 하나가 보장된다면 다음과 같이 할 수 있습니다.
SELECT ...
...
WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )
IN ( 'tag1', 'tag2', 'tag1', 'tag1', 'tag1')은 SQL Server에서 쉽게 최적화됩니다. 또한 직접 인덱스 검색을 얻을 수 있습니다.
에서 SQL Server 2016+당신은 사용할 수있는 STRING_SPLIT기능 :
DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';
SELECT *
FROM Tags
WHERE Name IN (SELECT [value] FROM STRING_SPLIT(@names, ','))
ORDER BY Count DESC;
또는:
DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';
SELECT t.*
FROM Tags t
JOIN STRING_SPLIT(@names,',')
ON t.Name = [value]
ORDER BY Count DESC;
허용 응답 코스 작업의 뜻과 길을 가야하는 것 중 하나이지만, 안티 패턴이다.
E. 값 목록으로 행 찾기
이는 응용 프로그램 계층 또는 Transact-SQL에서 동적 SQL 문자열을 만들거나 LIKE 연산자를 사용하는 것과 같은 일반적인 안티 패턴을 대체합니다.
SELECT ProductId, Name, Tags FROM Product WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';
원래 질문에는 요구 사항이
SQL Server 2008있습니다. 이 질문은 종종 중복으로 사용되기 때문에이 답변을 참고로 추가했습니다.
제 생각에이 문제를 해결하는 가장 좋은 소스는이 사이트에 게시 된 내용입니다.
CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS
BEGIN
--DECLARE @T Table (col1 varchar(50))
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
DECLARE @separator_position INT -- This is used to locate each separator character
DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
SET @array = @array + @separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
-- This is where you process the values passed.
INSERT into @T VALUES (@array_value)
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
-- This replaces what we just processed with and empty string
SELECT @array = STUFF(@array, 1, @separator_position, '')
END
RETURN
END
사용하다:
SELECT * FROM dbo.fnParseArray('a,b,c,d,e,f', ',')
크레딧 : Dinakar Nethi
나는 (그 이후 테이블 형식의 매개 변수를 전달 할 SQL 서버 2008 ), 그리고 작업을 수행 where exists하거나 내부 조인. 를 사용하여 XML을 사용한 sp_xml_preparedocument다음 해당 임시 테이블을 인덱싱 할 수도 있습니다 .
IMHO의 올바른 방법은 목록을 문자열로 저장하는 것입니다 (DBMS가 지원하는 길이에 따라 제한됨). 유일한 트릭은 (처리를 단순화하기 위해) 문자열의 시작과 끝에 구분 기호 (제 예에서는 쉼표)가 있다는 것입니다. 아이디어는 "즉시 정규화"하여 목록을 값당 하나의 행을 포함하는 1 열 테이블로 바꾸는 것입니다. 이것은 당신이 돌릴 수 있습니다
in (ct1, ct2, ct3 ... ctn)
로
(선택 ...)
또는 (아마도 선호하는 솔루션) 일반 조인을 사용합니다. 목록에서 중복 값 문제를 방지하기 위해 "고유"를 추가하면됩니다.
불행히도 문자열을 자르는 기술은 제품에 따라 상당히 다릅니다. 다음은 SQL Server 버전입니다.
with qry(n, names) as
(select len(list.names) - len(replace(list.names, ',', '')) - 1 as n,
substring(list.names, 2, len(list.names)) as names
from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as list
union all
select (n - 1) as n,
substring(names, 1 + charindex(',', names), len(names)) as names
from qry
where n > 1)
select n, substring(names, 1, charindex(',', names) - 1) dwarf
from qry;
Oracle 버전 :
select n, substr(name, 1, instr(name, ',') - 1) dwarf
from (select n,
substr(val, 1 + instr(val, ',', 1, n)) name
from (select rownum as n,
list.val
from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val
from dual) list
connect by level < length(list.val) -
length(replace(list.val, ',', ''))));
그리고 MySQL 버전 :
select pivot.n,
substring_index(substring_index(list.val, ',', 1 + pivot.n), ',', -1) from (select 1 as n
union all
select 2 as n
union all
select 3 as n
union all
select 4 as n
union all
select 5 as n
union all
select 6 as n
union all
select 7 as n
union all
select 8 as n
union all
select 9 as n
union all
select 10 as n) pivot, (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val) as list where pivot.n < length(list.val) -
length(replace(list.val, ',', ''));
(물론 "피벗"은 목록에서 찾을 수있는 최대 항목 수만큼 행을 반환해야합니다.)
당신이 가지고있는 경우에 SQL 서버 2008 이상 내가 사용하는 거라고 매개 변수 값을 갖는 테이블 .
SQL Server 2005 에 갇혀있을만큼 운이 좋지 않다면 다음 과 같은 CLR 함수를 추가 할 수 있습니다.
[SqlFunction(
DataAccessKind.None,
IsDeterministic = true,
SystemDataAccess = SystemDataAccessKind.None,
IsPrecise = true,
FillRowMethodName = "SplitFillRow",
TableDefinintion = "s NVARCHAR(MAX)"]
public static IEnumerable Split(SqlChars seperator, SqlString s)
{
if (s.IsNull)
return new string[0];
return s.ToString().Split(seperator.Buffer);
}
public static void SplitFillRow(object row, out SqlString s)
{
s = new SqlString(row.ToString());
}
이렇게 사용할 수 있습니다.
declare @desiredTags nvarchar(MAX);
set @desiredTags = 'ruby,rails,scruffy,rubyonrails';
select * from Tags
where Name in [dbo].[Split] (',', @desiredTags)
order by Count desc
나는 이것이 정적 쿼리가 갈 길이 아닌 경우라고 생각합니다. in 절의 목록을 동적으로 작성하고 작은 따옴표를 이스케이프하고 SQL을 동적으로 작성하십시오. 이 경우 작은 목록으로 인해 어떤 방법과도 큰 차이를 보지 못할 것입니다. 그러나 가장 효율적인 방법은 실제로 게시물에 작성된 그대로 SQL을 보내는 것입니다. 가장 예쁜 코드를 만드는 것보다 가장 효율적인 방법으로 작성하는 것이 좋은 습관이라고 생각합니다. SQL을 동적으로 빌드하는 것은 나쁜 습관이라고 생각합니다.
매개 변수가 커지는 많은 경우 분할 함수가 쿼리 자체보다 실행하는 데 더 오래 걸리는 것을 보았습니다. SQL 2008에서 테이블 값 매개 변수가있는 저장 프로시 저는 내가 고려할 유일한 다른 옵션이지만 귀하의 경우에는 속도가 느릴 것입니다. SQL은 어쨌든 목록에 대한 임시 테이블을 작성하기 때문에 TVP는 TVP의 기본 키를 검색하는 경우 큰 목록에 대해서만 더 빠를 것입니다 (목록이 큰 경우). 테스트하지 않으면 확실히 알 수 없습니다.
또한 기본값이 null이고 WHERE Column1 IN (@ Param1, @ Param2, @ Param3, ..., @ Param500)이있는 500 개의 매개 변수가있는 저장 프로시 저도 보았습니다. 이로 인해 SQL은 임시 테이블을 만들고 정렬 / 구별을 수행 한 다음 인덱스 검색 대신 테이블 스캔을 수행했습니다. 이것이 눈에 띄는 차이를 만들지 않을 정도로 충분히 작은 규모이지만 해당 쿼리를 매개 변수화하여 수행하는 작업입니다. IN 목록에 NULL을 사용하지 않는 것이 좋습니다. 마치 NOT IN으로 변경되면 의도 한대로 작동하지 않습니다. 매개 변수 목록을 동적으로 작성할 수 있지만 얻을 수있는 유일한 점은 객체가 작은 따옴표를 이스케이프한다는 것입니다. 개체가 매개 변수를 찾기 위해 쿼리를 구문 분석해야하기 때문에이 방법은 응용 프로그램 쪽에서도 약간 느립니다.
저장 프로 시저 또는 매개 변수가있는 쿼리에 대한 실행 계획을 다시 사용하면 성능이 향상 될 수 있지만 실행되는 첫 번째 쿼리에 의해 결정된 하나의 실행 계획에 고정됩니다. 많은 경우 후속 쿼리에는 이상적이지 않을 수 있습니다. 귀하의 경우 실행 계획의 재사용은 아마도 플러스가 될 것이지만 예제는 정말 간단한 쿼리이므로 전혀 차이가 없을 수 있습니다.
Cliffs 노트 :
귀하의 경우에 대해 목록에 고정 된 수의 항목을 사용하여 매개 변수화하거나 (사용하지 않으면 null) 매개 변수를 사용하거나 사용하지 않고 쿼리를 동적으로 작성하거나 테이블 값 매개 변수와 함께 저장 프로 시저를 사용하면 큰 차이가 없습니다. . 그러나 일반적인 권장 사항은 다음과 같습니다.
매개 변수가 거의없는 케이스 / 간단한 쿼리 :
테스트가 더 나은 성능을 보여줄 경우 매개 변수가있는 동적 SQL.
재사용 가능한 실행 계획이있는 쿼리, 단순히 매개 변수를 변경하거나 쿼리가 복잡한 경우 여러 번 호출됩니다.
동적 매개 변수가있는 SQL.
큰 목록이있는 쿼리 :
테이블 값 매개 변수가있는 저장 프로 시저입니다. 목록이 많이 다를 수있는 경우 저장 프로 시저에서 WITH RECOMPILE을 사용하거나 매개 변수없이 동적 SQL을 사용하여 각 쿼리에 대한 새 실행 계획을 생성합니다.
여기서 XML을 사용할 수 있습니다.
declare @x xml
set @x='<items>
<item myvalue="29790" />
<item myvalue="31250" />
</items>
';
With CTE AS (
SELECT
x.item.value('@myvalue[1]', 'decimal') AS myvalue
FROM @x.nodes('//items/item') AS x(item) )
select * from YourTable where tableColumnName in (select myvalue from cte)
기본적으로 테이블 값 함수 (문자열에서 테이블을 반환하는)를 IN 조건에 전달하여 이에 접근합니다.
다음은 UDF에 대한 코드입니다 (어딘가에서 Stack Overflow에서 가져 왔습니다. 지금은 소스를 찾을 수 없습니다)
CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
이것을 받으면 코드는 다음과 같이 간단합니다.
select * from Tags
where Name in (select s from dbo.split(';','ruby;rails;scruffy;rubyonrails'))
order by Count desc
엄청나게 긴 문자열이 아니라면 이것은 테이블 인덱스와 잘 작동합니다.
필요한 경우 임시 테이블에 삽입하고 인덱싱 한 다음 조인을 실행할 수 있습니다.
또 다른 가능한 해결책은 가변 개수의 인수를 저장 프로 시저에 전달하는 대신 뒤에있는 이름이 포함 된 단일 문자열을 전달하지만 '<>'로 묶어 고유하게 만드는 것입니다. 그런 다음 PATINDEX를 사용하여 이름을 찾습니다.
SELECT *
FROM Tags
WHERE PATINDEX('%<' + Name + '>%','<jo>,<john>,<scruffy>,<rubyonrails>') > 0
다음 저장 프로 시저를 사용하십시오. 여기 에서 찾을 수있는 사용자 정의 분할 기능을 사용합니다 .
create stored procedure GetSearchMachingTagNames
@PipeDelimitedTagNames varchar(max),
@delimiter char(1)
as
begin
select * from Tags
where Name in (select data from [dbo].[Split](@PipeDelimitedTagNames,@delimiter)
end
IN 절 안에 쉼표 (,)로 구분 된 문자열이 저장되어 있으면 charindex 함수를 사용하여 값을 가져올 수 있습니다. .NET을 사용하는 경우 SqlParameters로 매핑 할 수 있습니다.
DDL 스크립트 :
CREATE TABLE Tags
([ID] int, [Name] varchar(20))
;
INSERT INTO Tags
([ID], [Name])
VALUES
(1, 'ruby'),
(2, 'rails'),
(3, 'scruffy'),
(4, 'rubyonrails')
;
T-SQL :
DECLARE @Param nvarchar(max)
SET @Param = 'ruby,rails,scruffy,rubyonrails'
SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0
.NET 코드에서 위의 문을 사용하고 SqlParameter로 매개 변수를 매핑 할 수 있습니다.
편집 : 다음 스크립트를 사용하여 SelectedTags라는 테이블을 만듭니다.
DDL 스크립트 :
Create table SelectedTags
(Name nvarchar(20));
INSERT INTO SelectedTags values ('ruby'),('rails')
T-SQL :
DECLARE @list nvarchar(max)
SELECT @list=coalesce(@list+',','')+st.Name FROM SelectedTags st
SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0
이와 같은 가변 개수의 인수에 대해 내가 아는 유일한 방법은 SQL을 명시 적으로 생성하거나 원하는 항목으로 임시 테이블을 채우고 임시 테이블에 대해 조인하는 작업을 수행하는 것입니다.
ColdFusion 에서는 다음을 수행합니다.
<cfset myvalues = "ruby|rails|scruffy|rubyonrails">
<cfquery name="q">
select * from sometable where values in <cfqueryparam value="#myvalues#" list="true">
</cfquery>
다음은 쿼리 문자열에서 사용할 로컬 테이블을 다시 만드는 기술입니다. 이렇게하면 모든 구문 분석 문제가 제거됩니다.
문자열은 모든 언어로 작성할 수 있습니다. 이 예에서는 해결하려고했던 원래 문제이기 때문에 SQL을 사용했습니다. 나중에 실행할 문자열로 테이블 데이터를 즉시 전달하는 깨끗한 방법이 필요했습니다.
사용자 정의 유형 사용은 선택 사항입니다. 유형 생성은 한 번만 생성되며 미리 수행 할 수 있습니다. 그렇지 않으면 문자열의 선언에 전체 테이블 유형을 추가하십시오.
일반 패턴은 확장하기 쉽고 더 복잡한 테이블을 전달하는 데 사용할 수 있습니다.
-- Create a user defined type for the list.
CREATE TYPE [dbo].[StringList] AS TABLE(
[StringValue] [nvarchar](max) NOT NULL
)
-- Create a sample list using the list table type.
DECLARE @list [dbo].[StringList];
INSERT INTO @list VALUES ('one'), ('two'), ('three'), ('four')
-- Build a string in which we recreate the list so we can pass it to exec
-- This can be done in any language since we're just building a string.
DECLARE @str nvarchar(max);
SET @str = 'DECLARE @list [dbo].[StringList]; INSERT INTO @list VALUES '
-- Add all the values we want to the string. This would be a loop in C++.
SELECT @str = @str + '(''' + StringValue + '''),' FROM @list
-- Remove the trailing comma so the query is valid sql.
SET @str = substring(@str, 1, len(@str)-1)
-- Add a select to test the string.
SET @str = @str + '; SELECT * FROM @list;'
-- Execute the string and see we've pass the table correctly.
EXEC(@str)
SQL Server 2016+에서 또 다른 가능성은 OPENJSON함수 를 사용하는 것입니다.
이 접근 방식은 OPENJSON 에서 블로그에 게시 됩니다. ID 목록으로 행을 선택하는 가장 좋은 방법 중 하나입니다 .
아래의 전체 작업 예
CREATE TABLE dbo.Tags
(
Name VARCHAR(50),
Count INT
)
INSERT INTO dbo.Tags
VALUES ('VB',982), ('ruby',1306), ('rails',1478), ('scruffy',1), ('C#',1784)
GO
CREATE PROC dbo.SomeProc
@Tags VARCHAR(MAX)
AS
SELECT T.*
FROM dbo.Tags T
WHERE T.Name IN (SELECT J.Value COLLATE Latin1_General_CI_AS
FROM OPENJSON(CONCAT('[', @Tags, ']')) J)
ORDER BY T.Count DESC
GO
EXEC dbo.SomeProc @Tags = '"ruby","rails","scruffy","rubyonrails"'
DROP TABLE dbo.Tags
또 다른 대안이 있습니다. 쉼표로 구분 된 목록을 문자열 매개 변수로 저장 프로 시저에 전달하면됩니다.
CREATE PROCEDURE [dbo].[sp_myproc]
@UnitList varchar(MAX) = '1,2,3'
AS
select column from table
where ph.UnitID in (select * from CsvToInt(@UnitList))
그리고 기능 :
CREATE Function [dbo].[CsvToInt] ( @Array varchar(MAX))
returns @IntTable table
(IntValue int)
AS
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(MAX)
set @array = @array + ','
while patindex('%,%' , @array) <> 0
begin
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)
Insert @IntTable
Values (Cast(@array_value as int))
select @array = stuff(@array, 1, @separator_position, '')
end
return
end
UDF, XML이 필요하지 않은 답변이 있습니다. IN은 SELECT 문을 허용하므로 SELECT * FROM Test where Data IN (SELECT Value FROM TABLE)
문자열을 테이블로 변환하는 방법 만 있으면됩니다.
이는 재귀 CTE 또는 숫자 테이블 (또는 Master..spt_value)이있는 쿼리를 사용하여 수행 할 수 있습니다.
다음은 CTE 버전입니다.
DECLARE @InputString varchar(8000) = 'ruby,rails,scruffy,rubyonrails'
SELECT @InputString = @InputString + ','
;WITH RecursiveCSV(x,y)
AS
(
SELECT
x = SUBSTRING(@InputString,0,CHARINDEX(',',@InputString,0)),
y = SUBSTRING(@InputString,CHARINDEX(',',@InputString,0)+1,LEN(@InputString))
UNION ALL
SELECT
x = SUBSTRING(y,0,CHARINDEX(',',y,0)),
y = SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y))
FROM
RecursiveCSV
WHERE
SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y)) <> '' OR
SUBSTRING(y,0,CHARINDEX(',',y,0)) <> ''
)
SELECT
*
FROM
Tags
WHERE
Name IN (select x FROM RecursiveCSV)
OPTION (MAXRECURSION 32767);
나는 최고 투표 답변의 더 간결한 버전 을 사용합니다 .
List<SqlParameter> parameters = tags.Select((s, i) => new SqlParameter("@tag" + i.ToString(), SqlDbType.NVarChar(50)) { Value = s}).ToList();
var whereCondition = string.Format("tags in ({0})", String.Join(",",parameters.Select(s => s.ParameterName)));
태그 매개 변수를 두 번 반복합니다. 그러나 그것은 대부분의 경우 중요하지 않습니다 (병목 현상이 아니라면 루프를 펼치십시오).
성능에 정말 관심이 있고 루프를 두 번 반복하고 싶지 않다면 덜 아름다운 버전이 있습니다.
var parameters = new List<SqlParameter>();
var paramNames = new List<string>();
for (var i = 0; i < tags.Length; i++)
{
var paramName = "@tag" + i;
//Include size and set value explicitly (not AddWithValue)
//Because SQL Server may use an implicit conversion if it doesn't know
//the actual size.
var p = new SqlParameter(paramName, SqlDbType.NVarChar(50) { Value = tags[i]; }
paramNames.Add(paramName);
parameters.Add(p);
}
var inClause = string.Join(",", paramNames);
이 문제에 대한 또 다른 대답이 있습니다.
(2013 년 6 월 4 일에 게시 된 새 버전).
private static DataSet GetDataSet(SqlConnectionStringBuilder scsb, string strSql, params object[] pars)
{
var ds = new DataSet();
using (var sqlConn = new SqlConnection(scsb.ConnectionString))
{
var sqlParameters = new List<SqlParameter>();
var replacementStrings = new Dictionary<string, string>();
if (pars != null)
{
for (int i = 0; i < pars.Length; i++)
{
if (pars[i] is IEnumerable<object>)
{
List<object> enumerable = (pars[i] as IEnumerable<object>).ToList();
replacementStrings.Add("@" + i, String.Join(",", enumerable.Select((value, pos) => String.Format("@_{0}_{1}", i, pos))));
sqlParameters.AddRange(enumerable.Select((value, pos) => new SqlParameter(String.Format("@_{0}_{1}", i, pos), value ?? DBNull.Value)).ToArray());
}
else
{
sqlParameters.Add(new SqlParameter(String.Format("@{0}", i), pars[i] ?? DBNull.Value));
}
}
}
strSql = replacementStrings.Aggregate(strSql, (current, replacementString) => current.Replace(replacementString.Key, replacementString.Value));
using (var sqlCommand = new SqlCommand(strSql, sqlConn))
{
if (pars != null)
{
sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
}
else
{
//Fail-safe, just in case a user intends to pass a single null parameter
sqlCommand.Parameters.Add(new SqlParameter("@0", DBNull.Value));
}
using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
{
sqlDataAdapter.Fill(ds);
}
}
}
return ds;
}
건배.
유일한 승리는 플레이하지 않는 것입니다.
당신에게 무한한 가변성은 없습니다. 유한 가변성 만.
SQL에는 다음과 같은 절이 있습니다.
and ( {1}==0 or b.CompanyId in ({2},{3},{4},{5},{6}) )
C # 코드에서 다음과 같이합니다.
int origCount = idList.Count;
if (origCount > 5) {
throw new Exception("You may only specify up to five originators to filter on.");
}
while (idList.Count < 5) { idList.Add(-1); } // -1 is an impossible value
return ExecuteQuery<PublishDate>(getValuesInListSQL,
origCount,
idList[0], idList[1], idList[2], idList[3], idList[4]);
기본적으로 카운트가 0이면 필터가없고 모든 것이 통과됩니다. 개수가 0보다 크면 값이 목록에 있어야하지만 목록이 불가능한 값으로 5 개로 채워 져서 SQL이 여전히 의미가 있습니다.
때로는 절름발이 솔루션이 실제로 작동하는 유일한 솔루션입니다.
참고 URL : https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause
'Nice programing' 카테고리의 다른 글
| .gitignore 파일을 만드는 방법 (0) | 2020.09.27 |
|---|---|
| Ruby에서 쉘 명령 호출 (0) | 2020.09.27 |
| 함수 실행에 걸리는 시간을 측정하는 방법 (0) | 2020.09.27 |
| 선택 상자의 모든 옵션을 제거한 다음 하나의 옵션을 추가하고 jQuery로 선택하는 방법은 무엇입니까? (0) | 2020.09.27 |
| AngularJS 컨트롤러의 'this'vs $ scope (0) | 2020.09.27 |