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

神諭丶發表於2015-12-01
MySql儲存過程陣列
在儲存過程中，傳入的引數經常可能會有陣列，而mysql沒有自帶處理陣列的方法或函式。

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


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

使用例子：
  1. mysql> select FUN_GET_ELEMENT('第一個|第二個|第三個', '|', -1);
  2. +------------------------------------------------------------------------------+
  3. | FUN_GET_ELEMENT('第一個|第二個|第三個', '|', -1)                               |
  4. +------------------------------------------------------------------------------+
  5. | 上越界                                                                        |
  6. +------------------------------------------------------------------------------+
  7. 1 row in set (0.00 sec)
  9. mysql> select FUN_GET_ELEMENT('第一個|第二個|第三個', '|', 0);
  10. +------------------------------------------------------------------------------+
  11. | FUN_GET_ELEMENT('第一個|第二個|第三個', '|', 0)                               |
  12. +------------------------------------------------------------------------------+
  13. | 第一個                                                                       |
  14. +------------------------------------------------------------------------------+
  15. 1 row in set (0.00 sec)
  17. mysql> select FUN_GET_ELEMENT('第一個|第二個|第三個', '|', 1);
  18. +-----------------------------------------------------------------------------+
  19. | FUN_GET_ELEMENT('第一個|第二個|第三個', '|', 1)                               |
  20. +-----------------------------------------------------------------------------+
  21. | 第二個                                                                       |
  22. +-----------------------------------------------------------------------------+
  23. 1 row in set (0.01 sec)
  25. mysql> select FUN_GET_ELEMENT('第一個|第二個|第三個', '|', 2);
  26. +-----------------------------------------------------------------------------+
  27. | FUN_GET_ELEMENT('第一個|第二個|第三個', '|', 2)                               |
  28. +-----------------------------------------------------------------------------+
  29. | 第三個                                                                       |
  30. +-----------------------------------------------------------------------------+
  31. 1 row in set (0.00 sec)
  33. mysql> select FUN_GET_ELEMENT('第一個/第二個/第三個', '/', 5);
  34. +-----------------------------------------------------------------------------+
  35. | FUN_GET_ELEMENT('第一個/第二個/第三個', '/', 5)                               |
  36. +-----------------------------------------------------------------------------+
  37. | 下越界                                                                       |
  38. +-----------------------------------------------------------------------------+
  39. 1 row in set (0.00 sec)

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

FUN_GET_ELEMENT函式定義：
  1. DROP FUNCTION IF EXISTS FUN_GET_ELEMENT;

  3. delimiter ;;

  5. CREATE FUNCTION `FUN_GET_ELEMENT`(
  6.     `mainstring` varchar(1024) CHARSET utf8,
  7.     `mark` varchar(4) CHARSET utf8,
  8.     `count` int
  9.     )
  10.     RETURNS varchar(65) CHARSET utf8

  12. BEGIN

  14.     DECLARE v_string varchar(1024) CHARSET utf8 DEFAULT mainstring;
  15.     DECLARE v_element varchar(64) CHARSET utf8;
  16.     DECLARE i int DEFAULT 0;

  18.     IF(count > 0 and count <= FUN_GET_COUNT(mainstring, mark)) THEN
  19.         
  20.         WHILE(i < count) DO
  21.             
  22.             SET v_string = SUBSTRING(v_string, INSTR(v_string, mark) + 1, CHAR_LENGTH(v_string));  
  23.             SET i = i + 1;
  24.         
  25.         END WHILE;
  26.     
  27.     END IF;
  28.     
  29.     IF(count < 0) THEN

  31.         SET v_element = '上越界';

  33.     ELSEIF (count < FUN_GET_COUNT(mainstring, mark)) THEN

  35.         SET v_element = SUBSTRING(v_string, 1, INSTR(v_string, mark) - 1);

  37.     ELSEIF (count = FUN_GET_COUNT(mainstring, mark)) THEN

  39.         SET v_element = v_string;

  41.     ELSE

  43.          SET v_element = '下越界';

  45.     END IF;

  47. RETURN(v_element);

  49. END;;

  51. delimiter ;

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

該函式使用例子：
  1. mysql> select FUN_GET_COUNT('a|b|c','|');
  2. +----------------------------+
  3. | FUN_GET_COUNT('a|b|c','|') |
  4. +----------------------------+
  5. | 2                          |
  6. +----------------------------+
  7. 1 row in set (0.00 sec)


FUN_GET_COUNT函數定義：
  1. DROP FUNCTION IF EXISTS FUN_GET_COUNT;

  3. delimiter ;;

  5. CREATE FUNCTION `FUN_GET_COUNT`(
  6.     `mainstring` varchar(1024) CHARSET utf8,
  7.     `mark` varchar(200) CHARSET utf8
  8.     ) RETURNS int

  10. BEGIN
  11.     
  12.     DECLARE i int;
  13.     
  14.     SET i = (CHAR_LENGTH(mainstring) - CHAR_LENGTH(REPLACE(mainstring, mark, ''))) / CHAR_LENGTH(mark);
  15.     
  16.     RETURN i;

  18. END;;

  20. delimiter ;

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


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

  1. DROP PROCEDURE IF EXISTS PROC_TEST;

  3. delimiter ;;

  5. CREATE PROCEDURE `PROC_TEST`(
  6.     `text` varchar(64) CHARSET utf8
  7.     )

  9. BEGIN
  10.     
  11.     DECLARE v_text varchar(64) CHARSET utf8 DEFAULT text;
  12.     DECLARE v_element varchar(16) CHARSET utf8;
  13.     DECLARE v_element_count int DEFAULT 0;
  14.     DECLARE i int DEFAULT 0;

  16.     IF LENGTH(text) > 0 THEN

  18.         SET v_element_count = FUN_GET_COUNT(v_text, '|');
  19.         
  20.         WHILE (i <= v_element_count) DO

  22.             SET v_element = FUN_GET_ELEMENT(v_text, '|', i);
  23.             SELECT v_element;
  24.             SET i = i + 1;    

  26.         END WHILE;

  28.     END IF;

  30. END;;

  32. delimiter ;


陣列預設為字串陣列，若為數值陣列，則可在最後返回值通過系統函數CONVERT()轉換。
  1. mysql> SELECT CONVERT('1', SIGNED);
  2. +----------------------+
  3. | CONVERT('1', SIGNED) |
  4. +----------------------+
  5. | 1                    |
  6. +----------------------+
  7. 1 row in set (0.00 sec)


測試上述procedure：
  1. mysql> CALL PROC_TEST('test1|test2');
  2. +-----------+
  3. | v_element |
  4. +-----------+
  5. | test1     |
  6. +-----------+
  7. 1 row in set (0.00 sec)

  9. +-----------+
  10. | v_element |
  11. +-----------+
  12. | test2     |
  13. +-----------+
  14. 1 row in set (0.00 sec)

  16. Query OK, 0 rows affected (0.00 sec)


作者公眾號（持續更新）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1850718/，如需轉載，請註明出處，否則將追究法律責任。

相關文章