Ответы пользователя по тегу MySQL
  • Как сделать массовое обновление записей в mysql?

    @Akina
    Сетевой и системный админ, SQL-программист.
    WITH `cte` AS (
        SELECT 123 `id`, 1 `stat` UNION ALL
        SELECT 321     , 2        UNION ALL
     -- ...
        SELECT 456     , 8        UNION ALL
        SELECT 789     , 9
    )
    UPDATE `data`
    JOIN   `cte` USING ( `id` )
    SET `data`.`stat` = `cte`.`stat`;
    Ответ написан
    6 комментариев
  • Как сделать чтобы строка не вставлялась в БД, если не соблюдается формат поля?

    @Akina
    Сетевой и системный админ, SQL-программист.
    ALTER TABLE customer
    ADD CONSTRAINT CHECK(phone REGEXP '^[0-9]{3}-[0-9]{2}-[0-9]{3}$');

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=71012d...
    Ответ написан
    Комментировать
  • Нужно использовать триггеры или нет??

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

    Как я понимаю, мысль твоя бредёт вот в каком направлении: мы выполняем INSERT в основную таблицу, а триггер AFTER INSERT, имеющий доступ к сгенерированному значению автоинкрементного ключа именно свежесозданной записи, создаёт записи в связанных таблицах, используя именно это значение.

    Так вот именно эта мысль - она некорректная. Причём по совершенно элементарной причине. Данные для вставки в связанных таблицах (не поля связи - других полей) триггер тоже должен откуда-то взять. А вот корректно передать их достаточно непросто. Их нельзя вставить в блок данных основной записи - там просто места для этого нет, сервер перед выполнением INSERT проверяет входные данные на соответствие количества значений количеству переданных данных, соответствие типов, непревышение размеров и пр. Значит, данные придётся передавать за пределами запроса. Да, тут есть варианты, типа определённых пользователем переменных, временных таблиц и протчая - но всё это ненадёжно, сложно и совершенно несопровождаемо. Следует ещё учесть то, что триггеры - это конструкция неотключаемая. Либо она выполняется при абсолютно любом INSERT, и тогда есть определённые проблемы (триггер, предназначенный для работы при INSERT .. VALUES с единственным блоком данных, вряд ли корректно обработает вставку нескольких блоков данных, INSERT .. SELECT или LOAD DATA), либо для выполнения запросов на массовое добавление триггер надо удалять и потом пересоздавать - а если это происходит в конкурентной среде?

    В общем, триггер - неподходящая идея. Вот хранимая процедура (stored procedure) - это самое оно. Там можно реализовать логику ну практически любой сложности, с любыми обработками и проверками. Хотя и не следует забывать, что триггер выполняется наново для каждой отдельной вставляемой записи, так что его наличие, даже при достаточно простом и компактном коде, сильно скажется на скорости выполнения операций вставки, особенно вставки массовой.

    при регистрации пользователя, я получаю lastInserdId() и добавляю статичные данные для этого пользователя в различные таблицы

    Ненадёжно. Если между вставкой в основную таблицу и получением LAST_INSERT_ID() соединение будет разорвано и затем автоматически восстановлено, то полученное значение будет некорректным (вернее, получите NULL). А свежесозданная запись благополучно "повиснет в воздухе".
    Чисто теоретически, базовая таблица пользователей обязана обеспечивать уникальность записи даже без учёта синтетического первичного ключа (например, поле логина явно должно быть уникальным). А коли так, и с учётом того, что все значения для свежевставленной записи нам известны, можно использовать INSERT INTO slave SELECT 'literal', main.id FROM main WHERE uniquecolumn = 'new value'.

    PS. Не знаю, почему при обучении вставке данных все начинают с INSERT .. VALUES - как по мне, глубоко порочная практика. Сначала надо изучить и досконально освоить INSERT .. SELECT, и только потом упоминать про INSERT .. VALUES как более простой конструкции, применимой в частных случаях.
    Ответ написан
    1 комментарий
  • Возможно ли узнать последнюю дату изменения таблицы mysql?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Единственный действительно надёжный способ - это протоколирование. Например, триггер, фиксирующий время запроса в служебной таблице.

    Решение через INFORMATION_SCHEMA.TABLES.UPDATE_TIME - это плюс-минус лапоть. Для большинства движков - это время обновления файла таблицы в файловой системе, а вовсе даже не время выполнения последней записи по результатам выполнения последнего запроса на изменение данных (да-да, для долгого запроса это будет SYSDATE() последней изменённой записи, а не CURRENT_TIMESTAMP). Да и на дефолтном InnoDB не всё слава богу - кэширование запросто может добавить несколько секунд, а при отключенном file_per_table и на партиционированных таблицах так и вовсе не работает.
    Ответ написан
    1 комментарий
  • Как отсортировать запрос с UNION?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Если отформатировать запрос, то он выглядит так:
    SELECT * FROM `rsi` WHERE `tf`='4h'                         UNION 
    SELECT * FROM `rsi` WHERE `tf`='15m'                        UNION 
    SELECT * FROM `rsi` WHERE `tf`='4h'                         UNION 
    SELECT * FROM `rsi` WHERE `situation`='perek' AND `tf`='1d' UNION 
    SELECT * FROM `rsi`                                         UNION 
    
    SELECT * FROM `levels` WHERE `situation`='support' AND `tf`='4h' UNION 
    SELECT * FROM `levels` WHERE `tf`='1h' 
    
    ORDER BY `time` DESC LIMIT 0,20

    Сразу видно, что первые 4 подзапроса можно смело удалить.
    А потом надо смотреть на структуру таблицы rsi, ибо именно из неё будут взяты имена полей объединённого набора записей. Судя по ошибке, там просто нет поля time.
    Ответ написан
    1 комментарий
  • Как выбрать нужные записи из бд?

    @Akina
    Сетевой и системный админ, SQL-программист.
    SELECT *
    FROM table
    ORDER BY user_id = @user_id DESC, id DESC LIMIT 1

    Если заданный @user_id есть - вернётся соотв. запись. Если таких записей несколько - вернётся запись из них с максимальным id. Иначе вернётся просто запись с макс. id.

    Если же под "Если есть user_id вернуть запись с user_id" разумеется "ну хоть какой-то, лишь бы не NULL", то
    SELECT *
    FROM table
    ORDER BY user_id IS NULL, id DESC LIMIT 1
    Ответ написан
    Комментировать
  • Как сравнить такие столбцы?

    @Akina
    Сетевой и системный админ, SQL-программист.
    table1.id = SUBSTRING_INDEX(table2.id, '=', -1)
    Ответ написан
    Комментировать
  • Какую длину может содержать условие выборки по ID?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Есть ли ограничение?

    Есть. Ограничение на размер пакета.

    Кроме того, если список ID предварительно скинуть в temporary table primary key (id) engine=memory, то запрос при большом (~ более 10к значений) списке даже с учётом времени на создание и наполнение временной таблицы выполняется быстрее.
    Ответ написан
    Комментировать
  • Как синхронизировать столбцы в SQL?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Если связь между значениями - статическая, жёсткая и никогда не изменяемая, то используйте вычисляемое поле (generated column). Если значение поля, от которого зависит другое поле - числовое от единицы и далее, можно обойтись одним полем типа ENUM.

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

    Крайний случай - использование BEFORE INSERT/UPDATE триггеров.
    Ответ написан
    Комментировать
  • Как правильно будет сделать JOIN?

    @Akina
    Сетевой и системный админ, SQL-программист.
    andry33822, огорчу. Описанная мной в комментарии к вопросу процедура требует ВСТАВКИ записи в таблицу users и ОБНОВЛЕНИЯ существующей записи в таблице users_apikey. То есть INSERT в одну таблицу + UPDATE другой таблицы.

    В один запрос это никаким образом не укладывается. Но задачу можно решить. Триггером.

    CREATE TRIGGER tr_assign_apikey_to_user
    AFTER INSERT 
    ON users
    FOR EACH ROW
    UPDATE users_apikey SET username = NEW.username WHERE username = '' LIMIT 1;


    DEMO fiddle

    Правда, триггер простейший, и ничего не проверяет. По-хорошему надо как минимум проверить, что свободный apikey - есть, и если нет - то сгенерировать ошибку добавления юзера. Ну и не исключаю дополнительных условий - например, мне лично запись в apikey, не имеющая вменяемого значения в поле namekey, как-то не нравится.
    Ответ написан
    Комментировать
  • Что происходит при повторном запуске команды MySQL source?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Что происходит при повторном запуске? Проверяется наличие уже импортированных записей, они пропускаются и импортируется отсутствующие?

    Никаких проверок. Запуск с самого начала.

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

    В общем, как обычно. Если возникла ошибка, надо не пытаться запустить повторно, типа авось пролезет (почти наверняка - не пролезет), а разбираться, почему возникла ошибка, и устранять причину.
    Ответ написан
    Комментировать
  • Как восстановить базу sql из файлов /var/lib/mysql/* на другом сервере mysql?

    @Akina
    Сетевой и системный админ, SQL-программист.
    При попытки создать mysqldump база выдаёт ошибку:
    Illegal mix of collations (utf8_unicode_ci,COERCIBLE) and (utf8_general_ci,COERCIBLE) for operation 'locate'

    Скорректируйте @@collation_connection.

    Задача сделать копию битой БД mysql ... как из файлов "холодного резерва" создать sql-файл или как эту базу развернуть на другом сервере mysql?

    А смысл? если подключить файлы данных в новом местоположении, ошибки никуда не денутся.

    А так - создать таблицы в новом местоположении. Структура должна совпадать с точностью до запятых, и версия сервера тоже. Самое разумное - сделать бэкап чисто структуры, без данных, и развернуть. А потом DISCARD/IMPORT TABLESPACE, либо замена файлов при остановленном сервисе и последующий запуск с лечением и перезаписью метаданных и статистики.
    Ответ написан
  • Как правильно импортировать много данных из json в mysql?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Artikul2,
    MySQL 8.0.28-0ubuntu0.20.04.3


    Не делай ничего с этим JSON. Отдай его как есть на MySQL - у него парсинг получится лучше...
    INSERT INTO test (name, position, city, postcode, entered_at, income)
    SELECT data ->> '$[0]' name,
           data ->> '$[1]' position,
           data ->> '$[2]' city,
           data ->> '$[3]' postcode,
           STR_TO_DATE(data ->> '$[4]', '%Y\/%m\/%d') entered_at,
           REPLACE(SUBSTRING(data ->> '$[5]' FROM 2), ',', '') income
    FROM JSON_TABLE(@data,
                    '$.data[*]' COLUMNS (data JSON PATH '$')) jsontable


    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8c5e1c...

    Если надо отсеивать дубликаты - создать соотв. уникальный индекс, и использовать INSERT ODKU, REPLACE INTO либо INSERT IGNORE.
    Ответ написан
  • Как сменить пользователя в MySQL?

    @Akina
    Сетевой и системный админ, SQL-программист.
    Как сменить пользователя в MySQL?

    В текущем соединении - никак. Только (закрыть это и) создать новое соединение, с использованием другой учётной записи.
    Ответ написан
  • Как работать с готовой базой данных sql?

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

    В комментариях дампа указано, что источником дампа послужил сервер MySQL версии 8.0.28. Поэтому восстанавливать следует на сервере MySQL, причём крайне желательно на версии не ниже 8.0.4. При восстановлении на более младшей версии или на частично совместимой MariaDB возможны проблемы при восстановлении, которые потребуют ручной корректировки дампа - а для этого нужны определённые знания, которых, скорее всего, нет.

    Для восстановления на локальном MySQL-сервере самое разумное - загрузить клиент командной строки и в нём:
    • создать новую БД (CREATE DATABASE databasename;) (в комментариях дампа указано имя history_futures_binance, так что разумно использовать именно его, если такой БД на сервере нет)
    • сделать её текущей (USE databasename)
    • дать команду на развёртывание дампаSOURCE 'x:\path\filename.sql'.

    Скорее всего, всё будет выполнено без ошибок, и в результате БД будет восстановлена. Если же при выполнении возникнут проблемы, будет выведено какое-то сообщение об ошибке - и в зависимости от него нужно будет предпринимать какие-то корректирующие действия.
    Ответ написан
    Комментировать
  • Как удалить из трех или более таблиц mysql по одному id?

    @Akina
    Сетевой и системный админ, SQL-программист.
    DELETE 
    FROM userdata, moneys, user_roles 
    USING ( SELECT 525300849082105856 userid ) criteria
    LEFT JOIN userdata USING (userid)
    LEFT JOIN moneys USING (userid)
    LEFT JOIN user_roles USING (userid);
    Ответ написан
    1 комментарий
  • Почему LIKE разделяет _E на _ и Е - mysql?

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

    LIKE operator

    With LIKE you can use the following two wildcard characters in the pattern:

    • % matches any number of characters, even zero characters.
    • _ matches exactly one character.
    Ответ написан
    1 комментарий
  • Как посчитать общую длительность времени между строками определенной выборки в MySQL?

    @Akina
    Сетевой и системный админ, SQL-программист.
    WITH cte AS ( SELECT *, 
                         LAG(`datetime`) OVER (PARTITION BY login ORDER BY `datetime`) lag_datetime, 
                         LAG(event) OVER (PARTITION BY login ORDER BY `datetime`) lag_event 
                  FROM history )
    SELECT login, SUM(TIMESTAMPDIFF(MINUTE, lag_datetime, `datetime`)) duration
    FROM cte
    WHERE (event, lag_event) = (2,1)
    GROUP BY login;

    https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=398... (исходные данные подправлены).
    Ответ написан
    1 комментарий
  • Как использовать maxmind GeoLite2 на mysql 5.5, где нет inet6_aton?

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

    Не вижу проблемы. Импорт не использует inet6_aton. Соответственно после импорта можно просто удалить записи с IPv6-адресами. А затем ещё и изменить тип поля на VARBINARY(4).
    Ответ написан
  • Как группировать данные, если у них есть уникальные id?

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

    Вот есть некая группа, в ней все off_id одинаковы, но id различны. После группировки все эти записи будут сгруппированы в одну. А теперь, внимание, вопрос: ну и какое одно значение id из всех возможных сервер должен вернуть? не знаете? так ведь и сервер не знает... вот он и генерирует ошибку.

    Уберите id из выходного набора. Либо явно и чётко скажите серверу, какое именно значение из всех возможных в группе он должен вернуть. Наименьшее? требуйте MIN(id). Наибольшее? MAX(id). Хоть какое, любое? ANY_VALUE(id).
    Ответ написан
    Комментировать