MySQL에서 어레이 변수를 시뮬레이트하려면 어떻게 해야 합니까?
MySQL에는 배열 변수가 없는 것 같습니다.대신 무엇을 사용하면 좋을까요?
다음과 같은 두 가지 대안이 제시되고 있는 것 같습니다.set 타입의 스칼라 테이블 및 임시 테이블.내가 연관시킨 질문은 전자를 시사한다.그러나 배열 변수 대신 이러한 변수를 사용하는 것이 좋은 방법일까요?또는, 만약 내가 세트로 간다면, 세트로 된 관용구는 무엇과 같을까요?foreach무슨 일입니까?
배열 변수 대신 임시 테이블을 사용하고 있습니다.가장 훌륭한 해결책은 아니지만, 효과가 있다.
필드를 정식으로 정의할 필요는 없습니다.SELECT를 사용하여 필드를 만듭니다.
DROP TEMPORARY TABLE IF EXISTS my_temp_table;
CREATE TEMPORARY TABLE my_temp_table
SELECT first_name FROM people WHERE last_name = 'Smith';
(Create Table을 사용하지 않고 select 문에서 임시 테이블 작성을 참조하십시오).
에서는 MySQL을 사용하여 이를 할 수 .WHILE 디세이블로그:
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
...
);
FIND_를 사용해 보세요.MySql의 IN_SET() 함수입니다.
SET @c = 'xxx,yyy,zzz';
SELECT * from countries
WHERE FIND_IN_SET(countryname,@c);
주의: CSV 값을 사용하여 파라미터를 전달할 경우 StoredProcedure에서 변수를 설정할 필요가 없습니다.
현재 JSON 어레이를 사용하는 것은 명백한 해답입니다.
이것은 오래되었지만 여전히 관련이 있는 질문이기 때문에 나는 짧은 예를 들었다.JSON 함수는 mySQL 5.7.x/MariaDB 10.2.3 이후 사용 가능
ELT()보다 이 솔루션을 선호합니다.왜냐하면 이 솔루션은 실제로는 어레이에 가깝고 이 '어레이'는 코드에서 재사용할 수 있기 때문입니다.
다만, 주의해 주세요.일시적인 테이블을 사용하는 것보다 확실히 (JSON)이 훨씬 느립니다.그게 더 편리할 뿐이야, 임마
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();
어레이에 대해서는 알 수 없지만 일반 VARCHAR 열에 쉼표로 구분된 목록을 저장하는 방법이 있습니다.
또한 목록에서 무언가를 찾아야 할 경우 FIND_를 사용할 수 있습니다.IN_SET() 함수.
조금 늦은 답변인 것은 알지만, 최근 비슷한 문제를 해결해야 해서 다른 사람에게 도움이 될지도 모른다고 생각했습니다.
배경
'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);
나는 이것이 매우 구체적인 경우라는 것을 알고 있다.그러나 변수가 값의 배열을 저장해야 하는 다른 거의 모든 경우에 맞게 수정할 수 있습니다.
이게 도움이 됐으면 좋겠어요.
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/Anything 값이 있는지 확인하는 함수를 만들었습니다.
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에 "array" 파라미터를 전달할 수 있는 유일한 방법입니다.
어느 대답에도 ELT/FILD가 언급되어 있지 않은 것이 놀랍습니다.
ELT/FILD는 특히 정적 데이터가 있는 경우 어레이와 매우 유사하게 작동합니다.
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('BB','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)
배열 변수가 정말 필요한가요?
원래 MySQL 테이블 변수로 어레이를 추가하고 싶어 이곳에 왔기 때문에 물어봅니다.저는 데이터베이스 설계를 비교적 처음이어서 일반적인 프로그래밍 언어 방식으로 어떻게 할지 고민하고 있었습니다.
하지만 데이터베이스는 다릅니다.어레이를 변수로 사용하고 싶다고 생각했습니다만, 일반적인 MySQL 데이터베이스 프랙티스가 아닌 것이 판명되었습니다.
표준 프랙티스
어레이의 대체 솔루션은 다른 테이블을 추가한 후 외부 키를 사용하여 원래 테이블을 참조하는 것입니다.
예를 들어, 가구원 모두가 가게에서 사고 싶어하는 모든 아이템을 추적하는 앱을 상상해 보자.
처음에 생각했던 테이블을 작성하기 위한 명령어는 다음과 같습니다.
#doesn't work
CREATE TABLE Person(
name VARCHAR(50) PRIMARY KEY
buy_list ARRAY
);
buy_list는 콤마로 구분된 문자열 같은 것을 상정하고 있었던 것 같습니다.
하지만 MySQL에는 어레이 유형 필드가 없기 때문에 다음과 같은 것이 꼭 필요했습니다.
CREATE TABLE Person(
name VARCHAR(50) PRIMARY KEY
);
CREATE TABLE BuyList(
person VARCHAR(50),
item VARCHAR(50),
PRIMARY KEY (person, item),
CONSTRAINT fk_person FOREIGN KEY (person) REFERENCES Person(name)
);
여기서는 fk_person이라는 이름의 제약조건을 정의합니다.BuyList의 'person' 필드는 외국 키라고 되어 있습니다.즉, 다른 테이블의 기본 키, 특히 사용자 테이블의 '이름' 필드입니다.이것은 REFERENCES가 나타내는 것입니다.
또, 사람과 아이템의 편성을 주된 열쇠로 하고 있습니다만, 엄밀히 말하면 그럴 필요는 없습니다.
마지막으로, 사용자 목록의 모든 항목을 가져오려면 다음 쿼리를 실행할 수 있습니다.
SELECT item FROM BuyList WHERE person='John';
이것으로 존의 목록에 있는 모든 항목이 표시됩니다.어레이 불필요!
이것은, 다음의 값 리스트로 정상적으로 동작합니다.
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;
이것은 요소 목록을 포함하는 변수를 사용하는 솔루션입니다.간단한 쿼리에서 사용할 수 있습니다(스토어 프로시저를 사용하거나 테이블을 작성할 필요가 없음).
JSON_TABLE 기능을 사용하는 트릭은 다른 사이트에서 찾았습니다(mysql 8에서는 동작하지만 다른 버전에서는 동작하지 않습니다).
set @x = '1,2,3,4' ;
select c.NAME
from colors c
where
c.COD in (
select *
from json_table(
concat('[',@x,']'),
'$[*]' columns (id int path '$') ) t ) ;
또한 하나 이상의 변수가 empty_string으로 설정되어 있는 경우를 관리해야 할 수도 있습니다.이 경우 다른 트릭을 추가했습니다(x, y 또는 x와 y가 모두 빈 문자열인 경우에도 쿼리는 오류를 반환하지 않습니다).
set @x = '' ;
set @y = 'yellow' ;
select c.NAME
from colors
where
if(@y = '', 1 = 1, c.NAME = @y)
and if(@x = '', 1, c.COD) in (
select *
from json_table(
concat('[',if(@x = '', 1, @x),']'),
'$[*]' columns (id int path '$') ) t) ;
두 가지 버전 모두 MySQL 5.5에서 테스트된 버전에서는 사용할 수 없었습니다.ELT() 함수는 전체 집합을 반환합니다.WHIT 문장은 프로시저 컨텍스트에서만 사용할 수 있다는 점을 고려하여 솔루션에 추가했습니다.
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__;
솔직히 이건 좋은 방법이 아닌 것 같아요.정말 필요하다고 해도, 거의 읽을 수 없고, 꽤 느립니다.
어레이의 포인트는 효율적이지 않습니까?값을 통해 반복하는 경우 쉼표를 찾는 것보다 임시(또는 영구) 테이블에 커서를 두는 것이 더 합리적이라고 생각합니다.깔끔하기도 하고."mysql DECLAPE CURSOR"를 검색합니다.
랜덤 액세스의 경우 수치적으로 인덱스가 지정된 기본 키가 있는 임시 테이블입니다.유감스럽게도 가장 빠른 접근은 해시 테이블이지 진정한 랜덤 액세스가 아닙니다.
같은 문제를 볼 수 있는 또 다른 방법입니다.도움이 되기를 바라다
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,');
그런 테이블이 하나 있으면
mysql> select * from user_mail;
+------------+-------+
| email | user |
+------------+-------+-
| email1@gmail | 1 |
| email2@gmail | 2 |
+------------+-------+--------+------------+
및 어레이 테이블:
mysql> select * from user_mail_array;
+------------+-------+-------------+
| email | user | preferences |
+------------+-------+-------------+
| email1@gmail | 1 | 1 |
| email1@gmail | 1 | 2 |
| email1@gmail | 1 | 3 |
| email1@gmail | 1 | 4 |
| email2@gmail | 2 | 5 |
| email2@gmail | 2 | 6 |
CONCAT 기능을 사용하여 두 번째 테이블의 행을 하나의 배열로 선택할 수 있습니다.
mysql> SELECT t1.*, GROUP_CONCAT(t2.preferences) AS preferences
FROM user_mail t1,user_mail_array t2
where t1.email=t2.email and t1.user=t2.user
GROUP BY t1.email,t1.user;
+------------+-------+--------+------------+-------------+
| email | user | preferences |
+------------+-------+--------+------------+-------------+
|email1@gmail | 1 | 1,3,2,4 |
|email2@gmail | 2 | 5,6 |
+------------+-------+--------+------------+-------------+
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;
도움이 됐으면 좋겠다.
다음은 쉼표로 구분된 문자열을 루프하는 MySQL의 예입니다.
DECLARE v_delimited_string_access_index INT;
DECLARE v_delimited_string_access_value VARCHAR(255);
DECLARE v_can_still_find_values_in_delimited_string BOOLEAN;
SET v_can_still_find_values_in_delimited_string = true;
SET v_delimited_string_access_index = 0;
WHILE (v_can_still_find_values_in_delimited_string) DO
SET v_delimited_string_access_value = get_from_delimiter_split_string(in_array, ',', v_delimited_string_access_index); -- get value from string
SET v_delimited_string_access_index = v_delimited_string_access_index + 1;
IF (v_delimited_string_access_value = '') THEN
SET v_can_still_find_values_in_delimited_string = false; -- no value at this index, stop looping
ELSE
-- DO WHAT YOU WANT WITH v_delimited_string_access_value HERE
END IF;
END WHILE;
이 명령어는get_from_delimiter_split_string여기서 정의되는 함수:https://stackoverflow.com/a/59666211/3068233
이 답변은 개선할 수 있을 것 같습니다.이것을 시험해 보세요.
변수 '프랭크'는 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' 카테고리의 다른 글
| MySQL에서 고정 목록으로 항목을 주문하시겠습니까? (0) | 2022.09.29 |
|---|---|
| 날짜 범위에서 매일 행을 생성하시겠습니까? (0) | 2022.09.29 |
| IntelliJ IDEA 커뮤니티 에디션을 사용하여 스프링 부트 애플리케이션을 디버깅하는 방법 (0) | 2022.09.29 |
| "is" 연산자는 정수로 예기치 않게 동작합니다. (0) | 2022.09.29 |
| matplotlib의 리버스 colormap (0) | 2022.09.29 |