MySQL变动表结构

最新推荐文章于 2024-05-17 14:37:10 发布

abilities_zhx

最新推荐文章于 2024-05-17 14:37:10 发布

阅读量140

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_50295533/article/details/128187809

版权

MySQL变动表结构

1.批量修改该库中的字段

DROP PROCEDURE IF EXISTS updateColumn;
CREATE PROCEDURE updateColumn(in db_name varchar(100),in cul_name varchar(100),in default_value varchar(100),in remark varchar(100))
BEGIN
    -- 定义循环条件
    DECLARE flag INT DEFAULT 0;
    DECLARE tname VARCHAR(50);
		DECLARE dt VARCHAR(20);
    -- 查询指定数据库中含有指定列的表
    DECLARE result CURSOR FOR SELECT distinct TABLE_NAME,COLUMN_TYPE FROM  INFORMATION_SCHEMA.Columns WHERE TABLE_SCHEMA = db_name AND COLUMN_NAME = cul_name;
    -- 退出循环
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET flag = 1;
    -- 打开游标
    OPEN result;
    WHILE flag <> 1 DO
            -- 游标指向下一个位置,可以有多个数据,比如FETCH result INTO tname,ttype,...;
            FETCH result INTO tname,dt;
						
            -- 拼接字符串表名sql,根据需要使用CONCAT函数连接
            SET @execSql = CONCAT('alter table ', tname, ' MODIFY column ',cul_name,' ',dt,' default ',default_value);
						if remark!='' THEN
							SET @execSql=CONCAT(@execSql,'  COMMENT  ','''',remark,'''');
						END if;
						-- SELECT @execSql;
            PREPARE stmt FROM @execSql;
            EXECUTE stmt;
        END WHILE;
END;
-- 实例
CALL updateColumn('goods','create_time','CURRENT_TIMESTAMP','创建时间');
CALL updateColumn('goods','modify_time','CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP','更新时间');
CALL updateColumn('goods','is_delete',0,'');

2.修改单个表单字段


DROP PROCEDURE IF EXISTS upSingleTableDefault;
CREATE PROCEDURE upSingleTableDefault(in db_name varchar(100),in t_name varchar(100),in cul_name varchar(100),in default_value varchar(100),in remark varchar(100))
BEGIN
		DECLARE dt varchar(10);
		SELECT distinct column_type FROM  INFORMATION_SCHEMA.Columns WHERE TABLE_SCHEMA = CONCAT('',db_name,'')
		AND TABLE_NAME= CONCAT('',t_name,'') AND COLUMN_NAME = CONCAT('',cul_name,'') INTO dt;
		SET @execSql = CONCAT('alter table ', t_name, ' MODIFY column ',cul_name,' ',dt,' default ',default_value);
		if remark!='' THEN
			SET @execSql=CONCAT(@execSql,'  COMMENT  ','''',remark,'''');
		END if;
						-- SELECT @execSql;
		PREPARE stmt FROM @execSql;
		EXECUTE stmt;
END;
-- 实例
CALL upSingleTableDefault('goods','ent_behavior_log','state',0,'');
CALL upSingleTableDefault('goods','ent_oauth_client_details','state',0,'');
CALL upSingleTableDefault('goods','t_cmp_account_bind','state',1,'');