Писал я тут проект на mysql... Причем, не просто набор таблиц и связей - а "все по-взрослому", то есть: триггеры, хранимые, функции. То есть логика на стороне сервера.

Приступая к сему действу - тешил себя надеждой, что получится написать код, который может мигрировать на postgre вообще без правок. И - обломался. Нет такой радости. Более того, от версии к версии mysql в хранимых что-то добавляется (и, видимо, исчезет). А попытка перенести на postgre наталкивается на много ошибок, которые в основном относятся к специфике СУБД. Так что оставил эту идею и принялся ковырять mysql.

Из того, что заметил:

• динамический SQL в триггерах mysql нельзя. Совсем никак. Даже если вызывать процедуру, в которой есть динамический SQL - будет ошибка. Версия mysql 5.5.
• Если пишем триггер на таблицу в mysql, суть которого - изменение поля в той же таблице, на которой триггер, то синтаксис будет такой, например:

SET new.field = CONCAT (old.field, 'test')

где new и old - старое и новое состояние полей соответственно

А если так не написать - то ошибка вылезет.

• вообще динамический SQL себя ведет странно. Его как бы можно, но через prepare - execute.
• курсоры ведут себя странно. Есть таблица, из которой выбираю курсором, InnoDB. Пока она была предварительно заполнена - все хорошо. Как только начал в нее писать/удалять - так всё, ша. Задвоение данных. Об это плясал с бубном много... Но ничего лучше не придумал, как проверять в цикле на совпадение с предыдущим значением (костылииина, честно говоря). Но добиться от мускула адекватного поведения не смог. (Рестарт, сброс кешей, чистка кармы и прочее - не помогли).
• версию mysql можно посмотреть через SQL запрос SHOW version();

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

https://habrahabr.ru/post/328604/

Слйчайно наткнулся на FluentPDO - библиотеку для работы с PDO в PHP. Смысл в том, что библиотека является слоем абстракции между приложением, PDO-расширением PHP и SQL. Потестировав, понял, что удобно. А значит, есть смысл рассказать.

Для начала - ссылки.

1. FluentPDO на github: https://github.com/envms/fluentpdo
2. Подробная статья о том, как её использовать: https://www.sitepoint.com/getting-started-fluentpdo/

Смысл в том, что от программиста при использовании библиотеки требуется создать PDO-объект для работы с БД, передать его в fluentpdo, и дальше, используя встроенные методы - работать с SQL.

Например:

$pdo = new PDO("mysql:dbname=testdb", "testuser", "testpassword");
$fpdo = new FluentPDO($pdo);

Дальше "всё просто", например, простой select * from mytable where id=$userid будет выглядеть так:

$query = $fpdo->from('mytable')->where('id', $userid);

И чтобы обработать результат:

foreach($query as $row){
    
print $row['field1'] . ' -- ' . $row['field2'] . '<br>';
 }

Если нужны конкретные столбцы, то:

$query = $fpdo->from('mytable')
   ->select(array('field1', 'field3'))->where('id', $userid);

Ну и чисто для иллюстрации - insert:

$values = array('field1' => 'testval1', 
     'field2' => 'testval2', 
     'field3' => 'testval3'); 
     
$query = $fpdo->insertInto('mytable')->values(values); 
  
$insert = $query->execute();

Также поддерживаются все основные операции, включая разнообразные update, join, delete и т.д.

Кстати, по приведенной выше ссылке примеров сильно больше. Скажу, что я попробовал на тесте FluentPDO - мне понравилось. Как-то упрощает жизнь, с точки зрения того, что не надо писать километровый код и запросы выглядят пологичнее. Ну и вся мощь PDO - типа единого синтаксиса работы с разными БД, безопасности (использование placeholder), получается как бы на нашей стороне :)

Натолкнулся на любопытную особенность работы блокировки в mysql. Итак дано:

таблица table-1 и table-2. Из второй мы читаем, результат записываем в первую. Но читаем не просто - а достаточно хитро, примерно так

TRUNCATE TABLE table-1;
INSERT INTO table-1 SELECT * FROM table-2 WHERE flf2=(SELECT fld FROM table-2 WHERE fld3='value');

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

Соответственно, требуется обернуть вызовы блокировкой. Которая по идее должна выглядеть так:

LOCK TABLES table-1 WRITE, table-2 READ;
TRUNCATE TABLE table-1;
INSERT INTO table-1 SELECT * FROM table-2 WHERE flf2=(SELECT fld FROM table-2 WHERE fld3='value');
UNLOCK TABLES;

Но вот фигушки. В такой конструкции mysql упорно выдает ошибку ERROR #1100: Table 'table-2' was not locked with LOCK TABLES

Как оказалось, это не бага, это фича. Как написано в документации https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html - для каждого упоминения таблицы table-2 в SELECT требуется свой блокировочный алиас в LOCK'е. Вот так примерно:

LOCK TABLES table-1 WRITE, table-2 READ, table-2 AS t2 READ;
TRUNCATE TABLE table-1;
INSERT INTO table-1 SELECT * FROM table-2 WHERE flf2=(SELECT fld FROM table-2 AS t2 WHERE fld3='value');
UNLOCK TABLES;

В такой конструкции - всё будет работать.

И еще момент: когда используем LOCK TABLES, то в нем надо перечислить (заблокировать) все таблицы, к которым будет доступ на чтение / запись. В противном случае mysql выдаст Error #1100. Ту самую, что какая-то таблица не блокирована, ага.

Писал тут хранимую процедуру под mysql (что важно: с параметром типа string). Написал, запустил (из-под phpmyadmin)... получаю ошибку: #2014 - Commands out of sync; you can't run this command now. Я понимаю, когда ловлю такое из-под phpmyadmin - это еще куда ни шло... А вот когда запускаю из консоли, под рутом - получил ошибку "ERROR 1253 (42000): COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'". Ага, подумал я. И поменля кодировку всей базы на utf8. Не поверите: заработало! И из консоли, и из под pma.

Подозреваю, что для процедур, у которых на входе нетекстовый параметр - такой проблемы может не существовать. Но для текстового параметра - вот так вот.

Для памяти:

Изменяем кодировку для базы данных:

ALTER DATABASE `имя базы` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Изменяем кодировку для таблицы:

ALTER TABLE `tablename` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE `tablename` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Все естественно из-под рута.

Порой бывает интересно посмотреть, что там пишет в mysql твой скрипт (или, как вариант, развернутая на локальном хосте CMS, или... да что угодно).

Для этого было бы нехило иметь что-то типа монитора sql запросов. Кстати, для решения этой задачи не обязательно иметь дорогостоящее ПО или вообще специальное ПО - все можно сделать средствами Linux.

Как известно, в mysql за логирование отвечают дае глобальных настройки:

• general_log_file - там содержится имя файла лога
• general_log - там содержится факт (включено или нет логирование)

А еще есть такая замечательная команда tail - которая выводит на экнан последние строки лога... В общем, попытаемся собрать все это воедино.

Для начала я написал скрипт, который включает и выключает логирование "по запросу". (Можно было и без скрипта, но с ним удобнее). Сам скрит тут (после скачивания разархивировать и chmod +x). Запуск:

db-log.sh [start|stop]

соответственно, параметр start - включит логирование, stop - выключит.

В самом скрипте есть одна настройка (log_file) - место расположения файла. 

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

Ну и последняя часть марлезонского балета: открываем отдельный терминал, там пишем чтото типа:

sudo tail -f /tmp/mysql.log

И имеем на экране все запросы, которые сделаны к БД с момента старта логирования и запуска tail'a.

Как-то так. Просто, со вкусом, бесплатно, быстро =) Без красивостей, да, и в консоли - ну тут уж ничего не попишешь...

Пришлось тут переносить сайт с локального на нормальный хостинг. Все бы хорошо - да нет на нем предустановленных оболочек типа phpMyAdmin, а снаружи порты mysql, естественно, закрыты.

В общем, засада. Не знаю, кто как - а я нашел отличное решение.

Знакомитесь: Sypex Dumper 2.

Скрипт устанавливается в отдельную папку на сервере, и работает автономно. При запуске вводите имя и пароль на сервер mysql - он не хранит их нигде (что хорошо с позиции безопасности)? получаете доступ к собственно скрипту.

Собственно, он "заточен" под создание и восстановление бекапов в виде .sql файлов. Причем, по заверениям разработчиков, с большими по размерами файлами он справляется не менее легко, чем с маленькими. Не знаю, не проверял - но вот небольшие (120 Кб) .sql файлы, сгенерированные phpMyAdmin, он "съедает" на ура. Что, в общем от него и требовалось ))

Так что если кому надо - весьма рекомендую.

NB - на сайте есть платные версии, и есть бесплатная. Мне более чем хватило возможностей бесплатной, хотя в платной, наверное, тоже есть своя "фишка", и не одна.