{"id":5589,"date":"2017-02-08T01:46:29","date_gmt":"2017-02-08T08:46:29","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5589"},"modified":"2024-01-05T01:49:22","modified_gmt":"2024-01-05T08:49:22","slug":"mysql-bit","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-bit\/","title":{"rendered":"MySQL BIT Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL BIT<\/code> data type and how to store BIT<\/code> data in a column of a table.<\/p>\n\n\n\n

Introduction to MySQL BIT data type<\/h2>\n\n\n\n

The BIT<\/code> data type that allows you to store bit values, which are 0 and 1. <\/p>\n\n\n\n

Here’s the syntax for defining BIT<\/code> type of a column:<\/p>\n\n\n

column_name BIT(n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The BIT(n)<\/code> can store up to n-bit values. The n<\/code> can range from 1 to 64. The default value of n is 1 if you skip it. <\/p>\n\n\n\n
So the following syntaxes are equivalent:<\/p>\n\n\n
column_name BIT(1);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
and<\/p>\n\n\n
column_name BIT;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
BIT literals<\/h3>\n\n\n\n
To specify a bit value literal, you use b'val'<\/code> or 0bval<\/code> notation, which val<\/code> is a binary value that contains only 0 and 1.<\/p>\n\n\n\n
The leading b<\/code> can be written as B<\/code>, for example, the following are valid bit literals:<\/p>\n\n\n
b01\nB11<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
However, the leading 0b<\/code> is case-sensitive. Therefore, you cannot use 0B<\/code>. For example, the following is an invalid bit literal value:<\/p>\n\n\n
0B'1000'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
By default the character set<\/a> of a bit-value literal is the binary string as follows:<\/p>\n\n\n
SELECT<\/span> CHARSET<\/span>(B'); -- binary<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL BIT data type examples<\/h2>\n\n\n\n
The following statement creates a new table<\/a> named working_calendars<\/code> that has the days column is BIT(7)<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> working_calendars(\n    year<\/span> INT<\/span>,\n    week<\/span> INT<\/span>,\n    days<\/span> BIT<\/span>(7<\/span>),\n    PRIMARY KEY<\/span>(year<\/span>,week<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The values in the column days<\/code> indicate whether the day is a working day or day off i.e., 1: working day and 0: day off.<\/p>\n\n\n\n
INSERT<\/span> INTO<\/span> working_calendars(year<\/span>,week<\/span>,days<\/span>)\nVALUES<\/span>(2017<\/span>,1<\/span>,B'1111100'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following query retrieves data from the working_calendar<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n    year<\/span>, week<\/span>, days<\/span>\nFROM<\/span>\n    working_calendars;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------+------+------------+\n| year | week | days       |\n+------+------+------------+\n| 2017<\/span> |    1<\/span> | 0x7C<\/span>       |\n+------+------+------------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> \n    year<\/span>, week<\/span>, bin<\/span>(days<\/span>)\nFROM<\/span>\n    working_calendars;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------+------+-----------+\n| year | week | bin(days) |\n+------+------+-----------+\n| 2017<\/span> |    1<\/span> | 1111100<\/span>   |\n+------+------+-----------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
If you insert a value into a BIT(n)<\/code> column that is less than n<\/code> bits long, MySQL will pad zeros on the left of the bit value.<\/p>\n\n\n\n
Suppose the first day of the second week is off, you can insert 01111100<\/code> into the  days <\/code>column. However, the 111100<\/code> value will also work because MySQL will pad one zero to the left.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> working_calendars(year<\/span>,week<\/span>,days<\/span>)\nVALUES<\/span>(2017<\/span>,2<\/span>,B'111100'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To view the data you use the same query as above:<\/p>\n\n\n
SELECT<\/span> \n    year<\/span>, week<\/span> , bin<\/span>(days<\/span>)\nFROM<\/span>\n    working_calendars; <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------+------+-----------+\n| year | week | bin(days) |\n+------+------+-----------+\n| 2017<\/span> |    1<\/span> | 1111100<\/span>   |\n| 2017<\/span> |    2<\/span> | 111100<\/span>    |\n+------+------+-----------+\n2<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> \n    year<\/span>, week<\/span>, lpad<\/span>(bin<\/span>(days<\/span>),7<\/span>,'0'<\/span>)\nFROM<\/span>\n    working_calendars;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------+------+-----------------------+\n| year | week | lpad(bin(days),7<\/span>,'0'<\/span>) |\n+------+------+-----------------------+\n| 2017<\/span> |    1<\/span> | 1111100<\/span>               |\n| 2017<\/span> |    2<\/span> | 0111100<\/span>               |\n+------+------+-----------------------+\n2<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The output shows the expected format.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use MySQL BIT<\/code> data type to store BIT<\/code> data in 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
The output shows that MySQL removed the leading zeros before returning the result. To display it correctly, you can use the LPAD()<\/a><\/code> function:<\/p>\n\n\n
The output indicates that the bit value in the \u00a0days<\/code> column\u00a0is converted into an integer. To represent it as bit values, you use the BIN<\/a><\/code> function:<\/p>\n\n\n
Suppose that Saturday and Friday of the first week of 2017 are not working days, you can insert a row<\/a> into the working_calendars<\/code> table:<\/p>\n\n\n