[大数据]1.存储过程：将数据库中表存量数据中值为空的公共字段批量填充默认值

1.建立存储过程

程序如下：

DELIMITER $
CREATE PROCEDURE add_property (IN database_name VARCHAR (10),IN filed_name VARCHAR(20),IN default_value VARCHAR(20)) 
BEGIN 
  DECLARE cnt INT DEFAULT 0;
  DECLARE tb VARCHAR (20) ;
  DECLARE done INT DEFAULT FALSE ;
  DECLARE cur CURSOR FOR 
  SELECT 
    table_name 
  FROM
    information_schema.tables 
  WHERE table_schema = database_name ;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;
  OPEN cur ;
  posLoop :
  LOOP
    FETCH cur INTO tb ;
    IF done 
    THEN LEAVE posLoop ;
    END IF ;
    -- 定义关键sql语句--
    SET @_SQL7 =CONCAT("select * from information_schema.columns where table_name ='", tb,"' and column_name ='",filed_name,"';") ;
    SET @_SQL3 = CONCAT('update ',tb," set ",filed_name," = ","'",default_value,"' where ",filed_name,"is null;") ;
    # 预定义sql   预定义拼接好的sql语句
    PREPARE stmt7 FROM @_SQL7;
    # 查询该表中是否存在tran_timestamp字段
    EXECUTE stmt7;
    SET SQL_SAFE_UPDATES = 0;
    -- 获取查询影响的行数
    DEALLOCATE PREPARE stmt7;
    SET cnt = FOUND_ROWS();
    # 表中存在时间戳字段
    IF cnt>0 THEN 
      PREPARE stmt3 FROM @_SQL3;
      EXECUTE stmt3;
      DEALLOCATE PREPARE stmt3;
    END IF ;
  END LOOP posLoop ;
  CLOSE cur ;
END $

2.使用该存储过程

CALL add_property(‘peixun’,‘yan’,‘nibaba’);//第一个参数代表数据库名称，第二个参数是字段名称，第三个参数是给该列设置的值

3.实例

有一个名为peixun的数据库，

CREATE TABLE `login_record` (
   `Id` int(20) NOT NULL AUTO_INCREMENT,
   `User_id` int(10) NOT NULL,
   `Login_time` datetime NOT NULL,
   `yan` varchar(50) DEFAULT NULL,
   `tran_timestamp` datetime DEFAULT NULL,
   PRIMARY KEY (`Id`),
   KEY `user_id` (`User_id`),
   CONSTRAINT `user_id` FOREIGN KEY (`User_id`) REFERENCES `user_login` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
 
CREATE TABLE `user_login` (
   `Id` int(10) NOT NULL AUTO_INCREMENT,
   `Password` varchar(20) NOT NULL,
   `User_name` varchar(15) NOT NULL,
   `Create_time` datetime NOT NULL,
   `Login_time` datetime DEFAULT NULL,
   `Login_state` int(11) NOT NULL,
   `yan` varchar(50) DEFAULT NULL,
   `tran_timestamp` varchar(26) NOT NULL COMMENT '时间戳',
   PRIMARY KEY (`Id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

插入数据后运行存储过程：

CALL add_property('peixun','yan','nibaba');//第一个参数代表数据库名称，第二个参数是字段名称，第三个参数是给该列设置的值

结果：