为什么 TINYINT(1) 用作布尔值而 INT(1) 没有?
为什么 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.
相关文章