MySQL에서 배열 변수를 어떻게 시뮬레이션 할 수 있습니까?
나타납니다 MySQL이 배열 변수를 가지고 있지 않습니다. 대신 무엇을 사용해야합니까?
제안 된 두 가지 대안이있는 것 같습니다 : 집합 유형 스칼라 및 임시 테이블 . 내가 연결 한 질문은 전자를 제안합니다. 그러나 배열 변수 대신 이것을 사용하는 것이 좋은 습관입니까? 또는 세트로 이동하면 세트 기반 관용구는 foreach무엇입니까?
글쎄, 나는 배열 변수 대신 임시 테이블을 사용하고 있습니다. 최고의 솔루션은 아니지만 작동합니다.
공식적으로 필드를 정의 할 필요는 없으며 SELECT를 사용하여 생성하기 만하면됩니다.
CREATE TEMPORARY TABLE IF NOT EXISTS my_temp_table
SELECT first_name FROM people WHERE last_name = 'Smith';
( Create Table을 사용하지 않고 select 문에서 임시 테이블 만들기 도 참조하십시오 .)
WHILE루프를 사용하여 MySQL에서이를 수행 할 수 있습니다 .
SET @myArrayOfValue = '2,5,2,23,6,';
WHILE (LOCATE(',', @myArrayOfValue) > 0)
DO
SET @value = ELT(1, @myArrayOfValue);
SET @myArrayOfValue= SUBSTRING(@myArrayOfValue, LOCATE(',',@myArrayOfValue) + 1);
INSERT INTO `EXEMPLE` VALUES(@value, 'hello');
END WHILE;
편집 : 또는 다음을 사용하여 수행 할 수 있습니다 UNION ALL.
INSERT INTO `EXEMPLE`
(
`value`, `message`
)
(
SELECT 2 AS `value`, 'hello' AS `message`
UNION ALL
SELECT 5 AS `value`, 'hello' AS `message`
UNION ALL
SELECT 2 AS `value`, 'hello' AS `message`
UNION ALL
...
);
MySql의 FIND_IN_SET () 함수를 사용해보십시오.
SET @c = 'xxx,yyy,zzz';
SELECT * from countries
WHERE FIND_IN_SET(countryname,@c);
참고 : CSV 값으로 매개 변수를 전달하는 경우 StoredProcedure에서 변수를 설정할 필요가 없습니다.
배열에 대해서는 모르지만 일반 VARCHAR 열에 쉼표로 구분 된 목록을 저장하는 방법이 있습니다.
그리고 그 목록에서 무언가를 찾아야 할 때 FIND_IN_SET () 함수를 사용할 수 있습니다 .
요즘에는 JSON 배열을 사용하는 것이 분명한 대답이 될 것입니다.
이것은 오래되었지만 여전히 관련성이있는 질문이기 때문에 짧은 예를 만들었습니다. mySQL 5.7.x / MariaDB 10.2.3부터 JSON 함수를 사용할 수 있습니다.
ELT ()보다이 솔루션을 선호하는 이유는 배열과 비슷하고 코드에서이 '배열'을 재사용 할 수 있기 때문입니다.
하지만 조심하세요. (JSON)은 임시 테이블을 사용하는 것보다 확실히 훨씬 느립니다. 더 편리합니다. imo.
JSON 배열을 사용하는 방법은 다음과 같습니다.
SET @myjson = '["gmail.com","mail.ru","arcor.de","gmx.de","t-online.de",
"web.de","googlemail.com","freenet.de","yahoo.de","gmx.net",
"me.com","bluewin.ch","hotmail.com","hotmail.de","live.de",
"icloud.com","hotmail.co.uk","yahoo.co.jp","yandex.ru"]';
SELECT JSON_LENGTH(@myjson);
-- result: 19
SELECT JSON_VALUE(@myjson, '$[0]');
-- result: gmail.com
다음은 함수 / 프로 시저에서 어떻게 작동하는지 보여주는 간단한 예입니다.
DELIMITER //
CREATE OR REPLACE FUNCTION example() RETURNS varchar(1000) DETERMINISTIC
BEGIN
DECLARE _result varchar(1000) DEFAULT '';
DECLARE _counter INT DEFAULT 0;
DECLARE _value varchar(50);
SET @myjson = '["gmail.com","mail.ru","arcor.de","gmx.de","t-online.de",
"web.de","googlemail.com","freenet.de","yahoo.de","gmx.net",
"me.com","bluewin.ch","hotmail.com","hotmail.de","live.de",
"icloud.com","hotmail.co.uk","yahoo.co.jp","yandex.ru"]';
WHILE _counter < JSON_LENGTH(@myjson) DO
-- do whatever, e.g. add-up strings...
SET _result = CONCAT(_result, _counter, '-', JSON_VALUE(@myjson, CONCAT('$[',_counter,']')), '#');
SET _counter = _counter + 1;
END WHILE;
RETURN _result;
END //
DELIMITER ;
SELECT example();
DELIMITER $$
CREATE DEFINER=`mysqldb`@`%` PROCEDURE `abc`()
BEGIN
BEGIN
set @value :='11,2,3,1,';
WHILE (LOCATE(',', @value) > 0) DO
SET @V_DESIGNATION = SUBSTRING(@value,1, LOCATE(',',@value)-1);
SET @value = SUBSTRING(@value, LOCATE(',',@value) + 1);
select @V_DESIGNATION;
END WHILE;
END;
END$$
DELIMITER ;
연관 배열을 원하는 경우 열 (키, 값)이있는 임시 메모리 테이블을 만들 수 있습니다. 메모리 테이블을 갖는 것은 mysql에 배열을 갖는 것과 가장 가까운 것입니다.
내가 한 방법은 다음과 같습니다.
먼저 Long / Integer / whatever 값이 쉼표로 구분 된 값 목록에 있는지 확인하는 함수를 만들었습니다.
CREATE DEFINER = 'root'@'localhost' FUNCTION `is_id_in_ids`(
`strIDs` VARCHAR(255),
`_id` BIGINT
)
RETURNS BIT(1)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE strLen INT DEFAULT 0;
DECLARE subStrLen INT DEFAULT 0;
DECLARE subs VARCHAR(255);
IF strIDs IS NULL THEN
SET strIDs = '';
END IF;
do_this:
LOOP
SET strLen = LENGTH(strIDs);
SET subs = SUBSTRING_INDEX(strIDs, ',', 1);
if ( CAST(subs AS UNSIGNED) = _id ) THEN
-- founded
return(1);
END IF;
SET subStrLen = LENGTH(SUBSTRING_INDEX(strIDs, ',', 1));
SET strIDs = MID(strIDs, subStrLen+2, strLen);
IF strIDs = NULL or trim(strIds) = '' THEN
LEAVE do_this;
END IF;
END LOOP do_this;
-- not founded
return(0);
END;
이제 다음과 같이 쉼표로 구분 된 ID 목록에서 ID를 검색 할 수 있습니다.
select `is_id_in_ids`('1001,1002,1003',1002);
그리고 다음과 같이 WHERE 절 내에서이 함수를 사용할 수 있습니다.
SELECT * FROM table1 WHERE `is_id_in_ids`('1001,1002,1003',table1_id);
이것이 PROCEDURE에 "배열"매개 변수를 전달하는 유일한 방법이었습니다.
답변이 다소 늦었다는 것을 알고 있지만 최근에 비슷한 문제를 해결해야했고 이것이 다른 사람들에게 유용 할 것이라고 생각했습니다.
배경
'mytable'이라는 아래 테이블을 고려하십시오.
문제는 최신 3 개의 레코드 만 유지하고 systemid = 1 인 이전 레코드를 삭제하는 것이 었습니다 (다른 systemid 값이있는 테이블에 다른 레코드가 많이있을 수 있음).
다음 문장을 사용하여 이것을 할 수 있다면 좋을 것입니다.
DELETE FROM mytable WHERE id IN (SELECT id FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3)
그러나 이것은 MySQL에서 아직 지원되지 않으며 이것을 시도하면 다음과 같은 오류가 발생합니다.
...doesn't yet support 'LIMIT & IN/ALL/SOME subquery'
따라서 변수를 사용하여 값 배열이 IN 선택기에 전달되는 해결 방법이 필요합니다. 그러나 변수는 단일 값이어야 하므로 배열 을 시뮬레이션 해야합니다 . 트릭은 쉼표로 구분 된 값 목록 (문자열)으로 배열을 만들고 다음과 같이 변수에 할당하는 것입니다.
SET @myvar := (SELECT GROUP_CONCAT(id SEPARATOR ',') AS myval FROM (SELECT * FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3 ) A GROUP BY A.systemid);
@myvar에 저장된 결과는 다음과 같습니다.
5,6,7
다음으로 FIND_IN_SET 선택기는 시뮬레이션 된 배열에서 선택하는 데 사용됩니다.
SELECT * FROM mytable WHERE FIND_IN_SET(id,@myvar);
결합 된 최종 결과는 다음과 같습니다.
SET @myvar := (SELECT GROUP_CONCAT(id SEPARATOR ',') AS myval FROM (SELECT * FROM `mytable` WHERE systemid=1 ORDER BY id DESC LIMIT 3 ) A GROUP BY A.systemid);
DELETE FROM mytable WHERE FIND_IN_SET(id,@myvar);
나는 이것이 매우 특정한 경우라는 것을 알고 있습니다. 그러나 변수가 값의 배열을 저장해야하는 다른 경우에 맞게 수정할 수 있습니다.
이것이 도움이되기를 바랍니다.
배열의 요점이 효율적이지 않습니까? 값을 반복하는 경우 임시 (또는 영구) 테이블의 커서가 쉼표를 찾는 것보다 더 합리적이라고 생각합니다. 또한 깨끗합니다. "mysql DECLARE CURSOR"를 찾습니다.
무작위 액세스를 위해 숫자로 색인 된 기본 키가있는 임시 테이블. 불행히도 가장 빠른 액세스는 진정한 랜덤 액세스가 아닌 해시 테이블입니다.
이것은 값 목록에 대해 잘 작동합니다.
SET @myArrayOfValue = '2,5,2,23,6,';
WHILE (LOCATE(',', @myArrayOfValue) > 0)
DO
SET @value = ELT(1, @myArrayOfValue);
SET @STR = SUBSTRING(@myArrayOfValue, 1, LOCATE(',',@myArrayOfValue)-1);
SET @myArrayOfValue = SUBSTRING(@myArrayOfValue, LOCATE(',', @myArrayOfValue) + 1);
INSERT INTO `Demo` VALUES(@STR, 'hello');
END WHILE;
세트를 사용하는 두 버전 모두 나에게 적합하지 않았습니다 (MySQL 5.5로 테스트). ELT () 함수는 전체 집합을 반환합니다. WHILE 문은 PROCEDURE 컨텍스트에서만 사용할 수 있다는 것을 고려하여 솔루션에 추가했습니다.
DROP PROCEDURE IF EXISTS __main__;
DELIMITER $
CREATE PROCEDURE __main__()
BEGIN
SET @myArrayOfValue = '2,5,2,23,6,';
WHILE (LOCATE(',', @myArrayOfValue) > 0)
DO
SET @value = LEFT(@myArrayOfValue, LOCATE(',',@myArrayOfValue) - 1);
SET @myArrayOfValue = SUBSTRING(@myArrayOfValue, LOCATE(',',@myArrayOfValue) + 1);
END WHILE;
END;
$
DELIMITER ;
CALL __main__;
솔직히 말해서 이것이 좋은 습관이라고 생각하지 않습니다. 실제로 필요한 경우에도 거의 읽을 수없고 매우 느립니다.
ELT / FIELD에 대한 답변이 없다는 것에 놀랐습니다.
ELT / FIELD는 특히 정적 데이터가있는 경우 배열과 매우 유사하게 작동합니다.
FIND_IN_SET도 유사하게 작동하지만 보완 기능이 내장되어 있지 않지만 작성하기 쉽습니다.
mysql> select elt(2,'AA','BB','CC');
+-----------------------+
| elt(2,'AA','BB','CC') |
+-----------------------+
| BB |
+-----------------------+
1 row in set (0.00 sec)
mysql> select field('BB','AA','BB','CC');
+----------------------------+
| field('BB','AA','BB','CC') |
+----------------------------+
| 2 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select find_in_set('CC','AA,BB,CC');
+------------------------------+
| find_in_set('BB','AA,BB,CC') |
+------------------------------+
| 2 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('AA,BB,CC',',',2),',',-1);
+-----------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('AA,BB,CC',',',2),',',-1) |
+-----------------------------------------------------------+
| BB |
+-----------------------------------------------------------+
1 row in set (0.01 sec)
5.7.x 이후의 MYSQL 버전에서는 JSON 유형을 사용하여 배열을 저장할 수 있습니다. MYSQL을 통해 키로 배열 값을 얻을 수 있습니다.
ELT (index number, string1, string2, string3,…) 함수에서 영감을 받아 다음 예제가 배열 예제로 작동한다고 생각합니다.
set @i := 1;
while @i <= 3
do
insert into table(val) values (ELT(@i ,'val1','val2','val3'...));
set @i = @i + 1;
end while;
도움이 되었기를 바랍니다.
같은 문제를 보는 또 다른 방법. 도움이되는 희망
DELIMITER $$
CREATE PROCEDURE ARR(v_value VARCHAR(100))
BEGIN
DECLARE v_tam VARCHAR(100);
DECLARE v_pos VARCHAR(100);
CREATE TEMPORARY TABLE IF NOT EXISTS split (split VARCHAR(50));
SET v_tam = (SELECT (LENGTH(v_value) - LENGTH(REPLACE(v_value,',',''))));
SET v_pos = 1;
WHILE (v_tam >= v_pos)
DO
INSERT INTO split
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(v_value,',',v_pos),',', -1);
SET v_pos = v_pos + 1;
END WHILE;
SELECT * FROM split;
DROP TEMPORARY TABLE split;
END$$
CALL ARR('1006212,1006404,1003404,1006505,444,');
이 답변을 개선 할 수 있다고 생각합니다. 이 시도:
'장난'매개 변수는 CSV입니다. 즉. '1,2,3,4 ..... 등'
CREATE PROCEDURE AddRanks(
IN Pranks TEXT
)
BEGIN
DECLARE VCounter INTEGER;
DECLARE VStringToAdd VARCHAR(50);
SET VCounter = 0;
START TRANSACTION;
REPEAT
SET VStringToAdd = (SELECT TRIM(SUBSTRING_INDEX(Pranks, ',', 1)));
SET Pranks = (SELECT RIGHT(Pranks, TRIM(LENGTH(Pranks) - LENGTH(SUBSTRING_INDEX(Pranks, ',', 1))-1)));
INSERT INTO tbl_rank_names(rank)
VALUES(VStringToAdd);
SET VCounter = VCounter + 1;
UNTIL (Pranks = '')
END REPEAT;
SELECT VCounter AS 'Records added';
COMMIT;
END;
이 방법은 검색된 CSV 값 문자열을 루프가 반복 될 때마다 점점 더 짧아 지므로 최적화에 더 좋을 것이라고 생각합니다.
여러 컬렉션에 대해 이와 같은 것을 시도합니다. 저는 MySQL 초보자입니다. 함수 이름에 대해 죄송합니다. 어떤 이름이 가장 좋을지 결정할 수 없습니다.
delimiter //
drop procedure init_
//
create procedure init_()
begin
CREATE TEMPORARY TABLE if not exists
val_store(
realm varchar(30)
, id varchar(30)
, val varchar(255)
, primary key ( realm , id )
);
end;
//
drop function if exists get_
//
create function get_( p_realm varchar(30) , p_id varchar(30) )
returns varchar(255)
reads sql data
begin
declare ret_val varchar(255);
declare continue handler for 1146 set ret_val = null;
select val into ret_val from val_store where id = p_id;
return ret_val;
end;
//
drop procedure if exists set_
//
create procedure set_( p_realm varchar(30) , p_id varchar(30) , p_val varchar(255) )
begin
call init_();
insert into val_store (realm,id,val) values (p_realm , p_id , p_val) on duplicate key update val = p_val;
end;
//
drop procedure if exists remove_
//
create procedure remove_( p_realm varchar(30) , p_id varchar(30) )
begin
call init_();
delete from val_store where realm = p_realm and id = p_id;
end;
//
drop procedure if exists erase_
//
create procedure erase_( p_realm varchar(30) )
begin
call init_();
delete from val_store where realm = p_realm;
end;
//
call set_('my_array_table_name','my_key','my_value');
select get_('my_array_table_name','my_key');
PHP의 serialize ()를 사용해 보셨습니까? 이를 통해 변수 배열의 내용을 PHP가 이해하고 데이터베이스에 대해 안전합니다 (먼저 이스케이프했다고 가정).
$array = array(
1 => 'some data',
2 => 'some more'
);
//Assuming you're already connected to the database
$sql = sprintf("INSERT INTO `yourTable` (`rowID`, `rowContent`) VALUES (NULL, '%s')"
, serialize(mysql_real_escape_string($array, $dbConnection)));
mysql_query($sql, $dbConnection) or die(mysql_error());
번호가 매겨진 배열없이 똑같이 할 수도 있습니다.
$array2 = array(
'something' => 'something else'
);
또는
$array3 = array(
'somethingNew'
);
참고 URL : https://stackoverflow.com/questions/12176709/how-can-i-simulate-an-array-variable-in-mysql
'programing tip' 카테고리의 다른 글
| C # : MSTest 단위 테스트에서 "예외가 발생하지 않음"을 어떻게 확인합니까? (0) | 2020.10.29 |
|---|---|
| i 태그의 Alt 또는 제목 속성 (0) | 2020.10.29 |
| 알려진 속성이 아니기 때문에 'routerLink'에 바인딩 할 수 없습니다. (0) | 2020.10.29 |
| 컬렉션의 항목 합계 (0) | 2020.10.29 |
| 시스템 시작시 docker-compose up -d를 실행하는 방법은 무엇입니까? (0) | 2020.10.28 |