mysql--通过存储过程修改数据库中列的长度，并不影响列的其它属性

存储过程的适用场景

当一个或者多个数据库中存在很多表，并且很多表都含有某个字段，而且这些表这个字段都要修改长度，那么以下的存储过程就适用了。

1、创建存储过程-将涉及某个关键字的列所在的所有表及列的信息存储到一个视图中。

DROP PROCEDURE IF EXISTS pro_queryColInTable;
delimiter //
CREATE PROCEDURE `pro_queryColInTable`(in databaseName VARCHAR(20), in colName VARCHAR(20), in changeLen int)
BEGIN
	-- 第一个参数databaseName：数据库名
	-- 第二个参数colName：列关键字
	-- 第三个参数changeLen：小于这个长度的列
	DROP VIEW IF EXISTS colInTable_view;
	SET @sqlstr = "CREATE VIEW colInTable_view as ";  
	SET @sqlstr = CONCAT(@sqlstr , "SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `COLUMN_NAME`, `COLUMN_DEFAULT`, `IS_NULLABLE`, `DATA_TYPE`, `CHARACTER_MAXIMUM_LENGTH`, `CHARACTER_SET_NAME`, `COLLATION_NAME`, `COLUMN_KEY`, `COLUMN_COMMENT` FROM information_schema.columns WHERE TABLE_SCHEMA = '", databaseName, "' and COLUMN_NAME like ", '''%', colName, '%''', ' and CHARACTER_MAXIMUM_LENGTH < ', changeLen, ' and TABLE_NAME not like ', '''', '%view%', ''''); 
	select @sqlstr;
	PREPARE stmt FROM @sqlstr;  
	EXECUTE stmt;  
	DEALLOCATE PREPARE stmt; 
END; //
delimiter ;

2、创建存储过程-将视图中的数据用游标遍历然后生成修改字段的长度的sql，然后执行。

DROP PROCEDURE IF EXISTS pro_changeColLength;
delimiter //
CREATE PROCEDURE `pro_changeColLength`(in colName VARCHAR(20), in changeLen int)
BEGIN
	-- 该存储过程用来处理数据库中所有表中包含某个关键字的长度
	-- 第一个参数就是关键字，第二个参数就是字段修改后的长度。

  -- 定义变量
	DECLARE s int DEFAULT 0;
	DECLARE a VARCHAR(100);  -- 库名
	DECLARE b VARCHAR(100);  -- 表名
	DECLARE c VARCHAR(100);  -- 列名
	DECLARE d VARCHAR(50);   -- 列默认值
	DECLARE e VARCHAR(10);   -- 是否为空
	DECLARE f VARCHAR(50);   -- 字段类型
	DECLARE g VARCHAR(50);       -- 字段长度
	DECLARE h VARCHAR(50);   -- 默认字符集
	DECLARE i VARCHAR(50);   -- 排序规则
	DECLARE j VARCHAR(20);   -- 索引
	DECLARE k VARCHAR(1000);   -- 注释	
	
	DECLARE modifySql VARCHAR(1000); -- 修改字段长度的sql
	-- DECLARE querySql VARCHAR(1000); -- 修改字段长度的sql

	-- 定义游标，并将colintable_view中的结果集赋值到游标中
	DECLARE mycursor CURSOR FOR SELECT * FROM colintable_view;

	-- 声明当游标遍历完后将标志变量置成某个值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
	-- 打开游标
  open mycursor;
	
			-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
			fetch mycursor into a,b,c,d,e,f,g,h,i,j,k;
			
			
			-- 当s不等于1，也就是未遍历完时，会一直循环
			while s<>1 do
					IF locate(colName, c) > 0 && g < changeLen THEN
					
							-- 执行业务逻辑
							SET modifySql = CONCAT(' ALTER TABLE ', a, '.', b, ' MODIFY COLUMN ', c, ' ', f, '(', changeLen, ')', ' CHARACTER SET ', h, ' COLLATE ', i);
							
							IF e = 'YES' THEN
								SET modifySql = CONCAT(modifySql, ' NULL ');
							ELSE
								SET modifySql = CONCAT(modifySql, ' NOT NULL ');
							END IF;
							
							IF e = 'YES' && d IS NULL THEN 
								SET modifySql = CONCAT(modifySql, 'DEFAULT NULL');
							ELSEIF e = 'YES' && d IS NOT NULL && d <> '' THEN
								SET modifySql = CONCAT(modifySql, 'DEFAULT ', d);
							ELSEIF e = 'YES' && d IS NOT NULL && d = '' THEN
								SET modifySql = CONCAT(modifySql, 'DEFAULT ', '''', '''');
							ELSEIF e = 'NO' && d IS NOT NULL && d = '33' THEN
								SET modifySql = CONCAT(modifySql, 'DEFAULT ', d);
							ELSEIF e = 'NO' && d = '' THEN
								SET modifySql = CONCAT(modifySql, 'DEFAULT ', '''', '''');
							END IF;
							
							

							IF k IS NOT NULL && k <> '' THEN 
								SET modifySql = CONCAT(modifySql, ' COMMENT ', '''', k, '''');
							END IF;

							
							 
							set @v_sql = modifySql;   		-- 注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
						  prepare stmt from @v_sql;  	-- 预处理需要执行的动态SQL，其中stmt是一个变量
						  EXECUTE stmt;      					-- 执行SQL语句
						 	deallocate prepare stmt;    -- 释放掉预处理段
					END IF;
					
					
				  -- 当s等于1时表明遍历以完成，退出循环
          fetch mycursor into a,b,c,d,e,f,g,h,i,j,k;
					
      end while;
			
   -- 关闭游标
   close mycursor;	

END; //
delimiter ;

3、执行存储过程pro_queryColInTable

call pro_queryColInTable('zx_test', 'sku', 100);

4、执行存储过程pro_changeColLength

call pro_changeColLength('sku', 100);