With NO_ENGINE_SUBSTITUTION  disabled, for CREATE TABLE  the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE , a warning occurs and the table is not altered. 

With NO_ENGINE_SUBSTITUTION  enabled, an error occurs and the table is not created or altered if the desired engine is unavailable. 

• NO_UNSIGNED_SUBTRACTION 

Subtraction between integer values, where one is of type UNSIGNED , produces an unsigned result by default. If the result would otherwise have been negative, an error results: 

mysql> SET sql_mode = ’’; Query OK, 0 rows affected (0.00 sec) mysql> SELECT CAST(0 AS UNSIGNED) - 1; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in ’(cast(0 as unsigned) - 1)’ 

If the NO_UNSIGNED_SUBTRACTION  SQL mode is enabled, the result is negative: 

mysql> SET sql_mode = ’NO_UNSIGNED_SUBTRACTION’; mysql> SELECT CAST(0 AS UNSIGNED) - 1; +-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | -1 | +-------------------------+ 

If the result of such an operation is used to update an UNSIGNED  integer column, the result is clipped to the maximum value for the column type, or clipped to 0 if NO_UNSIGNED_SUBTRACTION  is enabled. With strict SQL mode enabled, an error occurs and the column remains unchanged. 

When NO_UNSIGNED_SUBTRACTION  is enabled, the subtraction result is signed, even if any operand is unsigned . For example, compare the type of column c2  in table t1  with that of column c2  in table t2 : 

mysql> SET sql_mode=’’; mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL); mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test; mysql> DESCRIBE t1; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | c2 | bigint(21) unsigned | NO | | 0 | | +-------+---------------------+------+-----+---------+-------+ mysql> SET sql_mode=’NO_UNSIGNED_SUBTRACTION’; mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test; mysql> DESCRIBE t2; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c2 | bigint(21) | NO | | 0 | | +-------+------------+------+-----+---------+-------+ 

This means that BIGINT UNSIGNED  is not 100% usable in all contexts. See Section 12.11, “Cast Functions and Operators” . 

