MySQL 저장 루틴에 어레이 전달

MySQL 저장 루틴에 어레이 전달

MySQL 저장 루틴에 문자열 배열을 매개 변수로 전달해야 합니다.배열이 길 수 있고 요소의 수가 고정되어 있지 않습니다.그런 다음 데이터를 처리할 수 있도록 문자열 값을 1개의 컬럼이 있는 메모리 내 테이블에 넣습니다.MySQL에서 할 수 있을지 모르겠어요.어쩌면 더러운 회피책이 필요할지도 모른다.

예를 들어 다음과 같은 문자열 값이 있습니다.

Banana, Apple, Orange

이제 MySQL에서 이 과일들에 대한 데이터를 얻고 싶습니다.Fruits표. 의사 코드:

create function GetFruits(Array fruitArray) 
   declare @temp table as
      fruitName varchar(100)
   end

   @temp = convert fruitArray to table
   select * from Fruits where Name in (select fruitName from @temp)
end

Microsoft SQL Server를 사용하면TEXT어레이를 XML 문자열로 입력하여 전송하면 메모리 내 테이블이 빠르게 생성됩니다.하지만 MySQL에서는 그 기술은 불가능하다고 생각합니다.

방법을 알려주시면 감사하겠습니다!

목록과 함께 문자열을 전달하고 준비된 문을 사용하여 쿼리를 실행할 수 있습니다(예: -).

DELIMITER $$

CREATE PROCEDURE GetFruits(IN fruitArray VARCHAR(255))
BEGIN

  SET @sql = CONCAT('SELECT * FROM Fruits WHERE Name IN (', fruitArray, ')');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

END
$$

DELIMITER ;

사용방법:

SET @fruitArray = '\'apple\',\'banana\'';
CALL GetFruits(@fruitArray);

간단하게 FIND_를 사용할 수 있습니다.다음과 같이 IN_SET:

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2

다음 작업을 수행할 수 있습니다.

select * from Fruits where FIND_IN_SET(fruit, fruitArray) > 0

이것은 내가 상황에 맞게 하는 데 도움이 됩니다. 이것이 당신에게 도움이 되길 바랍니다.

CREATE  PROCEDURE `test`(IN Array_String VARCHAR(100))
BEGIN
    SELECT * FROM Table_Name
    WHERE FIND_IN_SET(field_name_to_search, Array_String);

END//;

호출:

 call test('3,2,1');

임시 테이블과 결합을 사용합니다.함수에 임시 테이블을 전달할 필요가 없습니다. 함수는 글로벌합니다.

create temporary table ids( id int ) ;
insert into ids values (1),(2),(3) ;

delimiter //
drop procedure if exists tsel //
create procedure tsel() -- uses temporary table named ids. no params
READS SQL DATA
BEGIN
  -- use the temporary table `ids` in the SELECT statement or
  -- whatever query you have
  select * from Users INNER JOIN ids on userId=ids.id ;
END //
DELIMITER ;

CALL tsel() ; -- call the procedure

나는 내 문제에 대해 어색하지만 기능적인 해결책을 생각해냈다.1차원 어레이(더 많은 치수는 까다로울 수 있음) 및 입력에 적합합니다.varchar:

  declare pos int;           -- Keeping track of the next item's position
  declare item varchar(100); -- A single item of the input
  declare breaker int;       -- Safeguard for while loop 

  -- The string must end with the delimiter
  if right(inputString, 1) <> '|' then
     set inputString = concat(inputString, '|');
  end if;

  DROP TABLE IF EXISTS MyTemporaryTable;
  CREATE TEMPORARY TABLE MyTemporaryTable ( columnName varchar(100) );
  set breaker = 0;

  while (breaker < 2000) && (length(inputString) > 1) do
     -- Iterate looking for the delimiter, add rows to temporary table.
     set breaker = breaker + 1;
     set pos = INSTR(inputString, '|');
     set item = LEFT(inputString, pos - 1);
     set inputString = substring(inputString, pos + 1);
     insert into MyTemporaryTable values(item);
  end while;

예를 들어, 이 코드에 대한 입력은 문자열일 수 있습니다.Apple|Banana|Orange.MyTemporaryTable문자열이 포함된 3개의 행으로 채워집니다.Apple,Banana,그리고.Orange각각 다음과 같다.

문자열 처리 속도가 느리면 이 접근법이 무용지물이라고 생각했지만, 그것은 충분히 빨랐습니다(1,000개의 엔트리 배열에 불과 1초).

이게 도움이 됐으면 좋겠네요.

임시 테이블을 사용하지 않으려면 여기에 분할 문자열과 같은 함수를 사용할 수 있습니다.

SET @Array = 'one,two,three,four';
SET @ArrayIndex = 2;
SELECT CASE 
    WHEN @Array REGEXP CONCAT('((,).*){',@ArrayIndex,'}') 
    THEN SUBSTRING_INDEX(SUBSTRING_INDEX(@Array,',',@ArrayIndex+1),',',-1) 
    ELSE NULL
END AS Result;

• SUBSTRING_INDEX(string, delim, n)첫 번째 n을 반환합니다.
• SUBSTRING_INDEX(string, delim, -1)마지막만 반환합니다.
• REGEXP '((delim).*){n}'n개의 딜리미터가 있는지 확인합니다(즉, 경계 내에 있습니다).

이것은 문자 배열을 시뮬레이트하지만 문자열 배열을 시뮬레이트하기 위해 ELT를 기판으로 대체할 수 있습니다.

declare t_tipos varchar(255) default 'ABCDE';
declare t_actual char(1);
declare t_indice integer default 1;
while t_indice<length(t_tipos)+1 do
    set t_actual=SUBSTR(t_tipos,t_indice,1);
        select t_actual;
        set t_indice=t_indice+1;
end while;

이것이 질문에 완전히 답할 수 있을지는 모르겠지만(그렇지는 않지만) 비슷한 문제에 대해 제가 생각해 낸 해결책입니다.여기서는 LOCATE()를 딜리미터별로 한 번만 사용합니다.

-- *****************************************************************************
-- test_PVreplace

DROP FUNCTION IF EXISTS test_PVreplace;

delimiter //
CREATE FUNCTION test_PVreplace (
   str TEXT,   -- String to do search'n'replace on
   pv TEXT     -- Parameter/value pairs 'p1=v1|p2=v2|p3=v3'
   )
   RETURNS TEXT

-- Replace specific tags with specific values.

sproc:BEGIN
   DECLARE idx INT;
   DECLARE idx0 INT DEFAULT 1;   -- 1-origined, not 0-origined
   DECLARE len INT;
   DECLARE sPV TEXT;
   DECLARE iPV INT;
   DECLARE sP TEXT;
   DECLARE sV TEXT;

   -- P/V string *must* end with a delimiter.

   IF (RIGHT (pv, 1) <> '|') THEN
      SET pv = CONCAT (pv, '|');
      END IF;

   -- Find all the P/V pairs.

   SELECT LOCATE ('|', pv, idx0) INTO idx;
   WHILE (idx > 0) DO
      SET len = idx - idx0;
      SELECT SUBSTRING(pv, idx0, len) INTO sPV;

      -- Found a P/V pair.  Break it up.

      SELECT LOCATE ('=', sPV) INTO iPV;
      IF (iPV = 0) THEN
         SET sP = sPV;
         SET sV = '';
      ELSE
         SELECT SUBSTRING(sPV, 1, iPV-1) INTO sP;
         SELECT SUBSTRING(sPV, iPV+1) INTO sV;
         END IF;

      -- Do the substitution(s).

      SELECT REPLACE (str, sP, sV) INTO str;

      -- Do next P/V pair.

      SET idx0 = idx + 1;
      SELECT LOCATE ('|', pv, idx0) INTO idx;
      END WHILE;
   RETURN (str);
END//
delimiter ;

SELECT test_PVreplace ('%one% %two% %three%', '%one%=1|%two%=2|%three%=3');
SELECT test_PVreplace ('%one% %two% %three%', '%one%=I|%two%=II|%three%=III');
SELECT test_PVreplace ('%one% %two% %three% - %one% %two% %three%', '%one%=I|%two%=II|%three%=III');
SELECT test_PVreplace ('%one% %two% %three% - %one% %two% %three%', '');
SELECT test_PVreplace ('%one% %two% %three% - %one% %two% %three%', NULL);
SELECT test_PVreplace ('%one% %two% %three%', '%one%=%two%|%two%=%three%|%three%=III');

쿼리 세트가 제한된 범위 정수(최대 100 등)이고 복잡한 쿼리에서 더 나은 성능을 필요로 하는 경우 위의 답변에서 확장된 솔루션을 사용할 수 있습니다.

declare item int;

DROP TABLE IF EXISTS MyTemporaryTable;
CREATE TEMPORARY TABLE MyTemporaryTable ( columnName int );

set item = 1;
while (item < 100) do
   insert into MyTemporaryTable select item where FIND_IN_SET(item, app_list);
   set item = item + 1;
end while;

SELECT * FROM table where id in (select * from MyTemporaryTable)

언급URL : https://stackoverflow.com/questions/8149545/pass-array-to-mysql-stored-routine