Two ways to traverse an array string in a MySQL stored procedure

The first: Multiple use of the Substring_index () method

2. DELIMITER $$
4. DROP PROCEDURE IF EXISTS ' array ' $$
6. CREATE PROCEDURE ' array ' ()
8. BEGIN
10. SET @array_content="www mysql com hcymysql blog 51cto com";
12. SET @i=1;
14. SET @count=char_length (@array_content)-char_length (REPLACE (@array_content, ', ')) + 1;
16. --Draw the total number of array members
18. CREATE TABLE test.tmp (field1 VARCHAR (100));
20. While @i <= @count
22. Do
24. INSERT into Test.tmp VALUES
26. (Substring_index (Substring_index (@array_content, ", @i),",-1));
28. --Insert each member in turn
30. SET @[email protected]+1;
32. END while;
34. end$$
36. DELIMITER;

Second: Using a method that iterates through each character

drop procedure if exists pro_deletelog;--exists to deleteCREATE PROCEDURE Pro_deletelog (qualification varchar (100))--Define parametersbegindeclare i int;declare Start1 int;declare Length int;declare totallenght int;declare filed varchar (+);declare sqlstr varchar (+);declare stmtnovelsearch varchar (+);set I=1;set start1=1;set length=0;set Totallenght=length (qualification); --Calculate input parameter lengthSelect Totallenght; While i <=totallenght do--i=1 start--Select SUBSTRING (qualification,i,1);if (SUBSTRING (qualification,i,1) = ', ')--note that MySQL's SUBSTRING function intercepts strings starting with 1 instead of 0, unlike Java JavaScript. --to intercept a character from the first bit to see if it equals, Thenset filed=substring (qualification,start1,length);--intercept length characters from Start1 Select filed; Set @sqlStr = CONCAT (' INSERT into End_results (accessid,filedname,errinfo) SELECT * FROM (SELECT W1.accessid, \ ', filed, ' \ ' as Filedname, ', filed, ' from Wdd_audit W1, wdd_dbaccessinfo W2 where W1.accessid=w2.accessid ' A Where not EXISTS (select E. ', filed, ' from Exp_results e where E. ', filed, ' =a. ', filed, ' or (E. ', filed, ' Is null and A. '), fil (Ed, ' is null) ' );--Assemble SQL statementsSelect @sqlStr; PREPARE Stmtnovelsearch from @sqlStr;--PREPARE must use this method of communicationExecute stmtnovelsearch; deallocate prepare stmtnovelsearch;set start1=i+1;set length=0;Elseset length=length+1;--if not, the length of the Intercept plus 1End If;set i=i+1;end While;End --Call the Stored procedure field name to, separating attention to the last, endCall Pro_deletelog (' Payload,backresult,dataset,effectrow,clientuser,loginuser,clientprg, ')Personal recommendation of the first;

Two ways to traverse an array string in a MySQL stored procedure