1
00:00:00,150 --> 00:00:05,250
Next up, we'll have a brief discussion around modes, server, school modes.

2
00:00:05,340 --> 00:00:12,330
They're basically settings that we can turn on and off to change the behavior and the validations of

3
00:00:12,330 --> 00:00:13,290
my SQL.

4
00:00:13,680 --> 00:00:15,780
So the docs say basically the same thing.

5
00:00:15,780 --> 00:00:21,120
Modes affect the syntax that my SQL supports and the data validation checks it performs.

6
00:00:21,330 --> 00:00:23,040
So there's a bunch of these different settings.

7
00:00:23,040 --> 00:00:32,580
Some of them have somewhat self-explanatory names, like No backslash escapes or no unsigned subtraction,

8
00:00:32,580 --> 00:00:37,380
which means it's a setting that says you can't do subtraction with unsigned numbers.

9
00:00:37,470 --> 00:00:38,930
No, zero date.

10
00:00:38,940 --> 00:00:43,890
I believe there's a no division by zero, but some of the other ones are not as obvious.

11
00:00:43,890 --> 00:00:46,470
Like what is strict trans tables?

12
00:00:46,800 --> 00:00:48,960
Well, we're going to talk about that in this video.

13
00:00:48,960 --> 00:00:50,550
I'm going to show you some of these settings.

14
00:00:50,550 --> 00:00:55,930
But the first thing that I want to show you is how we can view the current modes.

15
00:00:55,950 --> 00:00:59,100
There's two different scopes for a SQL mode.

16
00:00:59,100 --> 00:01:00,660
We have the global mode.

17
00:01:00,660 --> 00:01:06,330
What settings are enabled globally and then what settings are enabled or disabled in your current session?

18
00:01:06,600 --> 00:01:10,560
So when you first start up a new session, if you haven't touched anything, they're going to be the

19
00:01:10,560 --> 00:01:11,230
same.

20
00:01:11,250 --> 00:01:12,900
I'll show you that really quickly.

21
00:01:13,200 --> 00:01:16,560
Let's do select and then at at Global.

22
00:01:18,100 --> 00:01:20,080
So it's kind of a hard one to remember.

23
00:01:20,450 --> 00:01:21,850
Todd School mode.

24
00:01:22,210 --> 00:01:25,090
This is not something that I generally mess with very much.

25
00:01:26,650 --> 00:01:29,110
So this shows us this output here.

26
00:01:29,170 --> 00:01:36,790
Says, Here's your school mode for the global scope and you'll see a bunch of different settings separated

27
00:01:36,790 --> 00:01:40,780
by commas, only full grouped by strict trans tables.

28
00:01:40,780 --> 00:01:45,070
No zero in date error for division by zero that one makes.

29
00:01:45,070 --> 00:01:46,780
It's pretty self-explanatory.

30
00:01:47,410 --> 00:01:49,150
No engine substitution.

31
00:01:50,320 --> 00:01:54,130
Then we have our session SQL mode.

32
00:01:56,350 --> 00:01:58,090
Which should be exactly the same.

33
00:01:58,090 --> 00:02:02,260
If you haven't changed anything in your current session and seeing as we haven't talked about how to

34
00:02:02,260 --> 00:02:05,950
change anything, I wouldn't expect you to have changed anything.

35
00:02:06,130 --> 00:02:07,660
So we're going to talk about some of these.

36
00:02:07,660 --> 00:02:11,620
We're not going to go over each individual mode because there's a decent number of them here.

37
00:02:11,620 --> 00:02:15,910
You can read about them all on the docs, but I want to at least talk about the ones that are enabled

38
00:02:15,910 --> 00:02:20,470
by default and what changes if we disable some of them.

39
00:02:20,470 --> 00:02:25,630
But before we can do that, we have to learn how to disable or how to change the modes.

40
00:02:25,960 --> 00:02:30,790
And the the interface for this is not exactly as straightforward as it could be.

41
00:02:30,790 --> 00:02:36,520
It's not hard, but we basically have to list them all in a piece of text in quotes, so we can set

42
00:02:36,520 --> 00:02:37,450
the global mode.

43
00:02:37,450 --> 00:02:38,470
I'm not going to do that though.

44
00:02:38,470 --> 00:02:44,110
I'm only going to set my session mode because then as soon as I stop or I quit my current session and

45
00:02:44,110 --> 00:02:48,570
restart the MySQL shell, then all of those changes will go away.

46
00:02:48,580 --> 00:02:53,620
But if I wanted to make it permanent and have it global affecting all users in all sessions, then I

47
00:02:53,620 --> 00:02:54,850
would set the global mode.

48
00:02:54,850 --> 00:02:58,210
But I'm not going to do that because I'm going to just leave the defaults.

49
00:02:58,210 --> 00:03:00,880
In general, they are the default modes for a reason.

50
00:03:00,880 --> 00:03:05,770
The ones that we see here, strict trans tables only full group by those are there.

51
00:03:05,770 --> 00:03:09,160
It's a decision that was made by the developers of my SQL.

52
00:03:09,250 --> 00:03:14,230
So unless you have a good reason to get rid of them for compatibility purposes with another database,

53
00:03:14,230 --> 00:03:17,140
some reason to get rid of them, go ahead and do that.

54
00:03:17,140 --> 00:03:21,580
But if you don't, I'm going to leave them, but I am going to show you how you can change them.

55
00:03:21,580 --> 00:03:27,550
So I'm going to set the session SQL mode and then I have to do an equal sign and then in quotes, provide

56
00:03:27,550 --> 00:03:29,590
the list of my modes.

57
00:03:29,830 --> 00:03:31,780
So let's do a simple example.

58
00:03:31,900 --> 00:03:36,790
Let's start by just reviewing the session SQL mode.

59
00:03:36,790 --> 00:03:37,690
It looks like this.

60
00:03:37,690 --> 00:03:42,880
I'm going to set it so that we don't have the error for division by zero.

61
00:03:42,880 --> 00:03:47,830
That will be our first example and there's no easy way just to say remove this mode.

62
00:03:47,830 --> 00:03:54,790
Instead, I have to set the mode to be this and then also this.

63
00:03:54,790 --> 00:03:58,200
So I just omit error for division by zero.

64
00:03:58,210 --> 00:04:03,250
Now let me show you what happens when I have this error for division by zero enabled, which is just

65
00:04:03,250 --> 00:04:03,730
the default.

66
00:04:03,730 --> 00:04:04,690
I didn't do that.

67
00:04:04,690 --> 00:04:08,890
I'll do something like select three divided by zero and I get this warning here.

68
00:04:08,890 --> 00:04:13,960
If I do show warnings, it says message division by zero.

69
00:04:14,470 --> 00:04:14,710
Right?

70
00:04:14,710 --> 00:04:16,060
So I get a warning for that.

71
00:04:16,060 --> 00:04:25,630
Now, if I set and then session SQL mode, this syntax right here and I set it equal to just an empty

72
00:04:25,630 --> 00:04:32,470
string that's going to disable all of my modes, or if I want to keep all these other ones and only

73
00:04:32,470 --> 00:04:34,450
get rid of error for division by zero.

74
00:04:34,720 --> 00:04:41,410
I can copy that, put it in quotes, add a comma and then add in everything after error.

75
00:04:41,410 --> 00:04:43,990
Four Division by zero, which is no engine substitution.

76
00:04:43,990 --> 00:04:45,580
We're not going to worry about that one anyway.

77
00:04:46,030 --> 00:04:48,880
But now what is it complaining to me about?

78
00:04:48,880 --> 00:04:50,080
It looks like.

79
00:04:51,470 --> 00:04:52,510
Oh, I have a space.

80
00:04:52,510 --> 00:04:53,710
You can't have a space.

81
00:04:55,840 --> 00:04:56,700
All right, There we go.

82
00:04:56,710 --> 00:04:58,210
It's a little finicky, as you can see.

83
00:04:58,210 --> 00:05:03,940
So I no longer have this error for division by zero or whatever it was called, error for division by

84
00:05:03,940 --> 00:05:04,780
zero mode.

85
00:05:04,780 --> 00:05:10,510
So if I try this same query, very simple one select three divided by zero, we don't get a warning

86
00:05:10,510 --> 00:05:11,170
anymore.

87
00:05:11,200 --> 00:05:13,180
This is what we had previously.

88
00:05:13,180 --> 00:05:15,970
I got one warning if I do show warnings.

89
00:05:16,790 --> 00:05:19,340
No warnings, just a simple example.

90
00:05:19,340 --> 00:05:22,780
But we have changed the behavior by altering the modes.

91
00:05:22,790 --> 00:05:25,340
Simple 11i probably wouldn't change.

92
00:05:25,340 --> 00:05:28,160
I probably want to have that error for division by zero.

93
00:05:28,160 --> 00:05:30,470
And then of course, you can read more about each one.

94
00:05:30,470 --> 00:05:35,120
If I go to error for division by zero, it says it affects the handling of this division by zero, blah

95
00:05:35,120 --> 00:05:35,870
blah blah.

96
00:05:36,530 --> 00:05:40,970
If this mode is not enabled, division by zero inserts null and produces no warning.

97
00:05:40,970 --> 00:05:42,080
That's what we just had.

98
00:05:42,080 --> 00:05:46,190
If it is enabled, it will produce a warning which we saw previously.

99
00:05:46,190 --> 00:05:47,870
And that's it for this first video.

100
00:05:47,870 --> 00:05:51,620
But now I'm going to go over some of these other modes and what they mean.