Mysql uses cursor bulk processing for table operations _mysql

I. Overview

This section describes using cursors to bulk table operations, including bulk index, bulk add fields, and more. If you are not familiar with stored procedures, variable definitions, preprocessing, read the previous articles on these three concepts, and only understand the three concepts before you can better understand this article.

Understanding MySQL variables and conditions: http://www.jb51.net/article/81375.htm

Understanding MySQL Prepare preprocessing statements: http://www.jb51.net/article/81378.htm

Understanding MySQL stored procedures and functions: http://www.jb51.net/article/81381.htm

Second, the text

1, Declaration cursor

DECLARE cursor_name Cursor for select_statement

This statement declares a cursor. You can also define multiple cursors in a subroutine, but each cursor in a block must have a unique name.

Note: A SELECT statement cannot have an into clause.

2, open the cursor

DECLARE cursor_name Cursor for select_statement

This statement opens the cursor that was previously declared.

3, Forward cursor

FETCH cursor_name into Var_name [, Var_name] ...

This statement reads the next line with the specified open cursor, if there is a next line, and advances the cursor pointer.

4, close the cursor

Close cursor_name

This statement closes the previously opened cursor.

5. Add Index in bulk

Share a cursor with a batch index, and the bulk operation becomes simpler when there are hundreds of tables in a library with different names.

#删除创建存储过程 DROP PROCEDURE IF EXISTS founttable; 
  DELIMITER $$ CREATE PROCEDURE founttable () BEGIN DECLARE tablename (64); #声明游标 DECLARE cur_founttable CURSOR for the SELECT table_name from INFORMATION_SCHEMA.
  TABLES WHERE table_schema= ' front ' and table_name like ' student% ';
  DECLARE EXIT HANDLER for don't found close cur_founttable;
  #打开游标 OPEN cur_founttable;
     REPEAT FETCH cur_founttable into TableName; #定义预处理 SET @SQLSTR1 = CONCAT (' CREATE index Flag on ', ', TableName, ' ', ' (Flag); 
     '); SET @SQLSTR2 = CONCAT (' Create index state ', ', ', TableName, ' ', ' (state); 
     '); SET @SQLSTR3 = CONCAT (' Create index upload on ', ', TableName, ' ', ' (upload); 
     '); SET @SQLSTR4 = CONCAT (' CREATE index ccflag on ', ', TableName, ' ', ' (Lockflag); 
     '); SET @SQLSTR5 = CONCAT (' Create index comes on ', ', TableName, ' ', ' (comes); 
     ');
     # # #SET @SQLSTR =concat (@SQLSTR1, @SQLSTR2, @SQLSTR3, @SQLSTR4, @SQLSTR5); 
     PREPARE STMT1 from @SQLSTR1; PREPARE STMT2 FROM @SQLSTR2; 
     PREPARE STMT3 from @SQLSTR3; 
     PREPARE STMT4 from @SQLSTR4; 
     PREPARE STMT5 from @SQLSTR5; 
     EXECUTE STMT1; 
     EXECUTE STMT2; 
     EXECUTE STMT3; 
     EXECUTE STMT4; 
     EXECUTE STMT5;
     Deallocate PREPARE STMT1;
     Deallocate PREPARE STMT2;
     Deallocate PREPARE STMT3;
     Deallocate PREPARE STMT4;
    Deallocate PREPARE STMT5;
     
  # SELECT @SQLSTR;
  UNTIL 0 End REPEAT;

#关闭游标 Close cur_founttable;


End $$ DELIMITER;

 Call Founttable ();

Here are a few details:

• Remember to modify the criteria you want to query when declaring a cursor
• It needs to be changed to the corresponding field in the preprocessing.
• When I define a condition variable, I use Exit here, which is interrupted when I encounter an error, and of course I can use continue.

Note: Because MySQL in the stored procedure can not be used to query the name of the variable directly as a table name, so the use of dynamic concatenation of the SQL method, but the usual set concat method does not work, so here is the use of prepare to precompile.

Summary

Although batch processing can sometimes improve the efficiency of the work, but the potential risk is very large, so before executing must be very confident that you execute the statement on the impact of the data, otherwise in the build environment is very dangerous.