Information Functions In MySQL

In this article, we will study the various Information Functions in MySQL. As the name suggests, information functions provide us with information about our MySQL database. These functions are very useful in understanding the technicalities of the MySQL database. They provide you with valuable information like the version of your MySQL database, the current user working on the database server, the names of all databases in your MySQL server, and so on. Let us take a look at the various information functions in MySQL that you will be using very often.

MySQL VERSION()

Suppose you want to know the version of your MySQL or MariaDB database server. For this, we use the VERSION() function. The VERSION() function, when used with the SELECT statement, displays the version of your database server. The result is returned as a string. Let us find the version of our SQL database servers. The query is –

SELECT VERSION(); Code language: SQL (Structured Query Language) (sql)

And the output is –

Note that this is my database server version. I am indeed using MariaDB of the version 10.1.35. This value will differ from machine to machine, according to your MySQL version.

MySQL CONNECTION_ID()

A connection ID used to establish a connection to a database. This value is unique for every connection among the connected clients. To find the connection ID for your database, use the CONNECTION_ID() function. The CONNECTION_ID() function is used to return the connection ID for the current connection to the MySQL database. We find the connection ID using the following query –

SELECT CONNECTION_ID();Code language: SQL (Structured Query Language) (sql)

And the output is –

Again, this value will differ for you. Also, connection ID can change. So if you try this function today and you get a connection ID as 3, chances are, you will get a different connection ID if you execute this function again tomorrow.

MySQL USER()

The MySQL USER() function is used to return the current user name and host name for the MySQL connection. It returns the result as a string in the utf8 character set. Let us see this function in action. The query is –

SELECT USER();Code language: SQL (Structured Query Language) (sql)

And the output is –

The first value (before the ‘@’) indicates the user name you specified when connecting to the server, and the second value (after the ‘@’) indicates the client host from which you connected.

MySQL CURRENT_USER()

The MySQL CURRENT_USER() Function is used to return the user name and host name combination for the MySQL account that the server used to authenticate the current client. Let us see an example of this. We use the below query.

SELECT CURRENT_USER();Code language: SQL (Structured Query Language) (sql)

And the output is –

Wait, you said username and hostname! Why is there only a hostname value here?

Well, it is important to note that the output of USER() and CURRENT_USER() might not be the same as we saw in this situation. What happened here is that even though I specified a username – ‘Kedar’ (as indicated by the value we got in the example of the USER() function), the server authenticated the client using an anonymous user account. Therefore, we see the user name part as an empty user name in the above output of the CURRENT_USER() function.

Also, CURRENT_USER() can also be just used as CURRENT_USER.

MySQL SESSION_USER()

The MySQL SESSION_USER() function is used to return the current user name and host name for the MySQL connection. This function is a synonym for the USER() function. Here is an example of the SESSION_USER() function.

SELECT SESSION_USER();Code language: SQL (Structured Query Language) (sql)

Output –

MySQL SYSTEM_USER()

The MySQL SYSTEM_USER() function is used to return the current user name and host name for the MySQL connection. This function is a synonym for the USER() function. Here is an example of the SYSTEM_USER() function.

SELECT SYSTEM_USER();Code language: SQL (Structured Query Language) (sql)

Output –

MySQL DATABASE()

The MySQL DATABASE() function is used to return the name of the current database. A NULL or a “” value is returned if there is no current database. Let us see an example of this –

SELECT DATABASE();Code language: SQL (Structured Query Language) (sql)

And the output is –

So the name of my current database is ‘test_jd’.

MySQL SCHEMA()

The SCHEMA() function is a synonym for the DATABASE() function. Let us see an example of this.

SELECT SCHEMA();Code language: SQL (Structured Query Language) (sql)

Output –

SHOW Databases Command

If we want to get a list of databases in your database server then the ‘SHOW Databases’ command is used. Let us see it in action.

SHOW Databases;Code language: SQL (Structured Query Language) (sql)

And we get a list of databases in our output as follows.

Note that, this result may be different for you depending on how many databases with different names you have in your database server.

You can also filter database names with the SHOW Databases command by using the LIKE clause. I want the names of those databases that begin with ‘test’. The query is –

SHOW Databases LIKE 'test%';Code language: SQL (Structured Query Language) (sql)

And the output is –

The USE command

Now that you have a list of databases, how about using them? First, we need to pass control into that database using the ‘USE’ Command. The syntax for it is –

USE database_name;Code language: SQL (Structured Query Language) (sql)

Let us see an example below, where I jump control to the ‘test_db’ database and then back to the ‘test_jd’ database.

USE test_db; 
USE test_jd;Code language: SQL (Structured Query Language) (sql)

And the output is –

Note, you get a message saying ‘Database changed’ as seen above when you change control to a different database.

SHOW Tables Command

Finally, if we want to get a list of tables in the current database then the ‘SHOW Tables’ command is used. Let us see it in action.

SHOW Tables;Code language: SQL (Structured Query Language) (sql)

And the output is –

Conclusion

Information Functions are absolutely important. You will find yourself using these very often, sometimes everyday. Make a proper note of this as they will make your life easy when it comes to MySQL.

References

• MySQL Official Documentation on Information Functions.