Появится , что MySQL не имеет переменных массива. Что я должен использовать вместо этого?
Кажется, есть две альтернативы: A скалярный тип и временные таблицы. Вопрос, который я связывал, предлагает первый. Но полезно ли использовать их вместо переменных массива? В качестве альтернативы, если я иду с наборами, какова будет идиома на основе набора, эквивалентная foreach?
Ну, я использовал временные таблицы вместо переменных массива. Не самое лучшее решение, но оно работает.
Обратите внимание, что вам не нужно формально определять свои поля, просто создайте их с помощью SELECT:
CREATE TEMPORARY TABLE IF NOT EXISTS my_temp_table
SELECT first_name FROM people WHERE last_name = 'Smith';
(См. также Создать временную таблицу из инструкции выбора без использования таблицы создания.)
Вы можете достичь этого в 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;
EDIT:
В качестве альтернативы вы можете сделать это с помощью 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_IN_SET() MySql например
SET @c = 'xxx,yyy,zzz';
SELECT * from countries
WHERE FIND_IN_SET(countryname,@c);
Примечание. У вас нет необходимости устанавливать переменную в StoredProcedure, если вы передаете параметр со значениями CSV.
Не знаю о массивах, но есть способ хранить списки, разделенные запятыми, в обычном столбце VARCHAR.
И когда вам нужно найти что-то в этом списке, вы можете использовать функцию FIND_IN_SET().
В настоящее время использование массива 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();
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/любое значение в списке значений, разделенных запятыми:
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;
Итак, теперь вы можете искать идентификатор в списке идентификаторов, разделенных запятыми, например:
select `is_id_in_ids`('1001,1002,1003',1002);
И вы можете использовать эту функцию внутри предложения WHERE, например:
SELECT * FROM table1 WHERE `is_id_in_ids`('1001,1002,1003',table1_id);
Это был единственный способ передать параметр "массив" в ПРОЦЕДУРУ.
Я знаю, что это немного запоздалый ответ, но недавно мне пришлось решить аналогичную проблему и подумал, что это может быть полезно для других.
Фон
Рассмотрим таблицу ниже под названием "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)
В версии MYSQL после 5.7.x вы можете использовать тип JSON для хранения массива. Вы можете получить значение массива с помощью ключа через MYSQL.
Вдохновленный функцией ELT (порядковый номер, строка1, строка2, строка3,…), я думаю, что следующий пример работает как пример массива:
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,');
Думаю, я могу улучшить этот ответ. Попробуйте следующее:
Параметр "Pranks" - это 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'
);