澳门金沙vip 4

【澳门金沙vip】mysql 开发基础系列13 选择合适的数据类型(下)

二. 浮点数与定点数 

  浮点数一般用于含有小数部分的数值,当数据的精度超过该列定义的实际精度时,则插入值被四舍五入到实际定义的精度值。在mysql中
float,double(或real)用来表示。
  定点数则是以字符串形式存放的。当数据的精度超过该列定义的实际精度时,则mysql会警告(默认的sqlmode),但也是按照四舍五入。如果sqlmode
是在传统模式下则会报错,在mysql中 deciaml或(numberic)用来表示。

--  下面看下浮点数与定点数的区别
CREATE TABLE test(c1 FLOAT(10,2),c2 DECIMAL(10,2))
INSERT INTO test VALUES(131072.32,131072.32)
SELECT * FROM test;

澳门金沙vip 1

  从上面的例子看出c1列插入131072.32值,实际变成了131072.31值。这是由于单精度浮点数表示时产生了误差,在精度要求高的应用中(比如货币)要使用定点数。

char 与 varchar

  1. char 属于固定长度的字符类型,而 varchar 属于可变长度的字符类型

  2. 检索时 char 会删除尾部的空格

  3. 不同存储引擎使用建议

    MyISAM:char

    MEMORY:char

    InnoDB:varchar

二、MYSQL数据类型的长度和范围

各数据类型及字节长度一览表:

数据类型 字节长度 范围或用法
Bit 1 无符号[0,255],有符号[-128,127],天缘博客备注:BIT和BOOL布尔型都占用1字节
TinyInt 1 整数[0,255]
SmallInt 2 无符号[0,65535],有符号[-32768,32767]
MediumInt 3 无符号[0,2^24-1],有符号[-2^23,2^23-1]]
Int 4 无符号[0,2^32-1],有符号[-2^31,2^31-1]
BigInt 8 无符号[0,2^64-1],有符号[-2^63 ,2^63 -1]
Float(M,D) 4 单精度浮点数。天缘博客提醒这里的D是精度,如果D<=24则为默认的FLOAT,如果D>24则会自动被转换为DOUBLE型。
Double(M,D) 8  双精度浮点。
Decimal(M,D) M+1或M+2 未打包的浮点数,用法类似于FLOAT和DOUBLE,天缘博客提醒您如果在ASP中使用到Decimal数据类型,直接从数据库读出来的Decimal可能需要先转换成Float或Double类型后再进行运算。
Date 3 以YYYY-MM-DD的格式显示,比如:2009-07-19
Date Time 8 以YYYY-MM-DD HH:MM:SS的格式显示,比如:2009-07-19 11:22:30
TimeStamp 4 以YYYY-MM-DD的格式显示,比如:2009-07-19
Time 3 以HH:MM:SS的格式显示。比如:11:22:30
Year 1 以YYYY的格式显示。比如:2009
Char(M) M
定长字符串。
VarChar(M) M 变长字符串,要求M<=255
Binary(M) M 类似Char的二进制存储,特点是插入定长不足补0
VarBinary(M) M 类似VarChar的变长二进制存储,特点是定长不补0
Tiny Text Max:255 大小写不敏感
Text Max:64K 大小写不敏感
Medium Text Max:16M 大小写不敏感
Long Text Max:4G 大小写不敏感
TinyBlob Max:255 大小写敏感
Blob Max:64K 大小写敏感
MediumBlob Max:16M 大小写敏感
LongBlob Max:4G 大小写敏感
Enum 1或2 最大可达65535个不同的枚举值
Set 可达8 最大可达64个不同的值
Geometry    
Point    
LineString    
Polygon    
MultiPoint    
MultiLineString    
MultiPolygon    
GeometryCollection    

一.  BloB和Text

1. 合成索引
  合成索引可以提高大文本字段BLOB和Text的查询性能,
合成索引是在表中增加一个字段存放散列值,这种技术只能用于精确匹配的查询,可以使用md5()或sha1,crc23()
来生成散列值, 数值型散列值可以提高存储效率 。下面演示下

--    hash_value字段用来存储散列值
CREATE TABLE t(id VARCHAR(100),context BLOB, hash_value VARCHAR(40))

--  插入数据  存放MD5散列值
INSERT INTO t VALUES(1, REPEAT('beijing',2),MD5(context));
INSERT INTO t VALUES(2, REPEAT('beijing',2),MD5(context));
INSERT INTO t VALUES(3, REPEAT('beijing 2008',2),MD5(context));

SELECT * FROM t;

澳门金沙vip 2

--  查询context值为 beijing 2008的记录,通过散列值来查询
SELECT * FROM t WHERE hash_value=MD5(REPEAT('beijing 2008',2));

澳门金沙vip 3

  上面展示了合成索引的用法,这种技术只用于精确匹配,在一定程度上减少i/0,提高查询效率,如果要使用模糊查询,可以考虑使用前缀索引。

2. 前缀索引

--  创建前缀索引,为字段前N个字符创建索引。这里是前100个字符进行模糊查询
CREATE INDEX idx_blob ON  t(context(100))
-- 查看是否用到前缀索引
 DESC SELECT * FROM t WHERE context LIKE 'beiing%0'

澳门金沙vip 4

  总结:
可以考虑把blob和text列移动到第二张数据表中,把原数据表的数据列转换为固定长度的数据行格式,减少碎片。

浮点数和定点数

  1. 浮点数一般用于表示含有小数点的数值。当一个字段被定义为浮点类型后,如果插入数据的精度超过该列定义的实际精度,则插入值会被四舍五入到实际定义的精度值
  2. 定点数是以字符串形式存放的,所以定点数可以更精确的保存数据
  3. 浮点数会产生误差,避免与浮点数的比较

选择数据类型的基本原则

前提:使用适合存储引擎。

选择原则:根据选定的存储引擎,确定如何选择合适的数据类型。

下面的选择方法按存储引擎分类:

  • MyISAM
    数据存储引擎和数据列:MyISAM数据表,最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。
  • MEMORY存储引擎和数据列:MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理的。
  • InnoDB 存储引擎和数据列:建议使用 VARCHAR类型。

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

下面说一下固定长度数据列与可变长度的数据列。

text 与 blob

  1. 二者的主要差别是 blob 能用来保存二进制数据,而 text 只能保存字符数据

  2. blob 和 text 值会引起一些性能问题,特别是在执行了大量的删除操作时

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

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

    合成索引就是根据大文本字段的内容建立一个散列值,并把这个值存储在单独的数据列中,接下来就可以通过检索散列值找到数据行了。但是,要注意这种技术只能用于精确匹配的查询。可以使用
    md5()sha1(),或 crc32() 函数生成散列值

  4. 在不必要的时候避免检索大型的 blob 或 text 值

  5. 澳门金沙vip,把 blob 或 text 分离到单独的表中

浮点数与定点数

为了能够引起大家的重视,在介绍浮点数与定点数以前先让大家看一个例子:
mysql> CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (0.29 sec)

mysql> insert into test values(131072.32,131072.32);
Query OK, 1 row affected (0.07 sec)

mysql> select * from test;
+———–+———–+
| c1        | c2        |
+———–+———–+
| 131072.31 | 131072.32 |
+———–+———–+
1 row in set (0.00 sec)

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

在mysql中float、double(或real)是浮点数,decimal(或numberic)是定点数。

浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的数据范围;它的缺点是会引起精度问题。在今后关于浮点数和定点数的应用中,大家要记住以下几点:

  1. 浮点数存在误差问题;
  2. 对货币等对精度敏感的数据,应该用定点数表示或存储;
  3. 编程中,如果用到浮点数,要特别注意误差问题,并尽量避免做浮点数比较;
  4. 要注意浮点数中一些特殊值的处理。

 

ref: