Recently to add new field to the table, because a lot of form, but they are all like this:
Ytx_qrcode_order_origin_201901,
Ytx_qrcode_order_origin_201902,
Ytx_qrcode_order_origin_201903
Is the back of the table name ending on the date, now is to add a field, all the tables if one by one to add is too slow, wonder is there any way to write a stored procedure, for example, according to the name in front of the fuzzy matching, and then to a field to add these tables?

CodePudding user response:

Has just passed the query has found a way that is using GROUP_CONCAT joining together to get the results of the query, and then take the results to perform,
Because table a lot so I first set the length of the query, or the length of the query is not
SET the SESSION group_concat_max_len=10240;
Next is the result of the query
SELECT
GROUP_CONCAT (alter table, table_schema, '. ', table_name, 'add column platform_code varchar (50); 'the SEPARATOR "")
The FROM information_schema. Tables WHERE
Ytx_qrcode_order table_schema='dcsbus AND table_name LIKE' % '.

PS: platform_code is I want to add the names of the fields, dcsbus is my library name, ytx_qrcode_order is I need to add the name of the table prefix is same

Finally the result is a SQL statement, you can perform the following:


Process and then copy the contents inside, probably waiting for more than 1 minute, the field current is added, the diagram below:


Because I'm not very familiar with stored procedure on this, see a pure process was used to realize online, link: https://blog.csdn.net/showchi/article/details/100051625 didn't quite understand, is there a big look at if use stored procedures, this should be how to modify?

CodePudding user response:

Basic DDL operations, spell the Alter statement string, reoccupy script,

https://blog.csdn.net/qq_39706570/article/details/106014157

The two words is main:

PREPARE sql_sentence FROM @ sql_content;
/* note that the sequence parameters, */
The EXECUTE sql_sentence USING @ param_id @ param_id2;

CodePudding user response:

DROP PROCEDURE IF the EXISTS testEndHandle;
DELIMITER $$

CREATE PROCEDURE testEndHandle ()
The BEGIN
DECLARE s_tablename VARCHAR (100);


# query ytx_qrcode_order opening table
DECLARE cur_table_structure CURSOR
FOR
The SELECT table_name
The FROM INFORMATION_SCHEMA. TABLES
WHERE table_schema='dcsbus AND table_name LIKE "ytx_qrcode_order %";


DECLARE the CONTINUE HANDLER FOR SQLSTATE '02000' SET s_tablename=NULL;

The OPEN cur_table_structure;

The FETCH cur_table_structure INTO s_tablename;

WHILE (s_tablename IS NOT NULL) DO
The SET @ MyQuery=CONCAT (" alter table ` ", s_tablename, "` add COLUMN ` platform_code ` varchar (50) COMMENT 'platform CODE");
PREPARE MSQL FROM @ MyQuery;

The EXECUTE MSQL; C # USING @;

The FETCH cur_table_structure INTO s_tablename;
END the WHILE;
The CLOSE cur_table_structure;

END;
$$

# execute the stored procedure
CALL testEndHandle ();