EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL BIT

MySQL BIT

Priya Pedamkar
Article byPriya Pedamkar

Updated June 5, 2023

MySQL BIT

Introduction to MySQL BIT

BIT is a data type used in MySQL. This type stores bit values within the range of 1-64. Generally, the BIT data type is defined in the CREATE TABLE or other defining SQL statements, and it is denoted as ‘BIT(n)’, where ‘n’ represents the number of bit values that can be stored. This ‘n’ value keeps the range from 1-64; if not defined in the statement, it defaults to a 1-bit value. This bit value will be storing binary values. We will discuss further on this data type below.

ADVERTISEMENT
Popular Course in this category
MYSQL Course Bundle - 18 Courses in 1 | 3 Mock Tests

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

The BIT data type describes the following:

BIT (n);

The keyword ‘BIT’ defines the data type to store binary values as bits, where the number of bits is specified by the variable ‘n’. You can use the following syntax to display the BIT data type as a binary value. And if otherwise, the corresponding decimal value will be returned.

BIN ( BIT (n))

Let’s look at the usage and examples of BIT data types.

How Does BIT Data Type Work in MySQL?

BIT (n) is defined with a statement like ‘create table’ etc. Please note that if the value’ n’ is not specified, the BIT value defaults to ‘1’ bit. We can create a table to understand the BIT data type. Our table is to store the attendance of students in a class for five working days. The table holds data for student name, attendance as BIT value in binary with ‘1’ denoting presence & ‘0’ denoting absence, and the class to which the student belongs. The create table statement is as below:

Query:

CREATE TABLE attendance (
STUDENT_NAME CHAR(50),
ATTENDANCE BIT(5),
CLASS INT
);

So the table name is attendance with three columns. We store the student’s name as a CHAR data type, the class as an INT data type, and the attendance as a BIT data type. The attendance field will have five values representing the five working days.

Let’s insert values into these fields.

Query:

INSERT INTO attendance (STUDENT_NAME, ATTENDANCE, CLASS)
VALUES ('ALAN',b'11111',5),
('EMIL',b'11000',5),
('ANNA',b'00111',5),
('JOHN',b'11101',5);

A bit literal can be used to insert a BIT value in SQL. Bit value literals can be defined using b’val’ or ‘0bval’.

The table now has four rows with data for four different student attendance.

We will retrieve the data with the SELECT query.

Query:

select * from attendance;

The simple SELECT query to retrieve complete data from table attendance.

Output:

MySQL BIT Example 1

The output retrieved needs to show correct data or data in our desired manner. We had input each student’s attendance for five days from Monday to Friday. But we haven’t specified how to display the bit value in the SELECT query while retrieving.

Now, let’s try retrieving the data as binary values themselves. For that, we need to call the column attendance as BIN() specifically.

Query:

SELECT STUDENT_NAME, BIN(ATTENDANCE), CLASS FROM attendance;

The BIN() function will display the binary values input as bit value literals in the binary format.

Output:

MySQL BIT Example 2

We now get each student’s attendance as ‘1’ and ‘0 ‘, thus making it easier to identify the days when each of them was present or absent. But Anna’s attendance is having a slight issue here. She was absent on Monday and Tuesday. So that was marked as ‘0’ for both days. Since the bit value literal started with zeros, it omitted the preceding zeros while displaying the output. This can confuse as the data is available only for three days out of 5 days.

To avoid this situation, we can use the LPAD and BIN functions.

Query:

SELECT STUDENT_NAME, LPAD(BIN(ATTENDANCE),5,'0') as ATTENDANCE, CLASS FROM attendance;

Output:

MySQL BIT Example 3

All attendance looks similar as the count of days is constantly five days.

There were several methods of retrieving a BIT data type in MySQL. In our variable definition statement, we had specified the number of bits in the variable attendance as 5 by the part of the query – attendance BIT(5). As discussed, this count can range from 1-64, and the default count is 1 when no digit is specified within ().

We can see some examples of different lengths in the BIT value.

Query:

CREATE TABLE sampleBITs (
sample1 BIT(10),
sample2 BIT,
sample3 BIT(50));

AI has created a table with three variables: sample1, sample2, and sample. Sample2 has no length specified, which means the length is 1. We can insert the values into this table.

Query:

INSERT INTO sampleBITs (sample1, sample2, sample3)
VALUES (b'1010101010', 0b1, B'10001100011000110001111111000110001100011100110001');
select * from sampleBITs;

Output:

Example 4

We have inserted values using all three valid bit value laterals here.

Query:

SELECT
LPAD(BIN(sample1),10,'0') as sample1,
LPAD(BIN(sample2),1,'0') as sample2,
LPAD(BIN(sample3),50,'0') as sample3
FROM sampleBITs;

Output:

Example 5

We have used LPAD and BIN functions to retrieve the data in a proper format.

NOTE: Bit literals can be specified as b’val’ or 0bval. The b’val’ can also be denoted as B’val’, as the literal is not case sensitive in this format. But the leading 0b is case sensitive and cannot be replaced with 0B. A bit-value literal is always a binary value by default in both notations.

Conclusion

In SQL, bit values are saved using the BIT data type, defined as BIT(n), where ‘n’ is the maximum number of bits that can be stored in the variable. The allowed value of ‘n’ ranges from 1 to 64. To input data into the BIT variable in SQL, bit value literals can be used, and the stored data can be retrieved using decimal, binary, or other data conversion functions.

Recommended Articles

We hope that this EDUCBA information on “MySQL BIT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Introduction to MySQL Operators
  2. Top 23 MySQL String functions
  3. MySQL vs SQLite | Top 14 Comparisons
  4. Guide to MySQL Timestamp
ADVERTISEMENT
C++ PROGRAMMING Course Bundle - 9 Courses in 1 | 5 Mock Tests
40+ Hour of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
ASP.NET Course Bundle - 28 Courses in 1 | 5 Mock Tests
123+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests
205+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
SOFTWARE TESTING Course Bundle - 13 Courses in 1
53+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW