修改表内容如果为 NULL,则改为空字符串

BEGINDECLARE a VARCHAR(200);DECLARE done int DEFAULT FALSE;DECLARE cur CURSOR for selecta.COLUMN_NAME from information_schema.`COLUMNS` a WHERE a.TABLE_NAME='tiu_emp';DECLARE CONTINUE HANDLER fo

達爾蓋的旗幟

2574人浏览 · 2015-06-15 20:08:55

達爾蓋的旗幟 · 2015-06-15 20:08:55 发布

DROP PROCEDURE IF EXISTS `aa`;

CREATE DEFINER = `root`@`%` PROCEDURE `aa`(IN table_name varchar(200))
BEGIN
DECLARE a VARCHAR(200);
DECLARE done int DEFAULT FALSE;
DECLARE cur CURSOR for select  a.COLUMN_NAME from information_schema.`COLUMNS` a WHERE a.TABLE_NAME= table_name ;
DECLARE CONTINUE HANDLER for NOT FOUND set done = true; 
OPEN cur;
read_loop: LOOP
    FETCH cur INTO a;
    IF done THEN
      LEAVE read_loop;
    END IF;
    set @b = CONCAT('update ',table_name , ' set ', table_name ,'.',a,' = case when ', a, ' is null then \'\' else ', a ,' end;');
    
    select @b;
    PREPARE str from @b; 
    EXECUTE str; 
    DEALLOCATE PREPARE str;
  END LOOP;
  select @b;
  CLOSE cur;
END;