One of the things I like about MySQL is that it allows you to change some of its global state as it’s running. This article will provide a quick overview of how to view the current variables and how to make changes.
Please note: All examples in this article were written using MySQL 5.5 and done in a development environment. Please do not try this on a production environment unless you are sure of the ramifications.
Global vs Session
MySQL has two levels of variables. The first is session which only affects your current connection to the server. If you’re just playing around you should use this because it should only hurt you if you do something wrong. The second is global which affects all the connections and any future connections until a reboot. We’re using this level in this article but if you want to set the session level of the variables change “global” to “session” in the examples below.
Seeing the Variables
Viewing all the variables can be done using the show global variables command:
317 rows is a bit much to read through to find a variable you’re interested in. Thankfully because show global variables is basically a query you can add a where clause:
You can also put the variable name in an SQL select query to perform calculations on it:
Updating a Variable
In order to update a variable we can use the set command. For this example, we’re going to set the SQL_MODE to the mode that’s the default in MySQL 8 (per https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html). You can do this to see how your code will handle the new modes.
Now we can see that the value has changed:
Like this post? Don't forget to follow us on Twitter and Facebook for updates.