将整个数据库的空字符串更新为NULL [英] Updating empty string to NULL for entire database

问题描述

我正在执行一些数据库清理，并注意到有很多列在各个列中同时包含空字符串和NULL值.

I'm performing some database clean up and have noticed that there are a lot of columns that have both empty strings and NULL values in various columns.

是否可以编写一条SQL语句来将数据库中每个表的每一列的空字符串更新为NULL，但不允许空的字符串除外?

Is it possible to write an SQL statement to update the empty strings to NULL for each column of each table in my database, except for the ones that do not allow NULL's?

我查看了information_schema.COLUMNS表，并认为这可能是开始的地方.

I've looked at the information_schema.COLUMNS table and think that this might be the place to start.

推荐答案

我想出了如何使用存储过程来做到这一点.下次我一定会考虑使用脚本语言.

I figured out how to do this using a stored procedure. I'd definitely look at using a scripting language next time.

DROP PROCEDURE IF EXISTS settonull;

DELIMITER //

CREATE PROCEDURE settonull()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE _tablename VARCHAR(255);
  DECLARE _columnname VARCHAR(255);
  DECLARE cur1 CURSOR FOR SELECT 
                           CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS table_name,
                           COLUMN_NAME AS column_name 
                           FROM information_schema.COLUMNS 
                           WHERE IS_NULLABLE = 'YES' 
                           AND TABLE_SCHEMA IN ('table1', 'table2', 'table3');

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO _tablename, _columnname;

    IF done THEN
      LEAVE read_loop;
    END IF;

    SET @s = CONCAT('UPDATE ', _tablename, ' SET ', _columnname, ' = NULL WHERE LENGTH(TRIM(', _columnname, ')) = 0' );
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  END LOOP;

  CLOSE cur1;
END//

DELIMITER ;

CALL settonull();

这篇关于将整个数据库的空字符串更新为NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！