MySQL uses stored procedures to iterate over user information and insert the corresponding field instance into the target table

Operating environment: There are table Game_list, fields: uid,score1,score2,seat_id,last_update;
The incoming parameter is I_player_detail, the incoming value is multiple user ID, before score, after score, seat number, and each user's data is separated by a semicolon (;);
Operation Objective: To insert the corresponding properties of each user into the corresponding field of the target table, last_update the date of the data update;
Incoming parameter i_player_detail, which holds multiple user information, each user's set of data separated by semicolons, each user's information multiple, such as
"User id,score,desk,seat;
User Id,score,desk,seat ... "

The code is as follows

Copy Code

--Using Stored procedures Delimiter $$ Use ' LOG_PDK ' $$ drop procedure if exists ' Game_c ' $$ CREATE PROCEDURE ' Game_c ' (in I_player_detail varchar (500)) SQL Security Invoker BEGIN DROP TABLE IF EXISTS ' temp_list '; --Create a temporary table, insert the intercepted data into the temporary table first CREATE temporary TABLE ' temp_list ' ( ' UID ' INT (a) UNSIGNED not NULL, ' Score1 ' INT (a) UNSIGNED not NULL, ' Score2 ' INT (a) UNSIGNED not NULL, ' Seat_id ' TINYINT (3) UNSIGNED not NULL ); --Declare str varchar (500);--Used to splice SQL dynamic statements DECLARE m_detail varchar (500); declare m_num tinyint; --When the incoming user information string contains a semicolon '; ', intercept Set m_num = position ('; ' in str)--returns 0 when there is no semicolon While M_num >= 1 do Begin Set @str = ' INSERT into temp_list values (' + substring (m_detail,1,m_num-1) + ') '--intercepts the first user's information (the character preceding the first semicolon) and inserts it into the temporary table Prepare statement1 from @str; Execute statement1; deallocate prepare statement1; Set m_detail = substring (m_detail,m_num+1); --Defines the string that removes the first user and semicolon section Set Set m_num = Position ('; ' in str); End while; --Extract all fields from the temporary table, add Time fields, insert into table game_list INSERT into ' game_list ' (' uid ', ' score1 ', ' score2 ', ' seat_id ', ' last_update ') SELECT ' uid ', ' score1 ', ' score2 ', ' seat_id ', current_date () From ' temp_list '; end$$ delimiter;