MySQL. InnoDB. Непонятное поведение INSERT ODKU.

Nexus_Noob · 19 Май 2017

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

Есть таблица:

PHP:

CREATE TABLE IF NOT EXISTS `webstore_products` (

  `id` int(11) NOT NULL,
  `sort` int(12) DEFAULT '0',
  `category_id` int(11) NOT NULL DEFAULT '0',
  `image` varchar(512) NOT NULL,
  `title` varchar(512) NOT NULL,
  `url` varchar(512) NOT NULL,
  `image_alt` varchar(512) NOT NULL,
  `subcategories` text NOT NULL,
  `analogues_products` text NOT NULL,
  `gift` varchar(256) NOT NULL,
  `discount` varchar(512) NOT NULL,
  `description` text NOT NULL,
  `activation` tinyint(1) NOT NULL DEFAULT '0',
  `vendore_code` varchar(512) NOT NULL,
  `amount` varchar(512) NOT NULL,
  `tag` int(11) NOT NULL DEFAULT '0',
  `show_tag` tinyint(1) NOT NULL DEFAULT '0',
  `price` varchar(512) NOT NULL,
  `min_price` varchar(512) NOT NULL,
  `youtube_link` varchar(512) NOT NULL,
  `commerceml_id` varchar(255) DEFAULT NULL,
  `create_date` int(11) NOT NULL DEFAULT '0',
  `in_stock_date` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=997 DEFAULT CHARSET=utf8;

INSERT INTO `webstore_products` (`id`, `sort`, `category_id`, `image`, `title`, `url`, `image_alt`, `subcategories`, `analogues_products`, `gift`, `discount`, `description`, `activation`, `vendore_code`, `amount`, `tag`, `show_tag`, `price`, `min_price`, `youtube_link`, `commerceml_id`, `create_date`, `in_stock_date`) VALUES (996, 996, 27, 'img.jpg', 'title', 'url', '', '', '', '', '', 'descr', 1, '', '7', 1, 0, '60', '0', '', 'IK8TYO08i9PQGvFGTeX7P3', 1490017800, 1495141310);

ALTER TABLE `webstore_products`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `commerceml_id` (`commerceml_id`),
  ADD KEY `sort` (`sort`),
  ADD KEY `activation` (`activation`,`amount`(255),`tag`,`in_stock_date`);

Единственное поле, которое может содержать «NULL» это «commerceml_id».

Запрос устанавливает остаток товара в 0, хотя должен устанавливать в 6.

PHP:

INSERT INTO webstore_products (id) VALUES ('996') ON DUPLICATE KEY UPDATE amount=CASE WHEN id=996 THEN amount=6 ELSE VALUES(amount) END

Подскажите в чем может быть причина такого поведения?

antson · 19 Май 2017

эта часть INSERT INTO webstore_products (id,amount) VALUES ('996',123) должна полностью вставлять запись со всеми полями - как понимаю, импортируешь прайс ..
в случае если товар уже есть обновляется только часть полей
UPDATE amount=123
изврат с case when не понял зачем.

fixxxer · 19 Май 2017

-CASE WHEN id=996
+CASE WHEN VALUES(id)=996

Хотя смысла при единственном значении в values я не улавливаю.
Вот если бы было insert-select, тогда еще понятно.

Nexus_Noob · 19 Май 2017

antson написал(а):
эта часть INSERT INTO webstore_products (id,amount) VALUES ('996',123) должна полностью вставлять запись со всеми полями - как понимаю, импортируешь прайс ..
в случае если товар уже есть обновляется только часть полей
UPDATE amount=123
изврат с case when не понял зачем.

На форуме опубликовал не весь запрос, а только его часть.
Таким образом вставляется/обновляется не 1 запись, а сразу несколько.

Попробовал изменить запрос, результат не изменился, amount упал в ноль.

PHP:

INSERT INTO webstore_products (id,amount) VALUES ('996',6) ON DUPLICATE KEY UPDATE amount=CASE WHEN id=996 THEN amount=6 ELSE VALUES(amount) END

Nexus_Noob · 19 Май 2017

fixxxer написал(а):
-CASE WHEN id=996
+CASE WHEN VALUES(id)=996

Тоже самое, amount стал равен нулю.

PHP:

INSERT INTO webstore_products (id,amount) VALUES ('996',6) ON DUPLICATE KEY UPDATE amount=CASE WHEN VALUES(id)=996 THEN amount=6 ELSE VALUES(amount) END

Так amount=1, должен быть равен 6.

PHP:

INSERT INTO webstore_products (id,amount) VALUES ('996',6) ON DUPLICATE KEY UPDATE amount=CASE WHEN VALUES(id)=996 THEN VALUES(amount)=6 ELSE VALUES(amount) END

Можете объяснить или поделиться ссылкой, если не сложно, почему стоит использовать «VALUES(field)» вместо «field»?

fixxxer · 19 Май 2017

PHP:

INSERT INTO webstore_products (id,amount) VALUES ('996',6) ON DUPLICATE KEY UPDATE amount=CASE WHEN VALUES(id)=996 THEN 6 ELSE VALUES(amount) END

Так должно работать. Правда, так будет 6 в любом случае.

VALUES(field) в ODKU ссылается на значение из VALUES из попытки insert-а, соответствующей текущему конфликту ключа.

Nexus_Noob · 19 Май 2017

fixxxer написал(а):
PHP:

INSERT INTO webstore_products (id,amount) VALUES ('996',6) ON DUPLICATE KEY UPDATE amount=CASE WHEN VALUES(id)=996 THEN amount=6 ELSE VALUES(amount) END

Так должно работать.

К сожалению для меня не работает, amount=0.

fixxxer · 19 Май 2017

PHP:

MariaDB [test]> create table webstore_products(id serial, amount integer);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> insert into webstore_products (amount) values (10), (20), (30), (40);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from webstore_products;
+----+--------+
| id | amount |
+----+--------+
|  1 |     10 |
|  2 |     20 |
|  3 |     30 |
|  4 |     40 |
+----+--------+
4 rows in set (0.00 sec)

MariaDB [test]> insert into webstore_products values (1, 60), (2, 70) on duplicate key update amount = case when values(id) = 1 then 100 else values(amount) end;
Query OK, 4 rows affected (0.00 sec)
Records: 2  Duplicates: 2  Warnings: 0

MariaDB [test]> select * from webstore_products;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  2 |     70 |
|  3 |     30 |
|  4 |     40 |
+----+--------+
4 rows in set (0.00 sec)

fixxxer · 19 Май 2017

Nexus_Noob написал(а):
К сожалению для меня не работает, amount=0.

А. Я там отредактировал. Обрати внимание, не then amount=6, а then 6.

Nexus_Noob · 19 Май 2017

fixxxer написал(а):
А. Я там отредактировал. Обрати внимание, не then amount=6, а then 6.

Теперь все верно, amount=6, огромное спасибо за помощь!

antson · 19 Май 2017

@Nexus_Noob,

Код:

'insert into ' . $this->base . ' (' . $keys . ') values ';

потом перечисляешь кучу  по полному списку ключей
(знач,знач,знач) , (знач,знач,знач) , (знач,знач,знач) , (знач,знач,знач) , (знач,знач,знач)

' on duplicate key update '

и для тех полей которые обновляются
поле = values('поле'),поле = values('поле'),поле = values('поле')

никаких кейсов с ид не нужно
поле = values('поле') - муська найдет в (знач,знач,знач) нужное сама

Nexus_Noob · 19 Май 2017

@antson, благодарю за совет, обязательно попробую этот метод и в случае успеха буду его использовать.

antson · 19 Май 2017

Я себе в виде хелпера оформил

Код:

            $mass = new DbMass($BD, 'price');
            foreach ($import as $item) {
                $mass->updateDelay($item); // $item - assoc array
            }
            $mass->updateDelayFix();

внутри собирается запрос, и как только достигается ограничение на длину запроса он выполняется.
а фикс выталкивает остатки данных.

тут динамически только (....),(....)
а начало и конец запроса строятся один раз при первом присвоении.
массив с ключами - именами полей . Кол-во элементов должно быть
одно и тоже при всех вызовах.

Yoskaldyr · 19 Май 2017

@antson, А какая база и тип таблиц? Просто в том же mysql+innodb пачка отдельных отдельных однотипных апдейтов в одной транзакции быстрее одного большого запроса из склееных апдейтов.

antson · 19 Май 2017

это очень старый функционал. времен myisam.
работает и ладно. пару месяцев назад профилировал 17 тысяч инсертов - превратились в 6 скл запросов. и выполнены были за 3 секунды

Yoskaldyr · 19 Май 2017

antson написал(а):
это очень старый функционал. времен myisam.

тогда да. объединение запросов в один - это один из немногих вариантов.
Я думал может особенности какого-то нового движка или базы.

fixxxer · 20 Май 2017

Честно говоря, единственный смысл в конструкции ODKU...VALUES()... я вижу только в комбинации INSERT ... SELECT ... ON DUPLICATE KEY UPDATE (например, когда есть пары записей "черновик" и "опубликованное", и надо опубликовать все черновики, замещая возможно существующие опубликованные записи).

Когда источник данных из кода, намного проще делать в транзакции, чем городить вот это все.

AnrDaemon · 20 Май 2017

fixxxer написал(а):
Когда источник данных из кода, намного проще делать в транзакции, чем городить вот это все.

А ещё добавлю, что перенос бизнес-логики в БД выглядит очень сомнительно.

MySQL. InnoDB. Непонятное поведение INSERT ODKU.

Nexus_Noob

Новичок

antson

Новичок

fixxxer

К.О.

Nexus_Noob

Новичок

Nexus_Noob

Новичок

fixxxer

К.О.

Nexus_Noob

Новичок

fixxxer

К.О.

fixxxer

К.О.

Nexus_Noob

Новичок

antson

Новичок

Nexus_Noob

Новичок

antson

Новичок

Yoskaldyr

"Спамер"

antson

Новичок

Yoskaldyr

"Спамер"

fixxxer

К.О.

AnrDaemon

Продвинутый новичок