MySQL中tinyint, smallint, mediumint, bigint和int的区别是什么?
在什么情况下应该使用这些?
MySQL中tinyint, smallint, mediumint, bigint和int的区别是什么?
在什么情况下应该使用这些?
当前回答
这些似乎是MySQL数据类型。
根据他们拿到的文件:
Tinyint = 1字节 Smallint = 2字节 Mediumint = 3字节 Int = 4字节 Bigint = 8字节
并且,自然地,接受越来越大范围的数字。
其他回答
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.
数据类型范围存储
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
例子
下面的示例使用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;
所需存储空间的大小以及数字的大小。
SQL Server:
Tinyint 1字节,0 ~ 255 Smallint 2字节,-215(-32,768)到215-1 (32,767) Int 4字节,-231(- 2147,483,648)到231-1 (2147,483,647) Bigint 8字节,-263(-9,223,372,036,854,775,808)到263-1 (9,223,372,036,854,775,807)
你可以将数字1存储在所有4个字节中,但是bigint将使用8个字节,而tinyint将使用1个字节。
它们占用不同的空间,它们有不同的可接受值范围。
下面是SQL Server的值的大小和范围,其他rdbms也有类似的文档:
MySQL Postgres Oracle(他们只有NUMBER数据类型) DB2
事实证明,它们都使用相同的规范(下面指出了一些小的例外),但支持这些类型的各种组合(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
它们支持相同的值范围(下面有一个例外),并且都有相同的存储需求:
| 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
“unsigned”类型仅在MySQL中可用,其余类型仅使用带符号的范围,只有一个显著的例外:SQL Server中的tinyint是无符号的,其值范围为0到255
区别在于分配给每个整数的内存数量,以及它们各自可以存储多大的数字。