Ответы пользователя по тегу MySQL
  • Update case или два запроса?

    @Joysi75
    Простые запросы проще сопровождать (нежели гроздья WHEN-ов).
    По скорости зависит от наличия одновременных запросов к этой же таблице, с точки зрения блокировок - кол-во их будет одинаковое.
    www.mysql.ru/docs/man/InnoDB_Locks_set.html и т.д.

    P.S. Подвопрос:
    ELSE text <--дополнительный вопрос-- это же не обязательно, зачем тогда так делать?
    Это во многих SQL серверах так(например, в Oracle) - анализатор не знает значений полей, участвующих в условиях When, потому ветка ELSE хотя и не обязательна, но может возникнуть RUNTIME ошибка при оптимизации запроса для выполнения.
    Ответ написан
  • Многопоточность и MySQL?

    @Joysi75
    А в чем проблема?
    Базу данных проблемы использующих ее приложений не волнуют. Она в порядке очереди будет обрабатывать поступающие запросы и выполнять их. При выполнении операций модификаций будет блокировать таблицу на изменение. Если будут параллельные запросы к данному ресурсу - положит их в очередь до завершение блокирующих операций, если параллельный запрос к незаблокированному ресурсу - запустит его выполнение не дожидаясь результатов предыдущих.

    У вас таблица с identity. И одновременно поступило пятьсот insert-ов. Все они встанут в очередь. И будут отработаны (будут выполнены или нет из-за некорректности данных). Единственное но, если одно приложение послало подряд не в транзакции два insert-а, никто не гарантирует что у них idenitity поля после вставки будут отличаться на единицу.

    И не стоит реализовывать в клиентской программе логику, например:
    вы вставили значение в таблицу c identity ключом, получили его на клиенте и по привычке однопользовательской БД решили получить количество записей в таблице как значение idenity поля (при условии что данные из нее вы не удаляете) для дальнейших действий. Вот тут может не прокатить, так как между последней ВАШЕЙ операцией Insert может кто-то еще вставить данные и вы не учтете их в логике приложения.

    P.S.
    Также помните, если вам надо выполнить в базе данных подряд несколько логически связанных операций , то оформите их как транзакцию - логический неделимый блок операций. При этом операции будут выполнены подряд последовательно, результат будет:
    -отражен в БД данных при условии , что все операции выполнены корректно
    -полностью отменен и БД восстановит состояние, в котором она была до выполнения первой операции в транзакции при условии, что какое либо действие в транзакции не исполнилось.
    Ответ написан
  • SELECT с неизвестной заранее вложенностью?

    @Joysi75
    Критично применение mysql ? В некоторых СУБД есть типы для хранения древовидных структур.
    Например MSSQL (express версия free) - https://msdn.microsoft.com/en-us/library/bb677290.aspx
    или обзорно тут https://habrahabr.ru/post/27774/
    Ответ написан
  • Как составить запрос MySQL?

    @Joysi75
    Мало входных данных в части хранения SQL.
    Если, например, организовать вашу структуру как список таблиц (id - первичные ключи):
    vegetables(id, name) овощи. Записи в таблице:(1, 'картофель')
    points(id, name) пункты перевозок. Записи в таблице: (1, 'Минск'), (2, 'Москва'), (3, 'Пермь'), (4, 'Тюмень')
    deliveries(vegetable, route, name). Записи в таблице: (1, 1, 'Поставка картошки из Белоруссии N 120'). Поля имеют внешние ключи на vegetables и routes
    routes (id, StartPoint, StopPoint). Записи в таблице: (1, 1, 4). Поля имеют внешние ключи на points
    pathes(route,fromPoint, toPoint). Записи в таблице: (1,1,2) , (1,2,3) (1,3,4). Поля имеют внешние ключи на points и routes.

    select  distinct vegetables.name 
      from vegetables, deliveries, routes, pathes
      where 
        deliveries.vegetable = vegetables.id and
        routes.id=deliveries.route and
        pathes.route=deliveries.route and
       exists (
          select * from pathes, points 
             where pathes.route=route.id and 
                from pathes.fromPoint=points.id and points.Name='Москва') and
       exists (
          select * from pathes, points 
             where pathes.route=route.id and 
                from pathes.toPoint=points.id and points.Name='Тюмень')

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

    P.S. Естественно добавляются еще доп поля (наличие обратного маршрута и т.п.) . Я просто хотел показать (как пример) - организацию выборки данных и их хранение.
    Ответ написан
  • Как задать необязательные параметры в mysql?

    @Joysi75
    1. Что мешает перед запросом проверить наличие параметра через if (или еще как) и в зависимости от наличия построить необходимый запрос ?
    2. Если только через SQL-выражение без применения if и т.п. и поле f1 в таблице строковое можно в запросе select * from t1 where ... and f1 like '%param%'
    3. Если поле f1 в таблице числовое то можно сколхозить через select * from t1 where ... and f1>=ifnull(param,минимально возможное значение столбца)

    и т.п. с другими типами
    Но во избежание ненужной загрузки для СУБД лучше строить правильный запрос.
    Ответ написан
  • Выбрать из таблицы MySQL по одинаковым значениям полей. Как лучше?

    @Joysi75
    Полностью согласен с Rsa97. Но если нельзя менять структуру БД и нормализовать ее, то ищите по вхождению подстроки в строку:
    1)
    select * from table where instr ( concat(';',addr,';'),';Москва;') > 0

    2)
    select * from table where instr ( concat(';',lang,';'),';рус;') > 0

    3)
    select * from table where instr ( concat(';',times,';'),';пт;') > 0

    и т.д. ';' обнесены, чтобы исключить например попадания Нью-Йорк вместо Йорк и т.п.
    Но все это жутко(даже бешено) неправильно по структуре и сильно увеличивает шанс хранения ошибочных данных и т.п.
    1) Переименуйте поля (addr -> city, times -> dayofweek и т.п.).
    2) Создайте таблицы справочники и сделайте внешний ключ на них
    3) Используйте правильный тип строки (очень плохо хранить все в текстовом виде, особенно ограниченные множества.)
    Ответ написан
  • Как правильно проводить "раскопки" сложной структуры БД на крупном проекте?

    @Joysi75
    • 1. Сделайте копию для тестовой базы (или баз) и "копайте в ней"
    • 2. Составьте схему объектов и субъектов СУБД :
      • дерево таблиц и внешних ключей между ними
      • представления (view) и их построение
      • хранимых процедур/функций (укажите с какими таблицами работают)
      • триггеров (на какие таблицы и пр и каких действиях действуют)
      • периодических работ (job-ы и т.п.) - с какими объектами работают и что делают.
      • права и пользователи (кому и что доступно в БД).
      • внешние источники (файлы, импорт/экспорт).
      • и т.п.

    • 3. Найдите монитор запросов (tracer текущих запросов к БД или хотя бы анализатор логов).
    • 4. Настройте проект на работу с данной тестовой СУБД,
    • 5. Начните со справочников (обычно таблицы на которые имеются внешние ссылки и имеющие обычно простую структуру по типу id и name ). Например, справочник групп товаров. Измените через интерфейс наименование, добавьте новое, удалите его. Смотрите какие запросы возникают, какие триггеры отрабатывают (возможно, но маловероятно вызываются хранимые процедуры - проанализируйте и их вызовы) - таким образом выделите в схеме СУБД таблицы справочники и назначение их полей.
    • 6. Далее приступайте ко сложным объектом. Схема та же (но результат может быть отражен на большем количестве объектов СУБД) - выполняете основные действия (например, добавить товары в корзину и т.п.) -> ловите возникающие с ними действия к СУБД (команды, вызов процедур и т.п.) -> отслеживаете изменения СУБД -> документируете объекты и их составляющие (например, таблицы и их поля)
    • 7. Попробуйте выполнить отчеты через интерфейс (если есть). Анализируйте команды к СУБД для их построения аналогично шагам 6-7.
    • 8. Надеюсь к этому шагу часть вопросов по схеме СУБД снимется.
    • 9. Очень рекомендую во время наименьшей нагрузки периодически (например, раз в сутки в полночь) делать копию боевой СУБД и разворачивать ее на тестовой для дальнейшего изучения и экспериментов.

    P.S. При анализе используйте названия таблиц и полей на предмет повторяемости. Например, изучаете работу с клиентом и таблица имеет название User. Поищите среди всех таблиц те, которые имеют поле User и анализируйте их назначение и т.п.
    Ответ написан
  • Как правильнее сделать выборку и построения графа рефераллов?

    @Joysi75
    MySQL обязательно использовать? Нет возможности сменить его на Postgres (или другие СУБД где есть рекурсивные запросы - Oracle/MSSQL/DB2)?

    Есть или нет готовые библиотеки для отрисовки из СУБД не знаю. Но возможно получить из таблицы пути для построения дерева. Если устроит то следующим образом.
    (К сожалению под рукой нет postgresql,) запрос примерно такой:
    WITH 
        RECURSIVE search_graph(id, ref_id) AS
        (
            SELECT id, ref_id
            FROM Users
            UNION ALL
            SELECT o.id, o.ref_id
            FROM Users o
        JOIN search_graph p ON p.ref_id = o.id
        )
        SELECT array_agg(id || ' -> ' ) AS "path"
        FROM search_graph
        GROUP BY id
        ORDER BY id
    ;

    Должен выдать:
    1 ->
    2 -> 1 ->
    3 -> 1 ->
    4 -> 1 ->
    5 -> 4 -> 1 ->

    Последний '->' можно при необходимости убрать через substring(str, from 1 for (length(str)-4))

    P.S. С MySQL форками и последними версиями не работал - может и появились рекурсивные запросы - не знаю. Или возможно через хранимую процедуру/функцию сделать аналог.
    Ответ написан
  • Насколько допустимы запросы без LIMIT?

    @Joysi75
    Плюсы использования limit:
    1) Ограничение и предсказуемость кол-во результирующих строк со стороны сервера,
    2) Меньше нагрузка на сеть,
    3) Проще проектировать GUI выдавая результат запроса "пачками" с определенным количеством строк.
    4) Подсказка для оптимизатора запросов, что приведет к увеличению производительности выборки со стороны сервера

    Минусы использования limit:
    1) Можно сломать логику и/или пропустить ошибку. Допустим в таблице справочника отсутствует primary key и задублировалась запись. Применяя выборки с limit 1 - не поймаете данную ошибку.
    2) Отсутствует в стандарте SQL до 2011.
    3) Не стандартизирован в SQL92. Многие СУБД имеют аналоги (top - MSSQL, Offset - вроде в Postgres; или, еще хуже - конструкции с where rownum = в Oracle ). То есть меняя версию СУБД Вам, возможно, придется переписать все подобного рода запросы.

    P.S. Такого рода споры о целесообразности применения конструкций СУБД необходимо решать с 2х точек зрения СУБД и Приложения. Уверены, что у Вас 99.999999% нет ошибок в реализации объектов СУБД и не планируете переходить на другие СУБД - применяйте limit, иначе задумайтесь.
    P.S.S. Аналогичен спор и о необходимости Order by. Серверные люди скажут, что order by - баловство, не стоит нагружать сервер задачами сортировки полученных данных - это "крестьянское" дело должно ложиться на плечи приложения :)
    Ответ написан
  • Выбрать друзей из таблицы?

    @Joysi75
    Примерно так (если понял вопрос), вместо 5 - нужное значение (подправил, после замечания автора топика):
    select t2.* from table1 t1,table1 t2 
      where 
         t1.from_user_id=t2.to_user_id and 
         t2.from_user_id=t1.to_user_id and 
         t1.from_user_id=5
    Ответ написан
  • Множественные объединения c условием?

    @Joysi75
    По моему все можно :-) ибо
    Я бы делал через subquery (удобно для выборок по разным условиям из составленного "набора" пересечений)
    select BSproduct from
      ( -- Список продуктов имеющих черный цвет и малый размер
        select product as BSProduct from
          (
            SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
              FROM  `product` AS t1
              JOIN `product_color` AS t2 ON t1.id = t2.product_id
              JOIN `color` AS t3 ON t2.color_id = t3.id
              JOIN `product_size` AS t4 ON t1.id = t4.product_id
              JOIN `size` AS t5 ON t4.size_id = t5.id
          ) 
        where color = "black" and size = "S"  
      ) as BlackAndSmallSize, 
      ( -- Список продуктов имеющих черный цвет и средний размер
        select product as BMProduct from
          (
            SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
              FROM  `product` AS t1
              JOIN `product_color` AS t2 ON t1.id = t2.product_id
              JOIN `color` AS t3 ON t2.color_id = t3.id
              JOIN `product_size` AS t4 ON t1.id = t4.product_id
              JOIN `size` AS t5 ON t4.size_id = t5.id
          ) 
        where color = "black" and size = "M"  
      ) as BlackAndMidSize, 
      ( -- Список продуктов имеющих большой размер
        select product as LProduct from
          (
            SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
              FROM  `product` AS t1
              JOIN `product_color` AS t2 ON t1.id = t2.product_id
              JOIN `color` AS t3 ON t2.color_id = t3.id
              JOIN `product_size` AS t4 ON t1.id = t4.product_id
              JOIN `size` AS t5 ON t4.size_id = t5.id
          ) 
        where size = "L"  
      ) as LargeSize
    where BSProduct=BMProduct and BMProduct=LProduct


    Не знаю как насчет MySQL и его форков, в некоторых SQL серверах можно заранее задавать подзапросы типа
    (
            SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
              FROM  `product` AS t1
              JOIN `product_color` AS t2 ON t1.id = t2.product_id
              JOIN `color` AS t3 ON t2.color_id = t3.id
              JOIN `product_size` AS t4 ON t1.id = t4.product_id
              JOIN `size` AS t5 ON t4.size_id = t5.id
          )

    с помощью команды WITH - тогда объем текста выборки сократится в разы.
    Ответ написан
  • SELECT auto_increment при создании копии структуры таблицы?

    @Joysi75
    Под рукой нет SQL сервера (возможны ошибки синтаксиса). Но следующий вопрос не давал бы мне спокойствия:
    А если в таблице источнике (old) было удаление строки (то есть id записи идут не подряд для autoincrement поля) ? Корректно ли оно отработает (и нет ли подводных камней при переносе при вашей схеме) ?

    Я бы делал так:
    1) Создал целевую (new) таблицу без auto_increment поля
    2) Скопировал в нее все данные INSERT `new_admin_logs` (SELECT * FROM `old_admin_logs`);
    3) Повесил триггер на INSERT (для функционала auto_increment)
    DELIMITER $$
    CREATE TRIGGER auto_inc_new_admin_logs
    BEFORE INSERT ON new_admin_logs FOR EACH ROW
    BEGIN
      DECLARE new1 INT; 
      SELECT  max(id) into new1 FROM new_admin_logs;
      SET NEW.id = new1 + 1;   
    END$$

    P.S. Если таблиц много, можно отдельно завести таблицу для хранения последних значений автоинкремент полей
    P.S.S. Я так переносил в Oracle старый (у которого нет Autoincrement полей) из MSSQL
    Ответ написан
  • Можно ли выполнить UPDATE + INSERT одним запросом?

    @Joysi75
    Если нужно 2 эти операции делать в связке, то можно закрепить это поведение в самой БД через триггер, который после выполнения UPDATE сам инициирует необходимый INSERT. Логику insert-а (в вопросе уже готовые значения) пропишите в теле триггера (для Update возможен доступ к значениям до выполнения команды через псевдозапись (или как правильно в Maria они называются) OLD, к новым значениям - через псевдозапись NEW.

    CREATE TRIGGER `update_users` AFTER UPDATE ON `users`
    FOR EACH ROW 
    BEGIN
       INSERT INTO payments(status_id, user_id, description, currency_id, 
           amount, bonus_amount, sum) 
        values('2' ,new.id, 'test', 643, 
           old.balance, old.balance+new.balance, new.balance);
    END;


    тут свои + и -.
    +) БД управляет сама поведением записей (на случай, если кто 'забудет' прописать вторую команду или оформить транзакцию, или кто вручную добавляет данные из PhpMySQLAdmin и т.п.) согласно заданной логике.
    -) Нужно хорошо знать БД (где и что она хранит и т.п. ;) )
    Вопрос производительности не имеет однозначного ответа, так как зависит от многих факторов (ресурсы и текущая нагрузка сервера, частота изменений ...)
    Ответ написан
  • Выборка данных из трех таблиц MySQL с условием многие к одному?

    @Joysi75
    Немного не догоняю.
    Одним запросом:
    select ads.id, ads_meta.text, ads_images.path ...
    from ads, ads_images, ads_meta
    where ads_images.ads_id=ads.id and ads_meta.ads_id=ads.id
    order by ads.id

    открыть запрос и внутри него пробежать по всем записям в цикле, внутри которого сравнивать текущее значение ads.id со значением на предыдущем шаге, и основываясь на этом формировать вывод или анализ.

    Если будете разбивать на несколько запросов , то все равно объем информации пересылаемой с сервера БД не уменьшиться, да и на клиенте придется держать доп массивы, чтобы сохранять данные для будущих сравнений в IN-выражениях.

    P.S. Экзотика:
    1)Создайте таблицу ads_exotic ( ads_id, ads_meta_data, ads_images_data)
    2) Заведите триггеры на модификацию таблиц ads_meta и ads_images, которые будут редактировать поля ads_meta_data и ads_images_data (допустим, добавляя/удаляя/изменяя через разделители отмодифицированные данные)
    Ну и позже банально select * from ads_exotic where ads_id=X
    Но это противоречит правилам нормализации БД.
    Ответ написан
  • Какой вариант хранения данных лучше?

    @Joysi75
    А использовать сторонние системы задания прав и аутентификации не рассматриваете?

    Если объектов аудита (новости, комментарии и т.п.) мало, то 1-й лучше:
    занимается меньше места, восемь комбинаций разрешений для прав в одной записи таблицы

    Если объектов аудита много, то лучше 2-й (или вводить дополнительную таблицу объект аудита), так как в первом случае придется добавлять расширением первой таблицы. Будет происходить добавлением полей (например, commentAdd, commentEdit, commentDelete и т.п.). И возможно, записи в таблице будут в этом случае сильно разрежены.

    P.S. А вообще (если не использовать стороннее решение), лучше немного поструктуризировать (если помимо новостей будут другие объекты аудита):
    - Таблицы объект аудита, Тип операции и т.п.
    Ответ написан
  • Как увеличить значение ячейки во всех строках таблицы?

    @Joysi75
    Можно и без where:
    UPDATE table1 SET col1 = col1+1

    Важный момент, решите что делать если значение поля null - прописать 1 или оставить null. Если первое - добавьте вторую команду update table1 set col1=1 where col is null, если второе - хватит и первой команды.
    Ответ написан
  • Как отсортировать составной запрос?

    @Joysi75
    Или есть подвох в вопросе или в исходном вопросе изменить строчку на
    JOIN `eav_values` as `val` ON (`products`.`value_id` = `val`.`value_id` and `products`.`value_id`=2)
    Ответ написан
  • Как правильно составить выборку из таблиц по связи многие-ко-многим и сделать CONCAT в MySQL?

    @Joysi75
    Примерно так (пишу с левого компа, где нет mysql - если что отредактируйте немного)
    select product_id as id, products.name,
      group_concat(sizes.value separator ';') as sizes 
    from product_sizes, sizes, products 
    where product_id=products.id and size_id=sizes.id 
    group by product_id


    Добавлено после комментария автора топика:
    Если необходимо выводить и те товары , у которых не указан размер, тогда:
    select products.id, products.name, 
      group_concat(sizes.value separator ';') as sizes 
    from product_sizes 
      inner join sizes on (size_id=sizes.id) 
      right join products on (product_id=products.id) 
    group by product_id order by id;
    Ответ написан
  • Как ускорить поиск по таблице?

    @Joysi75
    `Phone` varchar(15) NOT NULL
    Если позволяет место на сервере лучше смените на
    `Phone` char(15) NOT NULL или (если не хранятся в значениях нецифровые символы - скобки, например)`Phone` int NOT NULL
    Так как в этом случае будет индексироваться по ключу фиксированной длины (в случае с int еще лучше, будет индексироваться по 4/8 байтам вместо 15) - соответственно поиск будет быстрее.
    Ответ написан