MySQL 中的 tinyint、smallint、mediumint、bigint 和 int 有什么区别?

2021-11-20 00:00:00 integer mysql

What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?

在什么情况下应该使用这些?

In what cases should these be used?

推荐答案

它们占用的空间不同，可接受的值范围也不同.

They take up different amounts of space and they have different ranges of acceptable values.

这里是 SQL 的大小和值范围服务器，其他 RDBMS 也有类似的文档:

Here are the sizes and ranges of values for SQL Server, other RDBMSes have similar documentation:

MySQL
Postgres
Oracle(他们只是确实有 NUMBER 数据类型)
DB2

MySQL

Postgres

Oracle (they just have a NUMBER datatype really)

DB2

事实证明它们都使用相同的规范(除了下面提到的一些小例外)，但支持这些类型的各种组合(Oracle 不包括在内，因为它只有一个 NUMBER 数据类型，请参阅上面的链接):

Turns out they all use the same specification (with a few minor exceptions noted below) but support various combinations of those types (Oracle not included because it has just a NUMBER datatype, see the above link):

| SQL Server MySQL Postgres DB2 --------------------------------------------------- tinyint | X X smallint | X X X X mediumint | X int/integer | X X X X bigint | X X X X

并且它们支持相同的值范围(下面有一个例外)并且都具有相同的存储要求:

And they support the same value ranges (with one exception below) and all have the same storage requirements:

| Bytes Range (signed) Range (unsigned) -------------------------------------------------------------------------------------------- tinyint | 1 byte -128 to 127 0 to 255 smallint | 2 bytes -32768 to 32767 0 to 65535 mediumint | 3 bytes -8388608 to 8388607 0 to 16777215 int/integer | 4 bytes -2147483648 to 2147483647 0 to 4294967295 bigint | 8 bytes -9223372036854775808 to 9223372036854775807 0 to 18446744073709551615

无符号"类型仅在 MySQL 中可用，其余类型仅使用有符号范围，有一个明显的例外:SQL Server 中的 tinyint 是无符号的，并且具有取值范围为 0 到 255

The "unsigned" types are only available in MySQL, and the rest just use the signed ranges, with one notable exception: tinyint in SQL Server is unsigned and has a value range of 0 to 255

相关文章