Задать вопрос
Ответы пользователя по тегу MySQL
  • Будет ли использоваться индекс в запросе вида WHERE `field1` LIKE '_aa_a_'?

    @Akina
    Сетевой и системный админ, SQL-программист.
    будет ли использоваться индекс в запросе вида WHERE `field1` LIKE '_aa_a_'?

    50/50 - или будет, или нет.

    Однако если он будет использоваться, то не как индекс (сортированный список для выполнения index seek), а как компактная копия таблицы (как несортированный список для выполнения index scan).

    Тем не менее профит от использования индекса таким образом может быть, и весьма значительный. Главным образом он определяется соотношением размера записи к размеру поля. И чем это соотношение выше, тем более вероятен профит.
    Ответ написан
    1 комментарий
  • Как быстро найти совпадение в базах?

    @Akina
    Сетевой и системный админ, SQL-программист.
    SELECT *, 'db1' AS dbasename
    FROM db1.tablename
    WHERE phone = '123-45-67'
    UNION ALL
    SELECT *, 'db2'
    FROM db2.tablename
    WHERE phone = '123-45-67'
    UNION ALL
    ...

    Если список баз не фиксирован и должен формироваться в рантайме - то хранимая процедура с запросом по INFORMATION_SCHEMA.TABLES и динамическим SQL.
    Ответ написан
    Комментировать
  • Как правильно выбрать все товары?

    @Akina
    Сетевой и системный админ, SQL-программист.
    SELECT p.name, p.id 
    FROM products AS p
    JOIN product_country AS cp ON cp.product_id = p.id 
    WHERE cp.country_id IN (1,2)
    GROUP BY 1, 2
    HAVING COUNT(DISTINCT cp.country_id) = 2;
    Ответ написан
    Комментировать
  • Как получить нужные данные?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Может, требуется тривиальное
    SELECT *
    FROM tablename
    ORDER BY client_id IS NULL, hub_id IS NULL
    LIMIT 1

    ?
    Ответ написан
    1 комментарий
  • Поле json в MySQL 5.7 содержит массив типа '[1,3,5]' - как оптимально сделать выборку записей, в которых есть значения x || y || z?

    @Akina
    Сетевой и системный админ, SQL-программист.
    SELECT DISTINCT data.*
    FROM data
    JOIN ( 
        SELECT 2 value 
        UNION ALL 
        SELECT 5 
        UNION ALL 
        SELECT 7
        ) criteria ON JSON_CONTAINS(data.jsonarray, CAST(criteria.value AS JSON))
    Ответ написан
    2 комментария
  • Как раздавать уникальные записи таблицы во многопоточном парсере?

    @Akina
    Сетевой и системный админ, SQL-программист.
    parsed - 0 - страница еще не спаршена, 1 - страница спаршена.

    Не так.

    NULL - не парсилась.
    0 - парсинг выполнен.
    >0 - взято на парсинг соединением (функция CONNECTION_ID()) номер N.

    Соответственно попробовать зарезервировать запись на парсинг:
    UPDATE urls 
    SET parsed = CONNECTION_ID()
    WHERE parsed IS NULL
    ORDER BY datetime_added LIMIT 1;

    Получить зарезервированную запись и начать её парсинг:
    SELECT *
    FROM urls
    WHERE parsed = CONNECTION_ID();

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

    По окончании парсинга соответственно
    UPDATE urls 
    SET parsed = 0
    WHERE parsed = CONNECTION_ID();

    Ну и периодически выполняется event procedure, который находит записи, формально помеченные как обрабатываемые, но, судя по времени, обработчик подвис. Такие записи возвращаются на обработку
    CREATE EVENT clear_parsing_flag
    ON SCHEDULE EVERY 1 MINUTE
    DO
    UPDATE urls
    SET parsed = NULL
    WHERE parsed > 0
        -- считаем, что 5 минут более чем достаточно
      AND datetime_start_parsed < CURRENT_TIMESTAMP - INTERVAL 5 MINUTE;


    Само собой никаких пулов соединений, никаких открыть-закрыть - все операции выполняются в рамках одного persistent connection. Автовосстановление соединения при обрыве также запрещено.

    Если соединение развалилось, неважно по какой причине, бросаем обрабатываемую запись (шедулер вернёт её в необработанные), соединяемся заново и начинаем с самого начала, с резервирования.

    Ну и предусмотреть случай, когда записей на парсинг просто нет. Например, если 5 резервирований подряд не смогли получить запись на обработку, то, чтобы не ставить сервер раком, вводим между попытками резервирования задержку, например, на 5 секунд... ну и вываливаем баннер, что, походу, парсить нечего.
    Ответ написан
    Комментировать
  • Как добавить партицию, если уже есть maxvalue?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Да, нужно делать реорганизацию - операция потенциально требует переноса данных между партициями.

    Прмер:
    CREATE TABLE table_name (create_date DATE);
    
    ALTER TABLE table_name 
    PARTITION BY RANGE(create_date + 0) (
        PARTITION p_old VALUES LESS THAN(20111201),
        PARTITION p_1 VALUES LESS THAN(20120201),
        PARTITION p_2 VALUES LESS THAN(MAXVALUE)
    );

    Добавляем партицию (точнее, разделяем партицию на две):
    ALTER TABLE table_name
        REORGANIZE PARTITION p_2 INTO (
            PARTITION p_2 VALUES LESS THAN (20130101),
            PARTITION p_max VALUES LESS THAN (MAXVALUE)
    );

    DEMO
    Ответ написан
    Комментировать
  • Как оптимизировать запрос с подзапросами с COUNT?

    @Akina
    Сетевой и системный админ, SQL-программист.
    SELECT ebids.*
         , SUM(comment_system.itemtype='qqq') AS has_qqq_comment 
         , SUM(comment_system.itemtype='www') AS has_www_comment 
         , SUM(comment_system.itemtype='eee') AS has_eee_comment 
    FROM ebids
    JOIN comment_system ON comment_system.item_id = ebids.id
    WHERE ebids.status != 'auto'
    GROUP BY ebids.id
    ORDER BY ebids.id DESC
    LIMIT 0, 100
    Ответ написан
    Комментировать
  • Как держать в таблице только последние 10 записей?

    @Akina
    Сетевой и системный админ, SQL-программист.
    1. Создать сразу 10 записей с достаточно древней датой.
    2. Вместо вставки записи обновлять самую старую:
    UPDATE table
    SET {columns} = {values}
    ORDER BY created_at ASC LIMIT 1;

    Само собой created_at - ON UPDATE CURRENT_TIMESTAMP. Ну или обновлять вручную, если больше заняться нечем.

    Такая технология к тому же позволяет "на лету" изменять лимит записей. Надо больше? вставляем необходимое число записей с датой в далёком прошлом. Надо меньше? удаляем необходимое число самых старых записей.
    Ответ написан
    Комментировать
  • Что быстрее поиск по файлу JSON в PHP или в базе посредством MySQL?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Что будет лучше в плане скорости и нагрузки

    Определитесь. Или в приоритете скорость, или нагрузка. Но не то и другое одновременно.

    Если приоритетна скорость - то данные должны быть предварительно (а не в момент поступления запроса) загружены в PHP и там проиндексированы, причём должен использоваться алгоритм индексирования, обеспечивающий высокую скорость поиска. Поскольку речь идёт о поиске по подстроке, то, вероятно, это суффиксное дерево или аналогичная индексирующая структура.

    Если приоритетна нагрузка - то поиск следует производить средствами СУБД.
    Ответ написан
    Комментировать
  • Как оптимизировать INSERT запросы в PDO(phpmyadmin)?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Пример.

    На стороне PHP имеется массив строк:

    aaa
    bbb
    ccc

    Необходимо записать его в таблицу. Причём уложиться в один запрос. Длина списка, понятно, не фиксирована.

    Первое - объединяем/сериализуем этот массив в одно значение. Допустим, выбрали JSON массив. То есть ["aaa", "bbb", "ccc"]
    И именно этот JSON мы передадим в запрос.

    Далее на стороне MySQL надо разобрать JSON на элементы и вставить в таблицу. Это делается одним запросом.
    INSERT INTO destination_table (single_value)
    SELECT single_value
    FROM JSON_TABLE( ? ,
                     '$[*]' COLUMNS ( single_value VARCHAR(255) PATH '$' )) jsontable;

    И... а, собственно, всё.

    Осталось только убедиться, что размер строкового представления данных не превышает установленный в MySQL размер входящего пакета, и, если превышает, то соответствующим образом подрихтовать сессионную переменную max_allowed_packet. Впрочем, дефолтно там 67108864 байтов, это порядка 1480 байтов на элемент - должно хватить.
    Ответ написан
    Комментировать
  • Как вставить все значения у всех таблиц через JOIN?

    @Akina
    Сетевой и системный админ, SQL-программист.
    При всей неизбывной глупости такого решения:

    INSERT INTO main (table1_id, table2_id) 
    WITH 
    cte1 AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY id) rn FROM table1 ),
    cte2 AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY id) rn FROM table2 )
    SELECT cte1.ID, cte2.ID
    FROM cte1
    JOIN cte2 USING (rn);
    Ответ написан
  • Что я делаю не так, почему MySQL ругается?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Юзеры в диалогах независимы, потому и связывать их надо независимо.

    create table if not exists dialogs (
        id int primary key auto_increment, 
        user_id_one int not null, 
        foreign key (user_id_one) references users (id), 
        user_id_two int not null, 
        foreign key (user_id_two) references users (id)
    )
    Ответ написан
    Комментировать
  • Как найти следующую строчку в mysql?

    @Akina
    Сетевой и системный админ, SQL-программист.
    WITH
    cte AS (
      SELECT *, COALESCE(LEAD(id) OVER (ORDER BY id),
                         MIN(id) OVER ()) next_row_id
      FROM comments
    )
    SELECT * 
    FROM cte
    Ответ написан
    Комментировать
  • Можно ли в хранимых процедурах создать временную функцию?

    @Akina
    Сетевой и системный админ, SQL-программист.
    CREATE FUNCTION не входит в список конструкций, разрешённых к использованию в stored objects (procedures, functions, triggers, event procedures) и prepared statements.

    Ответ: НЕЛЬЗЯ.

    PS. В MySQL нет временных функций, все функции - статические. Встроенные, регистрируемые либо хранимые.
    Ответ написан
    1 комментарий
  • PHP не создаёт запись MySQL, ошибки не выводит, что делать?

    @Akina
    Сетевой и системный админ, SQL-программист.
    No index used in query/prepared statement ...

    что это означает?

    Это означает, что, по мнению PHP/mysqli, нет подходящего индекса для обработки запроса, сервер будет шерстить всю таблицу, что неэффективно. В общем, он просто старается сделать тебя счастливым (твоё мнение при этом никого не интересует).
    Возможные решения:
    1. Создать индекс users (username)
    2. Изменить настройки mysqli_report() (см. ответ SagePtr)

    без неё обойтись нельзя, т.к. одинаковые ники будут вызывать ошибки в некоторых уголках сайта
    что делать?

    Тогда альтернатив вообще нет.

    Создать уникальный индекс users (username).

    После этого код с проверкой на существование можно смело выбросить (дубликат в принципе не будет записываться сервером). Но обязательно добавить код для обработки ошибки дублирования при добавлении.
    Ответ написан
    Комментировать
  • Как правильно реализовать систему групп у пользователя?

    @Akina
    Сетевой и системный админ, SQL-программист.
    нужно что бы посты из бд отображались только пользователям определенной группы.

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

    FROM post
    JOIN user ON user_id = {текущий пользователь} AND FIND_IN_SET(post.group_id, user.group_ids_csv)

    Однако функция в связи - это прощай индексы и здравствуй фуллскан.

    ID username group
    1 admin 1,2,3


    Дурацкая схема хранения.
    Если есть связь M:N, то должна быть связующая таблица (user_id, group_id).
    Ответ написан
  • Как сделать жесткое исключение NOT IN в mysql?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Разумнее использовать WHERE NOT EXIST, который скорее всего будет более эффективен:
    SELECT [DISTINCT] number
    FROM table t1
    WHERE service NOT IN (1,2,3)
      AND NOT EXISTS (
        SELECT NULL
        FROM table t2
        WHERE t1.number = t2.number
          AND t2.service IN (1,2,3)
    )

    Индекс по (number, service) - весьма желателен.

    Если service - целочисленное поле со значениями от единицы и более, то для даных конкретных условий можно построить и более эффективный запрос:
    SELECT number
    FROM test
    GROUP BY number
    HAVING MIN(service) > 3
    Ответ написан
    Комментировать
  • Ошибка #1064. Как объявлять переменные в mysql 8.0?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Нет такого понятия как местные переменные.

    Есть переменные локальные. Они определяются в рамках блока кода в хранимой конструкции (функция, процедура, триггер и пр.), имеют определённый тип, и существуют только в том блоке, в котором определены. Имя начинается НЕ с символа @.

    Есть переменные, определённые пользователем. Имя начинается с символа @. Не требуют определения, существуют в течение всей сессии.

    В показанном коде - мешанина. Попытка определить пользовательскую переменную, что приводит к ошибке синтаксиса. Показанный код был бы корректен в MS SQL (SQL Server), но не в MySQL.

    В общем, собак - убрать. Всех.
    Ответ написан
  • Как вернуть опредленное значение, если запрос выдает название колонки?

    @Akina
    Сетевой и системный админ, SQL-программист.
    SELECT COALESCE( 
        (
            SELECT busy FROM `all_reservs` WHERE dates ='27.06.22' AND number='22' LIMIT 1
        ), 
        'no'
    ) AS busy;

    Если запрос вернёт запись (с одним полем), скаляризация преобразует её в значение, и COALESCE вернёт это значение.
    Если запрос не вернёт запись, скаляризация вернёт NULL, и COALESCE вернёт 'no'.
    LIMIT страхует от возврата более чем одной записи. По-хорошему ещё надо добавить ORDER BY.
    Ответ написан
    1 комментарий