Как сделать on duplicate key "для нескольких ключей"?

Question

[Игорь Самохин]

Здравствуйте,

CREATE TABLE `user_notification` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(10) unsigned NOT NULL,
  `article_id` int(10) unsigned default NULL,
  `magazine_id` int(10) unsigned default NULL,
  `user_page_id` int(10) unsigned default NULL,
  `date_time` datetime NOT NULL,
  `unix_date` int(10) unsigned NOT NULL,
  `comment_id` int(10) unsigned NOT NULL,
  `is_subscribed` enum('1','0') collate utf8_unicode_ci NOT NULL,
  `status` enum('send','not_send') collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `user_id` (`user_id`,`article_id`,`magazine_id`,`user_page_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

Как мне сделать чтобы update был только если попадаются одинаковые значения в колонках: `user_id`,`article_id`,`magazine_id`,`user_page_id`


например уже есть эти поля со значениями:


1, 2, NULL, NULL


и я делаю

INSERT INTO `user_notification`
	(`user_id`,`article_id`,`magazine_id`,`user_page_id`,`date_time`,`unix_date`,`comment_id`,`is_subscribed`,`status`)
	values 
	(1,2,NULL,NULL,NOW(),UNIX_TIMESTAMP(),3,'1','not_send') 
	ON DUPLICATE KEY UPDATE
	`date_time` = NOW()
	, unix_date = UNIX_TIMESTAMP()
	, comment_id = 4

и выполнится в запросе должна ветка update


а если хотя бы в одном из этих полей есть различия, то выполнился бы insert…

Answer 1

[sdevalex]

Создайте уникальный индекс для 4 полей…

alter table user_notification add unique index(`user_id`,`article_id`,`magazine_id`,`user_page_id`);

Comments

[sdevalex]

Одновременно для четырех полей, так можно. Запрос выше как раз это делает.

[Игорь Самохин]

я так и сделал в итоге: UNIQUE KEY `user_id` (`user_id`,`article_id`,`magazine_id`,`user_page_id`)

только auto_increment для id пришлось убрать ( если оставить, то нужен будет PK для id — но тогда мой запрос правильно не работает )

это нормально, что пришлось убрать auto_increment? с ним нужного мне результата никак не достигнуть?

[BoShurik]

Только что проверил, запрос с ON DUPLICATE KEY работает на такой таблице:

CREATE TABLE `user_notification` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `article_id` int(10) unsigned NOT NULL DEFAULT '0',
  `magazine_id` int(10) unsigned NOT NULL DEFAULT '0',
  `user_page_id` int(10) unsigned NOT NULL DEFAULT '0',
  `date_time` datetime NOT NULL,
  `unix_date` int(10) unsigned NOT NULL,
  `comment_id` int(10) unsigned NOT NULL,
  `is_subscribed` enum('1','0') COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('send','not_send') COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`article_id`,`magazine_id`,`user_page_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

[Игорь Самохин]

да, работает,

самое интересное что если у полей `article_id`,`magazine_id`,`user_page_id` — сделать по умолчанию не 0 а NULL — тогда не работает…

Answer 2

[ivnik]

Насколько я знаю ON DUPLICATE KEY UPDATE работает только с PRIMARY KEY. Т.е. чтобы использовать эту возможность вам придётся все эти поля занести в PK.

Comments

[Игорь Самохин]

Сделал как вы сказали — эти поля добавил в primary key, выполнил

INSERT INTO `user_notification`
(`user_id`
,`article_id`
,`magazine_id`
,`user_page_id`
,`date_time`
,`unix_date`
,`comment_id`
,`is_subscribed`
,`status`)
values
(1,2,0,0,NOW(),UNIX_TIMESTAMP(),3,'1','not_send')
ON DUPLICATE KEY UPDATE
`date_time` = NOW()
, unix_date = UNIX_TIMESTAMP()
, comment_id = 4

и у меня появилась запись с comment_id = 3, а должен был быть update, так как в таблице уже была запись с такими значениями для полей: 1,2,0,0 ( `user_id`,`article_id`,`magazine_id`,`user_page_id` )

[ivnik]

покажите show create table user_notification

[ivnik]

вероятно у вас PK включает не только эти четыре поля, а ещё какое-то поле?

[Игорь Самохин]

CREATE TABLE `user_notification` (
`id` int(10) unsigned NOT NULL auto_increment,
`user_id` int(10) unsigned NOT NULL default '0',
`article_id` int(10) unsigned NOT NULL default '0',
`magazine_id` int(10) unsigned NOT NULL default '0',
`user_page_id` int(10) unsigned NOT NULL default '0',
`date_time` datetime NOT NULL,
`unix_date` int(10) unsigned NOT NULL,
`comment_id` int(10) unsigned NOT NULL,
`is_subscribed` enum('1','0') collate utf8_unicode_ci NOT NULL,
`status` enum('send','not_send') collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`,`user_id`,`article_id`,`magazine_id`,`user_page_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6;

[ivnik]

Я ошибся, утверждая что требуется PK:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

Достаточно уникального индекса.

[Игорь Самохин]

спасибо за ваш труд

[Игорь Самохин]

так я прав что auto_increment придется убить? Не сталкивались? Интересно тогда почему у меня работает update для такой таблицы:

CREATE TABLE `users_save_auth` (
`id` int(10) unsigned NOT NULL auto_increment,
`user_id` int(10) unsigned NOT NULL,
`auth_key` varchar(32) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PACK_KEYS=0 AUTO_INCREMENT=1;

INSERT INTO `users_save_auth`
(`user_id`,`auth_key`)
values
(1, 'asdasdasdasd')
ON DUPLICATE KEY UPDATE
`auth_key` = values(`auth_key`)

по идее должен из за того что id это PK — не делать Update а делать INSERT…

Answer 3

[Игорь Самохин]

ужас, всё поехало.., извиняюсь…

Answer 4

[ivnik]

Нужно из PK удалить поле id, т.е. чтобы ON DUPLICATE KEY UPDATE сработал должен произойти конфликт PK.

Comments

[ivnik]

Не туда нажал…

[Игорь Самохин]

Не могу удалить PK на id — он у меня auto_increment

я пробовал делать unique на `user_id`,`article_id`,`magazine_id`,`user_page_id` — у меня работало но только если конфликт был хотя бы у одного, а мне надо чтобы был update только если сразу конфликт у всех этих полей

даже если я уберу auto_increment у id — и у остальных оставлю PK — факт конфликт будет будет когда хотя бы одно поле конфликтно? Мне то нужно чтобы факт конфликта был только если все эти поля конфликтны…

[Игорь Самохин]

Сделал, но пришлось пожертвовать auto_increment. Без этого никак? Вручную высчитывать id еще одним запросом выбирая последний?

[BoShurik]

Уникальный индекс делали для каждого поля отдельно или для всех вместе?

ALTER TABLE `user_notification` ADD UNIQUE (`unique_index` ,
`user_id` ,
`article_id`,
`magazine_id`,
`user_page_id`  
);

[BoShurik]

"`unique_index` ," — лишнее*

[Игорь Самохин]

Уникальный сделал для всех 4-ех. А какие различия будут в работе при обоих вариантах?

[BoShurik]

Если сделать отдельно, то работать не будет, т.к. можно будет хранить только одну запись для каждого user_id, например. Я просто уточнял.