dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Tip: Understanding sql_mode in MySQL

See how to use sql_mode to set values in MySQL.


advertisement

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)

 

Visit the DevX Tip Bank

 



   
Sridhar M S. is a Java developer from Bangalore, India. He holds a master's degree in Computer Science.
Thanks for your registration, follow us on our social networks to keep up-to-date