Возможности SQL mode:

1. Устанавливает строгую или мягкую проверку входных данных
2. Включает или отключает следование SQL стандарту
3. Обеспечивает лучшую синтаксическую совместимость с другими БД

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

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

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

1. Соответствует значению по умолчанию для только что установленной БД (никаких специальных режимов не установлено). Кавычки являются обязательными.

SET sql_mode = '';

1. Элемент нумерованного спискаУстановка одного режима sql_mode. Возможно два варианта – с кавычками и без них.

SET sql_mode = ANSI_QUOTES;
SET sql_mode = 'TRADITIONAL';

1. Элемент нумерованного спискаУстановка нескольких режимов sql_mode. Указание кавычек является обязательным!

SET sql_mode = 'IGNORE_SPACE,ANSI_QUOTES';
SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVIZION_BY_ZERO'

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

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

SET [GLOBAL|SESSION] sql_mode='параметры';
<code>
 
Для просмотра текущих значений глобального и сессионного режима сервера используйте следующие запросы:
 
<code mysql>
SELECT @@global.sql_mode;
SELECT @@session.sql_mode;

Помимо команды SET существует ещё два способа задания режима работы БД:

1. Запустить сервер с опцией –sql-mode=«<режимы>»
2. Установить в файле my.cnf (для unix подобных систем) или my.ini (для windows) параметр sql-mode=«<режимы>»

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

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)

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

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)

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

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)

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

Включает «строгий режим» для всех таблиц, поддерживающих транзакции, т.е. на InnoDB и BDB. Этот режим возвращает ошибку, вместо предупреждения в следующих случаях:

1. Тип входных данных не соответствует заданному типу. Например, вставка строки в колонку c числовым типом
2. Число или дата находится вне допустимого диапазона. Диапазон определяется типом данных. Например, для типа unsigned tinyint допустимым диапазоном являются числа от 0 до 255
3. При вставке данных пропущено значение колонки, для которой не задано значение по умолчанию и имеет атрибут NOT NULL
4. Длина значения выходит за пределы заданного диапазона. Например, для колонки типа CHAR(5) вы не сможете вставить строку более 5 символов
5. Для типов ENUM и SET отсутствует вставляемое или обновляемое значение

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

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

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

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)

Другой композитный режим, делающий MySQL «ANSI-подобным», т.е. приближенным к стандарту SQL. Включает в себя следующие режимы: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE. Последние два режима были обсуждены ранее, поэтому кратко опишу первые два: REAL_AS_FLOAT – тип данных real является синонимом float, а не double. PIPES_AS_CONCAT – разрешает использовать для конкатенации строк ( || ), вместо логического ИЛИ.

Генерирует ошибку в запросах, в которых GROUP BY имеет не полный список не агрегированных параметров из SELECT и HAVING.

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

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

пример запроса вызывающего ошибку

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

Ошибка

Ошибка 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

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

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`

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