Ответы пользователя по тегу MySQL
  • Почему в данных таблицы все не по порядку?

    @alexalexes
    О порядке добавления записей не нужно заботиться.
    У СУБД нет задачи хранить записи в строгом порядке, ваша задача понять, удовлетворит ли вас результат сортировки, если при выборке задать сортировку по darg-w:
    select *
      from verbs
    order by darg-w

    Если хотите получать список в порядке добавления, то логично сортировать по id:
    select *
      from verbs
    order by id

    Если хотите особую сортировку, которая не подчиняется ни id, ни естественному весу строк, то можете ввести новую колонку и следить за позиционированием строк при выборки сами, или по каким-то другим правилам вычислять позицию:
    select *
      from verbs
    order by position -- дополнительное поле, в которое будете сами или программно вбивать порядок сортировки

    Или запрос может выдергивать сам определенные строки, давая им высший приоритет (можно написать любое правило, что требует бизнес логика, или что в голову сбредет):
    select *
      from verbs
    order by case when id = 100 then 0 else 1 end -- даем записи id=100 наивысший приоритет, в любом состоянии списка она будет на 1 месте!
            , id -- остальные записи отсортируются в порядке добавления

    PS: Важно, чтобы поля, которые применяются в сортировке, были проиндексированы (в схеме СУБД были созданы индексы по данным полям и они своевременно пересчитаны).
    Ответ написан
    1 комментарий
  • Как удалить определённое словосочетание в значении MYSQL?

    @alexalexes
    1. Нужно знать что такое Update.
    2. Уметь работать с Update, не обнулив все подряд (семь раз select-ть с таким же where - один раз update-ть!)
    3. Некоторые строковые функции, например, replace.
    4. Режим работы транзакций и уровень изоляции таблиц при выполнении транзакций. Чтобы не было сюрпризом, когда работаете с выключенным autocommit-ом (это касается desktop программ - менеджеров СУБД), что данные не сохранились, когда забыли объявить commit после изменения данных, или уметь откатываться rollback-ом, если не объявили commit. Еще доступ к таблице будет заблокирован, пока вы принимаете решение после изменения данных - фиксировать их или нет, если у вас уровень изоляции транзакции на всю таблицу и вы работаете без autocommit.
    update superbase.table1
    set theme = replace(theme, 'комплект', '')
    where theme like '%комплект%'
    Ответ написан
    1 комментарий
  • Как сделать поиск в строке с разделителем в mysql?

    @alexalexes
    Но для это подойдет для поиска одного размера, а мне прилетает несколько. Не ужели скажем если 10 размеров прилетит, писать 10 запросов для проверки каждого размера?

    Да, будете страдать по полной, раз нет нормализованной структуры данных.
    Ни нормального выражения с in не построить, ни возможности создать индекс для ускорения поиска.
    Время поиска будет линейно расти от количества записей и умножаться на кол-во or, так как нельзя воспользоваться индексом.
    select * from table
    where 1 = 1 -- чтобы можно было однотипно подставить выражения с and или or, не заморачиваясь в каких случаях нужен and/or, а в каких нет.
    or find_in_set('L', replace(value, ';', ','))
    or find_in_set('XL', replace(value, ';', ','))
    ...
    or find_in_set('XXXL', replace(value, ';', ','))
    Ответ написан
    Комментировать
  • Как выбрать значения из БД по JSON?

    @alexalexes
    Если СУБД поддерживает тип данных JSON, а вы хотите сравнивать JSON тело как строку, то приведите содержимое из JSON в строку.
    SELECT * FROM tavle
     WHERE JSON_EXTRACT(json, '$') = jsons; -- атрибут json относится к таблице tavle, а jsons - плейсхолдер, куда вы подставляете строку, по которой будете искать тело?
    -- JSON_EXTRACT(атрибут_типа_JSON, '$') - это выражение конвертирует JSON в строку, аналогично как в JS будет JSON.stringify(объект_с_данными).
    Ответ написан
    2 комментария
  • Как исправить кракозябры вместо кириллических символов?

    @alexalexes
    Посмотрите в отладчике браузера (вкладка Сеть), какие заголовки возвращаются с ответом сервера.
    Если там заголовок с кодировкой отличен от utf-8, то сервер по умолчанию ее не использует.
    Либо нужно php.ini исправлять, либо в скрипте отдавать нужный заголовок:
    header('Content-Type: text/html; charset=utf-8');
    То, что вы в HTML указали:
    <meta charset="utf-8">
    Не дает никаких указаний серверу, в какой кодировке отдавать ответ.
    Ответ написан
    Комментировать
  • Как сделать логи изменения отдельной таблицы в MariaDB?

    @alexalexes
    Стандартная практика ведения истории изменения данных таблицы.
    Вешаете триггер по операциям UPDATE, INSERT, DELETE перед их выполнением (опция before) на интересуемую таблицу и вставляете данные из атрибутов old.* триггера в таблицу истории.
    Таблица истории:
    history_id - идент. записи истории
    next_history_id - идент. следующей записи истории в пределах одной записи отслеживаемой таблицы (у последней он null)
    oper_type - тип операции (UPDATE, INSERT, DELETE - можно числами записать)
    old.* - все атрибуты таблицы, значения до выполнения операции.
    new.* - все атрибуты таблицы, которые пытались внести операциями UPDATE или INSERT. Этот набор нужен, если вы следите за историей на транзакциях, которые были откатаны назад (неудачные попытки). В этом случае, в триггере нужно указать специальную опцию, чтобы он работал в режиме автономной транзакции.
    При внесении новой записи истории, в предыдущей записи по такому же первичному ключу отслеживаемой таблицы нужно добавлять next_history_id от history_id новой записи.
    Таким образом в отслеживаемой таблице будет актуальное состояние записи, а в таблице истории - цепочка изменений записей.
    По каждому первичному ключу записи отслеживаемой таблицы можно построить цепочку изменений по history_id и next_history_id.
    А по next_history_id is null можно быстро получить последнее изменение из этой цепочки.
    Естественно, к этим полям нужно добавить индексы.
    Ответ написан
    Комментировать
  • Как сменить кодировку MySQL?

    @alexalexes
    В документации это есть:
    ALTER DATABASE ... CHARACTER SET ... COLLATE ...
    Версия 5.7
    Версия 8.0
    PS: Есть вариант сменить кодировку в коннекте phpMyAdmin, но как будет работать админка - надо смотреть.
    PPS: Бекапы никто не отменял, делайте данное действие на тестовой базе, либо при наличии копии.
    Ответ написан
    Комментировать
  • Как вывести в одной строке значения по нескольким строкам для одного ID с разными типами?

    @alexalexes
    select C.id_client,
      (select Cv.value from client_value as Cv where Cv.type = 'name' and Cv.id_client = C.id_client) as client_name,
      (select Cv.value from client_value as Cv where Cv.type = 'address' and Cv.id_client = C.id_client) as client_address
    from client as C
    Ответ написан
    Комментировать
  • Как правильно хранить секунды в базе даных?

    @alexalexes
    1. Храните в БД целочисленно;
    2. Передавайте на фронт целочисленно;
    3. На фронте преобразуйте целочисленную длительность в нужный формат любым доступным способом.
    Ответ написан
    Комментировать
  • Как выбрать все последние запись где recipient = 1?

    @alexalexes
    Чтобы выдернуть по одному сообщению от каждого пользователя, нужно проранжировать сообщения особым счетчиком с подзапросом, имитирующим оконную функцию row_number.
    select a.*
      from (select m.*, u.*, count(select *
                                                  from messages as m2
                                                    join users as u2 on u2.id = m2.author
                                                where m2.recipient = 1
                                                    and u2.id = u.id -- имитация клаузы partition by
                                                    and m2.id > m.id -- имитация клаузы order by  ... desc
                                   ) as row_num, -- аналог row_number через оконную функцию:
                                 -- row_number() over(partition by u.id order by m.id desc) as row_num 
      from messages as m
       join users as u on u.id = m.author
    where m.recipient = 1
    ) as a
    where a.row_num = 1 -- берем 1 строку по ранжированному счетчику в пределах каждого id пользователя
    Ответ написан
    Комментировать
  • Как узнать процент похожести текста?

    @alexalexes
    Самое простое - натравить поисковик на собственный ресурс запросом:
    "site:yousite.ru фрагмент фразы".

    PS: Сайт на момент запроса должен быть проиндексирован поисковиком.
    Ответ написан
    Комментировать
  • Можно ли написать SQL запрос с выражением в WHERE и в SELECT, но без повторения этого выражения и без вложенного SELECT?

    @alexalexes
    Но будет 2 вычисления MD5

    Выражения в select вычисляются после того, как отобраны строки по условиям в where.
    Если выходная выборка совсем небольшая, то оптимизировать работу функции в колонках не нужно - это экономия на спичках.
    Другое дело, если вы используете вычисляемую функцию в where. Чтобы она быстро работала есть 3 пути:
    1. Если функция вычисляется из данных, которые не зависят от контекста запроса, то нужно отказаться от этой функции, которая вычисляется при каждом запросе. Нужно хранить ее результат в отдельном атрибуте.
    2. Сделать индекс с использованием этой функции на атрибуте, тогда при использовании этой же функции в where сработает индекс.
    3. Если есть возможность, то нужно так преобразовать выражение where, чтобы все аргументы вычисляемой функции или выражения (складываем, вычитаем, умножаем делим, case-им) оказались входными параметрами или константами. Чтобы такое произошло, нужна обратная функция для md5 - reverse_md5. Но такой функции по математическим соображениям не существует.
    Иначе выражение бы выглядело так:
    where value = reverse_md5(:hache)
    Ответ написан
    1 комментарий
  • Почему не вносятся изменения в БД?

    @alexalexes
    1. Нужно использовать подготовленные запросы, а не делать по старинке через mysqli_real_escape_string.
    2. Если вы текстом написали запрос update, он еще не начнет выполнятся, где-то нужно запустить execute.
    if(isset($_POST['ref_button']) )
    {
        $send_ref = $_POST["send_ref"];    
        $stmt = msqli_prepare($con, "SELECT * FROM usertable WHERE referral= ?");
        mysqli_stmt_bind_param($stmt, 's', $send_ref);  // s - тип данных строка, i - число, если не тот тип данных - нужно исправить
        $result = mysqli_stmt_execute($stmt);
        if(mysqli_num_rows($result) > 0)
        {
            $stmt = msqli_prepare($con, "UPDATE usertable SET owner = ? WHERE email = ?");
            mysqli_stmt_bind_param($stmt, 'ss', $send_ref, $email); // s - тип данных строка, i - число, если не тот тип данных - нужно исправить
            $result = mysqli_stmt_execute($stmt);
        }
    }
    Ответ написан
    Комментировать
  • Как правильно составить запрс с двумя INNER JOIN?

    @alexalexes
    Очевидно, что нужны алиасы к таблицам, особенно к тем, которые несколько раз присоединяются.
    select psc.*, 
      pt1.`value` as `picture`, 
      pt2.`value` as `sort_pos`
     from `pref_site_content` as psc
        inner join `pref_site_tmplvar_contentvalues` as pt1 on pt1.`contentid` = psc.`id` and pt1.`tmplvarid` = 37 
        inner join `pref_site_tmplvar_contentvalues` as pt2 on pt2.`contentid` = psc.`id` and pt2.`tmplvarid` = 111 
        where psc.`id` not in (898, 899, 900, 902)
           and (`template` = 40 and `published` = 1) -- тут, возможно, тоже можно добавить, если в таблицах поля одинаково названы
    Ответ написан
  • Как осуществить выборку по данным из другой таблицы?

    @alexalexes
    Если хоть один из продуктов производителя будет занесен в некачественные, то этот запрос для любого продукта этого производителя будет выводить spent = 1.
    SELECT distinct p1.`id`,
     p1.`name`, 
     p1.`serial`, 
     case
      when (select count(*)
      from `products` as p2
       join `spent`  as s2 on p2.`id` = s2.`product`
                                         and s2.`issued` = 1 -- какое значение в качестве некачественного продукта ?
     where p2.`serial` = p1.`serial`) > 0
      then 1
      else 0
       end as `spent` 
    from `products` as p1
    Ответ написан
    4 комментария
  • Как правильно сделать выборку из БД из двух таблиц?

    @alexalexes
    ВЫБРАТЬ (атрибуты таблицы из таблицы комментариев) ИЗ таблицы комментариев ГДЕ КодПользователя НЕ СУЩЕСТВУЕТ в подзапросе (выборки из таблицы игнор-листа с таким же кодом пользователя).

    Переводите это предложение буквально на синтаксис SQL - получите решение.
    Ответ написан
  • Как произвести поиск по огромной базе моментально и вывести данные?

    @alexalexes
    каким методом можно моментально/быстро найти нужную запись с данными?

    Создаете индексы в базе данных по полям, которые используются в условиях запросов, и тогда будет скорость.
    PS: Конечно, лучше иметь представление, как ORM создает текст запроса SQL, а потом обращается с ним к СУБД. И как анализировать этот запрос SQL, иначе работа с ORM превращается в практику работы с магией.
    Ответ написан
    2 комментария
  • Как лучше хранить заявки из форм в БД?

    @alexalexes
    А что вы так боитесь сделать в таблице кучу колонок?
    У записи заявки будет type_id - тип заявки, и в зависимости от этого будет использоваться разный набор колонок. Все колонки, которые опционально будут использоваться только в определенных типах будут с допущением null значениями.
    В основную записываем допустим имя, а в сводную то что придёт в формате ключ - значение.

    Имеет право на жизнь, но для разработчика будет сложнее сопровождать запросы. При написании запроса, если нужно вытащить или сравнить свойства заявок, будете писать на каждый такой момент подзапрос. Если захочется получать выборки с результатами в одну строку, то придется тоже использовать кучу подзапросов, возможно, сам текст запроса нужно будет формировать динамически.
    С точки зрения производительности. План запроса усложнится, нужно будет использовать индексы.
    2. Сохранять данные в json

    Если не будете использовать свойства, которые запишите в json для связывания таблиц, и формирования условий выборки, то можно использовать.
    Ответ написан
  • Логирование mysql запросов с ошибками?

    @alexalexes
    Пройдите регуляркой по запросам:
    Вариант 1. Прописать всем insert явные названия полей.
    Найдите случаи, когда перед values (через пробелы или переносы строк) нет закрывающийся скобки - пропишите явно названия полей для вставки.

    Вариант 2. Подсчитать у всех insert для каждой table правильное кол-во значений для вставки.
    По названию таблицы перед values и по запятым после values в скобках определить случаи, когда кол-во полей не то.

    PS: Переведите проект на mysqli или PDO.
    PSS: вангую, что в проекте полный треш с bind-ами параметров - тоже отдельная задача.
    Ответ написан
    Комментировать
  • Как получить строки с большими значениями?

    @alexalexes
    Как мне получить 12 строчек,

    применить в запросе клаузу limit

    по убывающей значения numberofVisits


    применить в запросе клаузу order by с опцией desc по данному полу выборки.
    Ответ написан
    Комментировать