Mysql 数据类型使用说明_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的字符串（空字符串）。