dcsimg
LinkedIn
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Jan 6, 2020

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 1

mysql> SELECT seconds FROM TBL_TIME;
Empty set (0.00 sec)
Sridhar MS
 
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date