将整个数据库的空字符串更新为 NULL

Question

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

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

我查看了 information_schema.COLUMNS 表并认为这可能是起点。

Answer 1

使用一条简单的 SQL 语句不可能做到这一点。

但您可以对每一列使用一个语句来完成此操作。

UPDATE TABLE SET COLUMN = NULL
WHERE LENGTH(COLUMN) = 0

或者，如果您想清空也有空格的项目：

UPDATE TABLE SET COLUMN = NULL
WHERE LENGTH(TRIM(COLUMN)) = 0

Answer 2

我认为这在 MySQL 中是不可能的，但使用您选择的脚本语言当然是可能的。

1. 首先获取所有表 SHOW TABLES
2. 然后对于每个表获取不同的列并找出允许 null 的表，可以使用 DESC TABLE、SHOW CREATE TABLE 或 SELECT * FROM information_schema.COLUMNS，选择您更愿意解析的那个
3. 然后对于允许 null 的每一列运行正常更新，将“”更改为 null。

准备花一些时间等待:)

Answer 3

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

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();