Несколько интересных приемов и особенностей работы с MySQL

Я думаю, что в процессе изучения той или иной СУБД каждый из вас не раз изобретал велосипеды для решения своих задач, не зная о существовании той или иной функции или приема, которые бы могли в разы ускорить выполнение запросов и уменьшить объем кода. В данной статье я хочу поделиться с вами своим опытом работы с очень «добрым» и «отзывчивым» MySQL, часто позволяющему программисту делать вещи, которые другие СУБД переварить бы не смогли. Материал будет полезен скорее тем, кто только решил углубиться в чудесный мир запросов, но возможно и опытные программисты найдут тут что-то интересное.

Удаление дубликатов

Очень часто на различных специализированных ресурсах мне встречались вопросы о том, как быстрее и оптимальнее избавиться от дублирующихся записей в таблице. Сразу же в голову приходит то, что нужно создать еще одну таблицу, идентичную данной, создать в ней уникальный ключ и скопировать в нее данные из исходной таблицы, скажем, с помощью INSERT IGNORE. Но существует и более простой способ. Достаточно просто создать в таблице уникальный ключ с помощью такого вот запроса:

ALTER IGNORE TABLE table1 ADD UNIQUE (field1, field2);

После добавления ключа все дубликаты удалятся автоматически.

Преобразование строки в число

Допустим перед вами встала задача поиска в таблице адресов address дома с определенным номером. Причем номер дома хранится в текстовом поле num, содержащим значения типа '1', '1а', '1/б', '2ы', '3йцукен' и т.п. И мы хотим выбрать дома с номером, состоящим из 1 и еще каких-то символов. Думаю многие сразу кинутся искать решение с использованием LIKE или REGEXP. Но проще будет использовать следующую фичу MySQL:

SELECT *
  FROM address
  WHERE num + 0 = 1;

Встретив арифметическую операцию, MySQL автоматически приводит все аргументы к числовому типу. В случае со строками будут просто отсечены все символы, начиная с первого не числового.
Вот такой вот запрос тоже спокойно выполнится без ошибок:

SELECT '1qwe3s' + '2regt3g';

И в результате мы получим ответ: 3.

Использование переменных в запросах

Тут я сразу приведу пример решения задачи, в которой переменная облегчит нам жизнь.
Имеем следующую таблицу table1:
id sum
1 35
2 25
3 10
4 55
5 12

Нужно вывести все эти поля и добавить к ним еще 2, onStart и total.
total = summ — onStart.
onStart равен значению total из предыдущей записи, для первой записи onStart = 0.
Т.е. в итоге мы должны получить такой вот результат:
id sum onStart total
1 35 0 35
2 25 35 -10
3 10 -10 20
4 55 20 35
5 12 35 -23

Использую переменную, мы сможем при решении данной задачи избавиться от лишних JOIN'ов и подзапросов:

SELECT t1.id, t1.summ, @i AS onStart, @i := t1.summ - @i AS total
  FROM table1 t1
  JOIN (SELECT @i := 0) var;

Подсчет количества различных записей в таблице

Еще одна часто встречающаяся задача. И тут я тоже сразу приведу пример.
Дана таблица table1 (id, f1, f2). Нужно написать запрос, который бы вернул нам следующий результат:
общее количество записей количество записей с f1 = 1 сумму значений f2 для f1 = 2

Конечно можно получить результат вот так:

SELECT COUNT(1),
      (SELECT COUNT(1) FROM table1 WHERE f1 = 1),
      (SELECT SUM(f2) FROM table1  WHERE f1 = 2)
  FROM table1;

Но очевидно, что это далеко не оптимальное решение. Придется для каждой записи выполнять еще два дополнительных подзапроса. И мы сделаем по-другому:

SELECT COUNT(1),
       SUM(f1 = 1),
       SUM(IF(f1 = 2, f2, 0))
  FROM table1;

Теперь другое дело. Всё, что нам нужно, мы посчитали за один проход по таблице.

Column 'id' in group statement is ambiguous

В этой части статьи я хочу обратить ваше внимание на одну интересную особенность MySQL.
Имеем такой запрос:

SELECT t1.id, t2.id
  FROM table1 t1
  JOIN table2 t2 ON t1.id = t2.id_t1
  GROUP BY id;

Видно, что в блоке GROUP BY мы забыли указать алиас у поля id, и соответственно при попытке выполнить запрос получили ошибку «Column 'id' in group statement is ambiguous». Казалось бы всё верно. Теперь изменим этот запрос:

SELECT t1.id, t2.f1
  FROM table1 t1
  JOIN table2 t2 ON t1.id = t2.id_t1
  GROUP BY id;

Мы убрали из списка выводимых полей t2.id и, о чудо, запрос отработал, данные были сгруппированы по t1.id. Другие СУБД, такие как, например, MS SQL или PostgreSQL и во втором случае выдали бы ошибку, но для MySQL второй запрос полностью корректен.
Так что я рекомендую вам быть более внимательными и всегда использовать алиасы перед полями, иначе потом при небольшом изменении запроса можно нарваться на ошибку.

Поиск данных за последнюю дату

И напоследок хочу привести еще один пример решения одной типичной не сложной часто встречающейся задачи. Почему-то у многих она часто вызывает затруднения.
Дана таблица платежей payments (id INT, uid INT, pay_date DATETIME, amount DECIMAL(15, 2)).
id – первичный ключ
uid – идентификатор юзера
pay_date – дата платежа
amount – сумма платежа
Нужно написать запрос, который бы вывел для каждого юзера дату и сумму последнего платежа.
UPD. Считаем, что юзер не может провести больше одного платежа за секунду. (Без этого условия постановка задачи некорректна). Тип pay_date изменен с DATE на DATETIME.
Я предлагаю вам следующее стандартное решение:

SELECT p.uid, p.amount
  FROM payments p
  JOIN
    (SELECT uid, MAX(pay_date) AS max_dt
       FROM payments
       GROUP BY uid) sel ON p.uid = sel.uid AND p.pay_date = sel.max_dt;
Ads
AdBlock has stolen the banner, but banners are not teeth — they will be back

More

Comments 83

    –8
    спасибо, полезно — добавил пост в избранное
      –10
      Интересные решения. Конечно не для рабочего варианта базы, а скорее как единичные запросы запускаемые вручную.
        +5
        Мне кажется, в подсчете количества различных записей в таблице вы предлагаете не оптимальный путь, потому что в нем не будут использоваться индексы. Другой способ — попробовать разбить один запрос на три и подобрать индексы. В конкретной задаче EXPLAIN SELECT подскажет, какой из способов быстрее.
          +4
          За что минусанули человека? Мне тоже кажется, что SUM(IF(f1 = 2, f2, 0)) индексы использовать если и будет, то гораздо менее оптимально, т.к. IF — это функция, и в неё нужно будет подать каждое существующее значение f1. В то же время, SELECT SUM(f2) WHERE f1 = 2 прекрасно использует индекс по условию, а потом уже просуммирует отфильтрованные данные.
            –5
            Насколько я помню, MySQL не использует индексы в подзапросах.
              +2
              Выполните Explain и посмотрите. Зачем гадать?
            +2
            Вы правы.
            При наличии индексов у полей f1 и f2 запрос предложенный автором в реальности (за исключением специально подогнанных случаев) будет работать заметно медленнее, чем запрос, который автор называет не оптимальным.
              –4
              Запрос с функциями также будет использовать индексы, и не обратится к основной таблице, если в селект не добавить других полей, а ключик сделать составным по (f1,f2). Почему нет?
                0
                Одно дело index seek, совсем другое — index scan.
                  –2
                  а значение count(1) в запрос упадет из астрала? =(
            +6
            Поиск данных за последнюю дату

            SELECT p.uid, p.amount
              FROM payments p
              JOIN
                (SELECT uid, MAX(pay_date) AS max_dt
                   FROM payments
                   GROUP BY uid) sel ON p.uid = sel.uid AND p.pay_date = sel.max_dt;
            

            В результатах будут дубликаты, если один пользователь совершил несколько платежей в один день.
              –4
              По моему GROUP BY не допустит этого.
                +1
                Вы ошибаетесь. Будут там дубликаты, при условии, что пользователь совершил несколько платежей в последний день.
                  –1
                  pastebin.com/7scfDNrq Минусуйте
                    +2
                    Вы структуру таблицы предложенную автором для этого запроса внимательно рассмотрели?

                    У него поле pay_date типа DATE.
                      +1
                      payments (id INT, uid INT, pay_date DATETIME, amount DECIMAL(15, 2))

                      Я один вижу в этой таблице тип DATETIME?
                      Единственное, что я не воспроизвёл в своей таблице, так это тип поля amount, но оно в данном случае не играет роли.
                        +3
                        >> payments (id INT, uid INT, pay_date DATETIME, amount DECIMAL(15, 2))
                        Я один вижу в этой таблице тип DATETIME?


                        Автор поступил очень не красиво и поправил структуру таблицы в статье, после того как появились большинство комментариев на эту тему (примерно 15.08.2012 00:15).

                        Но даже поле типа DATETIME в данном случае Вас не спасет. Все равно будут дубликаты, но с точнотью до секунды, а не до дня.
                  –3
                  distinct не допустил бы этого, group by группирует тупо
                    0
                    Дело не в group by, а в типе pay_date. Если он timestamp, то и дубликатов не будет. Ну они возможны, если юзер умудрился совершить 2 операции подряд с разницей в 1 секунду.
                      0
                      >> Дело не в group by, а в типе pay_date.
                      Нет, тут дело в не правильно построенном запросе.
                        –1
                        SELECT uid, MAX(pay_date) FROM payments GROUP BY uid;
                        

                        Данный запрос гарантирует уникальные значения, но соединение значений с таблицей — нет. Еще раз, если тип будет timestamp, а при добавлении строки в поле pay_date будет добавляться current_timestamp, то запрос отработает как надо, даже если пользователь совершит несколько платежей в один день. Добавление записи с разницей < 1 секунду маловероятно. Niga доказал правильность запроса.
                          0
                          >> Добавление записи с разницей < 1 секунду маловероятно.
                          Вы это серьезно?

                          >> Niga доказал правильность запроса.
                          Он ошибся со структурой таблицы, и я ему это сразу пояснил. Не верите? возьмите и проверьте. Всё увидите своими глазами.
                            –2
                            Вы это серьезно?

                            Всё упирается лишь в точность поля pay_date.

                            Он ошибся со структурой таблицы, и я ему это сразу пояснил. Не верите? возьмите и проверьте. Всё увидите своими глазами.

                            Так дело в запросе или в точности поля, хранящего время запроса? Вы уж определитесь.

                            В конце концов, можно решить это параноидальным запросом:
                            SELECT uid, amount
                              FROM payments
                              WHERE (id,uid) IN (SELECT MAX(id), uid
                                   FROM payments p
                                   GROUP BY uid, pay_date
                                   HAVING pay_date = (SELECT max(pay_date) FROM payments WHERE uid=p.uid)
                              );
                            

                              +1
                              Ага, вот только бОльший id не означает, что платёж был проведён позже. В наших бизнес-требованиях об это ничего не сказано. Знаете ли, бывают распределённые системы, карточки с чипами, и т.д., когда платёж совершается сегодня, а в БД попадает через неделю.
                              В общем, проблема задачи — в том, что типичной её назвать сложно. Слишком разные бывают требования.
                              Нужно показать данные о последнем платеже, а вот как выяснить, какой платёж на самом деле был последним, — не сказано.
                                –1
                                Ага, вот только бОльший id не означает, что платёж был проведён позже.

                                Внимательно посмотрите на HAVING.
                                  0
                                  Вы, наверное, не поняли, что я имел в виду. ВНУТРИ одной даты (наибольшей для данного uid), бОльший id не означает, что платёж был проведён позже. И неважно, какая точность у payment_date — день, или секунда. Всё равно может быть несколько платежей, совершённых за одну секунду.
                                  А ваш запрос выдаст точно тот же результат, что и мой, который я написал на час раньше. Критерии выбора те же самые, хотя структура запросов совсем разная.
                                  Проблема в том, что неизвестно, как выяснить, какой платёж выбирать из тех, что имеют одинаковое значение payment_date. Бизнес-требования недостаточно проработаны.
                                    –1
                                    либо повышать точность времени, либо принять, что с одинаковым временем более поздний платеж с бОльшим id. Мой запрос, удовлетворяющий этому условию, ниже вашего)
                                      0
                                      Ну да. Я просто хотел подчеркнуть, что ничего непонятно без чётких бизнес-требований. Возможно, что задача вообще нерешаема.
                                        –2
                                        Ага, а hello world стоит писать так.
                                          –2
                                          Ну мой-то вариант рабочий, в отличие от варианта в статье. «Малая вероятность ошибки» — это самая тупая отмазка, которая может быть у программиста.
                                            –1
                                            А вот сейчас автор статьи уточнил бизнес-требования, и его запрос стал рабочим, а ваш — излишне сложным.
                                –1
                                >> Так дело в запросе или в точности поля, хранящего время запроса? Вы уж определитесь.

                                Вы понимаете, что результат запроса зависит от структуру таблицы?
                    0
                    SELECT p1.uid, p1.amount
                    FROM payments p1
                    where not exists (select uid from payments p2 where p2.uid=p1.uid and p2.id>p1.id)
                      0
                      Сработает только для случаев, когда больший id означает более поздний платёж. Что совсем не обязательно истинно.
                        0
                        Делов-то, p2.pay_date>p1.pay_date вместо id. Главное — смысл.

                        select uid в подзапросе тоже возможно не оптимально, если индекс по uid отсутствует, а по id индекс есть.
                          0
                          pay_date вместо id
                          Тогда опять дубликаты появятся ;-)
                            0
                            Тогда задача не решаема? Максимум могу предложить суммировать все платежи в последний день, если их больше одного.
                              0
                              Да, я думаю, что в описанном варианте задача действительно нерешаема. Наиболее близким к искомому, наверное, будет вот такой вариант:

                              SELECT
                                p.uid,
                                p.amount
                              FROM
                                payments p
                              WHERE
                                p.id = (
                                  SELECT p2.id
                                  FROM payments p2
                                  WHERE p.uid = p2.uid
                                  ORDER BY p2.pay_date DESC, p2.id DESC
                                  LIMIT 1
                                )
                              

                              (онлайн-пример)
                                0
                                Ну или еще полухакерский вариант:

                                SELECT p.uid, p.amount
                                  FROM payments p
                                  JOIN
                                    (SELECT uid, MAX(pay_date*1000000 + id) AS max_dt
                                       FROM payments
                                       GROUP BY uid) sel ON p.uid = sel.uid
                                          AND (p.pay_date*1000000 + p.id) = sel.max_dt;


                                Думаю план у него будет плохим, да и 1000000 надо заменить на «очень большое значение». Короче, просто такой себе вариант
                                  0
                                  Ну, если говорить о «хаках», можно и вот так :)

                                  SELECT
                                    uid,
                                    CAST(
                                      SUBSTRING(
                                        MAX(CONCAT(pay_date, ',', LPAD(id, 11, '0'), ',', amount)),
                                        24
                                      ) AS SIGNED) amount
                                  FROM
                                    payments
                                  GROUP BY
                                    uid
                                  
                      0
                      Да, вы правы, спасибо, моя ошибка. Тут конечно же DATETIME.
                        0
                        На самом деле, это мало что меняет.
                          0
                          Ок, это меняет точность записи времени. Но дубликаты всё равно могут быть, хотя и с меньшей вероятностью.
                          +2
                          И что Вам даст DATETIME?
                          Он Вам даст те же проблемы, но с точностью до секунды, а не до дней, что собственно ничего не меняет. Ваш запрос все равно будет возвращать дубликаты.
                            0
                            Согласен с вами, был выбран неудачный пример, сейчас немного конкретизирую задачу.
                              0
                              Только обязательно укажите как Вы поставили задачу с самого начала, а потом новую постановку, а то получается, что мы здесь ерунду пишем в комментариях.
                                0
                                Вы привели какое-то абсолютно нереальное ограничение. Лучше бы написали, что если платежи произошли в одну секунду, то можно выбирать любой из них (например, тот, у которого больший id) — пользователю это подойдет. Ах да, хотя тогда бы ваш запрос не работал :)
                          +6
                          Column 'id' in group statement is ambiguous
                          Я не понял «о чудо» этого абзаца. Очевидно же что для GROUP, id двусмысленный.
                          Так добавьте ему смысла.
                          SELECT t1.id, t2.id
                            FROM table1 t1
                            JOIN table2 t2 ON t1.id = t2.id_t1
                            GROUP BY t1.id;

                            0
                            Именно об этом я и хотел сказать, чтобы люди не забывали добавлять смысла полям в группировке. Иначе можно нарваться на ошибку, добавив в список выводимых полей t2.id, хотя без него запрос прекрасно работал. В этом отличие MySQL от других СУБД, которые таких вольностей не допускают.
                            0
                            Плохо разбираюсь в MySQL, не подскажите, что за ключевое слово ON? Гугл плохо ищет по коротким словам.
                              +2
                              ON используется в совокупности с JOIN'ами: dev.mysql.com/doc/refman/5.0/en/join.html
                                0
                                После ON следует условие для выборки из связанных таблиц в случае JOIN.
                                  0
                                  ON — аналог WHERE, используемый при JOIN.
                                    +2
                                    Всем спасибо, все понял.
                                    –2
                                    Спасибо, хорошие рецепты.
                                      0
                                      > Column 'id' in group statement is ambiguous

                                      Для этого случая есть еще более простое решение: GROUP BY 1

                                      Цифры в GROUP BY или ORDER BY обозначают номер колонки. Это плохая практика для рабочего кода. Но очень удобно, когда вам надо просто извлечь информацию из таблицы (особенно если вы пишете запрос руками).
                                        –2
                                        Шикарно, наконец-то сбылась мечта идиота, я понял как сделать одно поле в выборке со счетчиком строк =)
                                          +2
                                          Дополню Ваш пост: Common MySQL Queries
                                            –7
                                            Вы пизданулись что ли все? Кто эти люди кто добавил статью в избранное? Вы хотите открыть что-то новое для себя в повторном чтении этих обычных запросов?
                                              0
                                              А вы про фичу с удалением дубликатов знали?
                                              Ну и, как видно из коментов, даже в обычных запросах сам автор сделал ошибку — так что не всё так просто (-:
                                                +1
                                                >> даже в обычных запросах сам автор сделал ошибку ...
                                                В данном случаи из этого следует, что автор плохо разбирается в СУБД MySQL и крайне не внимательный, а не то, что он рассказал, что то сложное и интересное.
                                                Он просто даже не потрудился протестировать свои запросы.
                                              0
                                              Если я узнал после прочтения статьи новое и добавил эту статью в избранное, то я «плохое слово»? Почему?
                                                0
                                                Про использование переменных не знал, спасибо. Решал такую задачу более громоздким запросом.
                                                  +2
                                                  SELECT * FROM address WHERE num + 0 = 1;

                                                  Жесть.
                                                  Это запрос из разряда «прощай индексы».
                                                  Хотя даже LIKE их будет использовать (хотя и плохенько).
                                                    +1
                                                    SELECT t1.id, t2.id
                                                      FROM table1 t1
                                                      JOIN table2 t2 ON t1.id = t2.id_t1
                                                      GROUP BY 1;
                                                    
                                                      –2
                                                      Лучше стараться не использовать специфичные для mysql фишки — легче будет мигрировать на другую СУБД, если вдруг понадобится
                                                        0
                                                        >> Лучше стараться не использовать специфичные для mysql фишки
                                                        Может Вы еще посоветуете не использовать специцифичные для СУБД ORACLE фишки?
                                                          0
                                                          Я ожидал, что в пример будет приведен эталон Оракл. Речь идет о нарушении стандартов. Например, group by мускула позволяет делать выборку полей, которые не участвуют в группировке. Это отход от стандарта. В большинстве случаев можно обойтись и без специфичных хаков
                                                            +1
                                                            >> Речь идет о нарушении стандартов.

                                                            Вы знаете сколько дополнений к стандартам практически в любой СУБД (MySQL, MSSQL, PostgreSQL, ORACLE, ...)?

                                                            Если говорить о миграции, то речь скорее надо поднимать об использовании уровня абстракции над СУБД, но никак не отказываться от всяких вкусностей, которые предлагают различные СУБД.
                                                              0
                                                              Да, наверное, соглашусь
                                                        0
                                                        Насколько все эти трюки совместимы с PostgreSQL?
                                                          +1
                                                          Вот ещё один хороший способ удалить дубликаты, не используя ключей

                                                          DELETE t1 FROM t1, t2 WHERE t1.id > t2.id AND t1.name = t2.name;
                                                            +2
                                                            Начало статьи реально порадовало ALTER IGNORE TABLE table1 ADD UNIQUE..., но в продолжении описаны методы, которые реально ломают использование индексов — огорчили.
                                                            На счет последнего примера я бы написал так:
                                                            SELECT ua.uid, ua.amount
                                                            FROM (
                                                               SELECT p.uid, p.amount
                                                               FROM payments p
                                                               ORDER BY pay_date DESC
                                                            ) ua
                                                            GROUP BY ua.uid
                                                            
                                                              +1
                                                              Я предлагал автору этот вариант.
                                                              По-моему мнению это запрос наиболее хорошо решает задачу в самой первой постановке и очень подходит для этой статьи, так как реализует именно особенность работы MySQL с GROUP BY в без агрегирующих функций, что в других СУБД может оказаться ошибочным.
                                                              –3
                                                              Нужно написать запрос, который бы вывел для каждого юзера дату и сумму последнего платежа.
                                                              UPD. Считаем, что юзер не может провести больше одного платежа за секунду. (Без этого условия постановка задачи некорректна). Тип pay_date изменен с DATE на DATETIME.


                                                              Возможно я открою вам глаза:
                                                              SELECT SELECT p.uid, p.amount FROM payments p GROUP BY p.uid ORDER BY p.pay_date DESC

                                                              Работает в разы быстрее чем джойны виртуальных таблиц, честно.
                                                                +2
                                                                … вот только результат возвращает совсем не тот, что нужен.
                                                                  –2
                                                                  Это вы проверили или просто ожидаемое поведение себе представили?
                                                                  Ну и да, возможно это от версии зависит (работает верно как в 5.1 так и в 5.5).
                                                                    +1
                                                                    был не прав.
                                                                    0
                                                                    Такой запрос вернет amount не за последнюю дату, будьте внимательнее.
                                                                      0
                                                                      угумс, обещаю в следующий раз не спешить тыкать кнопку «написать» :)
                                                                      Правка ниже
                                                                      –1
                                                                      исправлюсь, SELECT * FROM (SELECT p.uid, p.amount FROM payments p ORDER BY p.pay_date DESC) t GROUP BY t.uid
                                                                        0
                                                                        Запрос представленный serjoga выше точно такой же.
                                                                          –4
                                                                          очень часто случается что мнения и методы двух разных людей совпадают :)

                                                                    Only users with full accounts can post comments. Log in, please.