批量操作数据库的多个表新增字段

鮀城小帅

已于 2023-01-13 10:03:20 修改

阅读量901

点赞数

分类专栏：数据库文章标签：数据库 java sql 批量操作新增字段 Powered by 金山文档

于 2023-01-12 17:10:13 首次发布

本文链接：https://blog.csdn.net/weixin_42405670/article/details/128663234

版权

数据库专栏收录该内容

62 篇文章 16 订阅

订阅专栏

DROP PROCEDURE IF EXISTS testEndHandle;
DELIMITER $$
 CREATE PROCEDURE testEndHandle()
BEGIN
  DECLARE s_tablename VARCHAR(100);
 DECLARE cur_table_structure CURSOR
 FOR 
 SELECT table_name 
 FROM INFORMATION_SCHEMA.TABLES 
 -- table_schema 是数据库名  
 WHERE table_schema = 'book' AND table_name LIKE "y_%";
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s_tablename = NULL;
 OPEN cur_table_structure;
 FETCH cur_table_structure INTO s_tablename;
 label:WHILE ( s_tablename IS NOT NULL) DO
--  SET @MyQuery=CONCAT("alter table `",s_tablename,"` add COLUMN `create_by` varchar(255) COMMENT '由谁创建'");
--  SET @MyQuery=CONCAT("alter table `",s_tablename,"` add COLUMN `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '添加时间_时间戳'");
--  SET @MyQuery=CONCAT("alter table `",s_tablename,"` add COLUMN `update_by` varchar(255) COMMENT '由谁修改'");
--  SET @MyQuery=CONCAT("alter table `",s_tablename,"` add COLUMN `update_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间_时间戳'");
--  SET @MyQuery=CONCAT("alter table `",s_tablename,"` modify COLUMN `update_time_stamp` datetime(0) COMMENT '修改时间_时间戳'"); 
SET @MyQuery=CONCAT("alter table `",s_tablename,"` add COLUMN `remark` varchar(255) COMMENT '更新备注'");
  PREPARE msql FROM @MyQuery;
  EXECUTE msql ;
    
  FETCH cur_table_structure INTO s_tablename;
  END WHILE label;
 CLOSE cur_table_structure;
END;
CALL testEndHandle();