DanBrother的部落格

drop procedure if exists sp_array_test;

DELIMITER //
create procedure sp_array_test ()
BEGIN
declare current_label varchar(10);
declare label_array varchar(100);

SET label_array = 'aa,bb,cc,dd,ee,ff';
WHILE label_array != '' 
DO
  SET current_label = SUBSTRING_INDEX(label_array, ',', 1); 

  IF LOCATE(',', label_array) > 0 THEN
     SET label_array = SUBSTRING(label_array, LOCATE(',', label_array) + 1);
  ELSE
     SET label_array = '';
  END IF;
    
  SELECT concat("Current Tag: ", current_label) as "Current Label";

END WHILE;
END //
DELIMITER ;

mysql> call sp_array_test();
+-----------------+
| Current Label   |
+-----------------+
| Current Tag: aa |
+-----------------+
1 row in set (0.00 sec)

+-----------------+
| Current Label   |
+-----------------+
| Current Tag: bb |
+-----------------+
1 row in set (0.00 sec)

+-----------------+
| Current Label   |
+-----------------+
| Current Tag: cc |
+-----------------+
1 row in set (0.00 sec)

+-----------------+
| Current Label   |
+-----------------+
| Current Tag: dd |
+-----------------+
1 row in set (0.00 sec)

+-----------------+
| Current Label   |
+-----------------+
| Current Tag: ee |
+-----------------+
1 row in set (0.00 sec)

+-----------------+
| Current Label   |
+-----------------+
| Current Tag: ff |
+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)