27 июля 2009 г. MySQL

Не возможно использовать для обновления таблицу, в которой производишь выборку

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

Вот примеры запросов

Пробуем в лоб:

update `categories` set `size` = (`count` / ((max(`count`) - min(`count`)) / 10));
ERROR 1111 (HY000): Invalid use of group function

Попробуем вложенный селект:

update `categories` set `size` = (select (`count` / ((max(`count`) - min(`count`)) / 10)) from `categories`);
ERROR 1093 (HY000): You can't specify target table 'categories' for update in FROM clause

Попробуем жоин:

update `categories` as `c1` 
JOIN `categories` as `c2` 
using(`category_id`) 
set `c1`.`size` = (`c2`.`count` / ((max(`c2`.`count`) - min(`c2`.`count`)) / 10));

ERROR 1111 (HY000): Invalid use of group function

По отдельности все работает

Выводим "размер шрифта":

select (`count` / ((max(`count`) - min(`count`)) / 10)) from `categories`;
+--------------------------------------------------------+
| (`count` / ((max(`count`) - min(`count`)) / 10))     |
+--------------------------------------------------------+
|                                             3.47826087 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Обновляем поле с размером шрифта:

update `categories` set `size` = 3.47826087;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 21  Changed: 0  Warnings: 0

Решение

Погуглив, и поломав голову с disc'ом, я пришел к следующему решению, представленное ниже.

Я решил выделить вычисление процента в переменную @percent, далее создал вьюху для таблицы "categories" и жойню таблицу с вьюхой:

-- создаем коэффициент деления
set @percent = (select (max(`count`) - min(`count`)) / 10 from `categories`);

-- создаем вьюху
create view `categories_view` as select `category_id`, `count` from `categories`;

-- жойним таблицу и вьюху, обновляя данные
update `categories` as `c`
join `categories_view` as `cv`
using(`category_id`)
set `c`.`size` = `cv`.`count` / @percent;

Вот и все, приятного манокурения :)

UPD: Создадим процедуру и евент для этого события

/* Создаем вьюху и процедуру для установки размеров шрифта */
use kinsburg;

/* создаем вьюху */
CREATE VIEW `categories_view` AS SELECT `category_id`, `count` FROM `categories`;

/* создаем процедуру */
delimiter //
DROP PROCEDURE IF EXISTS `updateCategorySize`//
CREATE PROCEDURE `updateCategorySize` ()
BEGIN
    /* создаем коэффициент деления */
    SET @percent = (SELECT (max(`count`) - min(`count`)) / 10 FROM `categories`);
    /* жойним таблицу и вьюху, обновляя данные */
    UPDATE `categories` AS `c` JOIN `categories_view` AS `cv` USING(`category_id`) SET `c`.`size` = `cv`.`count` / @percent;
END//
delimiter ;

/* создаем евент для вызова процедуры раз в сутки */
CREATE
    DEFINER = kinsburg@localhost
    EVENT `updateCategorySizeEvent`
    ON SCHEDULE
      EVERY 1 DAY
    DO
      CALL updateCategorySize;

Adw0rd, Насколько я понимаю что после выполнения данной процедуры, что после желаемого изначально UPDATE, у нас будет стопицот категорий, с одним и тем же значением size.
Внимание вопрос: "Зачем хранить одно и то же значение в 9342423 экземплярах? Как же нормализация?"

ИМХО: по крону раз в те-же сутки меняем значение ".categories_cloud {
font-size:...." в CSS файле и не усложняем зря жизнь себе и БД :)

Ну а так да, вполне себе приятное решение.

adw0rd 28 июля 2009 г. 12:36

Насколько я понимаю что после выполнения данной процедуры, что после желаемого изначально UPDATE, у нас будет стопицот категорий, с одним и тем же значением `size`

Нет, не будет, я же проверяю сначала, прежде чем публиковать. ;)

adw0rd 28 июля 2009 г. 12:40

Зачем хранить одно и то же значение в 9342423 экземплярах? Как же нормализация?

вот что получается


+-------------+-------------+-------+------+
| category_id | url         | count | size |
+-------------+-------------+-------+------+
|           1 | action      |     8 |    3 |
|           2 | serial      |    17 |    7 |
|           3 | documentary |     0 |    0 |
|           4 | cartoon     |    10 |    4 |
|           5 | western     |     0 |    0 |
|           6 | detective   |     3 |    1 |
|           7 | drama       |    16 |    7 |
|           8 | historical  |     7 |    3 |
|           9 | comedy      |    23 |   10 |
|          10 | comics      |     0 |    0 |
|          11 | crime       |     4 |    2 |
|          12 | romance     |     6 |    3 |
|          13 | mistic      |     0 |    0 |
|          14 | parody      |     0 |    0 |
|          15 | adventure   |     8 |    3 |
|          16 | sport       |     0 |    0 |
|          17 | thriller    |     9 |    4 |
|          18 | horror      |     2 |    1 |
|          19 | fantastic   |     5 |    2 |
|          52 | fantasy     |     2 |    1 |
+-------------+-------------+-------+------+

Никаких "9342423 экземплярах" ;)

adw0rd 28 июля 2009 г. 12:42

И вот такое облако получаем:
cloud

dallone 28 июля 2009 г. 21:08

Нет, не будет, я же проверяю сначала, прежде чем публиковать. ;)

Хм... Просто запрос:

SELECT (`count` / ((max(`count`) - min(`count`)) / 10)) FROM `categories`;

При данных:


CREATE TABLE `categories` (
  `id` int(11) NOT NULL auto_increment,
  `size` int(11) NOT NULL default 0,
  `count` int(11) NOT NULL default 0,
  PRIMARY KEY  (`id`),
  KEY `count` (`count`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO `categories` (`id`, `count`) VALUES
(1, 5),
(2, 12),
(3, 18),
(8, 5),
(11, 5),
(12, 12),
(13, 18),
(18, 5),
(19, 2);

У меня лично выдает только одно значение - 1.2500(Как я понимаю для последнего столбца с id 19 (MySQL5). Или я что-то недопонимаю?

ИМХО все равно данные размера как таковые к категориям особого отношения не имеют, они грубо говоря связь облака тегов с категориями, да и юзаются они ведь только в нем, не так ли?

Поэтому можно было бы сделать таблицу:


CREATE TABLE `tag_cloud_size`(
`category_id` INT NOT NULL,
`size` INT NOT NULL DEFAULT 0,
PRIMARY KEY(`category_id`)
);

Ну и в ней собственно все их(значения) хранить.

adw0rd 28 июля 2009 г. 21:47

SELECT (`count` / ((max(`count`) - min(`count`)) / 10)) FROM `categories`;

да, именно этот запрос сделает всем строкам один результат, но этот запрос только для теста, посмотри внимательно процедуру, там этот кусок кода:

(max(`count`) - min(`count`)) / 10)

вынесен в отдельную переменную @percent и все работает наура, для всех строк

adw0rd 28 июля 2009 г. 21:49

ИМХО все равно данные размера как таковые к категориям особого отношения не имеют, они грубо говоря связь облака тегов с категориями, да и юзаются они ведь только в нем, не так ли?

Не согласен, зачем мне делать доп. таблицу, если эти данные необходимы для генерации облака (титл, урл, размер)?

Я считаю это лишним.

adw0rd 28 июля 2009 г. 21:50

Поэтому можно было бы сделать таблицу:
CREATE TABLE `tag_cloud_size`(
`category_id` INT NOT NULL,
`size` INT NOT NULL DEFAULT 0,
PRIMARY KEY(`category_id`)
);
Ну и в ней собственно все их(значения) хранить.

С тем же успехом можно было отделить "count" в другую таблицу.

dallone 28 июля 2009 г. 22:22

count имеет отношение к категориям,
size имеет отношение исключительно к облакам и нефиг ему делать в основной таблице.

Впрочем на вкус и цвет товарищей нет. Проехали :)

вынесен в отдельную переменную @percent и все работает наура, для всех строк

Ну да я это понял, просто меня переклинило на первом запросе, стормозил признаю.

P.S.
Впринципе вполне логично, что они гененрят только одну строку ибо MAX, MIN это же агрегатные функции GROUP BY.
Можно было бы заменить аццким запросом:


SELECT `count`
        /
        (
          (SELECT MIN(`count`) FROM `categories`)
          -
          (SELECT MIN(`count`) FROM `categories`)
        / 10
        )

FROM `categories`;

Твой вариант лучше безусловно прозрачнее и быстрее, мне просто занятся нечем пока футурама скачивается.

adw0rd 28 июля 2009 г. 22:29

`size` имеет отношение исключительно к облакам и нефиг ему делать в основной таблице.

Ну смотри, для генерации облака нам нужно помимо "размера" еще "имя жанра" и "ссылка, либо ид жанра", так?

Тогда логично чтобы они были в одной таблице, чтобы не делать лишнего жойна, так?

Тогда куда мне всунуть "размер"? Или ты мне предлагаешь одинаковые данные хранить в двух таблицах?

футурама скачивается.

Скоро выложим на кинсбурге, заходи ;)

Ad1ce 29 июля 2009 г. 1:51

Движок Кинсбурга самописный?
Чем оформляется подсветка синтаксиса кода на этом блоге?

adw0rd 29 июля 2009 г. 1:58

Ad1ce,

Да, самописный
Плагин Codecolorer

nistelroi 14 июня 2015 г. 22:03

Вообще есть два способа решения без процедур, вьюх и тому подобных извращений
http://ramzes.ws/blog/cant-specify-target-table-for-update-in-from-clause

adw0rd 14 июня 2015 г. 22:19

Вы про вариант с JOIN? В этой статье он тоже использовался и неудачно, возможно потому что статье 6 лет

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