How the VERSION() function works in Mariadb?

The MariaDB VERSION() function is quite useful for users who need to know the version of the MariaDB server they are working with.

Posted on

MariaDB is a popular fork of MySQL, known for its rich set of features and compatibility with MySQL. One of the functions provided by MariaDB is the VERSION() function, which can be quite useful for users who need to know the version of the MariaDB server they are working with.

Syntax

The syntax for the MariaDB VERSION() function is straightforward as it does not take any parameters and returns a string value representing the current version of the MariaDB server.

SELECT VERSION();

Examples

Example 1: Retrieving the Version of MariaDB

This example demonstrates how to retrieve the current version of the MariaDB server.

SELECT VERSION();

+----------------+
| VERSION()      |
+----------------+
| 11.2.2-MariaDB |
+----------------+

The output is the version of the MariaDB server which includes the version number and additional distribution information.

Example 2: Using VERSION() in a WHERE Clause

You can use the VERSION() function in a WHERE clause to conditionally select data based on the MariaDB server version.

SELECT * FROM system_info
WHERE server_version = VERSION();

Assuming system_info is a table containing a server_version column, this query will return rows where the server_version matches the current MariaDB version.

Example 3: Creating a Conditional Schema Update

This example shows how you might use the VERSION() function to apply schema updates conditionally based on the server version.

DELIMITER //

CREATE PROCEDURE update_schema()
BEGIN
  IF VERSION() LIKE '10.5%' THEN
    ALTER TABLE employee ADD COLUMN bio TEXT;
  END IF;
END //

DELIMITER ;

This stored procedure adds a new column to the employee table only if the MariaDB version starts with ‘10.5’.

Example 4: Comparing Versions

Sometimes, you might need to compare the current MariaDB version with a specific version string.

SELECT IF(VERSION() > '10.3', 'Update available', 'Up to date') AS update_status;

+------------------+
| update_status    |
+------------------+
| Update available |
+------------------+

This query checks if the current version is greater than ‘10.3’ and returns an appropriate message.

Example 5: Checking for Feature Availability

The VERSION() function can be used to check for the availability of certain features that are version-specific.

SELECT IF(VERSION() >= '10.2', 'JSON functions available', 'Upgrade required for JSON functions') AS feature_check;

+--------------------------+
| feature_check            |
+--------------------------+
| JSON functions available |
+--------------------------+

This query verifies if the JSON functions are available, which were introduced in MariaDB 10.2.

Here are a few functions related to the MariaDB VERSION() function:

  • MariaDB CONNECTION_ID() function returns the unique connection identifier for the current connection.
  • MariaDB CURRENT_USER() function returns the user name and host name combination for the MySQL account that the server used to authenticate the current client.
  • MariaDB DATABASE() function returns the default (current) database name.

Conclusion

The VERSION() function in MariaDB is a simple yet powerful tool for database administrators and developers. It provides an easy way to retrieve the server version, which is essential for maintaining compatibility and leveraging the latest features offered by MariaDB. By incorporating the VERSION() function into your SQL queries and database procedures, you can ensure that your applications remain robust and up-to-date.