devxlogo

Understanding sql_mode in MySQL

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)
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist