|
|
Задавайте вопросы, мы ответим
Вы не зашли.
Здравствуйте!
mysql 5.6.10
MyIsam
Вопрос понимания, как mysql делает update, а точнее где она подготавливает новые значения для полей - во внутренних переменных или непосредственно по указателю на живые данные.
Пример вопроса
мультитабличный update, но таблица одна и та же.
update price p1,price p2 set p1.a=p1.a+3, p2.a=p2.a+1 where p1.id=1 and p2.id=p1.id;
В результате данного запроса поле 'a' таблицы price изменится не на 4, а на 3 или на 1, в зависимости от того в каком прядке запрос обработает поля в set.
Получается, что запись p1.a=p1.a+3 прибавляет 3 не к живым данным в таблице, а к какой то переменной, в которую заранее было помещено значение 'a' из таблицы p1 ? и уже потом эта внутренняя переменная увеличенная на 3 запишется в таблицу?
И основной вопрос, можно ли данный прием использовать как легальный метод получения старых(до update) значений, например:
update price p1,price p2 set p1.a=300, p1.b=p2.a where p1.id=1 and p2.id=p1.id;
т.е. данным update мы в одном запросе и меняем поле и сохраняем его первоначальное значение в другом поле, не боясь того что последовательность выполнения не гарантирована mysql слева на право?
Неактивен
чтобы выполнить ваш milti update сначала требуется найти строки удовлетворяющие условию, т.е. фактически выполнить
select * from price p1,price p2 where p1.id=1 and p2.id=p1.id;
и уже на основе этих данных выполнить update
сначала обновится p1 и в таблицу price будет записано значение исходное+3
потом обновится p2 и в таблицу запишется исходное+1, т.к. p2.a у нас не изменилось.
в этом легко убедиться с помощью триггера, который будет логировать все изменения:
http://dev.mysql.com/doc/refman/5.7/en/update.html написал:
Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.
Неактивен
Добрый день!
Да, действительно, тест с триггером очень наглядный, видно что он залогировал оба изменения.
Просто самого понимания не было как делается апдейт, что предварительно выбираются изменяемые поля для каждого вхождения таблицы и только после этого делается изменения исходных ранее считанных значений, а не только что измененных в другом экземпляре таблицы.
Я просто для облегчения примера привел мультиапдейт с одной и той же таблицы, а так конечно в апдейте будет много таблиц и слева на право уже не работает, поэтому и искал варианты гарантированно узнать старое значение до возможного изменения.
Нашел так же способ через пользовательские переменные, с инициализацией в условии
update a=300, b=@var_a where id=1 and least(0,(@var_a:=a))=0
Но про переменные пользователя как то страшно везде описано, вплоть до игнорирования их анализатором в какие то моменты, а про множественный апдейт одной таблицы тоже нигде не написано и может быть пример с триггером не будет работать в других версиях если это конечно не правило работы DB.
Если это правильное поведение DB и в последствии не сломается, то это отличное решение, Спасибо!
Я смотрю в вашем примере с триггером тестировалось на MariaDB, слышал что это параллельное развитие Mysql. Скажите пожалуйста, безболезненно и без переделок можно перенести проект с Mysql на MariaDB? И .net connector и C++ dll все для нее такое же?
Неактивен
прикольное решение 
с переменными проблемы возникают когда их используют не в правильном месте, например, вычисляют в части where и результат может зависить от того по какому индексу выполняется в данный момент запрос. такой пример хорошо проиллюстрирован в теме http://sqlinfo.ru/forum/viewtopic.php?id=1742
в целом про переменные рекомендую посмотреть Оптимизация запросов MySQL с использованием пользовательских переменных
ваше решение надежно пока single update и условию id=1 соответствует 1 строка.
триггер будет работать однозначно всегда. есть только один недостаток - операция не атомарна, т.е. возможен вариант сбоя (вырубили свет, .. ) когда основной update выполниться а триггер нет или наоборот.
переход на mariadb и обратно должен пройти без проблем, отличия минимальны и зачастую касаются названия службы, коннекторы теже. в доке подробно описано https://mariadb.com/kb/en/mariadb/moving-to-mariadb/
Неактивен
На самом деле такой update нужен для корректного вычитания баланса покупателя при покупке услуги, а баланс сделан двумя отдельными полями - целая часть(поле a) и копеечная(поле b).
А параллельный update на балансе клиента м.б. как такой же update так и другой, вертающий на баланс сумму.
Допустим нужно вычесть с баланса клиента сумму за услуги в размере 100.99
update client t1, client t2, rules t3, perm t4
set t1.a=t1.a-100-case when t1.b<99 then 1 else 0 end,t2.b=case when t2.b>=99 then t2.b-99 else 100-(99-t2.b) end
where t1.id=1 and t2.id=t1.id and (t1.a*100+t1.b)>=10099 and t3.id=t1.id and (дополнительно проверка по t3) and t4.id=t3.id2 and (дополнительно проверка по t4);
В данном update целая часть баланс клиента меняется в таблице t1, а копеечная часть баланса в t2. Сделано это потому, что если менять целую часть и копейки в t1 а проверять на оригинальные копейки в t2, то получается t2 будет залочена не для записи а только для чтения. И кто знает как тут поступит mysql.
таблицы rules t3, perm t4 - это настроечные и разрешающие по клиенту покупать ту или иную услуги и участвуют только в условии.
во всех таблицах всегда выбирается только по одной строке.
Вот такой update он один раз залочит таблицу client для изменения и оба изменения будут атомарной операцией и между обоими изменением t1 и t2 в этом запросе не проскочит больше ни одного другого update на таблице client?
Отредактированно Lioha (13.04.2016 17:14:15)
Неактивен
Lioha написал:
В данном update целая часть баланс клиента меняется в таблице t1, а копеечная часть баланса в t2. Сделано это потому, что если менять целую часть и копейки в t1 а проверять на оригинальные копейки в t2, то получается t2 будет залочена не для записи а только для чтения. И кто знает как тут поступит mysql.
Нормально поступит.
Лочить t2 и писать в неё никто не будет, т.к. такой таблицы нет. Есть только таблица client, которая и будет заблокирована на запись.
Можете смело менять обе части в t1, используя t2 для проверки первоначальных значений. В этом случае у вас будет 1 update, а не 2 как сейчас.
multiple update не гарантирует порядок присвоения, но, имхо, это относится к случаю, когда меняются данные в разных таблицах. уточните это на bugs.mysql.com и возможно получится упростить запрос, используя таблицу client один раз.
Неактивен
"...Можете смело менять обе части в t1, используя t2 для проверки первоначальных значений..."
Т.е. если в update имеются два экземпляра одной таблицы, из которых на одну нужно накладывать блокировку записи, а на вторую блокировку чтения, то анализатор(или движек Mysql) наложит на эту таблицу всего лишь один раз и одну блокировку самую строгую - блокировку на запись, а не по порядку - т.е. сначала на чтение, проинициализирует(считает) нужные поля, используемые в запросе, отпустит блокировку и потом наложит блокировку уже на запись?
"...В этом случае у вас будет 1 update, а не 2 как сейчас...."
кстати, а если даже как сейчас(2 раза апдейтится таблица), то даже если обновляемые поля и содержатся в каких то индексах, разве индекс будет два раза обновляться(перестраивается), а не один раз в конечной стадии выполнения всего запроса? Да и сами новые данные вроде тоже один раз фиксироваться будут?
Или это имеется ввиду дополнительная нагрузка только на триггер, если он есть, т.к. он то точно 2 раза отработает в таком update.
"...multiple update не гарантирует порядок присвоения, но, имхо, это относится к случаю, когда меняются данные в разных таблицах...."
Это очень интересно, спасибо, попробуем выяснить. Но мы немного будем модернизировать запрос и там кроме
update client t1, client t2, rules t3, perm t4
появится еще одна табличка новая, в которой тоже будет делаться изменение, все в том же update, но там тоже будет выбираться только одна строка.
Т.е. получается, что multiple update в котором уже не одна таблица изменяема.
Это, кстати, надеюсь, никак не повлияет на "тактику" использовать два раза одну и ту же таблицу для получения старых данных.
И спасибо большое, что отвечаете!
Неактивен
Lioha написал:
Т.е. если в update имеются два экземпляра одной таблицы, из которых на одну нужно накладывать блокировку записи, а на вторую блокировку чтения, то анализатор(или движек Mysql) наложит на эту таблицу всего лишь один раз и одну блокировку самую строгую - блокировку на запись, а не по порядку - т.е. сначала на чтение, проинициализирует(считает) нужные поля, используемые в запросе, отпустит блокировку и потом наложит блокировку уже на запись?
Да, именно так - один раз.
Lioha написал:
кстати, а если даже как сейчас(2 раза апдейтится таблица), то даже если обновляемые поля и содержатся в каких то индексах, разве индекс будет два раза обновляться(перестраивается), а не один раз в конечной стадии выполнения всего запроса? Да и сами новые данные вроде тоже один раз фиксироваться будут?
индекс перестраивается при каждом update, если не указан DISABLE KEYS. Делает и его неявно multi update одной и той же таблицы? Не знаю, но полагаю, что нет.
Фиксация на диск? Это зависит от хранилища. В myisam каждый раз.
Lioha написал:
Т.е. получается, что multiple update в котором уже не одна таблица изменяема.
Это, кстати, надеюсь, никак не повлияет на "тактику" использовать два раза одну и ту же таблицу для получения старых данных.
нет, не повлияет
Lioha написал:
И спасибо большое, что отвечаете!
Да, не за что. Если выясните о порядке присвоения в multi update одной табицы, то отпишите.
Неактивен
