mysql数据类型的选择

mysql数据选择相信大家都应该知道,如果选择不好,会大大影响效率的。

要知道mysql都有什么数据类型,可以使用mysql帮助文档,输入 ? data types 即可,如下图:
在这里插入图片描述
再次输入 ? tinyint 可以看到 tinyint 类型在有符号的情况下范围是 -128~127,在无符号的情况下是 0 ~ 255。
在这里插入图片描述
所以查询数据类型的信息可以使用帮助文档来。下面就分析一下数据类型的使用。

一般情况下选择可以正确存储数据的最小数据类型。越小的数据类型通常更快,占用磁盘,内存和CPU缓存更小。简单的数据类型的操作通常需要更少的CPU周期。例如:整型比字符操作代价要小得多,因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。这也是很多开发者把ip地址(如 192.168.0.1)转为无符号整型来存储的原因。

尽量避免NULL:尽量制定列为NOT NULL,除非真的需要NULL类型的值。因为可能为NULL列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理。

整型数据介绍。
根据上面的分析,如果能确定整型的范围不存在负数的话,就应该设置成无符号类型,如果能确定整型的范围不会是负数并且最大值不超过 255,那么就应该选择最小的 无符号 tinyint。而不是int 或者其它。对应在创建表时,指定的数据类型大小,如 tinyint(3),int(16)等,只是用来显示,就是说显示出来的是按照设定好的大小呈现出来,但是实际存储依然是按照 tinyint 和int的实际大小来存储,也就是说 tinyint(1) 和 tinyint(8)实际存储的大小都是8位,所占的空间是相等的。

浮点型数据类型
浮点型数据类型有 real、float、double,这三种数据类型也被称作不确定数据类型,为啥不确定,就是后面保留的小数点值不确定。下面看一个例子:

CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));

insert into test values(131072.32,131072.32);

select * from test;

+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
#从上面的例子中我们看到c1列的值由131072.32变成了131072.31,这就是浮点数的不精确性造成的。

所以在存在使用比较运算符 =、<、> 、<=、>= 等时,要避免使用浮点数比较,在统计需要准确类型时,比如银行统计账号资金等,也不合适使用浮点数。而应该使用确定类型,比如 decimal。

字符型数据类型
字符型数据类型分为 char 和 varchar。
char是定长的,适合存储很短的字符串,或者所有值都接近同一个长度,例如MD5值。对于经常变更的数据,定长的char类型不容易产生碎片,在空间存储上也比varchar更有效率。
varchar类型用于存储可变长字符串,比char更节省空间。varchar需要使用1或2个额外字节记录字符串长度,如果列的最大长度小于或等于255字节,则使用1个字节表示,否则使用2个。

varchar适合使用该类型的情况:字符串列的最大长度比平均长度大很多列的更新很少,所以碎片不是问题;使用了UTF-8这样复杂的字符集,每个字符的使用不同字节数进行存储。varchar还会保留末尾的空格。

当存储CHAR时MySQL会默认删除行尾空格。因为CHAR值通常需要用空格进行填充以方便比较。对于经常变更的数据CHAR比较VARCHAR要好,因为定长的CHAR类型不容易产生碎片。对于更短的列CHAR比VARCHAR要更加节省空间,因为VARCHAR总是需要一个额外的字节来存储长度。

对于使用InnoDB存储引擎,建议使用varchar数据类型来代替char。因为对于InnoDB数据表,内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),因此在本质上,使用固定长度的CHAR列不一定比使用可变长度VARCHAR列简单。因而,主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR,因 此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

时间数据类型
时间数据类型有 date、time、datetime、timestamp。date格式是YYYY-MM-DD,time的格式是hh:mm:ss,datetime就是 YYYY-MM-DD hh:mm:ss。timestamp是时间戳格式,timedate存储是8字节大小,而timestamp是4字节大小,所以,时间最好是用timestamp类型来存储。

BLOB和TEXT类型
BLOB和TEXT是用来存储很大的数据来设计的字符串数据类型,分别采用二进制和字符方式进行存储。
实际上他们时属于两种不同数据类型家族:

字符类型: TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT;

二进制类型:TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB。

与其他数据类型不同,它们被当作单独的对象进行处理。当BLOB和TEXT值太大时InnoDB会使用专门的外部区域来进行存储,此时每个值只需要1—4个字节在行内存储一个指针即可,然后在外部存储区域存储实际的值。

BLOB和TEXT的仅有的区别在于BLOB类型存储的时二进制数据,没有排序规则或字符集,而TEXT类型有字符集和排序规则。
MySQL对BLOB和TEXT列进行排序时与其他类型时不同的,它只对每个列的最前max_sort_length字节而不是整个字符串做排序。
MySQL不能将BLOB和TEXT列全部长度进行字符串进行索引,也不能使用这些索引消除排序。

发布了39 篇原创文章 · 获赞 4 · 访问量 4234
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览