MySQL string segmentation and dynamic Execution stitching SQL

I used to mainly use the MSSQL, the recent project in the use of MySQL, oneself is a typical small white. Record a MySQL stored procedure today, where you need to split strings and execute SQL statements dynamically.

About string Segmentation I began to use LOCATE and position to do, do not know in proc sometimes error, a separate query can be. Later on the internet to find a MySQL stored procedure string segmentation article, many places are recommended to use SET @result = REVERSE (Substring_index (Substring_index (REVERSE (plate_sku_ Name, ', ', I)), ', ', 1);

As for the execution of SQL , PREPARE SQL1 from @sql1 is required ;   EXECUTE sql1; The feeling is also more complicated than MSSQL.

DELIMITER $$ DROP PROCEDURE IF EXISTS simple_while$$ CREATE PROCEDURE simple_while (in Period_no VARCHAR (7), in all_test_id TEXT, out Return_count INT) BEGIN SET return_count=0; SET @i=0; SET @arraylength=1+ (Length (all_test_id)-Length (REPLACE (all_test_id,',',"'))); While @i<@arraylength do SET @i[Email protected]+1; SET @result= REVERSE (Substring_index (REVERSE (Substring_index (all_test_id,',', @i)),',',1)); SET @sql=concat ('Update Test_', Period_no,'Set is_cancel = 1 WHERE all_test_id =', @result);         PREPARE SQL1 from @sql;         EXECUTE SQL1; SET Return_count=return_count+Row_count ();          END while; end$$ DELIMITER;

MySQL string segmentation and dynamic Execution stitching SQL