Процедура нормализации MySQL

Возникла у меня задачка приведения таблиц к форме 2НДФЛ ко второй нормальной форме. Причём задачка должна выполняться регламентно и желательно без моего участия. Для её автоматизации была написана процедура, которая получая на вход название таблицы и название колонки создаёт словарь уникальных значений, проставляет индексы и внешние ключи. Сейчас небольшой пример. Имеем таблицу foo_table, в колонке bar которой будут названия фруктов/овощей, а в колонке baz -- цвет.

CREATE  TABLE `example_db`.`foo_table` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `bar` VARCHAR(45) NULL ,
  `baz` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) );
INSERT INTO `example_db`.`foo_table` (`bar`, `baz`) VALUES ('apple', 'red');
INSERT INTO `example_db`.`foo_table` (`bar`, `baz`) VALUES ('apple', 'yellow');
INSERT INTO `example_db`.`foo_table` (`bar`, `baz`) VALUES ('apple', 'green');
INSERT INTO `example_db`.`foo_table` (`bar`, `baz`) VALUES ('banana', 'yellow');
INSERT INTO `example_db`.`foo_table` (`bar`, `baz`) VALUES ('melon', 'yellow');
INSERT INTO `example_db`.`foo_table` (`bar`, `baz`) VALUES ('radish', 'red');

Избыточность налицо.

Таблица с избыточными данными

Обещанная процедура:

delimiter //

CREATE PROCEDURE `normalize`(IN t_name VARCHAR(100), IN f_name VARCHAR(100))
    DETERMINISTIC
    COMMENT 'Alter table to NF2'
BEGIN
    DECLARE schema_name VARCHAR(50);
    SELECT DATABASE() INTO @schema_name;
    SET FOREIGN_KEY_CHECKS = 0;
    SET @sql_text = concat('DROP TABLE IF EXISTS dic_',f_name);
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
    SET FOREIGN_KEY_CHECKS = 1;

    SET @sql_text = concat('
    CREATE  TABLE dic_',f_name,' (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(255) NOT NULL ,
    PRIMARY KEY (`id`) )
    ENGINE = InnoDB');
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
    
    SET @sql_text = concat('INSERT INTO dic_',f_name,' (`name`) SELECT DISTINCT ',f_name,' from ',t_name);
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;

    SET @sql_text = (SELECT IF(
    (SELECT COUNT(*)
        FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE
        `table_schema` = @schema_name AND `table_name`=t_name and `column_name` = CONCAT(f_name,'_id')
    ) > 0,
        CONCAT('UPDATE `',@schema_name,'`.`',t_name, '` SET `',f_name,'_id`=NULL WHERE 1=1'),
        CONCAT('ALTER TABLE `',@schema_name,'`.`',t_name,'` ADD COLUMN `',f_name,'_id` INT NULL DEFAULT NULL  AFTER `',f_name,'` , 
            ADD CONSTRAINT `fk_',f_name,'_id`
            FOREIGN KEY (`',f_name,'_id` )
            REFERENCES `',@schema_name,'`.`dic_',f_name,'` (`id` )
            ON DELETE SET NULL
            ON UPDATE CASCADE
            , ADD INDEX `',f_name,'_id` (`',f_name,'_id` ASC) 
            , ADD INDEX `fk_',f_name,'_id` (`',f_name,'_id` ASC) ;')
    ));
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
    
    SET @sql_text = concat('UPDATE `',@schema_name,'`.`',t_name,'` t
        INNER JOIN `dic_',f_name,'` d on t.',f_name,'=d.name
        SET t.',f_name,'_id = d.id');
    
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;

-- Uncomment if need to delete column (f_name) from table (t_name)
/*
    SET @sql_text = concat('ALTER TABLE `',@schema_name,'`.`',t_name,'` DROP COLUMN `',f_name,'`;');
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
*/    
    DEALLOCATE PREPARE stmt;
END//

Использование простое:

call normalize('foo_table', 'baz');

В результате выполнения процедуры будет создана таблица dic_baz (имя таблицы формируется из имени поля с префиксом 'dic_'), в которую будет произведена выборка уникальных значений столбца baz таблицы foo_table. Далее в таблицу foo_table будет добавлена проиндексированная колонка baz_id с внешним ключом до таблицы dic_baz. И в завершение колонка baz_id будет заполнена соответствующими id-шниками. Построенная в phpMyAdmin диаграмма выглядит следующим образом:

Нормализация по полю baz

Если провести нормализацию по полю bar, то исходная таблица foo_table трансформируется до промежуточной таблицы (связь "многие-ко-многим"):

call normalize('foo_table', 'bar');

Нормализация по полю bar

В конце процедуры есть закомментированный кусок. Если его раскомментировать, то исходная колонка будет удалена из таблицы (в примерах выше).

Важные замечания

  1. Я тестировал процедуру исключительно на движке InnoDB, поддерживающим внешние ключи
  2. Длина поля name в таблицах словарей хардкодом ограничена 255 символами (`name` VARCHAR(255) NOT NULL ,)

Добавлено: 2014-11-06, обновлено: 2014-11-07


Поделиться:

Оставить комментарий

Комментарий появится после одобрения.

Поля со значком * обязательны для заполнения.