dcsimg
 

Submitted by: Sridhar MS(sridhar.ms@gmail.com)

Monday Jan 6th 2020 by Sridhar MS

Language: SQL, Expertise: Intermediate - See how to use sql_mode to set values 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 1

mysql> SELECT seconds FROM TBL_TIME;
Empty set (0.00 sec)
Home
Mobile Site | Full Site