MySql常用字段类型总结

/ 数据库 / 718浏览

数据类型

MySQL支持的类型非常多,选择正确的数据类型对于获得高性能至关重要。不管存储哪种数据类型的数据,下面几个简单的原则都有助于做出更好的选择。

⭐ 更小的通常更好

一般情况下,应该尽量使用可以正确存储的最小数据类型。更小的数据类型通常更快,因为他们占用更小的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。

⭐ 简单就好

简单数据类型的操作通常需要更少的CPU周期。例如,整型比字符操作代价更低,因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。(例如:应该使用MySQL内建类型,而不是使用字符串存储日期和时间;使用整型存储IP地址等等)

⭐ 尽量避免NULL

很多表都包含可为NULL(空值)的列,即使应用程序并不需要保存NULL值也是如此,这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。

如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列回事用更多的存储空间,在MySQL里也需要特殊处理。当可为NULL的列被索引时,每个索引记录都需要一个额外的字节,在MyISAM里甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。

通常把可为NULL的列改为NOT NULL带来的性能提升比较小,所以调优时没有必要首先在现有schema中查找并修改掉这种情况,除非确定这回导致问题。但是,如果在计划列上建立索引,就应该力量避免设计成可为NULL的列。

在为列选择数据类型时,第一步需要确定合适的大类型:数字、字符串、时间等。这通常是很简单的,但是会有一些数据类型的选择不那么的直观,很多MySQL的数据类型可以存储相同类型的数据,只是存储的长度和范围不一样、允许的精度不同,或者需要的物理空间(磁盘和内存空间)不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性。

比如,datetime 和 timestamp 列,decimal 和 double 列等等。

整数类型

现实中有两种类型的数字:整数和实数。如果存储整数,可以使用这几种类型:tinyint,smallint,mediumint,int,bigint ,分别使用 8,16,24,32,64 位存储空间。

整数类型有可选的UNSIGED属性,表示不允许负值,这大致可以使正数的上限提高一倍。(例如 tinyint unsiged 可以存储 0 到 255,而 tinyint 的存储范围是 -128 到 127)

MySQL可以为正数指定宽度,例如 int(11) ,对于大多数应用这个是没有意义的,它不会限制值的合法范围,只是规定了MySQL的一些交互工具用来显示字符的数。对于存储和计算来说, int(1)int(20) 是相同的。

实数类型

实数类型是带有小数点部分的数字。然后它们不只是为了存储小数部分,也可以使用 decimal 存储比 bigint 还大的整数。MySQL即支持精确类型,也支持不精确类型。

浮点类型FLOAT和DOUBLE支持使用标准的浮点运算进行近似计算。DECIMAL类型用于存储精确的小数。因为CPU不支持对DECIMAL的直接计算,相对而言,CPU直接支持原生浮点类型计算,所以浮点类型运算明显更快。

FLOAT占用4个字节,DOUBLE占用8个字节,MySQL使用DOUBLE作为内部浮点计算的类型。浮点类型通常比DECIMAL使用更少的空间,因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL(例如存储财务数据),但数据量叫大时,可以考虑使用bigint代替DECIMAL,这样可以避免浮点计算不精确和DECIMAL精确计算代价高的问题

字符串类型

Varchar

Varchar类型,用于存储可变长度字符串,是最常见的字符串数据类型。它比定长类型更节省空间。有一种情况例外,如果表使用 ROW_FORMAT=FIXED 创建的话,每一行都会使用定长存储,会很浪费空间。

Varchar需要使用1或2个额外字节记录字符串的长度,如果列长最大长度 小于等于 255字节,则只使用1个字节表示,否则使用2个字节。Varchar节省了存储空间,由于是变长度的,在UPDATE时可能使行变得比原来更长,这就会导致要做额外的工作。

下面这些情况适合使用Varchar:

慷慨是不明智的,使用Varchar(5) 和 Varchar(200) 存储 'Hello' 的空间开销是一样的,那么使用短的列有什么优势呢?更长的列会消耗更多的内存,因为MySQL通常会分配固定大小的内存来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也通用糟糕。所以最高的策略是只分配真正需要的空间。

Char

Char类型是定长的,Char和Varchar在逻辑上是一样的区别只是在存储格式上。Char类型的值会根据需要采用空格进行填充以方便比较。Char类型适合存储很短的字符串,或者所有值都接近同一个长度。

对于经常变更的数据,Char比Varchar更好,因为定长Char类型不易产生碎片。对于非常短的列,Char的存储空间也优于Varchar,例如用Char(1)来存储只有 Y和N的值,Char只用1个字节,而Varchar却需要2个字节,因为还有1个字节来记录长度的额外字节。

与Char和Varchar类似的类型还有Binary和Varbinary,他们存储的是二进制字符串。

Blob 和 Text

blob和text都是为存储大的数据而设计的字符串类型,分别采用二进制和字符方式存储。Blob 和 Text的仅有区别是:Blob存储的是二进制数据,没有排序规则和字符集,而Text又字符集和排序规则。

MySQL不能讲Blob和Text列全部长度的字符串进行索引,也不能使用这些索引消除排序。

日期和时间

MySQL提供2种相似的日期类型,Datetime和Timestamp。

Datetime,它能保存大范围的值,从 1001 年到 9999年,精度为秒,存储格式为YYYYMMDDHHmmSS的整数,与时区无关,使用8个字节存储空间。

Timestamp,就像它的名字一样,Timestamp类型保存了从 1970年1月1日午夜(格林尼治时间)以来的秒杀,他和UNIX时间戳相同。它只有4个字节的存储空间,因此它的范围比Datetime小得多,只能从1970年到2038年。

Timestamp显示的值也依赖于时区,MySQL服务器、操作系统、客户端连接都有时区设置。

除了特殊情况外,通常应该尽量使用Timestamp,因为它比Datetime空间效率更高。

alt

中国青海,卓尔山