affected rows 1 что значит
mysql_affected_rows — Возвращает число затронутых прошлой операцией рядов
Данное расширение устарело, начиная с версии PHP 5.5.0, и будет удалено в будущем. Используйте вместо него MySQLi или PDO_MySQL. Смотрите также инструкцию MySQL: выбор API и соответствующий FAQ для получения более подробной информации. Альтернативы для данной функции:
Описание
Список параметров
Возвращаемые значения
Если последний запрос был DELETE без указания WHERE и, соответственно, таблица была очищена, функция вернёт ноль во всех версиях MySQL до 4.1.2.
При использовании UPDATE, MySQL не обновит колонки, уже содержащие новое значение. Вследствие этого, функция mysql_affected_rows() не всегда возвращает количество рядов, подошедших под условия, только количество рядов, обновлённых запросом.
Запрос REPLACE сначала удаляет запись с указанным первичным ключом, а потом вставляет новую. Данная функция возвращает количество удаленных записей вместе с количеством вставленных.
Примеры
Пример #1 Пример использования mysql_affected_rows()
Результатом выполнения данного примера будет что-то подобное:
Пример #2 Пример использования mysql_affected_rows() с транзакциями
Результатом выполнения данного примера будет что-то подобное:
Примечания
Замечание: Транзакции
При использовании транзакций mysql_affected_rows() нужно вызывать после запросов INSERT, UPDATE, DELETE, но не после COMMIT.
Замечание: Запросы SELECT
Замечание: Каскадные внешние ключи
mysql_affected_rows() не подсчитывает ряды, неявно измененные ограничениями ON DELETE CASCADE и/или ON UPDATE CASCADE.
Смотрите также
mysql_affected_rows
mysql_affected_rows — Возвращает число затронутых прошлой операцией рядов
Данный модуль устарел, начиная с версии PHP 5.5.0, и удалён в PHP 7.0.0. Используйте вместо него MySQLi или PDO_MySQL. Смотрите также инструкцию MySQL: выбор API. Альтернативы для данной функции:
Описание
Список параметров
Возвращаемые значения
Если последний запрос был DELETE без указания WHERE и, соответственно, таблица была очищена, функция вернёт ноль во всех версиях MySQL до 4.1.2.
При использовании UPDATE, MySQL не обновит колонки, уже содержащие новое значение. Вследствие этого, функция mysql_affected_rows() не всегда возвращает количество рядов, подошедших под условия, только количество рядов, обновлённых запросом.
Запрос REPLACE сначала удаляет запись с указанным первичным ключом, а потом вставляет новую. Данная функция возвращает количество удалённых записей вместе с количеством вставленных.
Примеры
Пример #1 Пример использования mysql_affected_rows()
Результатом выполнения данного примера будет что-то подобное:
Пример #2 Пример использования mysql_affected_rows() с транзакциями
Результатом выполнения данного примера будет что-то подобное:
Примечания
Замечание: Транзакции
При использовании транзакций mysql_affected_rows() нужно вызывать после запросов INSERT, UPDATE, DELETE, но не после COMMIT.
Замечание: Запросы SELECT
Замечание: Каскадные внешние ключи
mysql_affected_rows() не подсчитывает ряды, неявно изменённые ограничениями ON DELETE CASCADE и/или ON UPDATE CASCADE.
Смотрите также
User Contributed Notes 11 notes
So if you use one SQL request to insert several rows at a time, and some are inserted, some are just updated, you won’t get the real count.
SCENARIO
1. You’re using MySQL 4.1x with foreign keys.
2. You have table t2 linked to table t1 by a CASCADE ON DELETE foreign key.
3. t2 has a UNIQUE key so that duplicate records are unacceptable.
3. You have a REPLACE query on t1 followed by an INSERT query on t2 and expect the second query to fail if there’s an attempted insert of a duplicate record.
PROBLEM
You notice that the second query is not failing as you had expected even though the record being inserted is an exact duplicate of a record previously inserted.
CAUSE
When the first query (the REPLACE query) deletes a record from t1 in the first stage of the REPLACE operation, it cascades the delete to the record that would be duplicated in t2. The second query then does not fail because the «duplicate» record is no longer a duplicate, as the original one has just been deleted.
calling mysql_affected_rows(null)
is not the same that calling mysql_affected_rows()
if($link)
$n=mysql_affected_rows($link);
else
$n=mysql_affected_rows();
Note that when the CLIENT_FOUND_ROWS connection flag was used, affected_rows returns the number of rows matched by the WHERE condition of an UPDATE query, even if the query doesn’t actually change those rows. I.e. for
INSERT INTO t(id, val) VALUES (1, ‘x’);
UPDATE t SET val = ‘x’ WHERE />
the number of affected rows will be 0 normally but 1 with CLIENT_FOUND_ROWS.
Here’s a little function I’ve been using for a while now, pass it two parameters (action command (1 or 0 see notes)) and a sql statement.
I’ve found this invaluable when trying to tie down large amounts of updates to a table, using this you can easily see where a query was successfully executed and the number of rows are affected, or where there are problems and a statement has failed for example.
Раскрываем магию MySQL или о строгости и мягкости MySQL
Очень часто в интернете встречаюсь со статьями, в которых приводят кучу примеров с якобы странным поведением MySQL по сравнению с другими БД. Чтобы стало понятно, о чём я говорю, приведу несколько примеров:
1. Деление на ноль возвращает NULL вместо ошибки
2. Выход за диапазон допустимых значений неявно приводит число к допустимому значению, а не к ошибке и откату транзакции
3. Вставка неверного типа данных также приводит к неявному преобразованию и успешному выполнению операции
Таких примеров я могу привести огромное число, но цель статьи не сделать очередное собрание высосанных из пальца примеров, а объяснить, почему происходит то или иное действие. Вся эта мистика MySQL давно описана в документации и легко объяснима, в чём вы сможете убедиться сами, прочитав статью до конца.
Для меня эта первая статья на хабре, поэтому я старался писать дотошно подробно. Уверен, что она будет полезна всем, кто работает с MySQL. Большую помощь в написании статьи оказала подготовка к сдаче на сертификат разработчика MySQL, а точнее книга «MySQL Certification Study Guide».
Итак, мой друг, начнём!
SQL Modes
SQL modes – это настройка поведения работы сервера MySQL, состоящая из режимов, каждый из которых контролирует какой-либо один аспект обработки запроса.
Возможности SQL mode:
1. Устанавливает строгую или мягкую проверку входных данных
2. Включает или отключает следование SQL стандарту
3. Обеспечивает лучшую синтаксическую совместимость с другими БД
По сути, SQL mode очень мощный механизм тюнинга БД, позволяющий гибко манипулировать обработкой запросов и уведомлениями MySQL.
Прежде чем мы перейдём к последующей теории, вы должны строго-настрого уяснить, что изменение режима SQL mode после создания и вставки данных в партиционные таблицы (partitioning tables) может привести к существенным изменениям в поведении таких таблиц, что, в свою очередь, может привести к потере или повреждению данных. Настоятельно рекомендуется, чтобы вы никогда не изменяли SQL режим после создания партиционных таблиц.
При репликации партиционных таблиц, отличающиеся параметры SQL mode на Primary и Slave MySQL серверах также может привести к проблемам. Для стабильной работы репликации между серверами, настройки SQL mode должны быть идентичными.
Теперь, после того, как вы осознали всю ответственность в использовании SQL режимов, перейдём к его сути.
Контроль текущего SQL режима происходит через системную переменную sql_mode. Для задания значения используется команда SET. Ниже представлены возможные варианты установки данного режима.
1. Соответствует значению по умолчанию для только что установленной БД (никаких специальных режимов не установлено). Кавычки являются обязательными.
2. Установка одного режима sql_mode. Возможно два варианта – с кавычками и без них.
3. Установка нескольких режимов sql_mode. Указание кавычек является обязательным!
Несмотря на то, что названия режимов регистронезависимые, я для удобства прочтения буду везде в статье писать их в верхнем регистре.
В примерах выше мы устанавливали режимы для текущей сессии, но если вы обладаете привилегиями суперпользователя, то можно задать глобальный режим для всего сервера и всех текущих коннектов, указав параметр GLOBAL. Полный синтаксис установки sql_mode выглядит так:
Для просмотра текущих значений глобального и сессионного режима сервера используйте следующие запросы:
Краткий справочник режимов
ANSI_QUOTES
Заставляет сервер интерпретировать двойную кавычку ( » ) точно также, как и обратную кавычку ( ` ), при этом она теряет способность обрамлять строки. Как можно было догадаться, этот режим заставляет MySQL приблизиться к SQL стандарту.
IGNORE_SPACE
По умолчанию, между функцией и открывающейся круглой скобкой нельзя устанавливать пробелы. Включение этого режима разрешает серверу игнорировать пробелы, но платой за такую вольность станет то, что все функции станут зарезервированными словами, а значит, при совпадении имени столбца с именем функции придётся в обязательном порядке экранировать такой столбец.
ERROR_FOR_DIVISION_BY_ZERO
При делении на ноль в строгом режиме генерируется ошибка, а нестрогом — предупреждение при выполнении операторов INSERT или UPDATE. Без этого параметра деление на ноль возвращает предупреждение и вставляет в таблицу NULL. Про строгость будет сказано в следующем режиме, пока постарайтесь абстрагироваться.
В приведённых примерах мы получали исключительно предупреждения, потому что строгий режим был выключен. Понимание строгости очень важное понятие для БД MySQL, потому что в классических базах данных такого нет. Забегая вперёд, скажу, что все БД изначально строгие и не позволяют тех вольностей, которые есть в MySQL. Мягкость MySQL сложилась исторически, когда ещё не было InnoDB. Посудите сами, в нетранзакционных таблицах действуют совершенно другие правила, нежели чем в транзакционных, поэтому следование жестким правилам зачастую приводило бы к нежелательному результату.
STRICT_TRANS_TABLES
Включает «строгий режим» для всех таблиц, поддерживающих транзакции, т.е. на InnoDB и BDB. Этот режим возвращает ошибку, вместо предупреждения в следующих случаях:
1. Тип входных данных не соответствует заданному типу. Например, вставка строки в колонку c числовым типом
2. Число или дата находится вне допустимого диапазона. Диапазон определяется типом данных. Например, для типа unsigned tinyint допустимым диапазоном являются числа от 0 до 255
3. При вставке данных пропущено значение колонки, для которой не задано значение по умолчанию и имеет атрибут NOT NULL
4. Длина значения выходит за пределы заданного диапазона. Например, для колонки типа CHAR(5) вы не сможете вставить строку более 5 символов
5. Для типов ENUM и SET отсутствует вставляемое или обновляемое значение
Более подробно об особенностях работы данного режима будет рассказано отдельно в последующей ниже главе.
STRICT_ALL_TABLES
STRICT_ALL_TABLES полностью идентично STRICT_TRANS_TABLES, но действие режима уже распространяется на все таблицы MySQL, а не только на транзакционные.
Из-за разницы подходов к работе транзакционных и не транзакционных таблиц не всегда есть смысл использовать данный режим. Если это вам ещё не очевидно, то в главах о строгом и нестрогом режимах вы поймёте разницу.
TRADITIONAL
Композитный режим, включает в себя целый набор режимов, в который входит «строгий режим», а также ряд других режимов, налагающих ограничения на входные данные.
Заставляет MySQL вести себя как большинство «традиционных» баз данных SQL.
Посмотрим на полный перечень режимов, который содержит в себе данный режим.
Другой композитный режим, делающий MySQL «ANSI-подобным», т.е. приближенным к стандарту SQL.
Включает в себя следующие режимы: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE.
Последние два режима были обсуждены ранее, поэтому кратко опишу первые два:
REAL_AS_FLOAT – тип данных real является синонимом float, а не double.
PIPES_AS_CONCAT – разрешает использовать для конкатенации строк ( || ), вместо логического ИЛИ.
ONLY_FULL_GROUP_BY
Генерирует ошибку в запросах, в которых GROUP BY имеет не полный список не агрегированных параметров из SELECT и HAVING.
Если вы желаете узнать обо всех SQL mode режимах и окунуться глубже в проблему, то милости прошу в официальную документацию http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html
Работа с SQL mode в PHP
По правде сказать, данную главу вряд ли можно назвать прикладной, потому что в реальных проектах конфигурировать нужно непосредственно на сервере MySQL, а не средствами языка программирования, поэтому глава скорее теоретическая, но для общего развития неплохо иметь ввиду и такой способ.
Чаще всего соединение с БД происходит через экземпляр класса PDO, поэтому рассмотрим его в деталях.
Есть два способа передать в БД специальные инструкции. Первый способ – передача в конструкторе. Посмотрим на полное описание конструктора:
Второй способ – конфигурирование на лету, через метод setAttribute;
Конечно, некоторые из вас могут возразить, что можно выполнять запросы методом query или exec, но поскольку изначально глава теоретическая, то не буду заострять внимание на таком способе.
Дополнительно о PDO можно прочитать в официальной документации php.net/manual/ru/book.pdo.php
Предопределённые PDO константы для работы с MySQL php.net/manual/ru/ref.pdo-mysql.php
Строгий режим
Мы уже немного познакомились со строгим режимом в главе SQL Mode, когда изучали режимы STRICT_TRANS_TABLES, STRICT_ALL_TABLES и композитный TRADITIONAL. Уже из самого названия легко догадаться, что все входные данные проверяются с особой тщательностью и в случае нарушений любых ограничений, вас неминуемо будет ждать ошибка.
Ошибка в транзакционных таблицах вызывает откат транзакции (rollback). Даже если ваши запросы не предварены командой start transaction, то неявно каждый запрос в отдельности по-любому будет обёрнут командами start transaction и commit. Так работают все традиционные БД, что в равной степени относится и к транзакционным таблицам MySQL. Из этого следует, что нарушив ограничение, вызывается rollback, который откатывает все изменения.
Для не транзакционных таблиц всё немного сложнее. Так, при вставке, обновлении или удалении нескольких строк, в случае ошибки отменяется только последнее действие, вместо полного отката. Проиллюстрирую это на примере.
Генерация ошибки происходит в следующих случаях:
1. Тип вставляемых данных отличается от заданного типа столбца
2. Опущено значение для столбца, которому не задано значение по умолчанию и имеет атрибут NOT NULL
3. Для чисел и дат – данные находятся вне диапазона допустимых значений
4. Для строк – превышение допустимой длины
5. Для типов ENUM и SET – значение не является допустимым для заданного перечисления
6. Для столбца, определённого как NOT NULL — вставка NULL
Дефолтные значения для типов данных
Если в insert запросе не указаны данные для одной из колонок, то MySQL будет обрабатывать эту ситуацию в следующем порядке:
1. Если столбец имеет значение по умолчанию, то используется это значение и на этом всё заканчивается, в противном случае происходит переход к следующему шагу
2. Если столбец не имеет параметр NOT NULL, то присваивается NULL и на этом всё заканчивается, в противном случае поведение зависит от переменной sql_mode, точнее от строгости самого режима.
Как можно было догадаться из предыдущей главы, строгий режим сразу вернёт ошибку, которая в свою очередь откатит транзакцию для транзакционных таблиц или отменит последнее действие для не транзакционных таблиц.
Нестрогий режим
Ура! Наконец-то, мы добрались до самой «загадочной» части статьи, которую некоторые освещают как некую магию MySQL, но, увы, это лишь фокусы на потеху детей. И так, поехали!
Возможно, нужно было ранее описать все случаи, для которых применяются правила проверки данных, но я решил сделать это только сейчас. Их всего три, но каждый из них требует отдельного рассмотрения.
Запросы на модификация данных: INSERT, UPDATE, REPLACE, LOAD DATA INFILE
Обновление описания схем таблиц: ALTER TABLE
Задание значения по умолчанию (DEFAULT) в описании колонки
Напоминаю, что в строгом режиме некорректные данные приведут к генерации ошибки и откат данных, а в нестрогом – значение будет не явно приведено к корректному значению и сгенерировано предупреждение. Для просмотра ошибок используйте SHOW WARNINGS.
Ниже будут подробно рассмотрены все случаи обработки некорректных значений и их разрешений на уровне БД.
Выход из диапазона допустимых значений
Если число меньше минимального значения допустимого диапазона, то присваивается минимально-допустимое число. Если больше максимального – максимально-допустимое.
Обработка строк
Строки длиннее заданной длины — усекаются.
ENUM и SET типы данных
Если присваиваемое значение колонке с типом ENUM не перечислено в определении ENUM, то MySQL преобразует его в пустую строку.
Если значение, которое присваивается SET столбцу содержит элементы, которые не перечислены в определении SET, то MySQL отбрасывает эти элементы, сохраняя значения только легальным элементам.
Преобразование в тип даты
При попытке сохранения значения, которое не может быть преобразовано в тип данных столбца, MySQL неявно преобразует его в значение по умолчанию для данного типа.
Таблица преобразований
| STRING | DATE | INT |
| ‘2010-03-12’ | ‘2010-03-12’ | 2010 |
| ’03-12-2010′ | ‘0000-00-00’ | 3 |
| ‘0017’ | ‘0000-00-00’ | 17 |
| ‘500 hats’ | ‘0000-00-00’ | 500 |
| ‘bartholomew’ | ‘0000-00-00’ | 0 |
Присвоение NULL для колонки с NOT NULL
Результат зависит от того, будет вставлена одна строка или множество в INSERT запросе.
При вставке одной строки возникает ошибка и изменения не применяются. При множественной вставке — MySQL неявно преобразует значение по умолчанию для этого типа данных.
Обновление описания схем таблиц: ALTER TABLE
При изменении типа данных на колонку налагаются ограничения нового типа, что может привести к неожиданному изменению самих данных по правилам описанных выше.
Если на колонку налагается ограничение NOT NULL, то все NULL значения конвертируются в значения по умолчанию для заданного типа данных текущей колонки. Значения по умолчанию описаны в главе «Дефолтные значения для типов данных»
Задание значения по умолчанию (DEFAULT) в описании колонки
Вообще, всё уже было сказано в прошлой главе, поэтому здесь нечего добавить.
Ну что ж, мой дорогой читатель. Теперь ты можешь по-праву назваться настоящим джедаем и получить чёрный пояс)))
Извлекаем плюсы
IGNORE
Ключевое слово IGNORE заставляет MySQL включать для такого запроса нестрогий режим. Также его можно использовать для генерации предупреждения вместо ошибки, при нарушении целостности первичного ключа (PRIMARY KEY) или уникальности (UNIQUE).
ON DUPLICATE KEY UPDATE
Такой вид запроса не совсем идеальный пример, но включил, чтобы лишний раз вспомнить, что такое вообще существует. Используется для выполнения вставки данных, либо обновлении при нарушении ограничения целостности первичного ключа (PRIMARY KEY) или уникальности (UNIQUE).
mysqli::$affected_rows
Описание
Список параметров
Возвращаемые значения
Если число затронутых строк больше чем максимальное значение целого числа ( PHP_INT_MAX ), то число затронутых строк будет возвращено в виде строки.
Примеры
Результат выполнения данных примеров:
Смотрите также
User Contributed Notes 6 notes
On «INSERT INTO ON DUPLICATE KEY UPDATE» queries, though one may expect affected_rows to return only 0 or 1 per row on successful queries, it may in fact return 2.
From Mysql manual: «With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated.»
Here’s the sum breakdown _per row_:
+0: a row wasn’t updated or inserted (likely because the row already existed, but no field values were actually changed during the UPDATE)
+1: a row was inserted
+2: a row was updated
While using prepared statements, even if there is no result set (Like in an UPDATE or DELETE), you still need to store the results before affected_rows returns the actual number:
If you need to know specifically whether the WHERE condition of an UPDATE operation failed to match rows, or that simply no rows required updating you need to instead check mysqli::$info.
As this returns a string that requires parsing, you can use the following to convert the results into an associative array.
Object oriented style:
For any UPDATE operation the array returned will have the following elements:
Array
(
[Rows matched] => 1
[Changed] => 0
[Warnings] => 0
)
This may seem obvious, but if you do an UPDATE with each of the values in your SET clause having the exact same value that is already in the table, then affected_rows returns 0. For example:
[Перевод] Обработка ошибок и транзакций в SQL Server. Часть 1. Обработка ошибок – быстрый старт
Привет, Хабр! Представляю вашему вниманию перевод статьи «Error and Transaction Handling in SQL Server. Part One – Jumpstart Error Handling» автора Erland Sommarskog.
1. Введение
Эта статья – первая в серии из трёх статей, посвященных обработке ошибок и транзакций в SQL Server. Её цель – дать вам быстрый старт в теме обработки ошибок, показав базовый пример, который подходит для большей части вашего кода. Эта часть написана в расчете на неопытного читателя, и по этой причине я намеренно умалчиваю о многих деталях. В данный момент задача состоит в том, чтобы рассказать как без упора на почему. Если вы принимаете мои слова на веру, вы можете прочесть только эту часть и отложить остальные две для дальнейших этапов в вашей карьере.
С другой стороны, если вы ставите под сомнение мои рекомендации, вам определенно необходимо прочитать две остальные части, где я погружаюсь в детали намного более глубоко, исследуя очень запутанный мир обработки ошибок и транзакций в SQL Server. Вторая и третья части, так же, как и три приложения, предназначены для читателей с более глубоким опытом. Первая статья — короткая, вторая и третья значительно длиннее.
Все статьи описывают обработку ошибок и транзакций в SQL Server для версии 2005 и более поздних версий.
1.1 Зачем нужна обработка ошибок?
Почему мы обрабатываем ошибки в нашем коде? На это есть много причин. Например, на формах в приложении мы проверяем введенные данные и информируем пользователей о допущенных при вводе ошибках. Ошибки пользователя – это предвиденные ошибки. Но нам также нужно обрабатывать непредвиденные ошибки. То есть, ошибки могут возникнуть из-за того, что мы что-то упустили при написании кода. Простой подход – это прервать выполнение или хотя бы вернуться на этап, в котором мы имеем полный контроль над происходящим. Недостаточно будет просто подчеркнуть, что совершенно непозволительно игнорировать непредвиденные ошибки. Это недостаток, который может вызвать губительные последствия: например, стать причиной того, что приложение будет предоставлять некорректную информацию пользователю или, что еще хуже, сохранять некорректные данные в базе. Также важно сообщать о возникновении ошибки с той целью, чтобы пользователь не думал о том, что операция прошла успешно, в то время как ваш код на самом деле ничего не выполнил.
Мы часто хотим, чтобы в базе данных изменения были атомарными. Например, задача по переводу денег с одного счета на другой. С этой целью мы должны изменить две записи в таблице CashHoldings и добавить две записи в таблицу Transactions. Абсолютно недопустимо, чтобы ошибки или сбой привели к тому, что деньги будут переведены на счет получателя, а со счета отправителя они не будут списаны. По этой причине обработка ошибок также касается и обработки транзакций. В приведенном примере нам нужно обернуть операцию в BEGIN TRANSACTION и COMMIT TRANSACTION, но не только это: в случае ошибки мы должны убедиться, что транзакция откачена.
2. Основные команды
Мы начнем с обзора наиболее важных команд, которые необходимы для обработки ошибок. Во второй части я опишу все команды, относящиеся к обработке ошибок и транзакций.
2.1 TRY-CATCH
Основным механизмом обработки ошибок является конструкция TRY-CATCH, очень напоминающая подобные конструкции в других языках. Структура такова:
Как правило, в CATCH откатывают любую открытую транзакцию и повторно вызывают ошибку. Таким образом, вызывающая клиентская программа понимает, что что-то пошло не так. Повторный вызов ошибки мы обсудим позже в этой статье.
Вот очень быстрый пример:
Результат выполнения: This is the error: Divide by zero error encountered.
Мы вернемся к функции error_message() позднее. Стоит отметить, что использование PRINT в обработчике CATCH приводится только в рамках экспериментов и не следует делать так в коде реального приложения.
Если вызывает хранимую процедуру или запускает триггеры, то любая ошибка, которая в них возникнет, передаст выполнение в блок CATCH. Если более точно, то, когда возникает ошибка, SQL Server раскручивает стек до тех пор, пока не найдёт обработчик CATCH. И если такого обработчика нет, SQL Server отправляет сообщение об ошибке напрямую клиенту.
Есть одно очень важное ограничение у конструкции TRY-CATCH, которое нужно знать: она не ловит ошибки компиляции, которые возникают в той же области видимости. Рассмотрим пример:
Как можно видеть, блок TRY присутствует, но при возникновении ошибки выполнение не передается блоку CATCH, как это ожидалось. Это применимо ко всем ошибкам компиляции, таким как пропуск колонок, некорректные псевдонимы и тому подобное, которые возникают во время выполнения. (Ошибки компиляции могут возникнуть в SQL Server во время выполнения из-за отложенного разрешения имен – особенность, благодаря которой SQL Server позволяет создать процедуру, которая обращается к несуществующим таблицам.)
Эти ошибки не являются полностью неуловимыми; вы не можете поймать их в области, в которой они возникают, но вы можете поймать их во внешней области. Добавим такой код к предыдущему примеру:
Теперь мы получим на выходе это:
На этот раз ошибка была перехвачена, потому что сработал внешний обработчик CATCH.
2.2 SET XACT_ABORT ON
В начало ваших хранимых процедур следует всегда добавлять это выражение:
Оно активирует два параметра сессии, которые выключены по умолчанию в целях совместимости с предыдущими версиями, но опыт доказывает, что лучший подход – это иметь эти параметры всегда включенными. Поведение SQL Server по умолчанию в той ситуации, когда не используется TRY-CATCH, заключается в том, что некоторые ошибки прерывают выполнение и откатывают любые открытые транзакции, в то время как с другими ошибками выполнение последующих инструкций продолжается. Когда вы включаете XACT_ABORT ON, почти все ошибки начинают вызывать одинаковый эффект: любая открытая транзакция откатывается, и выполнение кода прерывается. Есть несколько исключений, среди которых наиболее заметным является выражение RAISERROR.
Параметр XACT_ABORT необходим для более надежной обработки ошибок и транзакций. В частности, при настройках по умолчанию есть несколько ситуаций, когда выполнение может быть прервано без какого-либо отката транзакции, даже если у вас есть TRY-CATCH. Мы видели такой пример в предыдущем разделе, где мы выяснили, что TRY-CATCH не перехватывает ошибки компиляции, возникшие в той же области. Открытая транзакция, которая не была откачена из-за ошибки, может вызвать серьезные проблемы, если приложение работает дальше без завершения транзакции или ее отката.
Для надежной обработки ошибок в SQL Server вам необходимы как TRY-CATCH, так и SET XACT_ABORT ON. Среди них инструкция SET XACT_ABORT ON наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит.
Параметр NOCOUNT не имеет к обработке ошибок никакого отношения, но включение его в код является хорошей практикой. NOCOUNT подавляет сообщения вида (1 row(s) affected), которые вы можете видеть в панели Message в SQL Server Management Studio. В то время как эти сообщения могут быть полезны при работе c SSMS, они могут негативно повлиять на производительность в приложении, так как увеличивают сетевой трафик. Сообщение о количестве строк также может привести к ошибке в плохо написанных клиентских приложениях, которые могут подумать, что это данные, которые вернул запрос.
Выше я использовал синтаксис, который немного необычен. Большинство людей написали бы два отдельных выражения:
Между ними нет никакого отличия. Я предпочитаю версию с SET и запятой, т.к. это снижает уровень шума в коде. Поскольку эти выражения должны появляться во всех ваших хранимых процедурах, они должны занимать как можно меньше места.
3. Основной пример обработки ошибок
После того, как мы посмотрели на TRY-CATCH и SET XACT_ABORT ON, давайте соединим их вместе в примере, который мы можем использовать во всех наших хранимых процедурах. Для начала я покажу пример, в котором ошибка генерируется в простой форме, а в следующем разделе я рассмотрю решения получше.
Для примера я буду использовать эту простую таблицу.
Вот хранимая процедура, которая демонстрирует, как вы должны работать с ошибками и транзакциями.
Первая строка в процедуре включает XACT_ABORT и NOCOUNT в одном выражении, как я показывал выше. Эта строка – единственная перед BEGIN TRY. Все остальное в процедуре должно располагаться после BEGIN TRY: объявление переменных, создание временных таблиц, табличных переменных, всё. Даже если у вас есть другие SET-команды в процедуре (хотя причины для этого встречаются редко), они должны идти после BEGIN TRY.
Причина, по которой я предпочитаю указывать SET XACT_ABORT и NOCOUNT перед BEGIN TRY, заключается в том, что я рассматриваю это как одну строку шума: она всегда должна быть там, но я не хочу, чтобы это мешало взгляду. Конечно же, это дело вкуса, и если вы предпочитаете ставить SET-команды после BEGIN TRY, ничего страшного. Важно то, что вам не следует ставить что-либо другое перед BEGIN TRY.
Часть между BEGIN TRY и END TRY является основной составляющей процедуры. Поскольку я хотел использовать транзакцию, определенную пользователем, я ввел довольно надуманное бизнес-правило, в котором говорится, что если вы вставляете пару, то обратная пара также должна быть вставлена. Два выражения INSERT находятся внутри BEGIN и COMMIT TRANSACTION. Во многих случаях у вас будет много строк кода между BEGIN TRY и BEGIN TRANSACTION. Иногда у вас также будет код между COMMIT TRANSACTION и END TRY, хотя обычно это только финальный SELECT, возвращающий данные или присваивающий значения выходным параметрам. Если ваша процедура не выполняет каких-либо изменений или имеет только одно выражение INSERT/UPDATE/DELETE/MERGE, то обычно вам вообще не нужно явно указывать транзакцию.
В то время как блок TRY будет выглядеть по-разному от процедуры к процедуре, блок CATCH должен быть более или менее результатом копирования и вставки. То есть вы делаете что-то короткое и простое и затем используете повсюду, не особо задумываясь. Обработчик CATCH, приведенный выше, выполняет три действия:
не нужна, если нет явной транзакции в процедуре, но это абсолютно неверно. Возможно, вы вызываете хранимую процедуру, которая открывает транзакцию, но которая не может ее откатить из-за ограничений TRY-CATCH. Возможно, вы или кто-то другой добавите явную транзакцию через два года. Вспомните ли вы тогда о том, что нужно добавить строку с откатом? Не рассчитывайте на это. Я также слышу читателей, которые возражают, что если тот, кто вызывает процедуру, открыл транзакцию, мы не должны ее откатывать… Нет, мы должны, и если вы хотите знать почему, вам нужно прочитать вторую и третью части. Откат транзакции в обработчике CATCH – это категорический императив, у которого нет исключений.
Код повторной генерации ошибки включает такую строку:
Встроенная функция error_message() возвращает текст возникшей ошибки. В следующей строке ошибка повторно вызывается с помощью выражения RAISERROR. Это не самый простой способ вызова ошибки, но он работает. Другие способы мы рассмотрим в следующей главе.
Замечание: синтаксис для присвоения начального значения переменной в DECLARE был внедрен в SQL Server 2008. Если у вас SQL Server 2005, вам нужно разбить строку на DECLARE и выражение SELECT.
Финальное выражение RETURN – это страховка. RAISERROR никогда не прерывает выполнение, поэтому выполнение следующего выражения будет продолжено. Пока все процедуры используют TRY-CATCH, а также весь клиентский код обрабатывает исключения, нет повода для беспокойства. Но ваша процедура может быть вызвана из старого кода, написанного до SQL Server 2005 и до внедрения TRY-CATCH. В те времена лучшее, что мы могли делать, это смотреть на возвращаемые значения. То, что вы возвращаете с помощью RETURN, не имеет особого значения, если это не нулевое значение (ноль обычно обозначает успешное завершение работы).
Последнее выражение в процедуре – это END CATCH. Никогда не следует помещать какой-либо код после END CATCH. Кто-нибудь, читающий процедуру, может не увидеть этот кусок кода.
После прочтения теории давайте попробуем тестовый пример:
Давайте добавим внешнюю процедуру для того, чтобы увидеть, что происходит при повторном вызове ошибки:
Мы получили корректное сообщение об ошибке, но если вы посмотрите на заголовки этого сообщения и на предыдущее поближе, то можете заметить проблему:
Сообщение об ошибке выводит информацию о расположении конечного выражения RAISERROR. В первом случае некорректен только номер строки. Во втором случае некорректно также имя процедуры. Для простых процедур, таких как наш тестовый пример, это не является большой проблемой. Но если у вас есть несколько уровней вложенных сложных процедур, то наличие сообщения об ошибке с отсутствием указания на место её возникновения сделает поиск и устранение ошибки намного более сложным делом. По этой причине желательно генерировать ошибку таким образом, чтобы можно было определить нахождение ошибочного фрагмента кода быстро, и это то, что мы рассмотрим в следующей главе.
4. Три способа генерации ошибки
4.1 Использование error_handler_sp
Мы рассмотрели функцию error_message(), которая возвращает текст сообщения об ошибке. Сообщение об ошибке состоит из нескольких компонентов, и существует своя функция error_xxx() для каждого из них. Мы можем использовать их для повторной генерации полного сообщения, которое содержит оригинальную информацию, хотя и в другом формате. Если делать это в каждом обработчике CATCH, это будет большой недостаток — дублирование кода. Вам не обязательно находиться в блоке CATCH для вызова error_message() и других подобных функций, и они вернут ту же самую информацию, если будут вызваны из хранимой процедуры, которую выполнит блок CATCH.
Позвольте представить вам error_handler_sp:
Первое из того, что делает error_handler_sp – это сохраняет значение всех error_xxx() функций в локальные переменные. Я вернусь к выражению IF через секунду. Вместо него давайте посмотрим на выражение SELECT внутри IF:
Цель этого SELECT заключается в форматировании сообщения об ошибке, которое передается в RAISERROR. Оно включает в себя всю информацию из оригинального сообщения об ошибке, которое мы не можем вставить напрямую в RAISERROR. Мы должны обработать имя процедуры, которое может быть NULL для ошибок в обычных скриптах или в динамическом SQL. Поэтому используется функция COALESCE. (Если вы не понимаете форму выражения RAISERROR, я рассказываю о нем более детально во второй части.)
Отформатированное сообщение об ошибке начинается с трех звездочек. Этим достигаются две цели: 1) Мы можем сразу видеть, что это сообщение вызвано из обработчика CATCH. 2) Это дает возможность для error_handler_sp отфильтровать ошибки, которые уже были сгенерированы один или более раз, с помощью условия NOT LIKE ‘***%’ для того, чтобы избежать изменения сообщения во второй раз.
Вот как обработчик CATCH должен выглядеть, когда вы используете error_handler_sp:
Давайте попробуем несколько тестовых сценариев.
Заголовки сообщений говорят о том, что ошибка возникла в процедуре error_handler_sp, но текст сообщений об ошибках дает нам настоящее местонахождение ошибки – как название процедуры, так и номер строки.
Я покажу еще два метода вызова ошибок. Однако error_handler_sp является моей главной рекомендацией для читателей, которые читают эту часть. Это — простой вариант, который работает на всех версиях SQL Server начиная с 2005. Существует только один недостаток: в некоторых случаях SQL Server генерирует два сообщения об ошибках, но функции error_xxx() возвращают только одну из них, и поэтому одно из сообщений теряется. Это может быть неудобно при работе с административными командами наподобие BACKUP\RESTORE, но проблема редко возникает в коде, предназначенном чисто для приложений.
4.2. Использование ;THROW
В SQL Server 2012 Microsoft представил выражение ;THROW для более легкой обработки ошибок. К сожалению, Microsoft сделал серьезную ошибку при проектировании этой команды и создал опасную ловушку.
С выражением ;THROW вам не нужно никаких хранимых процедур. Ваш обработчик CATCH становится таким же простым, как этот:
Достоинство ;THROW в том, что сообщение об ошибке генерируется точно таким же, как и оригинальное сообщение. Если изначально было два сообщения об ошибках, оба сообщения воспроизводятся, что делает это выражение еще привлекательнее. Как и со всеми другими сообщениями об ошибках, ошибки, сгенерированные ;THROW, могут быть перехвачены внешним обработчиком CATCH и воспроизведены. Если обработчика CATCH нет, выполнение прерывается, поэтому оператор RETURN в данном случае оказывается не нужным. (Я все еще рекомендую оставлять его, на случай, если вы измените свое отношение к ;THROW позже).
Если у вас SQL Server 2012 или более поздняя версия, измените определение insert_data и outer_sp и попробуйте выполнить тесты еще раз. Результат в этот раз будет такой:
Имя процедуры и номер строки верны и нет никакого другого имени процедуры, которое может нас запутать. Также сохранены оригинальные номера ошибок.
В этом месте вы можете сказать себе: действительно ли Microsoft назвал команду ;THROW? Разве это не просто THROW? На самом деле, если вы посмотрите в Books Online, там не будет точки с запятой. Но точка с запятой должны быть. Официально они отделяют предыдущее выражение, но это опционально, и далеко не все используют точку с запятой в выражениях T-SQL. Более важно, что если вы пропустите точку с запятой перед THROW, то не будет никакой синтаксической ошибки. Но это повлияет на поведение при выполнении выражения, и это поведение будет непостижимым для непосвященных. При наличии активной транзакции вы получите сообщение об ошибке, которое будет полностью отличаться от оригинального. И еще хуже, что при отсутствии активной транзакции ошибка будет тихо выведена без обработки. Такая вещь, как пропуск точки с запятой, не должно иметь таких абсурдных последствий. Для уменьшения риска такого поведения, всегда думайте о команде как о ;THROW (с точкой с запятой).
Нельзя отрицать того, что ;THROW имеет свои преимущества, но точка с запятой не единственная ловушка этой команды. Если вы хотите использовать ее, я призываю вас прочитать по крайней мере вторую часть этой серии, где я раскрываю больше деталей о команде ;THROW. До этого момента, используйте error_handler_sp.
4.3. Использование SqlEventLog
Третий способ обработки ошибок – это использование SqlEventLog, который я описываю очень детально в третьей части. Здесь я лишь сделаю короткий обзор.
SqlEventLog предоставляет хранимую процедуру slog.catchhandler_sp, которая работает так же, как и error_handler_sp: она использует функции error_xxx() для сбора информации и выводит сообщение об ошибке, сохраняя всю информацию о ней. Вдобавок к этому, она логирует ошибку в таблицу splog.sqleventlog. В зависимости от типа приложения, которое у вас есть, эта таблица может быть очень ценным объектом.
Для использования SqlEventLog, ваш обработчик CATCH должен быть таким:
@@procid возвращает идентификатор объекта текущей хранимой процедуры. Это то, что SqlEventLog использует для логирования информации в таблицу. Используя те же тестовые сценарии, получим результат их работы с использованием catchhandler_sp:
5. Финальные замечания
Вы изучили основной образец для обработки ошибок и транзакций в хранимых процедурах. Он не идеален, но он должен работать в 90-95% вашего кода. Есть несколько ограничений, на которые стоит обратить внимание:
Перед тем как закончить, я хочу кратко коснуться триггеров и клиентского кода.
Триггеры
Пример для обработки ошибок в триггерах не сильно отличается от того, что используется в хранимых процедурах, за исключением одной маленькой детали: вы не должны использовать выражение RETURN (потому что RETURN не допускается использовать в триггерах).
С триггерами важно понимать, что они являются частью команды, которая запустила триггер, и в триггере вы находитесь внутри транзакции, даже если не используете BEGIN TRANSACTION.
Иногда я вижу на форумах людей, которые спрашивают, могут ли они написать триггер, который не откатывает в случае падения запустившую его команду. Ответ таков: нет способа сделать это надежно, поэтому не стоит даже пытаться. Если в этом есть необходимость, по возможности не следует использовать триггер вообще, а найти другое решение. Во второй и третьей частях я рассматриваю обработку ошибок в триггерах более подробно.
Клиентский код
У вас должна быть обработка ошибок в коде клиента, если он имеет доступ к базе. То есть вы должны всегда предполагать, что при любом вызове что-то может пойти не так. Как именно внедрить обработку ошибок, зависит от конкретной среды.
Здесь я только обращу внимание на важную вещь: реакцией на ошибку, возвращенную SQL Server, должно быть завершение запроса во избежание открытых бесхозных транзакций:
Это также применимо к знаменитому сообщению Timeout expired (которое является не сообщением от SQL Server, а от API).
6. Конец первой части
Это конец первой из трех частей серии. Если вы хотели изучить вопрос обработки ошибок быстро, вы можете закончить чтение здесь. Если вы настроены идти дальше, вам следует прочитать вторую часть, где наше путешествие по запутанным джунглям обработки ошибок и транзакций в SQL Server начинается по-настоящему.
… и не забывайте добавлять эту строку в начало ваших хранимых процедур: