MySql: Tinyint(2)与Tinyint(1)有什么区别?

I knew boolean in mysql as tinyint (1).

我知道mysql中的布尔值为tinyint(1)。

Today I see a table with defined an integer like tinyint(2), and also others like int(4), int(6) ...

今天，我看到一个定义了整数的表，像tinyint(2)，还有一些像int(4)、int(6)…

What does the size means in field of type integer and tinyint ?

在类型为integer和tinyint的字段中，大小意味着什么?

4 个解决方案

#1

It means display width

这意味着显示宽度

Whether you use tinyint(1) or tinyint(2), it does not make any difference.

无论您使用tinyint(1)还是tinyint(2)，它都没有任何区别。

I always use tinyint(1) and int(11), I used several mysql clients (navicat, sequel pro).

我总是使用tinyint(1)和int(11)，我使用了几个mysql客户端(navicat, sequel pro)。

It does not mean anything AT ALL! I ran a test, all above clients or even the command-line client seems to ignore this.

这没有任何意义!我运行了一个测试，上面的所有客户端甚至是命令行客户端似乎都忽略了这一点。

But, display width is most important if you are using ZEROFILL option, for example your table has following 2 columns:

但是，如果使用ZEROFILL选项，显示宽度是最重要的，例如，您的表有以下两列:

A tinyint(2) zerofill

一个非常小的整数(2)补零

B tinyint(4) zerofill

B非常小的整数(4)补零

both columns has the value of 1, output for column A would be 01 and 0001 for B, as seen in screenshot below :)

两列的值都为1，列A的输出值为01，而B的值为0001，如下面的屏幕截图所示:

#2

190

The (m) indicates the column display width; applications such as the MySQL client make use of this when showing the query results.

(m)表示列显示宽度;像MySQL客户端这样的应用程序在显示查询结果时就利用了这一点。

For example:

例如:

| v   | a   |  b  |   c |
+-----+-----+-----+-----+
| 1   | 1   |  1  |   1 |
| 10  | 10  | 10  |  10 |
| 100 | 100 | 100 | 100 |

Here a, b and c are using TINYINT(1), TINYINT(2) and TINYINT(3) respectively. As you can see, it pads the values on the left side using the display width.

这里a、b和c分别使用TINYINT(1)、TINYINT(2)和TINYINT(3)。如您所见，它使用显示宽度填充左侧的值。

It's important to note that it does not affect the accepted range of values for that particular type, i.e. TINYINT(1) still accepts [-128 .. 127].

需要注意的是，它并不影响特定类型的值的接受范围，即TINYINT(1)仍然接受[-128 ..127]。

#3

mysql> CREATE TABLE tin3(id int PRIMARY KEY,val TINYINT(10) ZEROFILL);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO tin3 VALUES(1,12),(2,7),(4,101);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tin3;
+----+------------+
| id | val        |
+----+------------+
|  1 | 0000000012 |
|  2 | 0000000007 |
|  4 | 0000000101 |
+----+------------+
3 rows in set (0.00 sec)

mysql>

mysql> SELECT LENGTH(val) FROM tin3 WHERE id=2;
+-------------+
| LENGTH(val) |
+-------------+
|          10 |
+-------------+
1 row in set (0.01 sec)


mysql> SELECT val+1 FROM tin3 WHERE id=2;
+-------+
| val+1 |
+-------+
|     8 |
+-------+
1 row in set (0.00 sec)

#4

About the INT, TINYINT... These are different data types, INT is 4-byte number, TINYINT is 1-byte number. More information here - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT.

INT,非常小的整数…它们是不同的数据类型，INT是4字节数，TINYINT是1字节数。更多信息在这里-整数，INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT。

The syntax of TINYINT data type is TINYINT(M), where M indicates the maximum display width (used only if your MySQL client supports it).

TINYINT数据类型的语法是TINYINT(M)，其中M表示最大显示宽度(仅在MySQL客户端支持时使用)。

Numeric Type Attributes.

数值类型的属性。

#1