Блог

Главная - Вопросы по программированию - MySQL Обновляет нулевые значения таблицы во время выполнения

MySQL Обновляет нулевые значения таблицы во время выполнения

#mysql #function #stored-procedures #triggers #dynamic-sql

#mysql #функция #хранимые процедуры #триггеры #динамический-sql

Вопрос:

Я пытаюсь обновить таблицу null значениями. Вот сценарий:

При моем первоначальном запуске таблица выглядела бы следующим образом:

  ---- ---------- ------- ------- ------- 
| id | item     | col_1 | col_2 | col_3 |
 ---- ---------- ------- ------- ------- 
| 1  | abc      | 10    | 20    | 30    |
 ---- ---------- ------- ------- -------

Однако в моих последующих запусках столбцы таблицы будут иметь дополнительные столбцы, которые будут выглядеть следующим образом:

  ---- ---------- ------- ------- ------- ------- ------- 
| id | item     | col_1 | col_2 | col_3 | col_4 | col_5 |
 ---- ---------- ------- ------- ------- ------- ------- 
| 1  | abc      | 10    | 20    | 30    | null  | null  |
 ---- ---------- ------- ------- ------- ------- ------- 
| 2  | ghq      | null  | 40    | null  | 50    | 60    | -- triggers the update of row_1. At the same time should update row_2
 ---- ---------- ------- ------- ------- ------- ------- 
| 3  | xyz      | 70    | 80    | null  | 90    | null  | -- updates row_3 and same updates moving forward
 ---- ---------- ------- ------- ------- ------- -------

Вот как это работает в серверной части:

  1. При первоначальном запуске создается таблица.
  2. При последующих запусках она изменяет таблицу для добавления дополнительных столбцов (col_4 и col_5).
  3. Затем обновляет записи, оставляя некоторые пустые ячейки.

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

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

Я быстро учусь, и мне просто нужны некоторые подсказки или руководства, по крайней мере, о том, какой подход к этому наилучший?

ПОПЫТКА 1: ХРАНИМАЯ ПРОЦЕДУРА ЦИКЛА WHILE

Код ошибки: 1054. Неизвестный столбец ‘@col_name’ в ‘списке полей’

 CREATE DEFINER=`admin`@`%` PROCEDURE `fill_null_values`()
BEGIN
    DECLARE c INT DEFAULT 0;          -- number of columns
    DECLARE r INT DEFAULT 0;          -- number of rows
    DECLARE i INT DEFAULT 0;          -- rows counter
    DECLARE e INT DEFAULT 0;          -- columns counter
    DECLARE col_name VARCHAR(100);    -- bad table column name
    
    /* initialize c = number of columns */
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = 'test_db' AND TABLE_NAME = 'bad_table'
        INTO c;
    
    /* initialize r = number of rows */
    SELECT COUNT(*) FROM bad_table INTO r;
    
    SET i = 0;
    SET e = 7; -- starts at column 7
    SET col_name = ''; 
    
    WHILE i <= r DO                                   
        WHILE e <= c DO

            /* get col_name */
            SET col_name = CONCAT("SELECT COLUMN_NAME
                                   FROM INFORMATION_SCHEMA.COLUMNS 
                                   WHERE TABLE_SCHEMA = 'test_db' 
                                      AND TABLE_NAME = 'canvass_prices'
                                      AND ORDINAL_POSITION = e"); 
               
            /* update null values */
            SET @sql = CONCAT("UPDATE bad_table 
                               SET `",col_name,"` = (
                                   SELECT ref_data FROM ref_table 
                                   JOIN bad_table ON ref_table.id = bad_table.id 
                                   WHERE bad_table.ref_data = i) 
                               WHERE `",col_name,"` IS NULL");

            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            SET e = e   1;
        END WHILE;

        SET i = i   1;
    END WHILE;    
END

Комментарии:

1. Это кричит о плохом дизайне БД. Если вы, вероятно, получите требуемые новые столбцы, то сделайте так, чтобы база данных записывала столбцы, так сказать, «вниз по странице» id | Item | ColName | ColValue | , например. Тогда вы можете изобрести 1000 новых col_n и вам никогда не придется возиться со структурой базы данных

2. запускает обновление row_1. В то же время следует обновить row_2 Забудьте — это невозможно. Триггер может изменять только значения в строке, обновление которой вызывает его запуск, и не может влиять на любые другие строки той же таблицы.

3. @RiggsFolly На самом деле это требование, и у меня нет выбора. Я уже выполнил ваше предложение и преобразовал его в столбец, все во время выполнения. Я только что достиг этого момента, пытаясь на этот раз заполнить пустые ячейки.

4. @Akina Спасибо, и я уже могу избежать срабатывания триггера. Я думаю, что единственный вариант, который у меня остался, — это цикл. Что вы думаете?

5. @RickyBelmont 1) Как я вижу, вы допускаете распространенную ошибку — вы думаете, что объявленная DECLARE col_name VARCHAR(100); переменная и пользовательская @col_name переменная — это одно и то же. Я должен сказать вам, что это неправда. 2) Другая ошибка — вы используете UPDATE bad_table SET @col_name = ... , INSERT синтаксис утверждает, что имя столбца должно быть в том месте, где вводится имя пользовательской переменной @col_name . Это приводит к ошибке. Если вы надеетесь, что переменная будет заменена ее значением — это снова неверно. Вам нужен подготовленный оператор.