转载自：http://www.mysqltutorial.org/mysql-bit/
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.