MySQL读书笔记--选择合适的数据类型

        在使用MySQL创建数据表时都会遇见一个问题,如何选择合适的数据类型.其实正是因为可选择的数据类型太多,才需要根据一些原则来挑选最合适的数据类型.接下来将详细介绍字符,数值,日期数据类型的一些选择原则.

1.char和varchar

        char和varchar类型类型相似,都用来存储字符串,但是他们保存和检索的方式不同,char属于固定长度的字符串类型,而varchar属于可变的字符类型.

        由于char类型是固定长度的,所以他的处理速度比varchar快得多,但是缺点是浪费储存空间,程序需要对尾部空格进行处理,所以对那些长度变化不大而且对查询效率有要求的可以考虑使用char类型存储.

        在MySQL中,不同的存储引擎对CHAR和VARCHAR的使用原则有所不同,这里简单说下.

        (1)MyISAM存储引擎:建议使用固定长度的数据列代替可变长度的数据列.

        (2)MEMORY存储引擎:目前都在使用固定长度的数据行存储,无论选用char还是varchar都会作为Char来处理.

        (3)InnoDB存储引擎:建议使用varchar类型,

2.Text和BLOB

        一般在保存少量字符串的时候我们会选择char或者是varchar;而在保存较大的文本的时候,通常会选择TEXT或者是BLOB.二者之间最主要的差别是BLOB能用来保存二进制数据,比如照片;而Text只能保存字符数据.

        (1)BLOB和TEXT值会引发一些性能问题

        删除操作会在数据库表中留下很大的空洞,以后填这些空洞的记录在插入的性能上会有影响,为了提高性能,建议定期使用OPTIMIZE功能对这类表进行碎片整理,避免因为空洞导致性能问题.

        (2)可以使用合成的索引来提高文本字段的查询性能.

        简单来说就是根据大文本字段的内容建立一个散列值,并把这个值存储在单独的数据列当中,接下来就可以通过检索散列值来找到数据行了.合成的散列索引对于那些BLOB和TEXT数据列特别有用,用散列标识符值查找的速度比sousuoBLOB列本身的速度快很多.

        (3)在不必要的时候避免检索大型的BLOB和TEXT值

        SELECT * 就不是一个很好的想法,除非能确定作为约束条件的WHERE字句只会找到所需要的数据行

        (4)把BLOB或TEXT列分离到单独的表当中

        在某些环境中,如果把这些数据列移动到第二章表中,可以把元数据表的数据列转换为固定长度的数据行格式,那么他就是有意义的.

3. 浮点数和定点数

        浮点数一般用与含有小数部分的数值,当一个字段被定义为浮点类型以后,如果插入数据的精度超过该列定义的实际精度,则插入值会被四舍五入到实际的精度值.定点数不同,定点数实际上是以字符串的形式存放的,所以定点数可以更精确的保存数据.

        所以在选择浮点数类型的时候要考虑四舍五入的问题,尽量保留足够的小数位,避免存储的数据不准确.

        而且浮点数类型会有误差,这是浮点数类型特有的问题,因此在精度要求比较高的应用中(比如货币)要使用的就是定点数而不是浮点数来保存数据.

        在今后的关于浮点数和定点数的应用中,用户要考虑到以下几个原则:

        (1)浮点数存在误差问题;

        (2)对货币等对精度敏感的数据,应该用定点数来表示或者是存储;

        (3)尽量避免浮点数的比较操作;

        (4)要注意浮点数的一些特殊值的处理

4.日期类型选择

        选择日期类型的规则如下:

        (1)根据实际需要来选择能够满足应用的最小存储的日期类型,如果应用只用记录年那么一个字节来存储YEAR类型完全可以满足,而不需要用4个字节来存储的DATE类型,这样不仅仅能节约存储空间还能提高查询效率.

        (2)如果要记录年月日时分秒,并且记录的年份较为久远,那么最好使用DATETIME,而不是TIMESTAMP,因为后者能表示的范围比DATETIME要短得多

        (3)如果记录的日期需要让不同的时区的用户使用,那么最好使用TIMESTAMP.因为日期类型中只有他能够和实际时区相对应.