Нумерация внутри групп при выборке в MySQL

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

Решение:

SET @curRow := 1, @marker := '';
SELECT *,
    CASE t.field
      WHEN @marker
      THEN @curRow := @curRow + 1
      ELSE @curRow := 1 AND @marker := t.field
    END AS range
FROM sometable t
ORDER BY t.field;

Идея в использовании переменных пользователя. Мы определяем номер внутри группы (@curRow) и некоторый маркер (@marker). В самом запросе сравниваем значение поля, определяющего группу (идентификатор класса, описи, корзины), с текущим значением переменной. Если они равны, то это значит, что группа продолжается и номер @curRow становится больше на единицу, в противном случае номер сбрасывается на значение по умолчанию, а маркер @marker становится равным значению поля, определяющего группу. При этом в поле range попадает искомый номер в последовательности внутри группы.

Важно при этом не забыть добавить сортировку по полю, определяющему группу.

Поиск по композитным ключам в MySQL

Предикат IN в MySQL позволяет проверять не только вхождение значения одного поля в список, но и вхождения значений нескольких полей одновременно:

SELECT * 
  FROM foo
 WHERE (bar, baz) IN ((1, 2), (3, 4), (5, 6));

Это может быть особенно полезно, когда первичный ключ — композитный.

Один запрос на обновление вместо нескольких в MySQL

Один запрос к базе данных вместо нескольких — это хорошо. Поэтому рано или поздно возникает вопрос, как оптимизировать простыню запросов, подобную этой:

UPDATE forms SET stem='биогенн', suffix='ый' WHERE id = 123480;
UPDATE forms SET stem='биогенн', suffix='ого' WHERE id = 123481;
UPDATE forms SET stem='биогенн', suffix='ому' WHERE id = 123482;
UPDATE forms SET stem='биогенн', suffix='ого' WHERE id = 123483;
UPDATE forms SET stem='биогенн', suffix='ый' WHERE id = 123484;
UPDATE forms SET stem='биогенн', suffix='ым' WHERE id = 123485;
UPDATE forms SET stem='биогенн', suffix='ом' WHERE id = 123486;
UPDATE forms SET stem='биогенн', suffix='ая' WHERE id = 123487;
UPDATE forms SET stem='биогенн', suffix='ой' WHERE id = 123488;
UPDATE forms SET stem='биогенн', suffix='ой' WHERE id = 123489;
UPDATE forms SET stem='биогенн', suffix='ую' WHERE id = 123490;
UPDATE forms SET stem='биогенн', suffix='ой' WHERE id = 123491;
UPDATE forms SET stem='биогенн', suffix='ою' WHERE id = 123492;
UPDATE forms SET stem='биогенн', suffix='ой' WHERE id = 123493;
UPDATE forms SET stem='биогенн', suffix='ое' WHERE id = 123494;
UPDATE forms SET stem='биогенн', suffix='ого' WHERE id = 123495;
UPDATE forms SET stem='биогенн', suffix='ому' WHERE id = 123496;
UPDATE forms SET stem='биогенн', suffix='ое' WHERE id = 123497;
UPDATE forms SET stem='биогенн', suffix='ым' WHERE id = 123498;
UPDATE forms SET stem='биогенн', suffix='ом' WHERE id = 123499;
UPDATE forms SET stem='биогенн', suffix='ые' WHERE id = 123500;
UPDATE forms SET stem='биогенн', suffix='ых' WHERE id = 123501;
UPDATE forms SET stem='биогенн', suffix='ым' WHERE id = 123502;
UPDATE forms SET stem='биогенн', suffix='ые' WHERE id = 123503;
UPDATE forms SET stem='биогенн', suffix='ых' WHERE id = 123504;
UPDATE forms SET stem='биогенн', suffix='ыми' WHERE id = 123505;
UPDATE forms SET stem='биогенн', suffix='ых' WHERE id = 123506;

Запросы однотипные, обновляем значение полей stem и suffix. Id — первичный ключ. Конечно, в рабочей базе поля stem быть не должно: очевидная избыточность данных, но нам оно нужно для демонстрации. Чисто в учебно-педагогических целях.

Теперь используем MySQL-функцию INSERT … ON DUPLICATE KEY UPDATE, которая по своему функционалу эквивалента оракловому UPSERT — вставляет несуществующие и обновляет существующие строки в таблице в рамках одного (!) запроса.

INSERT INTO forms(id,stem,suffix) 
     VALUES (123480,'биогенн','ый'),
            (123481,'биогенн','ого'),
            (123482,'биогенн','ому'),
            (123483,'биогенн','ого'),
            (123484,'биогенн','ый'),
            (123485,'биогенн','ым'),
            (123486,'биогенн','ом'),
            (123487,'биогенн','ая'),
            (123488,'биогенн','ой'),
            (123489,'биогенн','ой'),
            (123490,'биогенн','ую'),
            (123491,'биогенн','ой'),
            (123492,'биогенн','ою'),
            (123493,'биогенн','ой'),
            (123494,'биогенн','ое'),
            (123495,'биогенн','ого'),
            (123496,'биогенн','ому'),
            (123497,'биогенн','ое'),
            (123498,'биогенн','ым'),
            (123499,'биогенн','ом'),
            (123500,'биогенн','ые'),
            (123501,'биогенн','ых'),
            (123502,'биогенн','ым'),
            (123503,'биогенн','ые'),
            (123504,'биогенн','ых'),
            (123505,'биогенн','ыми'),
            (123506,'биогенн','ых') 
ON DUPLICATE KEY UPDATE stem=VALUES(stem), suffix=VALUES(suffix);

В итоге имеем 1 запрос вместо 27 запросов, приведенных выше.

Аналоги в некоторых других СУБД + SQL:2008 стандарт.

Партиционирование по списку в MySQL

Партиционирование (секционирование) данных — это реально крутая штука при больших объемах данных. Вот рабочий пример. Допустим, у нас есть два списка: грамматические категории и словоформы. Первому списку соответствует таблица grammar, второму — forms. В таблицах 65 и 4058805 записей соответственно. Отношение между таблицами — многие-ко-многим, роль вспомогательной таблицы выполняет таблица morphology. В ней 23044224 записей. Её-то мы и будем партиционировать.

Продолжить чтение →