Next up, we'll have a brief discussion around modes, server, school modes.
They're basically settings that we can turn on and off to change the behavior and the validations of
my SQL.
So the docs say basically the same thing.
Modes affect the syntax that my SQL supports and the data validation checks it performs.
So there's a bunch of these different settings.
Some of them have somewhat self-explanatory names, like No backslash escapes or no unsigned subtraction,
which means it's a setting that says you can't do subtraction with unsigned numbers.
No, zero date.
I believe there's a no division by zero, but some of the other ones are not as obvious.
Like what is strict trans tables?
Well, we're going to talk about that in this video.
I'm going to show you some of these settings.
But the first thing that I want to show you is how we can view the current modes.
There's two different scopes for a SQL mode.
We have the global mode.
What settings are enabled globally and then what settings are enabled or disabled in your current session?
So when you first start up a new session, if you haven't touched anything, they're going to be the
same.
I'll show you that really quickly.
Let's do select and then at at Global.
So it's kind of a hard one to remember.
Todd School mode.
This is not something that I generally mess with very much.
So this shows us this output here.
Says, Here's your school mode for the global scope and you'll see a bunch of different settings separated
by commas, only full grouped by strict trans tables.
No zero in date error for division by zero that one makes.
It's pretty self-explanatory.
No engine substitution.
Then we have our session SQL mode.
Which should be exactly the same.
If you haven't changed anything in your current session and seeing as we haven't talked about how to
change anything, I wouldn't expect you to have changed anything.
So we're going to talk about some of these.
We're not going to go over each individual mode because there's a decent number of them here.
You can read about them all on the docs, but I want to at least talk about the ones that are enabled
by default and what changes if we disable some of them.
But before we can do that, we have to learn how to disable or how to change the modes.
And the the interface for this is not exactly as straightforward as it could be.
It's not hard, but we basically have to list them all in a piece of text in quotes, so we can set
the global mode.
I'm not going to do that though.
I'm only going to set my session mode because then as soon as I stop or I quit my current session and
restart the MySQL shell, then all of those changes will go away.
But if I wanted to make it permanent and have it global affecting all users in all sessions, then I
would set the global mode.
But I'm not going to do that because I'm going to just leave the defaults.
In general, they are the default modes for a reason.
The ones that we see here, strict trans tables only full group by those are there.
It's a decision that was made by the developers of my SQL.
So unless you have a good reason to get rid of them for compatibility purposes with another database,
some reason to get rid of them, go ahead and do that.
But if you don't, I'm going to leave them, but I am going to show you how you can change them.
So I'm going to set the session SQL mode and then I have to do an equal sign and then in quotes, provide
the list of my modes.
So let's do a simple example.
Let's start by just reviewing the session SQL mode.
It looks like this.
I'm going to set it so that we don't have the error for division by zero.
That will be our first example and there's no easy way just to say remove this mode.
Instead, I have to set the mode to be this and then also this.
So I just omit error for division by zero.
Now let me show you what happens when I have this error for division by zero enabled, which is just
the default.
I didn't do that.
I'll do something like select three divided by zero and I get this warning here.
If I do show warnings, it says message division by zero.
Right?
So I get a warning for that.
Now, if I set and then session SQL mode, this syntax right here and I set it equal to just an empty
string that's going to disable all of my modes, or if I want to keep all these other ones and only
get rid of error for division by zero.
I can copy that, put it in quotes, add a comma and then add in everything after error.
Four Division by zero, which is no engine substitution.
We're not going to worry about that one anyway.
But now what is it complaining to me about?
It looks like.
Oh, I have a space.
You can't have a space.
All right, There we go.
It's a little finicky, as you can see.
So I no longer have this error for division by zero or whatever it was called, error for division by
zero mode.
So if I try this same query, very simple one select three divided by zero, we don't get a warning
anymore.
This is what we had previously.
I got one warning if I do show warnings.
No warnings, just a simple example.
But we have changed the behavior by altering the modes.
Simple 11i probably wouldn't change.
I probably want to have that error for division by zero.
And then of course, you can read more about each one.
If I go to error for division by zero, it says it affects the handling of this division by zero, blah
blah blah.
If this mode is not enabled, division by zero inserts null and produces no warning.
That's what we just had.
If it is enabled, it will produce a warning which we saw previously.
And that's it for this first video.
But now I'm going to go over some of these other modes and what they mean.