Mysql 简明教程
MySQL - Boolean Datatype
Boolean 数据类型用于表示逻辑和布尔代数的真值。它有两个可能的值:真或假。
A Boolean data type is used to represent truth values of logic and Boolean algebra. It has two possible values: either true or false.
例如,如果客户希望看到所有黑色的自行车,我们可以使用 BOOLEAN 运算符对它们进行筛选,如下表所示 −
For example, if a customer wants to see all the bikes that are black in colour, we can filter them using BOOLEAN operator, as given in the following table −

这里,“IS_BLACK”是 BOOLEAN 列,它会根据自行车的颜色返回 true 或 false 值。
Here, 'IS_BLACK' is the BOOLEAN column that returns either true or false values based on the colours of the bikes.
Boolean in MySQL
在 MySQL 中,没有内置的布尔或 Bool 数据类型。相反,MySQL 为我们提供了 TINYINT 数据类型来存储布尔值。
In MySQL, there is no built-in Boolean or Bool data type. Instead MySQL provides us with the TINYINT datatype to store the Boolean values.
MySQL 将值 0 视为 FALSE,将值 1 视为 TRUE。我们还可以使用 TINYINT 数据类型存储 NULL 值。
MySQL considers the value 0 as FALSE and 1 as TRUE. We can also store NULL values using the TINYINT datatype.
Syntax
以下是 MySQL 中 BOOLEAN 运算符的语法 −
Following is the syntax of the BOOLEAN operator in MySQL −
CREATE TABLE table_name (
Column_name BOOLEAN
);
Example
在 MySQL 中,0 被定义为 FALSE,任何非零值被定义为 TRUE −
In MySQL, 0 is defined as FALSE and any non-zero values are defined as TRUE −
SELECT TRUE, FALSE;
Output
如我们从下面的输出中看到的,TRUE 和 FALSE 分别表示为 1 和 0 −
As we can see in the output below, TRUE and FALSE are represented as 1 and 0 −
Example
在 MySQL 中,布尔值(TRUE 和 FALSE)不区分大小写 −
In MySQL, the Boolean values (TRUE and FALSE) are case-insensitive −
SELECT true, false, TRUE, FALSE, True, False;
Example
现在,使用以下查询创建名为 CUSTOMERS 的表。此处,AVAILABILITY 列指定了客户是否可用。如果位值是 0 (FALSE),则客户不可用。如果它为 1(TRUE),则客户可用 −
Now, let’s create a table with the name CUSTOMERS using the following query. Here, the AVAILABILITY column specifies whether the customer is available or not. If the bit value is 0 (FALSE), the customer is not available. If it is 1(TRUE), the customer is available −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(40),
AVAILABILITY BOOLEAN
);
下面是生成的输出−
Following is the output produced −
Query OK, 0 rows affected (0.02 sec)
要获取有关 CUSTOMERS 表的信息,请使用以下查询 −
To get the information about the CUSTOMERS table, use the following query −
DESCRIBE CUSTOMERS;
如果我们查看在创建表时已设置为 BOOLEAN 的 AVAILABILITY 列,它现在显示 TINYINT 的类型 −
If we look at the AVAILABILITY column, which has been set to BOOLEAN while creating the table, it now shows type of TINYINT −
现在,让我们使用以下 INSERT 查询在 CUSTOMERS 表中插入一些记录 −
Now, let us insert some records into the CUSTOMERS table using the following INSERT query −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', FALSE),
(2, 'Khilan', TRUE),
(4, 'Kaushik', NULL);
使用以下查询,我们可以显示表中的所有值 −
Using the below query, we can display all the values in the table −
SELECT * FROM CUSTOMERS;
我们可能会看到 AVAILABILITY 列中的值分别设置为 0 和 1。−
We can see the values in the AVAILABILITY column are set to 0 and 1 respectively. −
Replacing BOOLEAN 0,1 with TRUE and FALSE
如我们在上面的 CUSTOMERS 表中看到的,BOOLEAN 数据类型显示 0 和 1 值,而不是 TRUE 和 FALSE。在 MySQL 中,我们可以使用 CASE 语句将 BOOLEAN 数据类型转换为 TRUE 和 FALSE 值。
As we can see in the above CUSTOMERS table, the BOOLEAN data type shows 0 and 1 values instead of TRUE and FALSE. In MySQL, we can convert BOOLEAN data type to TRUE and FALSE values using the CASE statement.
MySQL CASE 语句是一个条件语句,它遍历条件,并在满足第一个条件时返回一个值。因此,一旦一个条件为真,它将会停止读取下一段代码并返回结果。
The MySQL CASE statement is a conditional statement that goes through conditions and return a values when the first condition is met. Therefore, once a condition is true, it will stop reading the next piece of code and return the result.
如果没有条件为真,它将返回 ELSE 子句中的值。如果没有 ELSE 子句,也没有条件为真,它将返回 NULL。
If no conditions are true, it will return the value in the ELSE clause. If no ELSE clause is present and no conditions are true, it returns NULL.
Syntax
以下是 MySQL 中 CASE 语句的语法 −
Following is the syntax of CASE statement in MySQL −
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE result
END;
Example
为了更好地理解,让我们考虑使用以下查询创建的 BIKES 表 −
To get a better understanding, let us consider the BIKES table created using the query below −
CREATE TABLE BIKES (
S_NO INT AUTO_INCREMENT PRIMARY KEY,
COMPANY VARCHAR(40) NOT NULL,
PRICE INT NOT NULL,
COLOUR VARCHAR(40) NOT NULL,
IS_BLACK BOOLEAN
);
Example
上述代码的输出如下:
Output of the above code is as follows −
Query OK, 0 rows affected (0.03 sec)
现在,让我们使用 INSERT 语句将值插入到 BIKES 表中,如下所示 −
Now, let us insert values into the BIKES table using the INSERT statement as shown below −
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK)
VALUES ('Royal Enfield', 300000, 'Black', 1);
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK)
VALUES ('BMW', 900000, 'Blue', 0);
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK)
VALUES ('Jawa', 150000, 'Black', 1);
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK)
VALUES ('Triumph', 1200000, 'Red', 0);
获得的 BIKES 表如下 −
The BIKES table obtained is as follows −
现在,让我们显示 BIKES 表中的所有记录,其中颜色 BLACK 由 TRUE 或 FALSE 表示 −
Now, let us display all the records from the BIKES table, where the colour BLACK is represented by either TRUE or FALSE −
SELECT *,
CASE IS_BLACK
WHEN 1 THEN 'TRUE'
WHEN 0 THEN 'FALSE'
END AS IS_BOOLEAN
FROM BIKES;
Example
在以下查询中,我们正在过滤 BIKES 表中颜色黑色为 TRUE 的记录 −
In the following query, we are filtering the records from the BIKES table where colour black is TRUE −
SELECT * FROM BIKES WHERE IS_BLACK IS TRUE;
Output
如我们从下面的输出中看到的,Royal Enfield 和 Jawa 是黑色(true)−
As we can see the output below, Royal Enfield and Jawa are black in color (true) −
Boolean Operator Using a Client Program
除了使用 MySQL 查询在 MySQL 表中执行布尔运算符外,我们还可以使用客户端程序对表执行另一种操作。
In addition to perform the Boolean Operator in MySQL table using MySQL query, we can also perform the another operation on a table using a client program.
MySQL 提供了各种连接器和 API,你可以使用这些连接器和 API 编写程序(用各自的编程语言),以便与 MySQL 数据库通信。提供的连接器使用 Java、PHP、Python、JavaScript、C++ 等编程语言。本部分提供了在 MySQL 表中执行布尔运算符的程序。
MySQL provides various Connectors and APIs using which you can write programs (in the respective programming languages) to communicate with the MySQL database. The connectors provided are in programming languages such as, Java, PHP, Python, JavaScript, C++ etc. This section provides programs to execute Boolean Operator in MySQL Table.