I don't to change a repository of all the fields of table
This is me to write a stored procedure:
The BEGIN
DECLARE _tableName VARCHAR (32);
DECLARE _done INT the DEFAULT 0;
DECLARE _cur CURSOR FOR
The SELECT table_name FROM information_schema. ` COLUMNS ` WHERE TABLE_SCHEMA='test' AND table_name NOT LIKE 'ACT %' group by table_name;
DECLARE the CONTINUE HANDLER FOR SQLSTATE '02000' SET _done=1; # error definition, tag loop end
The OPEN _cur;
REPEAT
The FETCH _cur INTO _tableName;
IF NOT _done THEN
The BEGIN
IF NOT the EXISTS (SELECT * FROM information_schema. The columns WHERE table_schema='test' AND table_name=_tableName AND column_name='PROJECT_ID') THEN
SET @ STMT:=CONCAT (_tableName "ALTER TABLE", "ADD the COLUMN PROJECT_ID VARCHAR (32);" );
PREPARE STMT FROM @ STMT;
The EXECUTE STMT.
# the ALTER TABLE _tableName ADD COLUMN PROJECT_ID VARCHAR (32).
END the IF;
END;
END the IF;
REPEAT UNTIL _done END;
The CLOSE _cur;
The SET _done=0; Only defined as 0 #, new cycle can be continued,
END

Perform tip: 1265 - Data truncated for column '_tableName' at row 13
Urgent solution!!

CodePudding user response:

DECLARE _tableName VARCHAR (32);
Set the length of the larger try

CodePudding user response:

Thank you autfish, indeed as expected

CodePudding user response:

Check whether there is a long table name in this statement, the original poster only defines _tableName VARCHAR (32).

The SELECT table_name FROM information_schema. ` COLUMNS ` WHERE TABLE_SCHEMA='test' AND table_name NOT LIKE 'ACT %' group by table_name;

CodePudding user response:

Qq_31198711
Deep sea wonders

Junction post rate: 0%

Reference

suggest you list your table structure, and provide the test data, and based on these test data that corresponds to the correct result,
Refer to the way the post questions http://bbs.csdn.net/topics/320211382

1. You create table XXX.. Statement
2. You insert into XXX... Statement
3. The result is what kind, and give a simple algorithm description)
4. Do you use the database name and version of (often asked in MS SQL server edition MySQL)

So people who want to help you to build the same environment with you directly, and in providing the test before, avoid the error in the text understanding,

CodePudding user response:

Thanks for ACMAIN_CHM advice

CodePudding user response:

Why I wrote it according to your storage structure, and then show a success, see table structure and it didn't change?