MySQL LOG2() and LOG10() Functions

In this tutorial, we will study the MySQL LOG2() and MySQL LOG10() functions. In a previous article, we studied about the MySQL LN() function which finds the natural logarithm (base e) of a number.

While studying maths, you must have also come across finding the logarithm to the base 2 or 10 of a number (log₂and log₁₀).

Since these are also widely used logarithmic operations, MySQL provides us with the MySQL LOG2() and LOG10 functions.

The LOG2() function is used to return the base-2 logarithm of a given number.
The LOG10() function is used to return the base-10 logarithm of a given number.

Syntax of MySQL LOG2()

LOG2(number);Code language: SQL (Structured Query Language) (sql)

Where ‘number’ is a numerical value whose base-2 logarithm is to be found out.

Syntax of MySQL LOG10()

LOG10(number);Code language: SQL (Structured Query Language) (sql)

Where ‘number’ is a numerical value whose base-10 logarithm is to be found out.

Examples of MySQL LOG2()

Let us start by looking at a couple of basic examples. Let us find the values of log₂ 10 and log₂ 3. The query for it is:

SELECT LOG2(10);
SELECT LOG2(3);Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL LOG2() With Negative Values

If we pass negative values to the MySQL LOG2() function, it returns NULL. Let’s see this behavior using the below example.

SELECT LOG2(-4.2); 
SELECT LOG2(-15);Code language: SQL (Structured Query Language) (sql)

And we get the output as,

MySQL LOG2() With Zero

If we pass zero to the LOG2() function, it returns NULL. Let us see this using the below example.

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

And we get the output as –

LOG2() With Expressions

Let us write a query for the following expression using the LOG2() function – 1 + 3 log₂ 2.

SELECT 1+3*LOG2(2);Code language: SQL (Structured Query Language) (sql)

And we get the output as follows.

Examples of MySQL LOG10()

Now let us look at a couple of basic examples of the LOG10() function. Let us find the values of log₁₀ 5 and log₁₀ 10. The query for it is –

SELECT LOG10(5); 
SELECT LOG10(10);Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL LOG10() With Negative Values

If we pass negative values to the LOG10() function, it returns NULL. Let’s see this behaviour using the below example.

SELECT LOG10(-4.2); 
SELECT LOG10(-36);Code language: SQL (Structured Query Language) (sql)

And we get the output as,

MySQL LOG10() With Zero

If we pass zero to the LOG10() function, it returns NULL. Let us see this using the below example.

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

And we get the output as –

LOG10() With Expressions

Let us write a query for the following expression using the LOG10() function – 5 log₁₀(5) + 3.

SELECT 5*LOG10(5)+3;Code language: SQL (Structured Query Language) (sql)

And we get the output as follows.

Using LOG2() and LOG10() With Tables

Consider the below Numbers table.

Simple Example

Using the SELECT Statement, LOG2() function and aliases, let us display the base-2 logarithm of the values in the X column. The query for it is –

SELECT ID, X, LOG2(X) AS LOG2_OF_X FROM Numbers;Code language: SQL (Structured Query Language) (sql)

And we get the output as follows –

Let us do something similar and find and display the base-10 logarithm of the values in the X column. Our query is –

SELECT ID, X, LOG10(X) AS LOG10_OF_X FROM Numbers;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

LOG2() and LOG10() with the UPDATE Statement

Let us now add two columns to the Numbers table which store the base-2 logarithm values and the base-10 logarithmic values of the X column.

Let us start by creating a column named LOG2_OF_X of type float using the ALTER statement. We will then add the base-2 logarithm values of the X column in it using the UPDATE statement and finally display the new table. The query is –

ALTER TABLE Numbers ADD LOG2_OF_X float; 
UPDATE Numbers SET LOG2_OF_X=LOG2(X); 
SELECT * FROM Numbers;Code language: SQL (Structured Query Language) (sql)

And the output is,

Next, let us create a column named LOG10_OF_X of type float using the ALTER statement. We will then add the base-10 logarithm values of the X column in it using the UPDATE statement and finally display the new table. The query is –

ALTER TABLE Numbers ADD LOG10_OF_X float; 
UPDATE Numbers SET LOG10_OF_X=LOG10(X); 
SELECT * FROM Numbers;Code language: SQL (Structured Query Language) (sql)

And we get the output as –

Conclusion

Finding the logarithm of a number to the base-2 and base-10 is one of the most important and widely used logarithmic operations.

References

MySQL Official Documentation of LOG2() and LOG10().