Today, the supervisor lets me add two fields to each table in the database, there are hundreds of tables in the database, it is obviously too time-consuming to add one by one, and there may be errors, such as missing a few tables without adding.
Landlord thought to use a cursor can easily solve this problem, the following is the test code I write with MySQL, not much, directly on the code:
Usestudentcourse, delimiter $$Drop procedure if existsAddColumn;Create procedureAddColumn ()BEGINDeclareTableNamevarchar( -); #存储表名Declare Str varchar( -); #要执行的sqlDeclareNumint; #表数量DeclareCurcursor forSelecttable_name fromInformation_schema.tableswhereTable_schema= 'Studentcourse'; #定义游标并赋值所有的表名Select Count(*) fromInformation_schema.tableswhereTable_schema= 'Studentcourse' intonum; #查找该数据库的表数量Opencur; #打开游标 whileNum> 0Do #循环FetchCur intoTableName;Set @sqlstr =Concat'ALTER TABLE', TableName,'Add column condition2 varchar (NOT NULL)'); #使用concat拼接sql语句, note the stitching of adjacent characters to leave spacesPrepare Str from @sqlstr; #预编译Execute Str; #执行语句deallocate PREPARE Str; #释放预编译SetNum=Num- 1;End while;End$ $delimitercall addcolumn; #调用存储过程
After execution, you can see the number of rows returned, but the value returned is inconsistent with the number of tables in my database. But I looked at the table structure and each table was successfully added to this field.
I am a rookie, want to learn the content of their own records, but also to provide reference to other people, do not like to spray, thank you!
MySQL uses cursors to add fields to all tables in the specified database