Because of the need to upgrade procedures, need to store the code, increase the table ks_goods a field, the stored procedure is as follows:

 DELIMITER//
DROP PROCEDURE IF the EXISTS ` checkTabColumns `; 
CREATE PROCEDURE checkTabColumns () 
The BEGIN 
# define variables 
Declare CurrentDatabase VARCHAR (100); 
Declare the rs char (4); 
Declare MDSTR char (10); # the value of this variable is what I want to increase the field name! 
Declare cursor_finished int (1); 
# define cursor 
Declare cursor_store CURSOR for the select storeSymbol from ks_store; 
Declare the CONTINUE HANDLER FOR the NOT FOUND SET cursor_finished=1; 

The set CurrentDatabase=database (); 
The open cursor_store; 
Loop1: loop 
The fetch cursor_store into rs; 
If cursor_finished=1 then 
Leave loop1. 
End the if; 
The set MDSTR=CONCAT (" md_ ", rs); # I want field name format is: md_SA 
The select MDSTR; # MDSTR here really has been assigned to format for I think! 
# check whether have the field 
If not the exists (select * from information_schema. The columns where TABLE_SCHEMA=CurrentDatabase AND TABLE_NAME='ks_goods' AND COLUMN_NAME=MDSTR) then 
The alter table ` ks_goods ` add column MDSTR tinyint (1) NOT NULL DEFAULT 0 COMMENT '1: stores the goods belong to the code, 0: do NOT belong to the' after ` isvalid `; 
# this way, although has added a new field, but field name is not md_SA, and is MDSTR??????? Why is that? How to increase the field name is md_SA? 
End the if; 
End loop loop1; 
The close cursor_store; 
END//
DELIMITER. 
CALL checkTabColumns (); 

Actually increase the fields below:

CodePudding user response:

After the add MDSTR, change into a md_SA

CodePudding user response:

Mysql table name, column name can't is variable, can consider to use to PREPARE

CodePudding user response:

Thank ACMAIN_CHM moderators!
This adds a knowledge point again!
To learn to prepare

CodePudding user response:

Where I'm going crazy, this is wrong

CodePudding user response:

Know what is going on, prepare must be user variables...

CodePudding user response:

Prepare must be @ the sort of