MySQL LOG2() and LOG10() Functions

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 (log2 and log10).

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);

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


Syntax of MySQL LOG10()

LOG10(number);

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 log2 10 and log2 3. The query for it is:

SELECT LOG2(10);
SELECT LOG2(3);

And the output is –

MySQL LOG2 Basic Example

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);

And we get the output as,

MySQL LOG2 Negative Value

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); 

And we get the output as –

Log2 Zero

LOG2() With Expressions

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

SELECT 1+3*LOG2(2);

And we get the output as follows.

Log2 Expression

Examples of MySQL LOG10()

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

SELECT LOG10(5); 
SELECT LOG10(10);

And the output is –

MySQL LOG10 Basic Example

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);

And we get the output as,

MySQL LOG10 Negative Value

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); 

And we get the output as –

Log10 Zero

LOG10() With Expressions

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

SELECT 5*LOG10(5)+3;

And we get the output as follows.

Log10 Expression

Using LOG2() and LOG10() With Tables

Consider the below Numbers table.

Numbers Table Log2
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;

And we get the output as follows –

Log2 Table Example 1

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;

And we get the output as,

Log10 Table Example 1

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;

And the output is,

Log2 Table Example 2

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;

And we get the output as –

Log10 Table Example 2

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