MySQL中的tinyint，smallint，mediumint，bigint和int有什么区别？ [英] What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?

问题描述

在MySQL中，tinyint，smallint，mediumint，bigint和int之间有什么区别？

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.

< a href =http://msdn.microsoft.com/en-us/library/ms187745.aspx?wa=wsignin1.0 =noreferrer>以下是SQL Server的值的大小和范围，其他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 datatype，参见上面的链接）：

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:

• tinyint ： 1字节，-128到+127 / 0到255（无符号）


• smallint ： 2个字节， - 32,768到+32,767 / 0到65,535（未签名）


• mediumint ： 3字节， - 8,388,608至8,388,607 / 0至16,777,215（未签名）


• int / 整数： 4字节， - 2,147,483,648至+2,147,483,647 / 0至4,294,967,295（未签名）


• bigint ： 8字节， - 9,223,372,036,854,775,808至9,223,372,036,854,775,807 / 0至18,446,744,073,709,551,615（未签名）

• tinyint: 1 byte, -128 to +127 / 0 to 255 (unsigned)
• smallint: 2 bytes, -32,768 to +32,767 / 0 to 65,535 (unsigned)
• mediumint: 3 bytes, -8,388,608 to 8,388,607 / 0 to 16,777,215 (unsigned)
• int/integer: 4 bytes, -2,147,483,648 to +2,147,483,647 / 0 to 4,294,967,295 (unsigned)
• bigint: 8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 / 0 to 18,446,744,073,709,551,615 (unsigned)

unsigned类型仅在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

这篇关于MySQL中的tinyint，smallint，mediumint，bigint和int有什么区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！