mysql 默认值批量更改_mysql 批量修改默认值

CREATE PROCEDURE `sp_clearchardefaultvalue`()

BEGIN

DECLARE s_tablename VARCHAR(100);

DECLARE s_fieldname VARCHAR(100);

/*显示表的数据库中的所有表

SELECT table_name FROM information_schema.tables WHERE table_schema='databasename' Order by table_name ;

*/

#显示所有varchar的字段

DECLARE cur_table_structure CURSOR

FOR

SELECT table_name,column_name

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_schema = 'databasename' AND (data_type ='varchar' OR data_type ='char') AND column_default IS NOT NULL

ORDER BY table_name;

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

OPEN cur_table_structure;

FETCH cur_table_structure INTO s_tablename,s_fieldname;

WHILE ( s_tablename IS NOT NULL) DO

SET @MyQuery=CONCAT("alter table `",s_tablename,"` alter column `",s_fieldname,"` drop default");

PREPARE msql FROM @MyQuery;

EXECUTE msql ;#USING @c;

FETCH cur_table_structure INTO s_tablename,s_fieldname;

END WHILE;

CLOSE cur_table_structure;

END$$