MySQL Advance function

The advance function operates numerical values, string values, and data types.  The advance function converts, displays, and compares given values as per requirement. Here, you find out database, table, and MySQL interface information. You can also find table-related data like null data. The advance function mostly includes BINARY(), CONVERT(), DATABASE(), and ISNULL() functions.

MySQL advance function syntax

Mysql advance function syntax gives below.

SELECT advance _function();

MySQL advance function with position syntax shows below.

SELECT advance _function(value);

MySQL advance function with several values syntax shows below.

SELECT advance _function(value1, value2);

MySQL Advance Functions

The following table shows advanced functions and its description.

Function	Description
BIN	This function displays a binary representation of numerical data.
BINARY	This function converts a given value into binary data.
CAST	This function converts a given data type value into a required datatype value.
COALESCE	This function displays the first non-null value in table data.
CONNECTION_ID	This function displays the unique and current connection ID.
CONV	This function converts a one-number base system to another numerical system.
CONVERT	This function converts a value into the required data type or character set of the table.
CURRENT_USER	This function displays the user name or hostname of the MySQL system.
DATABASE	This function displays the name of the current database.
IF	This function declares true and false conditions. If a condition is TRUE, then it displays the output.
IFNULL	This function declares a specific value that is null. If the query is not null, then it displays the given value.
ISNULL	This function is Boolean and displays 1 and 0 values based on null data.
LAST_INSERT_ID	This function displays the last row of the AUTO_INCREMENT id. It helps to insert and update the table data.
NULLIF	This function compares two queries or values. If two values are equal, then it shows the NULL output. If two values are not equal, then shows first value is output.
SESSION_USER	It shows the exits user name or hostname of the MySQL system.
SYSTEM_USER	It returns the exits user name or hostname of the MySQL system.
USER	The user() function shows the exits user name or hostname of the MySQL system.
VERSION	The version() function displays the current version of the MySQL system.

Examples of the MySQL advance function

The advance function works on the numerical value, string data, and MySQL database system interface. You can use a function to get version, system, database, connection id and different users. Here, you can convert from one type of data to another format of data. This function finds the null value and not the null value of the given data.

1. Example of the advance function without value

MySQL function returns database, version, and system information of the MySQL data management system interface. This example uses four advanced functions without value. Here, you do not need any value with function.

mysql > SELECT USER(), VERSION(), CONNECTION_ID(), DATABASE();

Output

This output image returns the user name, version of the MySQL system, connection id, and current database. Each MySQL system provides different output information with functions.

2. Example of the advance function with different user

Execute the below MySQL query to get different users using the advance function. This advanced function provides current user, session user and system user. You can get the last insert id of the database system. This function returns either a stable value or a different value as output. This advanced user function does not require a specific value.

mysql > SELECT LAST_INSERT_ID(), 
               CURRENT_USER(), 
               SESSION_USER(), 
               SYSTEM_USER();

Output

The above image displays different system's users of the mysql system. The output shows the current, session, and system user of the interface. The MySQL user provides the same or different information as per requirement. The first function column return id of the last insert data.

3. Example of the advance function with a value

You can use binary (bin), conversion (conv), and cast functions in this function. This function returns a value with a specific format. This advanced function requires a specific value. The binary function shows a value of the numerical and string data. The conv function converts from binary to numerical and vice versa. The cast function returns particular data and other data.  

mysql > SELECT BIN(21) AS binary_number,
	CONV (21, 10, 2) AS decimal_binary,
	CONV (10010, 2, 10) AS binary_decimal,
             CAST("2021-08-21" AS DATE) AS date_value;

Output

The image shows the output of the bin, conv, and cast function. The 21 numerical value converts into a binary value. The conv function converts from 21 values into 10101 binary values. The 10010 binary number converts into 18 decimal values. The last function shows data using date cast.

4. Example of the advance function with a null value

The advance function provides isnull, ifnull, and nullif functions. This function determines either the value is null or not null.

mysql > SELECT ISNULL (NULL),
	NULLIF ("NULL", 59),
	IFNULL ("NULL", "MySQL");

OUTPUT

If a function contains a null value, then the advance function returns a null value. If MySQL query contains a value, then the advance function returns available data.