MySQL 中的 tinyint、smallint、mediumint、bigint 和 int 有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2991405/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?
提问by Sein Kraft
What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?
MySQL 中的 tinyint、smallint、mediumint、bigint 和 int 有什么区别？
In what cases should these be used?
在什么情况下应该使用这些？
回答by Daniel DiPaolo
They take up different amounts of space and they have different ranges of acceptable values.
它们占用不同的空间量并且它们具有不同的可接受值范围。
Here are the sizes and ranges of values for SQL Server, other RDBMSes have similar documentation:
以下是 SQL Server 的大小和值范围，其他 RDBMS 也有类似的文档：
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 NUMBERdatatype, see the above link):
原来它们都使用相同的规范（除了下面提到的一些小例外），但支持这些类型的各种组合（Oracle 不包括在内，因为它只有一个NUMBER数据类型，请参阅上面的链接）：
| 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
The "unsigned" types are only available in MySQL, and the rest just use the signed ranges, with one notable exception: tinyintin SQL Server is unsignedand has a value range of 0 to 255
“无符号”类型仅在 MySQL 中可用，其余类型仅使用有符号范围，有一个明显的例外：tinyint在 SQL Server 中是无符号的，其值范围为 0 到 255
回答by SQLMenace
the size of storage required and how big the numbers can be
所需的存储大小以及数字可以有多大
on SQL Server
在 SQL Server 上
tinyint1 byte, 0 to 255
tinyint1 字节，0 到 255
smallint2 bytes, -2^15 (-32,768) to 2^15-1 (32,767)
smallint2 个字节，-2^15 (-32,768) 到 2^15-1 (32,767)
int4 bytes, -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
int4 个字节，-2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)
bigint8 bytes, -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
bigint8 个字节，-2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807)
you can store the number 1 in all 4, but a bigint will use 8 bytes while a tinyint will use 1 byte
您可以在所有 4 个中存储数字 1，但是 bigint 将使用 8 个字节，而 tinyint 将使用 1 个字节
回答by ANeves
Those seem to be MySQL data types.
那些似乎是 MySQL 数据类型。
According to the documentationthey take:
根据他们采取的文件：
- tinyint = 1 byte
- smallint = 2 bytes
- mediumint = 3 bytes
- int = 4 bytes
- bigint = 8 bytes
- tinyint = 1 字节
- smallint = 2 字节
- mediumint = 3 字节
- int = 4 字节
- bigint = 8 字节
And, naturally, accept increasingly larger ranges of numbers.
并且，自然地，接受越来越大的数字范围。
回答by Anil M
When it gets to real world usage of these datatypes, it is very important that you understand that using certain integer types could just be an overkill or under used. For example, using integer datatype for employeeCount in a table say employee could be an overkill since it supports a range of integer values from ~ negative 2 billion to positive 2 billion or zero to approximately 4 billion (unsigned). So, even if you consider one of the US biggest employer such as Walmart with roughly about 2.2 million employees using an integer datatype for the employeeCount column would be unnecessary. In such a case you use mediumint (that supports from 0 to 16 million (unsigned)) for example. Having said that if your range is expected to be unusually large you might consider bigint which as you can see from Daniel's notes supports a range larger than I care to decipher.
当谈到这些数据类型的实际使用时，了解使用某些整数类型可能只是过度使用或使用不足，这一点非常重要。例如，在表中为employeeCount 使用整数数据类型表示employee 可能是一种矫枉过正，因为它支持从~负20 亿到正20 亿或零到大约40 亿（无符号）的整数值范围。因此，即使您考虑美国最大的雇主之一，例如拥有大约 220 万名员工的 Walmart，使用employeeCount 列的整数数据类型也是不必要的。在这种情况下，您可以使用 mediumint（支持从 0 到 1600 万（无符号））。话虽如此，如果您的范围预计异常大，您可能会考虑 bigint，正如您从 Daniel 中看到的那样
回答by Alexsander Akers
The difference is the amount of memory allocated to each integer, and how large a number they each can store.
不同之处在于分配给每个整数的内存量，以及每个整数可以存储的数字大小。
回答by Vikram Rathaur
Data type Range Storage
数据类型范围存储
bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 Bytes
int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes
smallint -2^15 (-32,768) to 2^15-1 (32,767) 2 Bytes
tinyint 0 to 255 1 Byte
Example
例子
The following example creates a table using the bigint, int, smallint, and tinyint data types. Values are inserted into each column and returned in the SELECT statement.
以下示例使用 bigint、int、smallint 和 tinyint 数据类型创建一个表。值被插入到每一列中并在 SELECT 语句中返回。
CREATE TABLE dbo.MyTable
(
MyBigIntColumn bigint
,MyIntColumn int
,MySmallIntColumn smallint
,MyTinyIntColumn tinyint
);
GO
INSERT INTO dbo.MyTable VALUES (9223372036854775807, 214483647,32767,255);
GO
SELECT MyBigIntColumn, MyIntColumn, MySmallIntColumn, MyTinyIntColumn
FROM dbo.MyTable;
