MySQL Bulk DLL operation

Overview

This section describes using Cursors for bulk table operations, including batch indexing, batch adding fields, and more. If you are not familiar with stored procedures, variable definitions, and preprocessing, read the articles on these three concepts that I wrote earlier, and only understand the three concepts to get a better understanding of the article.

MySQL variables and conditions: 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

cursor_name 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

Cursor_name

This statement opens the previously declared cursor.

Forward cursor

cursor_name 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 cursor

Cursor_name

This statement closes the previously opened cursor.

Batch Add index

Share a batch index cursor, when a library has hundreds of table structure but the name of a different table, this time the bulk operation becomes simple.

#删除创建存储过程
DROP PROCEDURE IF EXISTS founttable;delimiter $ $CREATE PROCEDURE founttable () BEGIN DECLARE TableName varchar (64);
    #声明游标 DECLARE cur_founttable CURSOR for SELECT table_name from INFORMATION_SCHEMA.    TABLES WHERE table_schema= ' front ' and table_name like ' student% ';    DECLARE EXIT HANDLER for not 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 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 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 need 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 to break when I encounter an error, and of course I can use continue.

Note: Since MySQL cannot use the queried variable name directly as the table name in the stored procedure, the method of dynamically splicing SQL is used, but the usual set concat method does not work, so the prepare is used for precompilation.

Summarize

Batch processing Although it can sometimes improve the efficiency of the work, but the potential danger is also very large, so you must be very confident in the execution of the statement of the data impact, otherwise in the production environment is very dangerous

Http://www.cnblogs.com/chenmh/p/5308146.html

MySQL Bulk DLL operation (RPM)