MySQL Bit Functions

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

MySQL BIT_COUNT() function

BIT_COUNT() function

MySQL BIT_COUNT() returns the number of bits set in the argument.

Syntax :

BIT_COUNT(N)

Where N is an integer.

Example : MySQL BIT_COUNT() function

The following MySQL statement will return the number of bits for the specified numbers in the argument.

  1. SELECT BIT_COUNT(8),BIT_COUNT(24),BIT_COUNT(28),BIT_COUNT(255);  

Output :

mysql> SELECT BIT_COUNT(8),BIT_COUNT(24),BIT_COUNT(28),BIT_COUNT(255);

+--------------+---------------+---------------+----------------+

| BIT_COUNT(8) | BIT_COUNT(24) | BIT_COUNT(28) | BIT_COUNT(255) |

+--------------+---------------+---------------+----------------+

|            1 |             2 |             3 |              8 |

+--------------+---------------+---------------+----------------+

1 row in set (0.01 sec)

PHP script

   

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>php mysql examples</title>
</head>
<body>
<?php
echo "<h2>PHP MySQL BIT_COUNT() example : </h2>";
echo "<table border='1' style='border-collapse: collapse;border-color: silver;'>";
echo "<tr style='font-weight: bold;'>";
echo "<td width='100' align='center'>BIT_COUNT(8)</td><td width='100' align='center'>BIT_COUNT(24)</td><td width='100' align='center'>BIT_COUNT(28)</td><td width='100' align='center'>BIT_COUNT(255)</td>";
echo "</tr>";
include("../dbopen.php");
$result = mysql_query("SELECT BIT_COUNT(8),BIT_COUNT(24),BIT_COUNT(28),BIT_COUNT(255)");
while($row=mysql_fetch_array($result))
{
echo "<tr>";
  echo "<td align='center' width='200'>" . $row["BIT_COUNT(8)"] . "</td>";  echo "<td align='center' width='200'>" . $row["BIT_COUNT(24)"] . "</td>";
  echo "<td align='center' width='200'>" . $row["BIT_COUNT(28)"] . "</td>";  echo "<td align='center' width='200'>" . $row["BIT_COUNT(255)"] . "</td>";
echo "</tr>";
}
echo "</table>";
?>
</body>
</html>

Copy

Example - 2

The following MySQL statement will return the number of bits for arguments specified.

Code:

SELECT BIT_COUNT(b'1000'),BIT_COUNT(b'11000'),BIT_COUNT(b'11111111');

Copy

Sample Output:

mysql> SELECT BIT_COUNT(b'1000'),BIT_COUNT(b'11000'),BIT_COUNT(b'11111111');
+--------------------+---------------------+------------------------+
| BIT_COUNT(b'1000') | BIT_COUNT(b'11000') | BIT_COUNT(b'11111111') |
+--------------------+---------------------+------------------------+
|                  1 |                   2 |                      8 | 
+--------------------+---------------------+------------------------+
1 row in set (0.00 sec)

Calculate the number of days per month a user has visited a web page

In the following example we have used bit group functions and operators to calculate the number of days per month a user has visited a web page. The example table contains year-month-day values representing visits by users to the page. Here is the table structure with some raw data :

mysql> CREATE TABLE table1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL);

Query OK, 0 rows affected (0.48 sec)

mysql> INSERT table1 VALUES(2012, 1, 1), (2012, 1, 20), (2012, 1, 30), (2012, 2, 2), (2012, 2, 24), (2012, 2, 24);

Query OK, 6 rows affected (0.22 sec)

mysql> SELECT * FROM table1;

+------+-------+------+

| year | month | day  |

+------+-------+------+

| 2012 |    01 |   01 |

| 2012 |    01 |   20 |

| 2012 |    01 |   30 |

| 2012 |    02 |   02 |

| 2012 |    02 |   24 |

| 2012 |    02 |   24 |

+------+-------+------+

6 rows in set (0.00 sec)

Use the following query to determine how many different days in each month these visits occur. The query calculates how many different days appear in the table for each year-month combination, with automatic removal of duplicate entries.

mysql> SELECT year, month, BIT_COUNT(BIT_OR(1<

+------+-------+------+

| year | month | days |

+------+-------+------+

| 2012 |    01 |    3 |

| 2012 |    02 |    2 |

+------+-------+------+

2 rows in set (0.03 sec)