InnoDB это транзакционный, реляционный движок работающий на основе MySQL сервера. Начиная с 2001 года он поставляется в стандартной сборке, а с версии 5.1 может устанавливаться в качестве плагина (без необходимости перекомпилировать ядро сервера). Синтаксис очень простой.
START TRANSACTION;
...
COMMIT; -- или же ROLLBACK; если что-то пошло в логике не так
Про определение
Определение транзакционности и реляционности значат во-первых значат полноценную связанность таблиц через FK и как следствие - целостность данных при удалении рядов. С MyIsam как известно приходилось вручную удалять связанные данные в нескольких таблицах, в InnoDB - каскадное удаление одним запросом. Во-вторых поскольку для БД немыслимы параллельные версии данных как в SVN и некому эти версии объединять в одну ветку, но при этом необходима параллельная работа нескольких процессов (пользователей) с одними данными, то в качестве решения становится транзакции.
Очередь из запросов-автомобилей теперь пополняется атомарной транзакцией-автобусом. Естественно это плохо, поскольку чем длиней и дольше выполняется транзакция тем больше параллельных процессов будут ждать его. Для ускорения работы создаются остановки - типы и уровни блокировки данных. Для InnoDB по умолчанию это блокирование на уровне строки (по PK), тогда как в MyIsam атомарная операция блокирует всю таблицу.
Предлагаю решить интересную SQL-задачу. Думаю у среднего девелопера она займёт пол часа или больше (я же сразу спросил у SQL-гуру).
Условия
Возникла интересная задача при переходе с MyIsam на InnoDB. Как известно, полнотекстовый поиск у последнего движка отсутсвует, поэтому было два решения - делать обычное зеркало на MyIsam где был бы ктому же и очищенный от html текст (кстати так сделано в движке этого блога), либо более глубокая индексация с разбивкой на слова - я упоминал такую идею когда писал про морфологический поиск.
Второй вариант с одной стороны давал кучу головной боли с индексацией, очисткой, держанием многотысячной таблицы, логике поиска - задачи которые должна решать СУБД или расширенный индексатор типа sphinx. С другой стороны - выглядит инновативно, теоретически можно уже организовывать синонимы для тех или иных слов на уровне индекса или искать с учётом расстояния между словами. Вобщем гугл 2. На вскидку такой способ оказался в 6 раз быстрей InnoDB.
Сказано - сделано. Получилось 3 основных таблицы + собственно таблицы которые индексируются.
- words - id, word (varchar, уникальный)
- fields - id, field (varchar, уникальный; значения например "title", "content"), object_id (int; указывает на id статьи),
- word_fields - word_id, field_id, pos (int, положение в тексте)
Иногда хочется быстро вычислить сколько дней до дня рождения пользователя. Вы все видели это в Одноклассниках и ЖЖ. Это можно делать в запросе например таким образом для mysql 5
SELECT
TIMESTAMPDIFF(YEAR,users.birth_date,NOW()) years_old,
TIMESTAMPDIFF(DAY, NOW(), DATE_ADD(users.birth_date, INTERVAL 1+TIMESTAMPDIFF(YEAR,users.birth_date,NOW()) YEAR)) next_birth_days
FROM users
Нашёл в своём стареньком коде, может кому полезно будет..
Коллега Юра скинул версию для старенькой mysql 4, может это даже лучше:
SELECT
IF(
DATE_ADD(birth_date, INTERVAL (DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(birth_date,'%Y')) YEAR) > NOW(),
(DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(birth_date,'%Y')) -1,
(DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(birth_date,'%Y'))
) as years_old,
DATEDIFF(
DATE_ADD (birth_date, INTERVAL IF(
DATE_ADD(birth_date, INTERVAL (DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(birth_date,'%Y')) YEAR) > NOW(),
(DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(birth_date,'%Y')) ,
(DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(birth_date,'%Y')) + 1
) YEAR ),NOW()) as next_birth_days
FROM users
Есть таблица меню в БД с полем порядка order_id, но порядок неправильный и надо порядок обновить по алфавиту другого поля title. Сортировка по полю title исключается, потому что таблица представляет из себя иерархическое меню, где порядок может менять пользователь устанавливая order_id. Использовать отдельно серверную обработку (php) тоже невыгодно - лишнее разделение бизнес логики если это можно сделать в БД, да и на случай больших объемов данных это займёт больше ресурсов.
Решение
В MySQL к сожалению отсутсвует такая возможность ROWNUM, RANK(),
ROW_NUMBER() как в ORACLE или MSSQL. Присутсвие _rowid никак не влияет
на ситуацию. В нашем случае можно использовать переменные, введённые с пятой версии (или раньше):
SET @rank=0;
SELECT @rank:=@rank+1 AS rank, id FROM menu;
Для обновления такой таблицы через саму себя у меня не получилось, поэтому я просто скопировал таблицу под другим именем и выполнил такой запрос:
SET @rank=0;
UPDATE `menu` SET order_id=(
SELECT @rank:=@rank+1 FROM `menu2` WHERE `menu`.id=`menu2`.id LIMIT 1
) WHERE parentID=0 ORDER BY `title` ASC;
Подготовленные запросы имеются в MySQL начиная с 4.1 и нужны по трём причинам.
- Скорость. Если вы выполняете однообразные запросы, то mysql парсеру каждый раз приходится выполнять распознавание - какой тип запроса, какие данные передаются и тому подобное. Если сделать прототип запроса, а в последствии передавать только данные, то ясное дело что это скажется на скорости.
- Передача данных. Мало того что для подобных запросов передача бинарных данных не нуждается в конвертировании в строку, так и то что стандартное ограничение на один запрос имеет около 1 мб можно подвинуть благодаря тому что каждый кусок данных передаётся отдельным запросом, а не одним общим (INSERT скажем).
- Безопасность. Благодаря отделению данных от запроса вы можете обезопасить себя от SQL injection .
PREPARE some_statement_1 FROM "INSERT INTO my_table (`some_HTML`, `some_title`) VALUES ('?','?')";
SET @someparam_1 = "HTML SOURCE";
SET @someparam_2 = "TITLE HERE";
EXECUTE some_stament_1 USING @someparam_1,@someparam_2;
DEALLOCATE PREPARE some_statement_1;
Всё достаточно просто - объявил запрос, вместо переменных поставил символы вопросов, потом по порядку этих символов объявил переменные , выполнил запрос и удалил кэширование (которое только планируется сделать).
Использование подобной техники через PHP на низком уровне занимается PDO extension и mysqli правда порой не совсем удобным способом, ну а если хочется по-старинке эти запросы генерировать самому, то можно попробовать огромную PEAR:DB или DbSimple
