MySQL ON DUPLICATE KEY UPDATE с нулевым столбцом в уникальном ключе

Наша база данных веб-аналитики MySQL содержит сводную таблицу, которая обновляется в течение дня, когда импортируется новое мероприятие. Мы используем ON DUPLICATE KEY UPDATE, чтобы обобщение перезаписывало более ранние вычисления, но испытывает трудности, поскольку один из столбцов в сводной таблице UNIQUE KEY является необязательным FK и содержит значения NULL.

Эти NULL предназначены для обозначения "нет, и все такие случаи эквивалентны". Конечно, MySQL обычно рассматривает NULL как значение "неизвестно, и все такие случаи не эквивалентны".

Базовая структура выглядит следующим образом:

Таблица "Активность", содержащая запись для каждого сеанса, каждая из которых принадлежит кампании, с дополнительными фильтрами и идентификаторами транзакций для некоторых записей.

CREATE TABLE `Activity` (
    `session_id` INTEGER AUTO_INCREMENT
    , `campaign_id` INTEGER NOT NULL
    , `filter_id` INTEGER DEFAULT NULL
    , `transaction_id` INTEGER DEFAULT NULL
    , PRIMARY KEY (`session_id`)
);

Таблица "Сводка", содержащая ежедневные сводки общего количества сеансов в таблице действий, и общее количество сеансов, содержащих идентификатор транзакции. Эти резюме разделяются, причем по одному для каждой комбинации кампании и (необязательного) фильтра. Это не транзакционная таблица, использующая MyISAM.

CREATE TABLE `Summary` (
    `day` DATE NOT NULL
    , `campaign_id` INTEGER NOT NULL
    , `filter_id` INTEGER DEFAULT NULL
    , `sessions` INTEGER UNSIGNED DEFAULT NULL
    , `transactions` INTEGER UNSIGNED DEFAULT NULL
    , UNIQUE KEY (`day`, `campaign_id`, `filter_id`)
) ENGINE=MyISAM;

Фактический запрос суммирования выглядит примерно так: подсчет количества сеансов и транзакций, а затем группировка по кампании и (необязательный) фильтр.

INSERT INTO `Summary` 
    (`day`, `campaign_id`, `filter_id`, `sessions`, `transactions`)
    SELECT `day`, `campaign_id`, `filter_id
        , COUNT(`session_id`) AS `sessions`
        , COUNT(`transaction_id` IS NOT NULL) AS `transactions`
    FROM Activity
    GROUP BY `day`, `campaign_id`, `filter_id`
ON DUPLICATE KEY UPDATE
    `sessions` = VALUES(`sessions`)
    , `transactions` = VALUES(`transactions`)
;

Все отлично работает, за исключением резюме случаев, когда filter_id имеет значение NULL. В этих случаях предложение ON DUPLICATE KEY UPDATE не соответствует существующей строке, и каждый раз записывается новая строка. Это связано с тем, что "NULL!= NULL". Однако, когда мы сравниваем уникальные ключи, нам нужно "NULL = NULL".

Я ищу идеи для обходных решений или отзывов о тех, с которыми мы пришли. Обходные решения, о которых мы думали, до сих пор следуют.

• Удалите все итоговые записи, содержащие значение ключа NULL, перед запуском сводки. (Это то, что мы делаем сейчас) Это имеет отрицательный побочный эффект при возврате результатов с отсутствующими данными, если запрос выполняется во время процесса суммирования.

• Измените столбец DEFAULT NULL на DEFAULT 0, который позволяет последовательно использовать UNIQUE KEY. Это имеет отрицательный побочный эффект чрезмерного усложнения разработки запросов к сводной таблице. Это заставляет нас использовать много "CASE filter_id = 0 THEN NULL ELSE filter_id END" и делает неудобное соединение, поскольку все остальные таблицы имеют фактические значения NULL для filter_id.

• Создайте представление, которое возвращает "CASE filter_id = 0 THEN NULL ELSE filter_id END" и непосредственно использует этот вид вместо таблицы. Сводная таблица содержит несколько сотен тысяч строк, и мне сказали, что производительность представления довольно плохая.

• Разрешить создание повторяющихся записей и удалять старые записи после завершения сводки. Имеются аналогичные проблемы для их удаления заблаговременно.

• Добавьте суррогатный столбец, содержащий 0 для NULL, и используйте этот суррогат в UNIQUE KEY (на самом деле мы могли бы использовать PRIMARY KEY, если все столбцы NOT NULL).
  Это решение кажется разумным, за исключением того, что приведенный выше пример является лишь примером; фактическая база данных содержит полдюжины сводных таблиц, одна из которых содержит четыре столбца с нулевым значением в UNIQUE KEY. Некоторые обеспокоены тем, что накладные расходы слишком много.

У вас есть лучшее обходное решение, структура таблицы, процесс обновления или наилучшая практика MySQL, которая может помочь?

EDIT: Чтобы прояснить "значение null"

Данные в сводных строках, содержащих столбцы NULL, считаются принадлежащими друг другу только в том смысле, что они представляют собой единую строку "catch-all" в сводных отчетах, суммируя те элементы, для которых эта точка данных не существует или неизвестна, Таким образом, в контексте самой сводной таблицы значение означает "сумма тех записей, для которых неизвестно значение". С другой стороны, в реляционных таблицах это действительно NULL-результаты.

Единственная причина поместить их в уникальный ключ в сводной таблице - это позволить автоматическое обновление (путем включения DUPLICATE KEY UPDATE) при повторном подсчете итоговых отчетов.

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

Изменение остальных таблиц данных с явным значением "THERE_IS_NO_ZIP_CODE" 0 и значением специальной записи в таблице ZipCodePrefix, представляющей это значение, является неправильным - это отношение действительно равно NULL.

Ответы

CREATE TABLE IF NOT EXISTS bar (
    id INT PRIMARY KEY AUTO_INCREMENT,
    datebin DATE NOT NULL,
    baz1_id INT DEFAULT NULL,
    vbaz1_id INT AS (COALESCE(baz1_id, -1)) STORED,
    baz2_id INT DEFAULT NULL,
    vbaz2_id INT AS (COALESCE(baz2_id, -1)) STORED,
    blam DOUBLE NOT NULL,
    UNIQUE(datebin, vbaz1_id, vbaz2_id)
);

INSERT INTO bar (datebin, baz1_id, baz2_id, blam)
    VALUES ('2016-06-01', null, null, 777)
ON DUPLICATE KEY UPDATE
    blam = VALUES(blam);