mysql> SELECT NOT 1 BETWEEN -5 AND 5; -> 0 mysql> SET sql_mode = ’HIGH_NOT_PRECEDENCE’; mysql> SELECT NOT 1 BETWEEN -5 AND 5; -> 1 

• IGNORE_SPACE 

Permit spaces between a function name and the (  character. This causes built-in function names to be treated as reserved words. As a result, identifiers that are the same as function names must be quoted as described in Section 9.2, “Schema Object Names” . For example, because there is a COUNT() function, the use of count  as a table name in the following statement causes an error: 

mysql> CREATE TABLE count (i INT); ERROR 1064 (42000): You have an error in your SQL syntax 

The table name should be quoted: 

mysql> CREATE TABLE ‘count‘ (i INT); Query OK, 0 rows affected (0.00 sec) 

The IGNORE_SPACE  SQL mode applies to built-in functions, not to loadable functions or stored functions. It is always permissible to have spaces after a loadable function or stored function name, regardless of whether IGNORE_SPACE  is enabled. 

For further discussion of IGNORE_SPACE , see Section 9.2.5, “Function Name Parsing and Resolution” . 

• NO_AUTO_VALUE_ON_ZERO 

NO_AUTO_VALUE_ON_ZERO  affects handling of AUTO_INCREMENT  columns. Normally, you generate the next sequence number for the column by inserting either NULL  or 0  into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0  so that only NULL  generates the next sequence number. 

This mode can be useful if 0  has been stored in a table’s AUTO_INCREMENT  column. (Storing 0  is not a recommended practice, by the way.) For example, if you dump the table with mysqldump  and then reload it, MySQL normally generates new sequence numbers when it encounters the 0  values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. For this reason, mysqldump  automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO . 

• NO_BACKSLASH_ESCAPES 

Enabling this mode disables the use of the backslash character ( \ ) as an escape character within strings and identifiers. With this mode enabled, backslash becomes an ordinary character like any other, and the default escape sequence for LIKE  expressions is changed so that no escape character is used. 

• NO_DIR_IN_CREATE 

When creating a table, ignore all INDEX DIRECTORY  and DATA DIRECTORY  directives. This option is useful on replica servers. 

• NO_ENGINE_SUBSTITUTION 

Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE  specifies a storage engine that is disabled or not compiled in. 

By default, NO_ENGINE_SUBSTITUTION  is enabled. 

Because storage engines can be pluggable at runtime, unavailable engines are treated the same way: 

