在儲存過程中，傳入的引數經常可能會有陣列，而mysql沒有自帶處理陣列的方法或函式。

需求是這樣的：
傳入陣列形如：【aaa|bbb|ccc】
返回值形如：【aaa】、【bbb】、【ccc】

自己寫了一個自定義函式來完成上述需求：

使用例子：

mysql> select FUN_GET_ELEMENT('第一個|第二個|第三個', '|', -1);
+------------------------------------------------------------------------------+
| FUN_GET_ELEMENT('第一個|第二個|第三個', '|', -1) |
+------------------------------------------------------------------------------+
| 上越界 |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select FUN_GET_ELEMENT('第一個|第二個|第三個', '|', 0);
+------------------------------------------------------------------------------+
| FUN_GET_ELEMENT('第一個|第二個|第三個', '|', 0) |
+------------------------------------------------------------------------------+
| 第一個 |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select FUN_GET_ELEMENT('第一個|第二個|第三個', '|', 1);
+-----------------------------------------------------------------------------+
| FUN_GET_ELEMENT('第一個|第二個|第三個', '|', 1) |
+-----------------------------------------------------------------------------+
| 第二個 |
+-----------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select FUN_GET_ELEMENT('第一個|第二個|第三個', '|', 2);
+-----------------------------------------------------------------------------+
| FUN_GET_ELEMENT('第一個|第二個|第三個', '|', 2) |
+-----------------------------------------------------------------------------+
| 第三個 |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select FUN_GET_ELEMENT('第一個/第二個/第三個', '/', 5);
+-----------------------------------------------------------------------------+
| FUN_GET_ELEMENT('第一個/第二個/第三個', '/', 5) |
+-----------------------------------------------------------------------------+
| 下越界 |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

其中傳入的值有三個：
第一個是要處理的陣列，
第二個是陣列內元素與元素的分隔符，比如 | /
第三個是要取出的元素索引，比如，0為第一個元素，1為第二個元素，以此類推，若輸入的索引沒有值，則返回上越界或下越界

FUN_GET_ELEMENT函式定義：

DROP FUNCTION IF EXISTS FUN_GET_ELEMENT;
delimiter ;;
CREATE FUNCTION `FUN_GET_ELEMENT`(
`mainstring` varchar(1024) CHARSET utf8,
`mark` varchar(4) CHARSET utf8,
`count` int
)
RETURNS varchar(65) CHARSET utf8
BEGIN
DECLARE v_string varchar(1024) CHARSET utf8 DEFAULT mainstring;
DECLARE v_element varchar(64) CHARSET utf8;
DECLARE i int DEFAULT 0;
IF(count > 0 and count <= FUN_GET_COUNT(mainstring, mark)) THEN
WHILE(i < count) DO
SET v_string = SUBSTRING(v_string, INSTR(v_string, mark) + 1, CHAR_LENGTH(v_string));
SET i = i + 1;
END WHILE;
END IF;
IF(count < 0) THEN
SET v_element = '上越界';
ELSEIF (count < FUN_GET_COUNT(mainstring, mark)) THEN
SET v_element = SUBSTRING(v_string, 1, INSTR(v_string, mark) - 1);
ELSEIF (count = FUN_GET_COUNT(mainstring, mark)) THEN
SET v_element = v_string;
ELSE
SET v_element = '下越界';
END IF;
RETURN(v_element);
END;;
delimiter ;

上述UDF呼叫了另外一個函數FUN_GET_COUNT()，這一個函數同樣也是自定義的。
用於獲取傳入的字串陣列的最大索引（元素數目-1）。

該函式使用例子：

mysql> select FUN_GET_COUNT('a|b|c','|');
+----------------------------+
| FUN_GET_COUNT('a|b|c','|') |
+----------------------------+
| 2 |
+----------------------------+
1 row in set (0.00 sec)

FUN_GET_COUNT函數定義：

DROP FUNCTION IF EXISTS FUN_GET_COUNT;
delimiter ;;
CREATE FUNCTION `FUN_GET_COUNT`(
`mainstring` varchar(1024) CHARSET utf8,
`mark` varchar(200) CHARSET utf8
) RETURNS int
BEGIN
DECLARE i int;
SET i = (CHAR_LENGTH(mainstring) - CHAR_LENGTH(REPLACE(mainstring, mark, ''))) / CHAR_LENGTH(mark);
RETURN i;
END;;
delimiter ;

（當然也可以將兩個UDF合併在一起。）

在儲存過程中使用起來也是十分方便：

DROP PROCEDURE IF EXISTS PROC_TEST;
delimiter ;;
CREATE PROCEDURE `PROC_TEST`(
`text` varchar(64) CHARSET utf8
)
BEGIN
DECLARE v_text varchar(64) CHARSET utf8 DEFAULT text;
DECLARE v_element varchar(16) CHARSET utf8;
DECLARE v_element_count int DEFAULT 0;
DECLARE i int DEFAULT 0;
IF LENGTH(text) > 0 THEN
SET v_element_count = FUN_GET_COUNT(v_text, '|');
WHILE (i <= v_element_count) DO
SET v_element = FUN_GET_ELEMENT(v_text, '|', i);
SELECT v_element;
SET i = i + 1;
END WHILE;
END IF;
END;;
delimiter ;

陣列預設為字串陣列，若為數值陣列，則可在最後返回值透過系統函數CONVERT()轉換。

mysql> SELECT CONVERT('1', SIGNED);
+----------------------+
| CONVERT('1', SIGNED) |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)

測試上述procedure：

mysql> CALL PROC_TEST('test1|test2');
+-----------+
| v_element |
+-----------+
| test1 |
+-----------+
1 row in set (0.00 sec)
+-----------+
| v_element |
+-----------+
| test2 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

作者公眾號（持續更新）

MySQL儲存過程中處理陣列 UDF程式碼分享

相關文章