对MySQL数据类型的相识-百分百源码网

对MySQL数据类型的相识

发布时间：09/01 来源：未知浏览：关键词：

对MySql数据类型的认识：

由于Mysql独占的特性和实现细节对机能的影响是很明显的，由于做好Mysql数据库的设计很关键。关于数据库设计，我们不得不提表字段的类型选中，由于Mysql支撑的数据类型非常多，因此怎样选中准确的数据类型关于获得高机能至关重要。不管要储备的数据是啥类型，我们都需要按照一些数据库设计原则来思考。

选中数据类型的思索

更小的平常是更好的（一样状况下，应当尽大概使用准确储备数据的最小数据类型。）

为什么呢？

（1）由于更小的数据类型平常更快，由于它们占用更少的磁盘、内存和CPU缓存，并且处置时需要的CPU周期也更短。
（2）要确保没有低估需要储备的值的范畴，更小是相对与数据类型的最大值范畴来讲的。
（3）假如没法肯定哪个数据类型是最好的，就选中你认为不会超越范畴的最小类型。

简便就好（简便数据类型的操纵平常需要更短的CPU周期。）

为什么呢？下面有几个例子说明一下缘由。

（1）整型比字符串操纵代价更低，由于字符串集和校订规则（排序规则）是的字符比力比整型比力更复杂。
（2）储备日期和时间应当使用Mysql内建的类型（date,time,datatime）。
（3） IP地址的储备应当用整型(int)。

尽量幸免 NULL （空值）

为什么呢？

（1）许多表都包括可为NULL的列，就算程序并不需要留存NULL也是如此，这是由于列的默许属性就是可为NULL。平常状况下最好指定列NOT NULL，除非真的需要储备NULL。
（2）假如查询中包括可为NULL的列，关于Mysql来说是很难优化的，由于NULL的列使得索引，索引统计和值比力都更复杂。可为NULL的列会使用更多的储备空间，在Mysql里也需要非凡处置。当可为NULL的列被索引时，每个索引记载需要一个额外的字节，在MyISAM里乃至还大概致使牢固大小的索引变成可变大小的索引。
（3）平常把可为NULL的列改为NOTNULL带来机能晋升比力小，假如方案在列上建索引的话，就应当尽量幸免设计成可为NULL的列。（也有一个例外，那就是在InnoDB中，会使用独自的位（bit）来储备NULL值，所以对稀少数据有很好的空间效力。）

总结

在为列选中数据类型时，第一步需要肯定适宜的大类型（数字、字符串、时间等等），这平常是很简便的，那么下一步就是选中详细的类型了。

许多Mysql的数据类型可以储备雷同类型的数据，只是储备的长度和范畴不一样、同意的精度不一样，或者需要的物理空间（磁盘和内存空间）不一样。雷同大类型的不一样子类型数据有时候也有一些非凡的行动和属性。比方：DATATIME 和 TIMESAMP列都可以储备雷同类型的数据（时间和日期）并且准确到秒，然而TIMESTAMP只使用DATATIME一半的储备空间，并且会按照时区转变，具有非凡的主动更新能力。别的TIMESTAMP同意的时间范畴要小得多，有时候它的非凡能力会成为障碍，这都是我们开发者需要思考的。

整数类型

有两个类型的数字：整数（whole number）和实数（real number）。

假如储备整数，可以使用这几种整数类型：TINNYINT（8）、SMALLINT（16）、MEDIUMINT（24）、INT（32）、BIGINT（64）。

整数类型有可选的的UNSIGNED属性，表示不同意为负值，这大致可以是正数的上限提高一倍。

比方：TINYINT UNSIGNED可以储备的范畴是0~255，而TINYINT的储备范畴是-127~128.

有符号和无符号类型使用雷同的储备空间，并具有雷同的功效.

因此可以按照实际状况选中适宜的类型。

你的选中决议Mysql是如何在内存和磁盘中留存数据的。

整数一样选中64位的BIGINT整数，即便在32位环境下也是如此。（但是一些聚合函数是例外，它们是使用DECIMAL或DOUBLE停止运算的）

Mysql可认为整数类型指定宽度。

比方：INT（11），对大多数利用这是没成心义的：它不会限制值的合法范畴，只是规定了Mysql的一些交互工具（例如Mysql命令行客户端）用来显示字符的个数。关于储备和运算来讲，INT（1）和INT（20）是雷同的。

一些第三方储备引擎（比方Infobright）有时也有自定义的储备格局和紧缩方案，并不必然使用常见的Mysql内置引擎的方式。

实数类型

实数是带有小数部分的数字。

它们不只是将来储备小数部分，也可以使用DECIMAL储备比BIGINT还要大的整数。Mysql既支撑准确类型，也支撑不准确类型。DECIMAL类型用于储备准确的小数。

在Mysql5.0或者更高版本支撑准确运算，而在Mysql4.1乃至更早版本中使用浮点运算会显现非常（主如果精度的亏损致使的）FLOAT和DECIMAL类型都可以指定进度。

关于DECIMAL列可以指定小数点前后所同意的最大位数，这会影响列的空间耗损。有许多办法可以指定FLOAT（浮点）列所需要的精度，这会使得Mysql暗暗选中了不一样的数据类型，或者在储备时对值停止取舍，但是这些精度往往都是非标准的，所以一样倡议只指定数据类型不指定精度。

由于需要额外的空间和运算开销，所以应当尽量只在对小数停止准确运算时才使用DECIMAL。比方储备财务数据，但是假如数据量比力大的时候，可以思考使用BIGINT代替DECIMAL，将需要储备的货币单位按照小数的位数乘以响应的倍数即可。FLOAT和DOUBLE类型支撑使用标准的浮点运算停止近似运算。

字符串类型

Mysql支撑多种字符串类型，每品种型还有许多变种。其中VARCHAR和CHAR是两种最主要的字符串类型。

留意：Mysql储备引擎储备CHAR或者VARCHAR值的方式在内存中和在磁盘上大概不一样，所以Mysql效劳器从储备引擎读取的值大概需要转换为别的一种储备格局。

VARCHAR类型用于储备可变长字符串，是最常见的字符串数据类型。

VARCHAR比定长类型更节约空间，由于它仅使用必要的空间（越短的字符串使用越少的空间）。

VARCHAR需要使用1或2个额外字节记载字符串的长度。

VARCHAR节约了储备空间，所以对机能是有帮忙的。

下面是一些VARCHAR适合使用的场景：
（1）字符串列的最大长度比均匀长度大许多。
（2）列的更新很少，所以碎片不是问题。
（3）使用了像UTF-8这样复杂的字符集，每个字符都使用不一样的字节数停止储备。

CHAR类型是定长的。（Mysql总是按照定义的字符串长度分配足够的空间）

CHAR适合储备很短的字符串，或者所有值都接近统一个长度。

和VARCHAR和CHAR相似的类型还有BINARY和VARBINARY，它们储备的都是二进制字符串。

留意：使用VARCAHR（5）和VARCHAR（200）储备“hello”的空间开销都是一样的，那么使用更短的列有什么优势呢？（事实证明有很大的优势）

更长的列会耗损更多的内存，由于Mysql平常会分配牢固大小的内存块来留存内部值。特别是使用内存暂时表停止排序或者操纵时会特殊糟糕。在利用磁盘暂时表停止排序时也一样糟糕。

留意：归根到底，最好的战略是只分配真正需要的空间。

BLOB和TEXT类型

BLOB和TEXT都是为储备很大的数据而设计的字符串数据类型，离别使用二进制和字符方式储备。

实际上它们离别属于两组不一样的数据类型家族：字符串类型有TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT；

二进制类型有TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB；

ENUM类型

可以使用枚举（ENUM）代替字符串类型。许多时候倡议使用枚举列代替常用的字符串类型。

（1）枚举列可以把一些不反复的字符串储备成一个预定义的汇合。
（2）Mysql在储备枚举时非常紧凑，会按照列表值的数目紧缩到一到两个字节中。
（3）Mysql在内部会将每个值在列表中的位置留存为整数，并且在表的.frm文件中留存“数字-字符串”映射关系的“查寻表”。

留意：有一个令人吃惊的地方是，枚举字段是依照内部储备的整数而不是定义的字符串停止排序的。

留意：枚举最不好的地方是：字符串列表是牢固的，增加或者删除字符串必需使用ALTER TABLE，因此关于一系列将来大概会改动的字符串，使用枚举并不是一个好主意，除非接受只能在列表末尾增加元素。

留意：由于Mysql把每个枚举值留存为整数，并且必需停止查寻才能转换为字符串，所以枚举列有一些开销。

日期和时间类型

Mysql有许多类型可以留存日期和时间值，比方YEAR和DATE。

Mysql能储备的最小时间粒度为秒（MariaDB支撑微秒级别的事件类型）。但是Mysql也可以使用微秒级别的粒度停止暂时运算。

大部分时间类型都没有替换品，因此没有什么是最好选中的问题。

接下来独一的问题是留存日期和时间的时候需要做什么。

DATETIME

（1）这个类型能留存大范畴的值，从1001年到9999年，精度为秒。
（2）DATETIME把时间和日期封装到格局为YYYYMMDDHHMMSS的整数中，与时区无关。
（3）DATETIME使用8个字节的储备空间。

TIMESTAMP

（1）TIMESTAMP类型留存了从1970年1月1日午夜以来的秒数，它和UNIX时间戳雷同。
（2）TIMESTAMP只使用4个字节的储备空间，因此它的范畴比DATETIME小得多。
（3）TIMESTAMP显示的值依靠时区。

DATETIME和TIMESTAMP的对照:

（1）默许状况下，假如插入时没有指定第一个TIMESTAMP列的值，Mysql则设定这个列的值为当前时间。（这是DATETIME没有的特性）
（2）在插入一行记载时，Mysql默许也会更新第一个TIMESTAMP列的值。
（3）TIMESTAMP列默许为NOT NULL，这与其他的数据类型不一样。

总结

（1）除了非凡行动之外，平常也应当尽大概使用TIMESTAMP，由于它比DATETIME空间效力更高。
（2）一样来讲不倡议把UNIX时间戳留存为整数值，这不会带来任何收益，用整数留存时间戳格局平常不利便处置。
（3）假如需呀储备比秒更小粒度的日期和时间值，可以使用BIGINT类型储备微秒级别的时间戳，或者使用DOUBLE储备秒之后的小数部分，也可以用MariaDB替换Mysql。

位数据类型

BIT定义一个包括单个位的字段，BIT（2）储备2个位，最大长度是64个位。

留意：一样倡议慎重使用BIT类型，关于大部分利用来讲最好幸免使用这品种型。

选中标识符

为identifier（标识列）选中适宜的数据类型非常重要。

一样来讲更有大概用标识列与其他值停止比力，或者通过标识列寻觅其他列。

中选择标识列的类型时，不仅仅需要思考储备类型，还需要思考Mysql对这品种型如何施行运算和比力。

一旦选定了一品种型，要确保在所有关联表中都使用一样的类型。

在可以知足值的范畴需求，并且预留将来增长空间的前提下，应当选中最小的数据类型。

留意：整数平常是标识列最好的选中，由于它们很快并且可以使用AUTO_INCREMENT。留意：ENUM和SET是最糟糕的选中了；假如大概也尽大概幸免使用字符串作为标识列，由于它们很耗损空间并且平常比数字类慢。

全文总结

关于数据库设计，必然要三思而后行，选中最适合的数据列类型还有决议数据列的大小都是很关键的一步。其实大可不必惊慌，不管关于任何类型需求的数据表设计，你只要记住一个原则，很重要很重要很重要的原则：尽大概使用准确储备数据的最小数据类型。