mysql:nastrojka:sql_modes []

Эта страница только для чтения. Вы можете посмотреть её исходный текст, но не можете его изменить. Сообщите администратору, если считаете, что это неправильно.
======SQL modes======
====описание====
<note>
SQL modes – это настройка поведения работы сервера MySQL, состоящая из режимов, каждый из которых контролирует какой-либо один аспект обработки запроса.
</note>
Возможности SQL mode:
  - Устанавливает строгую или мягкую проверку входных данных
  - Включает или отключает следование SQL стандарту
  - Обеспечивает лучшую синтаксическую совместимость с другими БД
По сути, SQL mode очень мощный механизм тюнинга БД, позволяющий гибко манипулировать обработкой запросов и уведомлениями MySQL.

Настоятельно рекомендуется, чтобы вы никогда не изменяли SQL режим после создания партиционных таблиц.
При репликации партиционных таблиц, отличающиеся параметры SQL mode на Primary и Slave MySQL серверах также может привести к проблемам. Для стабильной работы репликации между серверами, настройки SQL mode должны быть идентичными.

Контроль текущего SQL режима происходит через системную переменную sql_mode. Для задания значения используется команда SET. Ниже представлены возможные варианты установки данного режима.

  - Соответствует значению по умолчанию для только что установленной БД (никаких специальных режимов не установлено). Кавычки являются обязательными.
<code mysql>
SET sql_mode = '';
</code>

  - Элемент нумерованного спискаУстановка одного режима sql_mode. Возможно два варианта – с кавычками и без них.
<code mysql>
SET sql_mode = ANSI_QUOTES;
SET sql_mode = 'TRADITIONAL';
</code>

  - Элемент нумерованного спискаУстановка нескольких режимов sql_mode. Указание кавычек является обязательным!
<code mysql>
SET sql_mode = 'IGNORE_SPACE,ANSI_QUOTES';
SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVIZION_BY_ZERO'
</code>


Несмотря на то, что названия режимов регистронезависимые, я для удобства прочтения буду везде в статье писать их в верхнем регистре.

В примерах выше мы устанавливали режимы для текущей сессии, но если вы обладаете привилегиями суперпользователя, то можно задать глобальный режим для всего сервера и всех текущих коннектов, указав параметр GLOBAL. Полный синтаксис установки sql_mode выглядит так:

<code mysql>
SET [GLOBAL|SESSION] sql_mode='параметры';
<code>

Для просмотра текущих значений глобального и сессионного режима сервера используйте следующие запросы:

<code mysql>
SELECT @@global.sql_mode;
SELECT @@session.sql_mode;
</code>

Помимо команды SET существует ещё два способа задания режима работы БД:
  - Запустить сервер с опцией --sql-mode="<режимы>"
  - Установить в файле my.cnf (для unix подобных систем) или my.ini (для windows) параметр sql-mode="<режимы>"



====Краткий справочник режимов====

===ANSI_QUOTES===

Заставляет сервер интерпретировать двойную кавычку ( " ) точно также, как и обратную кавычку ( ` ), при этом она теряет способность обрамлять строки. Как можно было догадаться, этот режим заставляет MySQL приблизиться к SQL стандарту.

<code mysql>
mysql> CREATE TABLE test11 (`order` INT NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.28 sec)

mysql> CREATE TABLE test12 ("order" INT NULL) ENGINE = InnoDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"order" INT NOT NULL) ENGINE = InnoDB' at line 1

mysql> SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test12 ("order" INT NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.08 sec)
</code>
----

===IGNORE_SPACE===

По умолчанию, между функцией и открывающейся круглой скобкой нельзя устанавливать пробелы. Включение этого режима разрешает серверу игнорировать пробелы, но платой за такую вольность станет то, что все функции станут зарезервированными словами, а значит, при совпадении имени столбца с именем функции придётся в обязательном порядке экранировать такой столбец.

<code mysql>
mysql> SELECT COUNT   (*) FROM test12;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM test12' at line 1

mysql> SET sql_mode = 'IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT   (*) FROM test12;
+-----------+
| COUNT (*) |
+-----------+
|         0 |
+-----------+
1 row in set (0.01 sec)
</code>

----
===ERROR_FOR_DIVISION_BY_ZERO===

При делении на ноль в строгом режиме генерируется ошибка, а нестрогом — предупреждение при выполнении операторов INSERT или UPDATE. Без этого параметра деление на ноль возвращает предупреждение и вставляет в таблицу NULL. Про строгость будет сказано в следующем режиме, пока постарайтесь абстрагироваться.

<code mysql>
mysql> SELECT 1 / 0;
+-------+
| 1 / 0 |
+-------+
|  NULL |
+-------+
1 row in set (0.00 sec)


mysql> SHOW WARNINGS;
Empty set (0.00 sec)

mysql> INSERT INTO test12 VALUES (1/0);
Query OK, 1 row affected (0.02 sec)

mysql> SHOW WARNINGS;
Empty set (0.00 sec)

mysql> SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 1 / 0;
+-------+
| 1 / 0 |
+-------+
|  NULL |
+-------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)

mysql> INSERT INTO test12 VALUES (1/0);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------+
| Level   | Code | Message       |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)

mysql> INSERT INTO test12 VALUES ('some string'/0);
Query OK, 1 row affected, 2 warnings (0.02 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'some string' |
| Warning | 1365 | Division by 0                                   |
+---------+------+-------------------------------------------------+
2 rows in set (0.00 sec)
</code>


В приведённых примерах мы получали исключительно предупреждения, потому что строгий режим был выключен. Понимание строгости очень важное понятие для БД MySQL, потому что в классических базах данных такого нет. Забегая вперёд, скажу, что все БД изначально строгие и не позволяют тех вольностей, которые есть в MySQL. Мягкость MySQL сложилась исторически, когда ещё не было InnoDB. Посудите сами, в нетранзакционных таблицах действуют совершенно другие правила, нежели чем в транзакционных, поэтому следование жестким правилам зачастую приводило бы к нежелательному результату.
----
===STRICT_TRANS_TABLES===

Включает «строгий режим» для всех таблиц, поддерживающих транзакции, т.е. на InnoDB и BDB. Этот режим возвращает ошибку, вместо предупреждения в следующих случаях:
  - Тип входных данных не соответствует заданному типу. Например, вставка строки в колонку c числовым типом
  - Число или дата находится вне допустимого диапазона. Диапазон определяется типом данных. Например, для типа unsigned tinyint допустимым диапазоном являются числа от 0 до 255
  - При вставке данных пропущено значение колонки, для которой не задано значение по умолчанию и имеет атрибут NOT NULL
  - Длина значения выходит за пределы заданного диапазона. Например, для колонки типа CHAR(5) вы не сможете вставить строку более 5 символов
  - Для типов ENUM и SET отсутствует вставляемое или обновляемое значение
Более подробно об особенностях работы данного режима будет рассказано отдельно в последующей ниже главе.
----
===STRICT_ALL_TABLES===

**STRICT_ALL_TABLES** полностью идентично STRICT_TRANS_TABLES, но действие режима уже распространяется на все таблицы MySQL, а не только на транзакционные.
Из-за разницы подходов к работе транзакционных и не транзакционных таблиц не всегда есть смысл использовать данный режим. Если это вам ещё не очевидно, то в главах о строгом и нестрогом режимах вы поймёте разницу.
----
===TRADITIONAL===

Композитный режим, включает в себя целый набор режимов, в который входит «строгий режим», а также ряд других режимов, налагающих ограничения на входные данные.
Заставляет MySQL вести себя как большинство «традиционных» баз данных SQL.
Посмотрим на полный перечень режимов, который содержит в себе данный режим.

<code mysql>
mysql> SET sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
</code>

----
===ANSI===

Другой композитный режим, делающий 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.

<code mysql>
mysql> SELECT name, address, MAX(age) FROM test GROUP BY name;
ERROR 1055 (42000): 't.address' isn't in GROUP BY

mysql> SELECT name, MAX(age) as max_age FROM test GROUP BY name HAVING max_age < 30;
Empty set (0.00 sec)
ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause
</code>

Обойти ошибку можно с помощью костылей

пример запроса вызывающего ошибку
<code mysql>
SELECT 
                    ss_catalog_vendors._id, 
                    ss_catalog_vendors._disabled, 
                    ss_catalog_vendors.pop_vendors, 
                    ss_catalog_vendors.filename,
                    ss_catalog_vendors.`name`
                FROM ss_catalog_vendors
                INNER JOIN ss_catalog_goods ON ss_catalog_goods.vendor = ss_catalog_vendors._id AND ss_catalog_goods._disabled = 0
                WHERE ss_catalog_vendors._disabled=0 AND ss_catalog_vendors.filename is not null
                group by ss_catalog_vendors.`name

</code>
Ошибка
<code mysql>
Ошибка SQL (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'muser_mv_new.ss_catalog_vendors._id' 
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
</code>

решение проблемы с помощью костылей
<code mysql>
SELECT 
	ANY_VALUE(ss_catalog_vendors._id) _id, 
	ANY_VALUE(ss_catalog_vendors._disabled) _disabled, 
	ANY_VALUE(ss_catalog_vendors.pop_vendors) pop_vendors, 
	ANY_VALUE(ss_catalog_vendors.filename) `filename`,
	ss_catalog_vendors.`name`

FROM ss_catalog_vendors
INNER JOIN ss_catalog_goods ON ss_catalog_goods.vendor = ss_catalog_vendors._id AND ss_catalog_goods._disabled = 0
WHERE ss_catalog_vendors._disabled=0
group by ss_catalog_vendors.`name`
</code>


Если вы желаете узнать обо всех SQL mode режимах и окунуться глубже в проблему, то милости прошу в официальную документацию http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html
Инструменты пользователя

Инструменты сайта

Инструменты страницы
