Mysql 简明教程
MySQL - BIT
在编程语言中,位表示基本数据单元。它只能存储两个值,表示为 0 或 1。
A bit represents the basic unit of data in programming languages. It can store only two values, represented as 0 or 1.
The MySQL BIT Data Type
MySQL BIT 数据类型用于存储指定范围内的二进制值。范围由你分配给 BIT 列的位数决定。
The MySQL BIT data type is used to store binary values within a specified range. The range is determined by the number of bits you allocate to the BIT column.
如果我们尝试插入一个整数,而不是 BIT 值,MySQL 会将其自动转换为 BIT 值。我们必须确保要添加的整数必须在可转换为 BIT 值的范围内。
If we try to insert an integer value instead of BIT values, MySQL automatically converts them into BIT values. We have to ensure that the integer value we are adding must be within the range for conversion to BIT values.
例如,如果你有一个 BIT(3) 列,它可以在二进制中存储从 000 到 111 的值,这在整数格式中对应于 0 到 7。如果你尝试将整数 8 插入此 BIT(3) 列,你将收到一条错误消息,因为 8 在二进制中是 1000,超出此列的有效范围。
For instance, if you have a BIT(3) column, it can store values from 000 to 111 in binary, which corresponds to 0 to 7 in integer format. If you try to insert the integer 8 into this BIT(3) column, you’ll get an error because 8 in binary is 1000, which is outside the valid range of the column.
Syntax
以下是 MySQL BIT 数据类型语法:
Following is the syntax of the MySQL BIT datatype −
BIT(n)
此处, n 值的范围为 1 到 64。如果你没有提供“n”值,默认值为 1,产生一个单比特 BIT 列。因此,以下查询将产生相同的结果:
Here, the range of n value is from 1 to 64. If you don’t provide the "n" value, the default is 1, resulting in a single-bit BIT column. Hence, the following queries will give the same output −
Column_name BIT(1);
and
Column_name BIT;
Bit Value Literal
-
To specify bit value literals, you can use the b’val or 0bval notations, where val is a binary value containing only 0s and 1s. The leading 'b' is case-insensitive.
-
Note that the 0b notation is case-sensitive, so 0B'1000' is an invalid bit literal value.
Example
让我们创建一个名为 STUDENTS 的表,并对 AGE 列使用 BIT 数据类型,如下所示 −
Let us create a table named STUDENTS and use the BIT data type for the AGE column as shown below −
CREATE TABLE STUDENTS(
ID int auto_increment,
NAME varchar(40),
AGE BIT(3),
primary key (ID)
);
以下是所获得的输出 −
Following is the output obtained −
Query OK, 0 rows affected (0.01 sec)
现在,我们将 AGE 列中的值“5”和“3”插入到 STUDENTS 表中 −
Now, we are inserting the values "5" and "3" into the AGE column of the STUDENTS table −
INSERT INTO STUDENTS (NAME, AGE) VALUES
('Varun', 5),
('Akash', 3);
上述查询的输出如下所示:
Output of the above query is as shown below −
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
我们可以使用以下查询显示 STUDENTS 表中 AGE 列中插入的值 −
We can use the following query to display the inserted values in the AGE column of the STUDENTS table −
SELECT * from STUDENTS;
我们可以在下面的输出中看到,值“5”和“3”以二进制格式存储 −
We can see in the output below that the values "5" and "3" are stored in binary format −
现在,让我们插入另一个值“10”。在二进制格式中,“10”表示为“1010”。然而,我们定义的 AGE 列只能达到三个位。因此,以下查询将生成一个错误,因为值 10 大于 7 −
Now, let us insert another value "10". In binary format, "10" is represented as "1010". However, we defined the AGE column to have a range of only three bits. Therefore, the following query will generate an error because the value 10 is greater than 7 −
INSERT INTO STUDENTS (NAME, AGE) VALUES ('Priya', 10);
输出表明数据对 AGE 列来说太长了。
The output indicates that the data is too long for the AGE column.
ERROR 1406 (22001): Data too long for column 'AGE' at row 1
要将位值文字插入到 “AGE” 列中,您可以使用 B’val 表示法。在这里,我们插入的是“110”,它等价于整数值“6”,如下所示 −
To insert bit value literals into the "AGE" column, you can use the B’val notation. Here, we are inserting "110" which is equivalent to the integer value "6" as shown below −
INSERT INTO STUDENTS (NAME, AGE) VALUES('Priya', B'110');
获得的结果如下 −
The result obtained is as follows −
Query OK, 1 row affected (0.01 sec)
让我们使用以下查询显示 “STUDENTS” 表中的所有记录 −
Let us display all the records in the "STUDENTS" table using the following query −
SELECT * from STUDENTS;
我们可以在下面的输出中看到,值“6”已被以二进制格式插入为“0x06” −
We can see in the output below that the value "6" has been inserted in binary format as "0x06" −
Verification
要验证并以二进制/位格式显示“AGE”列中的插入值,您可以使用 MySQL BIN()函数 −
To verify and display the inserted values in the "AGE" column in binary/bit format, you can use the MySQL BIN() function −
SELECT ID, NAME, BIN(AGE) FROM STUDENTS;
输出以二进制格式显示值 −
The output shows the values in binary format −
在上面的输出中,我们可以看到前导零被移除了。如果我们希望显示它们,我们可以使用 LPAD 函数,如下所示 −
In the above output, we can see that the leading zeros are removed. If we want to display them, we can use the LPAD function as shown below −
SELECT ID, NAME, LPAD(BIN(AGE), 5, "0") FROM STUDENTS;
以下是所获得的输出 −
Following is the output obtained −