Обновление пустой строки до NULL для всей базы данных

Я выполняю некоторую очистку базы данных и заметил, что есть много столбцов, которые имеют как пустые строки, так и значения NULL в разных столбцах.

Можно ли написать оператор SQL для обновления пустых строк до NULL для каждого столбца каждой таблицы в моей базе данных, за исключением тех, которые не допускают NULL?

Я просмотрел таблицу information_schema.COLUMNS и думаю, что с нее можно было бы начать.


mysql stored-procedures information-schema
person hafichuk    schedule 02.12.2011    source источник

Ответы (3)


arrow_upward
5
arrow_downward

Это невозможно сделать с помощью одного простого оператора SQL.

Но вы можете сделать это, используя один оператор для каждого столбца.

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

или, если вы хотите обнулить элементы, которые также имеют пробелы:

UPDATE TABLE SET COLUMN = NULL
WHERE LENGTH(TRIM(COLUMN)) = 0
person O. Jones    schedule 02.12.2011
comment
Спасибо за базовый синтаксис. Я надеялся, что будет способ избежать этого для каждого столбца. - person hafichuk; 03.12.2011

arrow_upward
1
arrow_downward

Я не думаю, что это возможно в MySQL, но, конечно, с языком сценариев по вашему выбору.

  1. Начните с получения всех таблиц SHOW TABLES
  2. Затем для каждой таблицы получите разные столбцы и выясните, какие из них допускают нуль, либо с DESC TABLE, SHOW CREATE TABLE, либо с SELECT * FROM information_schema.COLUMNS, возьмите тот, который вы предпочитаете анализировать.
  3. Затем для каждого столбца, допускающего нуль, запустите обычное обновление, которое изменит "" на нуль.

Приготовьтесь потратить некоторое время на ожидание :)

person Andreas Wederbrand    schedule 02.12.2011

arrow_upward
0
arrow_downward

Я понял, как это сделать с помощью хранимой процедуры. В следующий раз я определенно рассмотрю возможность использования языка сценариев.

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();
person hafichuk    schedule 02.12.2011