你的位置:首页 > 数据库

[数据库]Mysql 数据类型使用说明


  FLOAT 和DOUBLE 类型支持使用标准的浮点运算进行近似计算。

  DECIMAL类型用于存储精确的小数。

  因为cpu不支持对DECIMAL的直接计算,所以在Mysql5.0及更高的版本中,MYSQL服务器自身实现了DECIMAL的高精度计算。相对而言,cpu直接支持原生浮点计算,所以浮点运算冥想更快。

  浮点和DECIMAL类型都可以指定精度。对于DECIMAL列,可以指定小数点前后允许的最大位数。这会影响到列的空间消耗。Mysql5.0和更高将数字打包保存到一个二进制字符串中(每4个字节存储9个数字)。例如DECIMAL(18,9)小数点两边各存储9个数字,一共使用9个字节:小数点签的数字用4个字节,小数点后的数字用4个字节,小数点本身占一个字节。

  MYSQL 5.0 和更高的版本中的decimal类型允许最多65个数字。而早期的Mysql版本中这个限制是254个数字,并且保存为未压缩的字符串(每个数字一个字节)。然而这些(早期)版本实际上并不能在计算中使用这么大的数字,因为DECIMAL只是一种存储格式,在计算中DECIMAL会转化成DOUBLE类型。

  浮点类型在存储同样范围的值时,通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节,相比FLOAT有更高的精度和更大的范围。和证书类型一样,能选择的只是存储类型;Mysql使用DOUBLE作为内部浮点计算的类型。

  因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但数据量比较大的时候,可以考虑使用BIGINT带铁DECIMAL,将需要存储的货币单位根据小叔点的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分,则可以把所有金额乘以100W,然后将结果存储在BIGINT里,这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

  字符串类型

  VARCHAR和CHAR类型

  VARCHAR和CHAR是两种主要的字符串类型。不幸的是,很汗精确的解释这些值是怎么存储在磁盘和内存中的,因为这跟存储引擎的具体实现有关。

  VARCHAR

  VARCHAR 类型用于存储可变长字符串,是最常见的字符串数据类型。它比定长类型更省空间,因为它仅使用必要的空间。有一种情况例外:如果MYSQL表使用ROW_FORMAT=FIXED创建的话,每一行都会使用定长存储,这很浪费空间。

  VARCHAR需要使用1-2个额外字节记录字符串的长度:如果勒的最大长度小于或等于255,则只需一个字节表示,否则使用2个字节。假设使用latin1字符集,一个varchar(10)的列需要11个字节存储空间。VARCHAR(1000)则需要1002个字节,因为需要2个字节存储长度信息。

  VARCHAR节省了存储空间,所以对性能也有帮助。但是,由于行是变长的,在UPDATE时可能使行变得比原来更长,这就导致需要做额外的工作。如果一个行占用的空间增长,并且在页内没有更多的存储空间可以存储,在这种情况下,不同的存储引擎醋栗方式是不一样的。例如MYISAM 会将行拆成不同的片段存储,INNODB则需要分裂页来使行可以放进页内。

  下面这些情况使用VARCHAR是合适的:字符串列的最大长度比平均长度大很多;列更新的很少,所以碎片不是问题;使用了像utf-8这样复杂的字符集,每个字符都使用不同的字节数进行存储。

  在5.0或者更高的版本,Mysql在存储和检索时会保留末尾空格。但在4.1或更老的版本,mysql会提出末尾空格。

  INNODB则更灵活,它可以把过长的varchar存储为blob,我们稍后讨论这个问题。

  CHAR

  CHAR类型是定长的:MYSQL 总是根据定义的字符串长度分配足够的空间。当存储CHAR时,MYSQL会删除所有的末尾空格。CHAR会根据需要采用空格进行填充以方便比较。

  char适合存储很短的字符串,或者所有值都近似一个长度。例如,char非常适合存储密码的MD5值。对于经常变更的数据,CHAR也比VARCHAR更好,因为订场的char类型不容易产生碎片。对于非常短的列,char比varchar 在存储效率上也更有效率。例如用char(1) 来存储只有Y和N的值,如果采用单字节字符集只需要一个字节,但是varchar(1)却需要2个字节,因为还有一个记录长度的额外字节。

  填充和街区空格的行为在不同的存储引擎是一样的,因为这是在mysql服务器层进行处理的。

  与char和varchar类似的类型还有binary和varbinary,它们存储的是二进制字符串。二进制字符串跟常规字符串非常类似,但是二进制字符串存储的是字节码而不是字符。填充也不一样:MYSQL填充binary采用的是\0 (零字节)而不是空格,在检索时也不会去掉填充值。

  当需要存储二进制数据,并且希望mysql使用字节码而不是字符码进行比较时,这些类型是非常有用的。而精致比较的优势不仅仅体现在大小写敏感上。MYSQL比较binary字符串时,每次按一个字节,并且根据该字节的数值进行比较。因此二进制比较比字符比较简单很多,所以也就更快。

---------------------------------------------------------------------------------------------------------------------------------------------------------------

  tips:使用varchar(5)和varchar(200)存储‘hello’的空间开销是一样的。name使用更短的列有什么优势吗?

     事实证明有很大的优势。更长的列会消耗更多的内存,因为mysql通常会分配固定大小的内存来保持内部值。友情是使用内存临表进行排序或操作时会特别糟糕。在利用磁盘临表时进行排序时也同样糟糕。

    所以最好的策略是只分配真正需要的空间。

BLOB 和TEXT类型

  BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符串方式存储。

  实际上,它们分别属于两组不同的数据类型家族:字符类型是TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT;对应二进制类型是TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB。BLOB是SMALLBLOB的同义词,TEXT是SMALLTEXT的同义词。

  与其他类型不同,MYSQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当blob和text值太大时,INNODB会使用专门的“外部”存储区来进行存储,因此每个值在行内需要1-4个字节存储一个指针,然后在外部存储区域存储世纪的值。

  BLOB和TEXT家族之间的不同是BLOB类型存储的是二进制数据,没有排序规则或字符集,而text类型有字符集和排序规则。

  MYSQL对BLOB和TEXT列进行排序与其他类型是不同的:它只对每个列的最掐面max_sort_length字节而不是整个字符串做排序。如果只需要排前面一小部分字符集,则可以减小max_sort_length的配置,或者使用order by sustring(column,length)。

  mysql不能将blob和text列全部长度的字符串进行索引,也不能使用这些索引消除排序。

  使用枚举(ENUM)代替字符串类型

  有时候可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定的集合。mysql 在存储枚举时非常紧凑,会根据列表值得数量压缩到一个或者两个字节中。mysql在内部会将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存‘数字-字符串’映射关系的查找表。

  另外一个令人吃惊的地方是,枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。

  枚举最不好的地方是,字符串列表是固定的,添加或者删除字符串必须使用ALTER TABLE。因此对于一系列未来可能会改变的字符串,使用枚举不是一个好主意,出发能接受只在列表尾添加元素

  由于myslq把每个枚举值保存为整数,并且必须进行查找才能转换为字符串,所以枚举列有一些开销。通常枚举的列表都是比较小的,所以开销还可以控制,蛋也不能保证一直如此。在特定的情况下,把char/varchar列与枚举列进行关联,可能会比直接关联char/varchar 列更慢。

  日期和时间类型

  DATETIME

  这个类型能保存大范围的值,从1001到9999年,精度为秒。它把日期和时间封装到各式为YYYYMMDDHHMMSS的整数中,与时区无关使用8个字节的存储空间。

  默认情况下mysql以一种可排序的、无歧义的各式显示datetime值,例如‘2015-11-17 23:20:00’。这是ANSI标准定义的日期和时间的表示方法。

  TIMESTAMP

  就像它的名字一样,TIMESTAMP类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数,它和Unix时间戳相同。TIMESTAMP只使用4个字节的存储空间,因此它的范围比datetime小的多:只能表示1970年到2038年。mysql 提供了FROM_UNIXTIME()函数把Unix时间戳转化为日期,并提供了UNIX_TIMESTAMP()函数把日期转换为UNIX时间戳。

  timestamp显示的值也依赖于时区。mysql服务器、操作系统、以及客户端连接都有时区设置。

  因此存储值为0的timestamp在美国东部时区显示为1969-12-31 19:00:00 与格林尼治时间差5个小时。有必要强调一下这个区别:如果在多个时区存储或访问数据,timestamp和datetime的行为将很不一样。前者提供的值与时区有关系,后者则保留文本表示的日期时间。

  timestamp也有datetime没有的特殊属性。默认情况下,如果插入时没有指定第一个timestamp列的值,mysql则设置这个列的值为当前时间,在插入一行记录时,mysql也会默认更细第一个timestamp的值(除非在update语句中明确指定了值)。你可以配置任何timestamp列的插入和更新行为。最后,timestamp 列默认为not null,这也和其他数据类型不一样。

  除了特殊行为之外,通常也应该尽量使用timestamp ,因为他比datetime空间效率更高。有时候,人们会将Unix时间戳存储为整数值,但这并不会带来任何收益。使用整数保存时间戳的格式通常不方便处理,所以我们不推荐这样做。

  如果需要存储比秒更小粒度的日期和时间值怎么办?myslq 目前没有提供合适的数据类型,但是可以使用自己的存储格式:可以使用bigint类型存储微妙级别的时间戳或者使用double存储秒之后的小数部分。或者也可以使用MariaDB来代替mysql。

  位数据类型(BIT)

  BIT列的最大长度是64个位。

  MYSQL把bit当作字符串类型,而不是数字类型。当检索bit(1)的值时,结果是一个包含二进制0或1的字符串,而不是ASCII码的‘0’或‘1’。然而,在数字上下文的场景中检索时,记过是将位字符串转换成数字。如果需要和另外的值比较结果,一定要记住这一点。例如:如果存储一个值b‘00111001’二进制的值为57,到bit(8)的列并检索它,的到的内容是字符串码为57的字符串。也就是说,的到ascii码为57的字符“9”。但是在数字上下文场景中,的到的数字是57.

  这是相当令人费解的,所以应该谨慎的使用bit类型。对于大部分应用最好避免使用这种类型。

  如果想在一个bit的存储空间中粗糙一个true或false值,另一个方法是创建一个可以为空的char(0)列。改列可以保持空值(NULL)或者长度为0的字符串(空字符串)。