A2-04-01.MySQL DATA TYPES-Pramatic Uses of MySQL BIT Data Type

转载自：http://www.mysqltutorial.org/mysql-bit/

Home / Basic MySQL Tutorial / Pramatic Uses of MySQL BIT Data Type

Pramatic Uses of MySQL BIT Data Type

 

Summary: this tutorial introduces you to MySQL BIT data type that allows you to store bit values.

Introduction to MySQL BIT data type

MySQL provides the BIT type that allows you to store bit values. The BIT(m) can store up to m-bit values, which m can range from 1 to 64.

The default value of m is 1 if you omit it. So the following statements are the same:

 

1	column_name BIT(1);

and

 

1	column_name BIT;

To specify a bit value literal, you use b'val' or 0bval notation, which val is a binary value that contains only 0 and 1.

The leading b can be written as B, for example:

 

1 2

b01 B11

are the valid bit literals.

However, the leading 0b is case-sensitive, therefore, you cannot use 0B. The following is an invalid bit literal value:

 

1

0B'1000'

By default the character set of a bit-value literal is the binary string as follows:

 

1	SELECT CHARSET(B'); -- binary

 

MySQL BIT examples

The following statement creates a new table named working_calendar that has the days column is BIT(7):

CREATE TABLE working_calendar(
    y INT,
    w INT,
    days BIT(7),
    PRIMARY KEY(y,w)
);

　　

The values in days column indicate the working day or day off i.e., 1: working day and 0: day off.

Suppose the Saturday and Friday of the first week of 2017 are not the working days, you can insert a rowinto the working_calendar table as follows:

 

1 2	INSERT INTO working_calendar(y,w,days) VALUES(2017,1,B'1111100');

The following query retrieves data from the working_calendar table:

 

1 2 3 4

SELECT     y, w , days FROM     working_calendar;

As you see, the bit value in the  days column is converted into an integer. To represent it as bit values, you use the BIN function:

 

1 2 3 4

SELECT     y, w , bin(days) FROM     working_calendar;

If you insert a value to a BIT(m) column that is less than m bits long, MySQL will pad zeros on the left of the bit value.

Suppose the first day of the second week is off, you can insert 01111100 into the  days column. However, the 111100 value will also work because MySQL will pad one zero on the left.

 

1 2	INSERT INTO working_calendar(y,w,days) VALUES(2017,2,B'111100');

To view the data you use the same query as above:

 

1 2 3 4

SELECT     y, w , bin(days) FROM     working_calendar;

As you can see, MySQL removed the leading zeros prior returning the result. To display it correctly, you can use the LPAD function:

 

1 2 3 4

SELECT     y, w , lpad(bin(days),7,'0') FROM     working_calendar;

It is working fine now.

In this tutorial, you have learned how to use the MySQL BIT type to store bit values.