Apart from the functions defined in previous chapters, MySQL has a range of other useful functions. Some, more frequently used, are listed in sub-chapters below:

RAND()

The RAND() function produces a random number from 0 to 1. It can be used to select a random row from a table. The statement below displays the data of the table in a random order.

SELECT * FROM tblstudent ORDER BY RAND();

ABS(num)

The ABS() function returns the absolute value of an input number. The example below shows how:

SELECT ABS(-2);

The example produces '2' as output.

BIT_COUNT(num)

The COUNT() function returns the number of active bits from an input number. The example:

SELECT BIT_COUNT(2);

This gives output of '1'.

CEIL(num) or CEILING(num)

The CEIL() or CEILING() functions return rounded value of an input number. The returned number is rounded to the higher side of that input number. The example below explains how:

SELECT CEILING(5.2);

The examples outputs of '6'.

EXP(num)

The EXP() function returns the value of a raised to the power of 'num'. As shown here:

SELECT EXP(3);

This results with the output of '20.085537'.

FLOOR(num)

The FLOOR() function gives the rounded value of an input number. The returned number is rounded to the lower side of the input number.

SELECT FLOOR(5.2);

The example above outputs 5.

MOD(N,M)

The MOD() function returns the mod of 'N' divided by 'M'. The example below shows how:

SELECT MOD(10,2);

The result is '0'.

POWER(X,Y)

The POWER() function returns the value of 'X' to the power of 'Y', as shown here:

SELECT POW(2, 3);

The result is '8'.

SQRT(num)

The SQRT function returns the square root of a given number. The following example shows how:

SELECT SQRT(49);

The result of the square root from above is '7'.

TRUNCATE(num,val)

The TRUNCATE() function truncates the value of 'num' up to the decimal places passed as 'val' variable. This example shows a practical statement:

SELECT TRUNCATE(4.567, 2);

The example above results in number '4.56'.

VERSION()

The VERSION() function returns the string indicating the used MySQL version number.

SELECT VERSION();

The output may be something like '5.6.25-standard'.

DATABASE()

The DATABASE() function returns the current database name.

SELECT DATABASE();

The output is a name of our database, such as 'mydatabase'.

USER()

The USER() function returns the current MySQL user name and host name as a string of utf8 character set.

SELECT USER();

The output of this example is something like 'brenkoweb_user@localhost'.

CHARSET(str)

The CHARSET() function returns the character set of the input string.

SELECT CHARSET('abc');

The example outputs 'latin1_swedish_ci' or any other used charset.

COLLATION(str)

The COLLATION() function returns the collation set of the input string.

SELECT COLLATION('abc');

The example outputs 'latin1' or similar.