Understanding sql_mode in MySQL

In MySQL, the sql_mode can have values set, so that the behaviour of the SQL being executed henceforth behaves as per the mode set.

Considering the following use case where MySQL is not running in strict mode:

mysql> SET sql_mode='';Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE TBL_TIME (seconds TINYINT);Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO TBL_TIME SET seconds = 128;Query OK, 1 row affected, 1 warning (0.01 sec)mysql> SELECT seconds FROM TBL_TIME;+---------+| seconds |+---------+|     127 |+---------+1 row in set (0.01 sec)

Here, we can see that the value in the column seconds has been truncated to 127 due to the data type of the column.

Whereas, with a STRICT mode enforced, the behaviour is as below.

mysql> SET sql_mode='STRICT_ALL_TABLES';Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE TBL_TIME (seconds TINYINT);Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO TBL_TIME SET seconds = 128;ERROR 1264 (22003): Out of range value adjusted for column 'seconds' at row 1mysql> SELECT seconds FROM TBL_TIME;Empty set (0.00 sec)
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Related Posts