MySQL uses cursors for batch table operations, and mysql uses cursors

MySQL uses cursors for batch table operations, and mysql uses cursors

I. Overview

This topic describes how to use cursors to operate tables in batches, including adding indexes and fields in batches. If you are not familiar with stored procedures, variable definitions, and preprocessing, read the article on these three concepts that I have previously written, only by first understanding these three concepts can we better understand this article.

Understanding MySQL variables and conditions: http://www.bkjia.com/article/81375.htm

Understanding Mysql prepare preprocessing statement: http://www.bkjia.com/article/81378.htm

Understanding MySQL stored procedures and functions: http://www.bkjia.com/article/81381.htm

Ii. Text

1. Declare the 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:The SELECT statement cannot have an INTO clause.

2. Open the cursor

DECLARE cursor_name CURSOR FOR select_statement

This statement opens the previously declared cursor.

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 disables the previously opened cursor.

 5. Batch add Indexes

Share a batch index adding cursor. When there are hundreds of tables with the same structure but different names in a database, batch operation becomes simple.

# Delete the Stored procedure drop procedure if exists FountTable; DELIMITER $ create procedure FountTable () begin declare TableName varchar (64 ); # DECLARE the cursor declare cur_FountTable cursor for select TABLE_NAME from cursor where TABLE_SCHEMA = 'front' AND TABLE_NAME LIKE 'student % '; declare exit handler for not found CLOSE cur_FountTable; # OPEN the cursor open cur_FountTable; repeat fetch cur_FountTable INTO TableName; # define preprocessing SET @ SQLSTR1 = CONCAT ('create index Flag on', ''', TableName, ''', '(Flag );'); SET @ SQLSTR2 = CONCAT ('create index State on ', ''', 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 extract FROM @ SQLSTR3; PREPARE STMT4 FROM @ SQLSTR4; PREPARE STMT5 FROM @ SQLSTR5; EXECUTE STMT1; EXECUTE extract; EXECUTE STMT3; EXECUTE STMT4; EXECUTE STMT5; PREPARE STMT1; optional PREPARE STMT2; deallocate prepare STMT3; deallocate prepare STMT4; deallocate prepare STMT5; # SELECT @ SQLSTR; UNTIL 0 end repeat; # CLOSE cursor; END $ DELIMITER; CALL FountTable ();

Here are several details:

• When declaring a cursor, remember to modify the conditions you want to query.
• In preprocessing, you also need to change the corresponding field.
• When defining conditional variables, here I use EXIT to interrupt when an error occurs. Of course, you can also use CONTINUE.

Note: Since mysql cannot directly use the queried variable name as the table name during the storage process, the dynamic concatenation SQL method is used here, however, the general set concat method does not work, so PREPARE is used here for pre-compilation.

 Summary

Although batch processing can improve work efficiency sometimes, it brings great potential risks. Therefore, you must be very confident about the impact of the statements you execute on the data before execution, otherwise, the generated environment is very dangerous.

Articles you may be interested in:

• SQL database batch processing script
• Usage of mysql Stored Procedure cursor Loop
• In-depth analysis on usage and closure of Mysql cursors
• Detailed usage of MySQL-based cursors
• Mysql Dynamic Cursor Learning (mysql Stored Procedure cursor)
• Instances that use cursors in mysql stored procedures
• Example of jumping out and continuing operations of the cursor loop in the MySQL Stored Procedure
• Mysql Stored Procedure nested Using cursor sample code
• Create and Delete tables in MySQL