如何将BOOL值插入MySQL数据库 [英] How to Insert BOOL Value to MySQL Database

问题描述

我正在尝试使用以下脚本将值插入MySQL(v 5.5.20)中的BOOL数据类型中:

I am trying to insert values into a BOOL data type in MySQL (v 5.5.20) using the following script:

CREATE DATABASE DBTest;
USE DBTest;
DROP TABLE IF EXISTS first;
CREATE TABLE first (id INT AUTO_INCREMENT NOT NULL PRIMARY KEY , name VARCHAR(30) ,sale  BOOL,); 
INSERT INTO first VALUES ("", "G22","TRUE");
INSERT INTO first VALUES ("", "G23","FALSE");

但是INSERT语句只是将TRUE和FALSE选项的0(零)插入到布尔列中！您能告诉我为什么会这样吗?

But the INSERT statement just inserts 0 (Zero) to the boolean column for both TRUE and FALSE options! Can you please let me know why this is happening?

推荐答案

TRUE和FALSE是关键字，不应用字符串引用:

TRUE and FALSE are keywords, and should not be quoted as strings:

INSERT INTO first VALUES (NULL, 'G22', TRUE);
INSERT INTO first VALUES (NULL, 'G23', FALSE);

通过将它们引用为字符串，MySQL然后将它们强制转换为它们的整数等效项(因为布尔值实际上只是MySQL中的一个字节INT)，对于任何非数字字符串，该值都将转换为零.因此，对于表中的两个值，您都会得到0.

By quoting them as strings, MySQL will then cast them to their integer equivalent (since booleans are really just a one-byte INT in MySQL), which translates into zero for any non-numeric string. Thus, you get 0 for both values in your table.

mysql> SELECT CAST('TRUE' AS SIGNED), CAST('FALSE' AS SIGNED), CAST('12345' AS SIGNED);
+------------------------+-------------------------+-------------------------+
| CAST('TRUE' AS SIGNED) | CAST('FALSE' AS SIGNED) | CAST('12345' AS SIGNED) |
+------------------------+-------------------------+-------------------------+
|                      0 |                       0 |                   12345 |
+------------------------+-------------------------+-------------------------+

但是关键字返回其对应的INT表示形式:

But the keywords return their corresponding INT representation:

mysql> SELECT TRUE, FALSE;
+------+-------+
| TRUE | FALSE |
+------+-------+
|    1 |     0 |
+------+-------+

还请注意，我已将双引号替换为单引号，这是更标准的SQL字符串附件.最后，我用NULL替换了id的空字符串.空字符串可能会发出警告.

Note also, that I have replaced your double-quotes with single quotes as are more standard SQL string enclosures. Finally, I have replaced your empty strings for id with NULL. The empty string may issue a warning.

这篇关于如何将BOOL值插入MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！