MySQL has a feature called SQL Mode, where we can set certain options on how the database engine will handle certain situations in our queries.
The values can be set globally or just for our session.
A list of the values can be found at: MySQL – Server SQL Modes

Querying the SQL mode variable will give us the current settings:

SELECT @@sql_mode;

For me, the default values are:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_ENGINE_SUBSTITUTION

We can set the values like this:

SET SESSION sql_mode = 'ANSI';

ANSI is a group of settings, so setting that gives us:
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,
ONLY_FULL_GROUP_BY,ANSI

Now for a few examples of how different values affect the query processing. We’ll start with the double pipe operator.

 
SELECT 'ABC' || 'DEF';

With the Default mode, we get a 0. In MySQL, the double pipes mean a logical OR.
When we set ANSI mode, we get ‘ABCDEF’ as an answer. ANSI mode includes the PIPES_AS_CONCAT setting, so || will concatenate strings.

For the other examples, we’ll create a simple table first:

DROP TABLE IF EXISTS TestTableUnsigned;
CREATE TABLE TestTableUnsigned (
    ID int UNSIGNED NOT NULL PRIMARY KEY
);

First, we’ll look at inserting a negative value into the ID column. We’ve set the column as unsigned, which will reject any negative values.

INSERT INTO TestTableUnsigned VALUES (-1);

With the default settings, this insert gives us an error:
“SQL Error [1264] [22001]: Data truncation: Out of range value for column ‘ID'”

When we use the ANSI settings, the insert will succeed, but a value of 0 will be inserted.

Next, we’ll try dividing by 0. Normally, a SELECT in MySQL that divides by 0 will return NULL.
We’ll try inserting into our test table:

INSERT INTO TestTableUnsigned SELECT 1/0;

The Default mode gives us an error:
“SQL Error [1365] [22001]: Data truncation: Division by 0”.

When we use the ANSI settings, the insert will succeed, but a value of 0 will be inserted.

So that’s an overview of SQL Mode and its settings. In general, the ANSI mode is more relaxed, and will try to prevent errors from coming up, even with bad data. The default mode is a little stricter, and will raise errors in these situations.