为什么 TINYINT(1) 用作布尔值而 INT(1) 没有? [英] Why does TINYINT(1) function as a boolean but INT(1) does not?

问题描述

为什么 TINYINT(1) 作为布尔值工作?我理解官方文档的方式，(1) 应该意味着它的显示宽度为 1，所以如果我在其中存储 56，我认为它应该打印 5.但由于某种原因，它总是打印 1 或 0.

Why does TINYINT(1) work as a boolean? The way I understood the official docs, the (1) should mean it has a display width of 1, so if I store 56 in it, I thought it should print 5. But for some reason it always prints either 1 or 0.

另一种情况是，如果我将 56 存储在 INT(1) 中，那么它会打印 56(至少根据 SQLFiddle).这是怎么回事?

And another case is if I store 56 in an INT(1), then it prints 56 (at least according to SQLFiddle). What's going on here?

推荐答案

MySQL 整数类型括号中的 (1) 与数据类型接受的值的范围或它的存储方式无关.它仅用于显示.

The (1) in parentheses for a MySQL integer type has nothing to do with the range of values accepted by the data type, or how it is stored. It's only for display.

另见我对 MySQL 中的类型的回答:BigInt(20) vs Int(20) 等.

TINYINT 与 TINYINT(1) 或 TINYINT(2) 或 TINYINT(64) 没有什么不同.它是一个 8 位有符号整数数据类型，它接受从 -128 到 127 的任何 8 位整数值.

TINYINT is no different from TINYINT(1) or TINYINT(2) or TINYINT(64). It's an 8-bit signed integer data type, and it accepts any 8-bit integer value from -128 to 127.

mysql> create table b (i tinyint(1));

mysql> insert into b values (42);

mysql> select * from b;
+------+
| i    |
+------+
|   42 |
+------+

为方便起见，MySQL 支持 BOOL 的别名，它立即被 TINYINT(1) 替换.

For convenience, MySQL supports an alias for BOOL, which is replaced immediately by TINYINT(1).

mysql> create table b2 (i bool);

mysql> show create table b2;

CREATE TABLE `b2` (
  `i` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

正如我所说，(1) 的使用几乎没有任何意义，它只是一种约定，因此如果您看到 TINYINT(1)，则可以合理地假设该列打算用作布尔值.但是 MySQL 中没有任何东西可以阻止您在其中存储其他整数值.

As I said, the use of (1) means almost nothing, it's only a convention so that if you see TINYINT(1) it's reasonable to assume the column is intended to be used as a boolean. But nothing in MySQL prevents you from storing other integer values in it.

如果您希望一列只接受 0 或 1，您可以使用 BIT(1):

If you want a column to accept only 0 or 1, you can use BIT(1):

mysql> create table b3 (i bit(1));

mysql> insert into b3 values (0), (1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into b3 values (-1);
ERROR 1406 (22001): Data too long for column 'i' at row 1

mysql> insert into b3 values (2);
ERROR 1406 (22001): Data too long for column 'i' at row 1

尽管与 TINYINT 相比，这并没有节省任何空间，因为给定列的存储空间向上舍入到最近的字节.

This doesn't save any space compared to TINYINT though, because the storage for a given column rounds up to the nearest byte.

PS:尽管来自@samdy1 的回答，TINYINT 根本不存储 strings '0' 或 '1'，它存储 integers 0 或 1，以及其他 -128 到 127 的整数. SQL 中不需要引用整数，我经常不明白为什么有这么多开发者这样做.

PS: Despite answer from @samdy1, TINYINT does not store strings '0' or '1' at all, it stores integers 0 or 1, as well as other integers from -128 to 127. There is no need to quote integers in SQL, and I am often puzzled why so many developers do.

这篇关于为什么 TINYINT(1) 用作布尔值而 INT(1) 没有?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！