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

Overview

The Latest

positive contribution tech

Technology’s Positive Contributions to Society

Technology has and continues to improve our lives. From the business world to the world of medicine, and our day-to-day lives, you can’t go a day without interacting with at least one form of technology. While some fear technology may be going too far, there are many ways in which

How to Choose From The Best Big Data Platforms in 2023

How to Choose From The Best Big Data Platforms in 2023

As big data continues to become increasingly popular in the business world, companies are always looking for better ways to process and analyze complex data. The process critically depends on the platform that manages and analyzes the data. In this article, we will provide a guide to help you choose

Why transparent code is a good idea

Why Transparent Code is a Good Idea

Code is used to make up the apps and software we use every day. From our favorite social media platforms to our online banking services, code is the framework used to build these tools that help make our lives easier. Code is complex. Software today requires large teams of programmers

The Role of WordPress Hosting in Website Speed and Performance

The Role of WordPress Hosting in Website Performance

The term “WordPress hosting” refers to a specific type of web hosting service that offers hardware and software configurations tailored to the needs of WP sites. It’s important to remember that a WP hosting package is not required to host WordPress webpages. WP web pages are also compatible with standard

Data Privacy vs. Data Security: What you Should Know

Data Privacy vs. Data Security: What you Should Know

Data privacy and data security are often used interchangeably, but they are two completely different things. It’s important to understand the difference for anyone who handles sensitive information, such as personal data or financial records. In this article, we’ll take a closer look at data privacy vs. data security. We’ll