MySQL batch Dll operations, mysql batch dll operations

MySQL batch Dll operations, mysql batch dll operations
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.

 

MySQL variable and condition: http://www.cnblogs.com/chenmh/p/5203429.html

PREPARE preprocessing statement: http://www.cnblogs.com/chenmh/articles/5308085.html

MySQL stored procedures and functions: http://www.cnblogs.com/chenmh/p/5201473.html

Body cursor Declaration 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: The SELECT statement cannot have an INTO clause.

Open cursor

OPEN cursor_name

This statement opens the previously declared cursor.

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.

Close the cursor

CLOSE cursor_name

This statement disables the previously opened cursor.

 

Batch add index

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.

# Deleting a 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.

 

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly give the link at the beginning of the article. Welcome to discussion