Mysql 数据类型

目录

• 数据类型
  • CHAR与VARCHAR类型
    • 两者性能比较
    • 各种存储引擎对两者的影响
  • Text与BLOB
    • 两者区别
    • 分类
    • 常见问题
  • 浮点数与定点数
    • 注意
  • 日期类型的选择
    • 注意

数据类型

CHAR与VARCHAR类型

两者类型类似。都是用来存储字符串。但是保存和检索的方式不同。

CHAR - 固定长度的字符类型

VARCHAR - 可变长度的字符类型

varchar类型需要多出1到2字节来存储字符长度，一般字符数小于255的时候，使用一个字节来存储。如果字符数大于255，则使用两个字节来存储

值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
''	' '	4字节	''	1字节
'ab'	'ab '	4字节	'ab'	3字节
'abcd'	'abcd'	4字节	'abcd'	5字节
'abcde'	'abcd'	4字节	'abcd'	5字节

注意：Mysql运行在非“严格模式”时候。最后一行的值才可以存储

从CHAR(4)到VARCHAR(4)列检索的值不总是相同的，因为检索的时候从CHAR列删除了尾部的空格。

两者性能比较

由于CHAR是固定的长度。所以它处理的速度比VARCHAR快很多。但是缺点是浪费存储空间，程序需要对行尾的空格进行处理。
因此对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用CHAR类型来存储

再使用VARCHAR类型的时候，不能因为VARCHAR类型的长度可变，就选择一个十分长的长度。任然需要按需定义长度。

定义一个十分长的varchar会一定程度上会影响程序的效率。同时还可能会引起mysql的bug

各种存储引擎对两者的影响

• MyISAM存储引擎：建议使用固定长度的数据列代替可变长度的数据列
• MEMORY存储引擎：目前都使用固定长度的数据行进行存储。因此无论使用CHAR或者VARCHAR都没有关系。两者都是作为CHAR进行处理
• InnoDB存储引擎：建议使用VARCHAR。对于InnoDB数据表来说，内部的行存储没有区分固定长度和可变长度列（所有的数据行使用指向数据列值得头指针）。因此主要的影响因素主要是数据航使用的存储总量。由于CHAR平均占用的空间多余VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O较好

Text与BLOB

存储大量文本的时候。我们一般使用Text与BLOB来存储

两者区别

• BLOB可以存储二进制数据，比如照片等。而TEXT只能保存字符数据。

分类

两者还包括：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT与TINYBLOB，BLOB，MEDIUMBLOB，LONGBLOB

应该根据实际情况选择最小的存储单位

常见问题

• BLOB与TEXT会引起一些性能问题。尤其是进行大量的删除操作的时候
  删除操作会在表中留下很大的‘空洞’，以后填入这些‘空洞’的记录在插入时候的性能会受到影响。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片化整理

  OPRIMIZE TABLE t\G
  

  当查看表的时候，表的占用容量减少。对于InnoDB存储引擎，OPTIMIZE语句被自动转化为recreate+analyzz语句

• 可以使用合成（Synthetic）索引来提高大文本字段（BLOB或TEXT）的查询性能。
  简单来说。合成索引就是根据大文本字段的内容建立一个散列值。并把这个值存储再单独的数据列中。但是，这种技术只能用于精确匹配的查询（散列值对于“< >=”操作符没有用处）
  可以使用MD5()函数生成散列值，也可以使用SHA1()或CRC32(),或者使用自己的应用程序来计算散列值.数值型散列值可以高效率地存储
  如果散列算法生成的字符串尾部有空格.则不要存储再CHAR字段中.而是使用VARCHAR字段

  create table t (id varchar(100),context blob,hash_value varchar(40));
  create index idx_hash_value on t(hash_value)
  
  insert into t values(1,repeat('beijing',2),md5(context));
  
  # 进行查询
  select * from t where hash_value=md5(repeat('beijing 2008'),2);
  

  上面的例子展示了使用合成索引的用法.由于这种技术只能用于精确匹配

  如果需要对BLOB或者CLOB字段进行模糊查询,MYSQL提供了前缀索引.也就是只为了字段前n列创建索引

  # 为前100个字符建立索引优化
  create index idx_blob on t(context(100));
  desc select * from t where context like 'beijing%' \G
  

  如果text小于100字符,则索引不会使用.如果再模糊查询一开始使用%.索引不会使用

• 在不必要的使用避免检索大型的BLOB或TEXT值

• 将BLOB或TEXT列分离到单独的表中

浮点数与定点数

浮点数一般用于表示含有小数部分的数值,当一个字段被定义为浮点类型后.如果插入的数据的精度超过该列定义的实际精度.则插入值会被四舍五入到实际定义的精度值,然后插入.
四舍五入的过程不会报错.在Mysql中,float和double(或real)用来表示浮点数.

定点数不同于浮点数.定点数实际上是以字符串形式存放的.所以定点数可以更为精确地保存数据.通常使用decimal来进行保存
如果实际插入的数值精度大于实际定义的精度.则MySQL会进行警告(默认的SQLMode下),但是数据会四舍五入后插入
如果SQLMode是在TRADITIONAL(传统模式)下,则会直接报错

而对于浮点数的运算.通常也不是整数.因此浮点数的比较最好使用范围比较

注意

• 浮点数存在误差问题
• 对货币等对精度敏感的数据,应该使用定点数来存储
• 再编程中如果用到浮点数,要特别注意误差问题.并尽量避免做浮点数的比较
• 要注意浮点数中一些特殊值的处理

日期类型的选择

MySQL提供的日期类型有

• DATE
• TIME
• DATETIME
• TIMESTAMP

注意

• 根据实际来选择日期,如果只是存储年份,则使用1个字节来存储,而不是使用4个字节的datetime
• timestamp记录的时间比较短.如果要记录较长的时间,请使用datetime
• 如果记录的如期需要不同的用户使用.则最好使用timestamp.因为日期类型中只有它能够和实际市区相对应