SQL IN 절 매개 변수화
해야 합니까?IN변수 개수의 인수가 있는 절, 예를 들어 다음과 같이요?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
이 쿼리에서는 인수 수를 1 ~5 의 범위에서 지정할 수 있습니다.
이 (또는 XML)에는 전용 스토어드 프로시저를 사용하지 않는 것이 좋습니다만, SQL Server 2008에 특화된 우아한 방법이 있다면, 저는 그것을 받아들일 수 있습니다.
다음과 같이 각 값을 파라미터화할 수 있습니다.
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 주입에 대해 열려 있지 않습니다.명령어에 삽입된 유일한 텍스트텍스트는 사용자 입력에 기반하지 않습니다.하드코드된 "@tag" 접두사와 어레이 인덱스를 기반으로 합니다.인덱스는 항상 정수이며 사용자가 생성하지 않으며 안전합니다.
사용자가 입력한 값은 여전히 파라미터에 채워지기 때문에 취약성이 없습니다.
편집:
주입은 별도로 하고, 위와 같이 다양한 수의 매개 변수를 수용하기 위해 명령 텍스트를 구성하면 SQL Server가 캐시된 쿼리를 이용하는 데 방해가 된다는 점에 유의하십시오.결과적으로 (단순히 SQL 자체에 술어 문자열을 삽입하는 것이 아니라) 처음에 매개 변수를 사용하는 것의 가치를 거의 확실하게 잃게 됩니다.
캐시된 쿼리 계획이 가치가 있는 것은 아니지만 IMO는 그다지 복잡하지 않아 많은 이점을 얻을 수 있습니다.컴파일 비용이 실행 비용에 근접할 수도 있지만(또는 이를 초과할 수도 있습니다) 여전히 밀리초입니다.
RAM이 충분하다면 SQL Server는 파라미터의 공통 카운트에 대한 계획도 캐시할 것입니다.언제든지 5개의 파라미터를 추가하고 지정되지 않은 태그는 NULL로 할 수 있다고 생각합니다.쿼리 플랜은 같아야 하지만, 나에게는 매우 추악하게 느껴집니다(스택 오버플로우에서는 그럴 가치가 있을지도 모릅니다).
또한 SQL Server 7 이후에서는 쿼리가 자동으로 파라미터화되므로 성능 측면에서는 파라미터를 사용할 필요가 없습니다.단, 보안 측면에서는 특히 사용자가 입력한 데이터에서는 매우 중요합니다.
다음은 제가 사용한 빠르고 더러운 기술입니다.
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 하지 않으면 null이 작동하지
그 밖에도 청결하다고 생각되는 방법이 있기 때문에, 계속 읽어 주세요.
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= new SqlMetaData(columnName, SqlDbType.NVarChar, 50); //50 as per SQL Type
return values.Select(v =>
{
var r = new SqlDataRecord(metadata);
r.SetValues(v);
return r;
});
}
@Doug에 따라 업데이트
★★★★★★는 해 .var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);
첫 번째 값 길이가 설정되므로 첫 번째 값이 3자일 경우 설정된 최대 길이 3 및 3자를 초과하면 다른 레코드가 잘립니다.
한번.var metadata= new SqlMetaData(columnName, SqlDbType.NVarChar, maxLen);
★★★★★★-1최대 길이입니다.
원래 질문은 "쿼리를 매개 변수화하는 방법..."이었습니다.
이것은 그 원래의 질문에 대한 답이 아니다.다른 답변에서는 그 방법에 대한 매우 좋은 시연들이 몇 가지 있습니다.
마크 브라켓의 첫 번째 답변('각 값을 파라미터화할 수 있습니다'로 시작하는 첫 번째 답변)과 마크 브라켓의 두 번째 답변)을 참조해 주세요.나(및 다른 231개)가 업베이트한 우선 답변은 다음과 같습니다.그의 답변에 제시된 접근방식은 1) 바인드 변수를 효과적으로 사용할 수 있도록 하고 2) 사거 가능한 술어에 사용할 수 있도록 한다.
선택된 답변
여기서 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비교 연산자
구멍의 패치 적용
하기 위한 은 " " " " " " "를 입니다.%와일드카드 문자(연산자의 이스케이프 구에 익숙하지 않은 사용자는 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 '$'
SQL Server뿐만 아니라 Oracle이나 MySQL에서도 동작하기 때문에 이스케이프보다 이 방법을 선호합니다.(일반적으로 \backslash를 이스케이프 문자로 사용합니다.이것은 정규 표현에서 사용되는 문자이기 때문입니다.하지만 왜 인습에 얽매여 있는가!
그 성가신 괄호들
를 대괄호로 할 수 .[]따라서 적어도 SQL Server에 대해서는 아직 수정이 완료되지 않았습니다.괄호 쌍은 특별한 의미가 있기 때문에 그것들도 피해갈 필요가 있습니다. - 캐럿분들이^괄호 안에 있습니다. 아무 것도 수 요.% ★★★★★★★★★★★★★★★★★」_기본적으로 괄호의 특별한 의미를 비활성화하기 때문에 괄호 안의 문자는 이스케이프됩니다.
일치하는 브래킷 쌍을 찾는 것은 그리 어렵지 않을 것입니다.싱글톤 % 및 _의 발생을 처리하는 것보다 조금 더 어렵습니다.(싱글톤 괄호는 리터럴로 간주되어 회피할 필요가 없기 때문에 괄호만 모두 회피하는 것은 충분하지 않습니다.테스트 케이스를 더 이상 실행하지 않고서는 감당할 수 없을 정도로 논리가 흐트러지고 있습니다.
인라인 표현이 복잡해지다
SQL의 인라인 표현식이 점점 길어지고 추해지고 있습니다.우리는 아마 성공할 수 있을 것이다. 하지만 하늘은 뒤에 있는 불쌍한 영혼을 도와 그것을 해독해야 한다.인라인 표현에 대한 팬으로서, 나는 여기에 그것을 사용하지 않는 경향이 있는데, 주된 이유는 엉망인 이유를 설명하고 그것에 대해 사과하는 댓글을 남기기 싫기 때문이다.
함수는 어디에 있습니까?
SQL에서 인라인식으로 처리하지 않으면 사용자 정의 함수를 사용할 수 있습니다.또한 Oracle에서처럼 인덱스를 정의하지 않는 한 작업 속도가 향상되지 않습니다.함수를 생성해야 하는 경우 SQL 문을 호출하는 코드로 생성하는 것이 좋습니다.
또한 이 기능은 DBMS 및 버전에 따라 동작에 차이가 있을 수 있습니다.(모든 데이터베이스 엔진을 서로 바꿔 사용할 수 있기를 열망하는 Java 개발자들에게 외칩니다.)
도메인 지식
열의 도메인에 대한 전문 지식(즉, 열에 대해 적용되는 허용 값 집합)이 있을 수 있습니다.컬럼에 저장되어 있는 값에는 퍼센트 기호, 밑줄 또는 괄호 쌍이 포함되지 않는다는 것을 사전에 알고 있을 수 있습니다.이 경우 해당 사례에 대한 간단한 코멘트를 첨부합니다.
열에 저장된 값에는 % 또는 _ 문자를 사용할 수 있지만, 제약 조건에서는 값이 LIKE 비교 "safe"가 되도록 정의된 문자를 사용하여 이러한 값을 이스케이프해야 할 수 있습니다.다시 한 번, 허용되는 값 집합, 특히 이스케이프 캐릭터로 사용되는 캐릭터에 대한 간단한 코멘트를 하고 Joel Spolsky의 접근방식을 따릅니다.
그러나 전문지식과 보증이 없다면 최소한 이러한 애매한 코너 케이스에 대처하는 것을 고려하고 그 행동이 합리적이고 "사양에 따라"인지 고려하는 것이 중요합니다.
기타 문제 반복
나는 다른 사람들이 이미 일반적으로 고려되는 우려의 영역 중 일부를 충분히 지적했다고 믿는다.
SQL 주입(사용자가 제공한 것으로 보이는 정보를 가져와서 바인드 변수를 통해 정보를 제공하는 대신 SQL 텍스트에 포함합니다.바인드 변수를 사용할 필요는 없습니다.SQL 주입을 통해 문제를 해결할 수 있는 편리한 방법 중 하나일 뿐입니다.이 문제를 해결하기 위한 다른 방법이 있습니다.
인덱스 검색 대신 인덱스 스캔을 사용하는 최적화 계획, 와일드카드 이스케이프를 위한 표현식 또는 함수의 가능한 필요성(식 또는 함수에 대한 인덱스 생성)
바인드 변수 대신 리터럴 값을 사용하면 scalability에 영향을 줍니다.
결론
조엘 스폴스키의 접근법이 마음에 들어요똑똑해.그리고 그것은 동작한다.
하지만 그것을 보자마자, 잠재적인 문제를 발견했고, 그것을 그냥 넘어가는 것은 내 본성이 아니다.다른 사람의 노력에 비판적인 것은 아닙니다.많은 개발자들이 자신의 작업에 매우 많은 투자를 하고 신경을 많이 쓰기 때문에 개인적으로 받아들이고 있다는 것을 알고 있습니다.그러니 이해해 주세요 이건 개인적인 공격이 아닙니다제가 여기서 확인하고자 하는 것은 테스트보다는 생산 과정에서 발생하는 문제의 유형입니다.
매개 변수를 문자열로 전달할 수 있습니다.
그래서 당신은 끈을 가지고 있다.
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초). Stack Overflow가 LINQ를 사용하여 SQL에 접속하고 있었다고 생각했지만 삭제되었을 가능성이 있습니다.다음은 LINQ에서 SQL로 동일한 내용을 보여 줍니다.
var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };
var results = from tag in Tags
where inValues.Contains(tag.Name)
select tag;
로로그그그그다다이미 는 과거로 .Contains조항은 내게는 더 역행하는 것 같다.직장에서 프로젝트에 대해 같은 쿼리를 수행해야 할 경우 로컬 어레이와 SQL Server 테이블을 결합함으로써 잘못된 방법으로 작업을 수행하려고 했습니다. LINQ-to-SQL 변환기가 어떻게든 변환을 처리할 수 있을 것으로 생각했기 때문입니다.그렇지 않았지만, 설명적인 오류 메시지를 제공했고 Contains를 사용하는 방법을 안내했습니다.
어쨌든 이 쿼리를 권장되는 LINQPad에서 실행하고 이 쿼리를 실행하면 SQL LINQ 공급자가 생성한 실제 SQL을 볼 수 있습니다.각 값이 매개 변수화되어 있는 것을 보여 줍니다.IN절을 클릭합니다.
에서 전화를 걸고 있는 경우.NET, Dapper 도트넷을 사용할 수 있습니다.
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});
여기 대퍼가 생각을 하니까 안 해도 돼물론 LINQ에서 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;
»SQL Server 2016+기능을 사용할 수 있습니다.
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)) + ',%';
부록:
「 」의 개선을 위해서STRING_SPLIT 행된 값을이 좋습니다.
DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails,sql';
CREATE TABLE #t(val NVARCHAR(120));
INSERT INTO #t(val) SELECT s.[value] FROM STRING_SPLIT(@names, ',') s;
SELECT *
FROM Tags tg
JOIN #t t
ON t.val = tg.TagName
ORDER BY [Count] DESC;
에는 필수가 .
SQL Server 2008이 질문은 중복으로 사용되는 경우가 많기 때문에 이 답변을 참고용으로 추가했습니다.
이것은 아마도 반쪽의 고약한 방법일 것이다, 나는 그것을 한 번 사용했지만, 꽤 효과적이었다.
당신의 목표에 따라 그것은 유용할 수 있습니다.
- 하나의 열이 있는 임시 테이블을 만듭니다.
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
이것은 역겹지만, 적어도1개를 가지고 있는 것이 보증되고 있는 경우는, 다음과 같이 할 수 있습니다.
SELECT ...
...
WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )
IN('tag1', 'tag2', 'tag1', 'tag1', 'tag1')이 있으면 SQL Server에 의해 쉽게 최적화됩니다.또한 직접 인덱스 검색도 가능합니다.
테이블 타입 파라미터(SQL Server 2008이므로)를 전달하고where exists또는 내부 결합입니다.을 사용하여 .sp_xml_preparedocument임시 테이블을 인덱싱할 수도 있습니다.
이 문제를 해결할 수 있는 가장 좋은 소스는 이 사이트에 게시된 내용이라고 생각합니다.
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
IMHO는 목록을 문자열(DBMS가 지원하는 문자열에 따라 길이가 제한됨)에 저장하는 것이 적절합니다.단, 유일한 트릭은 (처리를 단순화하기 위해) 문자열의 선두와 끝에 구분자(이 예에서는 쉼표)가 있는 것입니다.즉, "즉각 정규화"하여 목록을 값당 하나의 행이 포함된 한 열 표로 만드는 것입니다.이렇게 하면
입력(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, ',', ''));
(물론 "pivot"은 목록에서 찾을 수 있는 최대 항목 수만큼 행을 반환해야 합니다.)
SQL Server 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인 500개의 파라미터가 있고 WHERE Column1 IN(@Param1, @Param2, @Param3, ..., @Param500)이 있는 스토어드 프로시저도 있습니다.이로 인해 SQL은 임시 테이블을 작성하고 정렬/구분을 수행한 다음 인덱스 검색 대신 테이블 스캔을 수행합니다.이는 기본적으로 해당 쿼리를 파라미터화함으로써 수행할 수 있는 작업입니다.단, 이는 눈에 띄는 차이는 없지만 충분히 작은 규모입니다.IN 목록에 NULL을 넣지 말 것을 강력히 권장합니다.NOT IN으로 변경되면 의도대로 동작하지 않습니다.파라미터 목록을 동적으로 작성할 수 있지만 오브젝트가 단일 따옴표에서 제외된다는 것만이 유일하게 눈에 띕니다.오브젝트가 파라미터를 찾기 위해 쿼리를 해석해야 하기 때문에 이 접근법은 응용 프로그램 측에서도 약간 느립니다.SQL에서는 파라미터화된 쿼리가 쿼리를 실행하는 횟수만큼 sp_prepare, sp_execute를 호출한 후 sp_unprepare를 호출하기 때문에 더 빠를 수도 있고 그렇지 않을 수도 있습니다.
저장 프로시저 또는 매개 변수화된 쿼리에 대한 실행 계획을 재사용하면 성능이 향상될 수 있지만, 처음 실행된 쿼리에 의해 결정된 하나의 실행 계획에 구속됩니다.이는 대부분의 경우 후속 쿼리에 적합하지 않을 수 있습니다.이 경우 실행 계획을 재사용하는 것이 유리할 수 있지만, 이 예는 매우 단순한 쿼리이기 때문에 전혀 차이가 없을 수 있습니다.
클리프 노트:
어떤 경우든 리스트 내의 고정 개수의 항목을 사용한 파라미터화(사용하지 않은 경우 Null), 파라미터가 있는지 여부에 관계없이 쿼리를 동적으로 빌드하거나 테이블 값 파라미터가 있는 스토어드 프로시저를 사용해도 큰 차이는 없습니다.단, 일반적인 권장사항은 다음과 같습니다.
파라미터가 적은 케이스/심플한 쿼리:
동적 SQL 테스트에서 더 나은 성능을 보일 경우 매개 변수를 사용할 수 있습니다.
재사용 가능한 실행 계획을 포함하는 쿼리. 단순히 매개 변수를 변경하거나 쿼리가 복잡한 경우 여러 번 호출됩니다.
동적 매개 변수를 사용하는 SQL.
목록이 큰 쿼리:
테이블 값 매개 변수가 있는 저장 프로시저.목록이 크게 다를 수 있는 경우 저장 프로시저에서 RECOMFILE과 함께 사용하거나 매개 변수 없이 동적 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 구 내에 저장되어 있는 경우 charindex 함수를 사용하여 값을 가져올 수 있습니다.를 사용하는 경우.그런 다음 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
기본적으로는 테이블 값 함수(문자열에서 테이블을 반환함)를 IN 상태로 전달합니다.
이것은 UDF의 코드입니다(스택 오버플로우 어딘가에서 입수했습니다만, 현재 소스를 찾을 수 없습니다).
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
문자열이 너무 길지 않으면 테이블 인덱스와 함께 사용할 수 있습니다.
필요한 경우 임시 테이블에 삽입하고 인덱싱한 다음 조인을 실행할 수 있습니다.
이와 같은 다양한 인수의 경우 SQL을 명시적으로 생성하거나 임시 테이블에 원하는 항목을 입력하고 임시 테이블에 참여시키는 방법밖에 없습니다.
또 하나의 가능한 해결책은 변수 수의 인수를 저장 프로시저에 전달하는 것이 아니라 뒤에 있는 이름을 포함하는 단일 문자열을 전달하되 '<>'로 둘러싸서 고유하게 만드는 것입니다.그런 다음 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
여기 또 다른 대안이 있다.저장 프로시저에 문자열 매개 변수로 쉼표로 구분된 목록을 전달하면 다음과 같습니다.
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
ColdFusion에서는 다음 작업을 수행합니다.
<cfset myvalues = "ruby|rails|scruffy|rubyonrails">
<cfquery name="q">
select * from sometable where values in <cfqueryparam value="#myvalues#" list="true">
</cfquery>
다음은 쿼리 문자열에서 사용할 로컬 테이블을 다시 만드는 기술입니다.이렇게 하면 구문 분석 문제가 모두 제거됩니다.
문자열은 임의의 언어로 작성할 수 있습니다.이 예에서는 SQL이 원래 해결하려고 했던 문제이기 때문에 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+에서는 이 기능을 사용할 수도 있습니다.
이 접근법은 ID 목록별로 행을 선택하는 가장 좋은 방법 중 하나인 OPENJSON에 대해 블로그에 게재되어 있습니다.
아래 전체 작업 예시는 다음과 같습니다.
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
IN이 선택문을 받아들이기 때문에 UDF, XML이 필요 없는 답변이 있습니다(예: SELECT * FROM Test where Data IN (SELECT Value FROM TABLE)).
문자열을 테이블로 변환하는 방법만 있으면 됩니다.
이것은 재귀적인 CTE 또는 숫자 테이블을 사용한 쿼리(또는 마스터)를 사용하여 수행할 수 있습니다.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);
여기 이 문제에 대한 또 다른 답이 있습니다.
(새 버전은 13년 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
'programing' 카테고리의 다른 글
| DLL 의존성을 확인하는 방법 (0) | 2023.04.17 |
|---|---|
| TableView용 NSIndexPath 작성 방법 (0) | 2023.04.17 |
| 만약... 또는 만약...윈도 배치파일에서 (0) | 2023.04.17 |
| 터미널에서 Git 트리를 표시할 수 없습니다. (0) | 2023.04.17 |
| 이미지를 배경으로 이동하시겠습니까? (0) | 2023.04.17 |