{"id":10870,"date":"2023-10-09T07:35:54","date_gmt":"2023-10-09T14:35:54","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/mysql-aggregate-functions\/mysql-bit_and\/"},"modified":"2023-11-09T17:57:13","modified_gmt":"2023-11-10T00:57:13","slug":"mysql-bit_and","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-aggregate-functions\/mysql-bit_and\/","title":{"rendered":"MySQL BIT_AND() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL BIT_AND<\/code> function to perform bitwise AND of all bits in a column of a table.<\/p>\n\n\n\n

Introduction to MySQL BIT_AND() function<\/h2>\n\n\n\n

MySQL BIT_AND()<\/code> function is an aggregate function<\/a> that performs a bitwise AND <\/strong>operation of all bits in a column of a table. <\/p>\n\n\n\n

The bitwise AND compares two binary strings of the same length. It examines each pair of corresponding bits and performs the logical AND operation on them. If both bits are 1, the result is 1 otherwise the result is 0.<\/p>\n\n\n\n

The following example performs a bitwise AND on two binary numbers: 0101 (decimal 5) and 0011 (decimal 3) that results in 0001 (decimal 1):<\/p>\n\n\n

0101 (decimal 5)\n0011 (decimal 3)\n----\n0001 (decimal 1)<\/code><\/span><\/pre>\n\n\n
Here’s the basic syntax of the BIT_AND()<\/code> function:<\/p>\n\n\n
SELECT<\/span> \n  BIT_AND<\/span>(column_name) \nFROM<\/span> \n  table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In practice, you may store data in binary form or use flags represented as individual bits. <\/p>\n\n\n\n
For example, in a permissions or access control, each bit can represent a specific permission such as read, write, and execute. The BIT_AND()<\/code> function allows you to manipulate permission data at the bit level more efficiently.<\/p>\n\n\n\n
MySQL BIT_AND() function example<\/h2>\n\n\n\n
First, create a new table<\/a> user_permissions<\/code> that has two columns user_id<\/code> and permission_mask<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> user_permissions (\n    user_id INT<\/span> PRIMARY KEY<\/span>,\n    permission_mask INT<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> user_permissions (user_id, permission_mask)\nVALUES<\/span>\n    (1<\/span>, 7<\/span>),  -- Binary: 0111<\/span>\n    (2<\/span>, 3<\/span>),  -- Binary: 0011<\/span>\n    (3<\/span>, 5<\/span>);  -- Binary: 0101<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we use three rows, each with a user_id<\/code> and a permission_mask<\/code> represented in binary form.<\/p>\n\n\n\n
Third, find the common permissions among the users using the BIT_AND()<\/code> function:<\/p>\n\n\n
SELECT<\/span> \n  BIT_AND<\/span>(permission_mask) AS<\/span> common_permissions \nFROM<\/span> \n  user_permissions;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------------------+<\/span>\n| common_permissions |\n+--------------------+<\/span>\n|                  1 |\n+--------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The result of BIT_AND(permission_mask)<\/code> is  1<\/code>, which corresponds to the binary 0001<\/code>. It means that the common permission of the three users is the first permission (the rightmost bit).<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL BIT_AND()<\/code> function to return the bitwise AND of all bits in a column of a table.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, insert some rows<\/a> into the user_permissions<\/code> table:<\/p>\n\n\n