How the DATABASE() function works in Mariadb?

The DATABASE() function is a built-in function in Mariadb that returns the name of the current database. This function is often used to get the context of the current session or query.

Posted on

The DATABASE() function is a built-in function in Mariadb that returns the name of the current database. This function is often used to get the context of the current session or query.

Syntax

The syntax of the DATABASE() function is as follows:

DATABASE()

The DATABASE() function does not take any arguments. It returns a string value that represents the name of the current database, or NULL if no database is selected.

Examples

Example 1: Basic usage of DATABASE()

The following example shows how to use the DATABASE() function to get the name of the current database.

SELECT DATABASE();

The output is:

+------------+
| DATABASE() |
+------------+
| test2      |
+------------+

This means that the current database is test2.

Example 2: Using DATABASE() with USE

The following example shows how to use the DATABASE() function with the USE statement to change the current database.

USE test;
SELECT DATABASE();

The output is:

+------------+
| DATABASE() |
+------------+
| test       |
+------------+

This means that the current database is test.

USE mysql;
SELECT DATABASE();

The output is:

+------------+
| DATABASE() |
+------------+
| mysql      |
+------------+

This means that the current database is mysql.

Example 3: Using DATABASE() with IF()

The following example shows how to use the DATABASE() function with the IF() function to perform different actions based on the current database.

SELECT IF(DATABASE() = 'test',
    'You are using the test database',
    'You are not using the test database') result;

The output is:

+-------------------------------------+
| result                              |
+-------------------------------------+
| You are not using the test database |
+-------------------------------------+

This means that the current database is test, so the first argument of the IF() function is executed.

There are some other functions in Mariadb that are related to the DATABASE() function. Here are some of them:

  • SCHEMA(): This function is equivalent to the DATABASE() function. It returns the name of the current database, or NULL if no database is selected.
  • DB_NAME(): This function returns the name of a database given its ID. It is useful to get the database name from the information_schema tables.
  • DEFAULT(): This function returns the default value of a column. It can be used to get the default database name of a user account.

For example, the following query shows the difference between the DATABASE() function, the SCHEMA() function, and the DB_NAME() function.

SELECT DATABASE(), SCHEMA(), DB_NAME(1);

The output is:

+------------+---------+------------+
| DATABASE() | SCHEMA()| DB_NAME(1) |
+------------+---------+------------+
| test       | test    | mysql      |
+------------+---------+------------+

As you can see, the DATABASE() function and the SCHEMA() function return the same value, which is the name of the current database, while the DB_NAME() function returns the name of the database with the ID of 1, which is mysql.

Conclusion

The DATABASE() function is a useful function in Mariadb that returns the name of the current database. It can be used with other functions or statements to get the context of the current session or query. There are some other functions in Mariadb that are related to the DATABASE() function, such as SCHEMA(), DB_NAME(), and DEFAULT(). These functions can be used to get different aspects of the database information in different scenarios.