Перебор записей БД по одной CakePHP 2.x

Тот редкий случай, когда не хочется выбирать все записи сразу, а есть желание выбирать по одной. Как в PHP с помощью mysql_query() / mysql_fetch_row().

В документации на Cake ничего похожего не описано, но возможность такая все-таки есть. По крайней мере в случае Mysql (и, видимо, других источников данных БД).

Модель CakePHP обращается к экземпляру класса Datasource для выборки данных, однако, на самом деле, это экземпляр класса DboSource, наследник Datasource. А у DboSource есть методы execute() и fetchRow(). Поэтому такая конструкция вполне работает:

Внутри модели, конечно, все тоже работает. Просто я из контроллера пробовал :) Как правильно, в соответствии с CakeWay, составлять строку запроса сами разберетесь, не новички.

mysqldump и AUTO_INCREMENT

Забавно, но mysqldump не может вывести структуру таблицы без того, чтобы не указать текущее значение AUTO_INCREMENT. Даже если не выводить данные, а только структуру.

Это помогает:

UPDATE нельзя INSERT

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

Эта тема пару месяцев назад обсуждалась в ЖЖ сообществе ru_php, но как-то вяло. Попробую этим небольшим постом обобщить разные методики.

Самый простой метод: сначала SELECT COUNT(*), а потом, по результатам, либо INSERT, либо UPDATE. Если речь идет об обновлении одной записи на этом методе можно остановиться.

Увы, если надо вставить много записей, этот метод начинает изрядно тормозить. Для такого случая у MySQL есть несколько разных методов.

REPLACE — наиболее очевидный и самый неудобный. Сервер пытается вставить новую строку, если возникает ошибка с повторяющимся уникальным ключом, запись удаляется и потом снова добавляется новая. Увы, при этом значения тех полей, которые обновлять не надо сбрасываются в дефолтные. Использовать эту команду можно, если обновляется полностью вся запись, что случается редко. Например, если в таблице есть колонка created, содержащая дату создания записи, то REPLACE уже не подходит. Кроме того, в случае, если большинство записей заменяется, а не добавляется, получается конструкция INSERT-(ошибка)-DELETE-INSERT — три запроса!

INSERT … ON DUPLICATE KEY UPDATE … — этот вариант сильно интереснее. Сервер пытается вставить запись, если получает ошибку c дублирующимся ключом, обновляет поля, указанные в после UPDATE. Работает отлично, в документации сказано, что эта конструкция специально задумана для вставки множества записей. Но пара неприятных моментов есть.

  1. Первый, как мне казалось, довольно экзотичный, но я с ним все-таки столкнулся. В конструкции INSERT можно в качестве источника данных использовать подзапрос SELECT. Но, в случае с INSERT … ON DUPLICATE KEY UPDATE, в подзапросе не должно быть группировки GROUP BY.
  2. Второй момент для многих неважен. Если записей для обновления сильно больше чем новых, то получается все равно много лишних обращений к БД — ведь вся конструкция это сочетание INSERT-(ошибка)-UPDATE. Лучше, чем с REPLACE, но все же…

Долгое время я пользовался UPDATE, а потом проверял mysql_affected_rows() (в CakePHP это $DataSource->lastAffected(). То есть проверял количество обновленных записей и, если получал 0, добавлял запись. У этого метода есть очень существенный недостаток: если среди данных для обновления встречаются две одинаковых строки, то при повторном UPDATE сервер, как самый умный, не обновляет запись. Соответственно число обновленных записей, affected rows, при построчном переборе, будет равно 0 несмотря на то, что такая строка в таблице есть. Соответственно решение о вставки новой строки будет ошибочным.

Устав мириться с ожидаемым, но неприятным поведением mysql_affected_rows() я порылся в документации на MySQL и нашел еще одну функцию: mysql_info(). Она выдает обычную строчку со статистикой последнего запроса к БД, причем для разных типов запросов строчки разные. Для UPDATE выглядит так:

Rows matched: 40 Changed: 40 Warnings: 0

Зато содержит чрезвычайно ценную информацию: количество записей, которые попадают под условие для обновления. Это те цифры, что после слова ‘matched:‘. Ну, регулярное выражение для получения циферок из строки сами напишите? ;-)

P.S. У меня ощущение deja vu — мне кажется, что я уже писал похожий пост. Перерыл дневники — нет такого. Или это черновик был…

Поиск по дате

Детские граблиВот уж не подумал бы, что на совершенно тривиальных запросах к MySQL можно наступить на детские грабли.

Я не люблю обновлять MySQL ни на своей машине, ни на сервере. Каждый раз гадаешь: «понадобится бэкап или или нет?» Поэтому имеет место некоторая несогласованность версий, на которую я не слишком сильно обращал внимание. До сегодняшнего вечера. Итак у меня в наличии на домашней машине MySQL 5.1.22-rc-community, win32. На сервере соответственно MySQL 5.0.45 из бокса CentOS 5.

Самая обычная таблица в БД, с самым обычным полем created типа DATETIME.

Однако запрос

работает на них совершенно по-разному!

На домашней машине все, как ожидается, возвращается количество записей за 12-е число. На сервере — 0 записей. Читать далее Поиск по дате