Как одним запросом сделать проверка на наличие записи перед insert по четырем полям?

Question

[Виктор Серобаба]

Всем привет.
Подскажите как можно сформировать запрос так, чтобы insert отправлялась одним запросом.
Есть таблица :
CREATE TABLE IF NOT EXISTS `wp_views` (
`post_id` bigint(20) unsigned NOT NULL,
`user_id` bigint(20) unsigned DEFAULT NULL,
`ip_address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`session_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
KEY `fk_posts_id` (`post_id`),
KEY `fk_users_id` (`user_id`),
KEY `k_ip` (`ip_address`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Нужно вставить новую запись так чтобы было учтено условие:
не было повтора post_id, не было повтора user_id , не было повтора ip_address и не было повтора session_id в течении одного дня. То есть если сегодня запись для post_id=1,user_id=1,ip_address='0.0.0.1',session_id='dsfsdfsdfsffsdfs' уже была добавлена то больше добавить сегодня было ничего нельзя. А завтра снова можно было бы добавить.
Решение запроса через php нельзя: поступает огромное количество запросов (больше 20 в секунду) и если разбивать на два то появляются дубликаты.

Comments

[Евгений Вольф]

Ну так на вскидку, нужно иметь таблицу с примерно следующими полями:
post_id, user_id, ip_address, session_id, date - где первые 4 поля Вы озвучили сами, а 5-е -- это поле с датой, в которой хранится сегодняшний день соотв. (т.е. дата создания записи). И на всех этих 5-и полях висит индекс UNIQUE. В этом случае, сервер не позволит продублировать запись.

[Виктор Серобаба]

Евгений Вольф: не подходит вариант. UNIQUE для post_id не может быть так как могут быть спокойно дублированные данные. Это счетчик уникальный посетителей страницы - поэтому post_id=1 может быть в таблице несколько раз , user_id=1 тоже несколько раз и ip_address тоже может быть один и тот же несколько раз. Саму таблицу я уже скинул и там уже есть поля и уже есть пара милионов записей ( сайт рабочий и живой. При увеличении нагрузки начал лажать)

[Максим Федоров]

Виктор Серобаба: Евгений имеет ввиду сделать составной индекс на 5 полей а не на одно поле

[Виктор Серобаба]

Максим Федоров: составной пробовал - не пролез. Выдает что"слишком большая длина ключа"

[ThunderCat]

делаешь ключ составным из полей post_id, user_id, ip_address, session_id, created_date, делаешь уникальным, и при вставке делаешь инсерт ор апдейт он дупликэйт. Немного черезпопно, но задачу вроде решает

[Rsa97]

Виктор Серобаба: Логично, что большие. Когда вы в последний раз видели IP-адрес, длиной 255 символов, да ещё и в utf8mb4 (то есть 1021 байт)? Даже для IPV6, при правильном хранении, достаточно 16 байт, а для IPV4 - 4 байт.
А id сессии у вас тоже содержит 255 символов? И тоже в универсальной кодировке?

[Виктор Серобаба]

Rsa97: Для правильного хранения IP прийдется переделывать очень много кода php , причем его писал не и там сильно прямо наворотили . А вот уменьшить в символах думаю можно но всеравно не пролазит
ThunderCat: пока тоже рассматривается такой вариант , но придется таблицу "перешерстить" и в кодах учитывать это новое поле .

[Rsa97]

В любом случае можно сократить для IPV6 до 39 байт (VARCHAR(39) CHARACTER SET latin1), для IPV4 до 15 байт (VARCHAR(15) CHARACTER SET latin1).

[vyrkmod]

Rsa97: присоединяюсь. Хотя поле в индексе можно запросто делать короче индексируемого.

[Виктор Серобаба]

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

[Rsa97]

Подумайте, какие именно вам нужны ключи. Хотите ли вы считать уникальным посещением заход с разных IP но с одний сессией? А с разных IP, но с одним user_id? Когда определитесь, что будет считаться уникальным, создавайте соответствующие ключи. Возможно предварительно необходимо будет почистить базу от уже имеющихся дублей.

Answer 1

[Виктор Серобаба]

Всем спасибо. Учел все предложения. Задача решена.
1. Преобразовал поля ip_address (varchar(15)) и session_id (varchar(50))
2. Добавил поле date_day (date)
3. Запустил UPDATE `wp_views` SET `date_day` = DATE(`created_date`)
4. Создал сложный уникальный ключ post_id-user_id-date_day-ip_address-session_id
И все заработало

Answer 2

[vyrkmod]

Вариант: добавить поле DATE куда будет писаться текущий день и собрать уникальный индекс из этого и четырёх полей из задачи. Условие будет выполняться железно. Только вот пользователям будут показываться какие-то ошибки базы данных, а за это можно и канделябром...
Решение правильное: в php перед записью проверять наличие сегодняшнего поста и нормально описывать пользователю почему у него что-то не получилось. И хайлоад тут не проблема.

Comments

[Виктор Серобаба]

как раз так и пробую сейчас. На эксперементальной таблице таблице ключ создался . Теперь всю таблицу залью и буду проверять что она выдаст

Answer 3

[romy4]

Да, как сказал vyrkmod добавить поле Date и уникальный ключ на все 4 поля. Но нет никакой проблемы нормально обрабатывать ошибки mysql и увидев ошибку duplicate key делать что-то другое по логике.
Решение проверять допзапросом в php. ну такой себе вариант.

Answer 4

[ThunderCat]

делаешь ключ составным из полей post_id, user_id, ip_address, session_id, created_date, делаешь уникальным, и при вставке делаешь инсерт ор апдейт он дупликэйт. Немного черезпопно, но задачу вроде решает.

пока тоже рассматривается такой вариант , но придется таблицу "перешерстить" и в кодах учитывать это новое поле.

Если уже тормозит база/код, все - без рефакторинга в любом случае не обойтись, надо брать и делать.

Как вариант: поставить редис и хранить "внутридневной" ключ в нем, например хеш от этих значений, с экспайром до конца дня. При наличии ключа ничего не делать, сократится число запросов в бд, а редис отрабатывает практически моментально и уж точно гораздо быстрее дб.