MySQL之Schema与数据类型优化(四)

Schema与数据类型优化

日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR和DATE.MySQL能存储的最小时间粒度为秒(MariaDB支持微秒级别的时间类型)。但是MySQL也可以使用微秒级别的粒度进行临时运算，接下来会展示如何绕开这种存储限制。大部分时间类型都没有替代品，因此没有什么事最佳选择的问题。唯一的问题是保存日期和时间的时候需要做什么。MySQL提供两种相似的日期类型:DATETIME和TIMESTAMP。对于很多应用程序，它们都能工作，但是在某些场景，一个比另一个工作得好。

• 1.DATETIME
  这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。默认情况下，MySQL以一种可排序的、无歧义的格式显式DATETIME值，例如"2008-01-16 22:37:08"。这是ANSI标准帝国一的日期和时间表示方法。
• 2.TIMESTAMP
  就像它的名字一样，TIMESTAMP类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多:只能表示1970年到2038年。MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把七日转换为Unix时间戳。MySQL4.1以及更新的版本按照DATETIME的方式格式化TIMESTAMP的值，但是MySQL4.0以及更老的版本不会在各个部分之间显式任何标点符号。这仅仅是显式格式上的区别，TIMESTAMP的存储格式在各个版本都是一样的。
  TIMESTAMP显式地值也依赖于时区。MySQL服务器、操作系统，以及客户端连接都有时区设置。因此，存储值为0地TIMESTAMP在美国东部时区显式为"1969-12-31 19:00:00"，与格林尼治时间差5个小时。有必要强调一下这个区别:如果在多个时区存储或访问数据，TIMESTAMP和DATETIME的行为将很不一样。前者提供的值与时区有关系，后者则保留文本表示的日期和时间。
  TIMESTAMP也有DATETIME没有的特殊属性。默认情况下，如果插入时没有指定第一个TIMESTAMP的值，MySQL则设置这个列的值为当前时间(TIMESTAMP的行为规则比较复杂，并且在不同的MySQL版本里会变动，所以你应该验证数据库的行为是你需要的。一个好的方式是修改完TIMESTAMP列后用SHOW CREATE TABLE命令检查输出)。在插入一行记录时，MySQL默认也会更新第一个TIMESTAMP列的值(除非在UPDATE语句中明确指定了值)。你可以配置任何TIMESTAMP列插入和更新行为。最后，TIMETSAMP列默认为NOT NULL，这也和其他的数据类型不一样。

除了特殊行为之外，通常也应该尽量使用TIMESTAMP,因为它比DATETIME空间效率更高。有时候人们会将Unix时间戳存储为整数值，但这不会带来任何收益。用整数保存时间戳的格式通常不方便处理，所以不推荐这样做。如果需要存储比秒更小粒度的日期和时间值怎么办呢？MySQL目前没有提供合适的数据类型，但是可以使用自己的存储格式:可以使用BIGINT类型存储微秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分，这两种方式都可以，或者也可以使用MariaDB替代MySQL

位数据类型

MySQL有少数 集中存储类型使用紧凑的位存储数据。所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。

• 1.BIT
  在MySQL5.0之前，BIT是TINYINT的同义词。但是在MySQL5.0以及更新版本，这是一个特性完全不同的数据类型。可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义一个包含单个位的字段,BIT(2)存储2个位，依此类推，BIT列的最大长度是64个位。BIT的行为因存储引擎而异。MyISAM会打包存储所有的BIT列，所以17个单独的BIT列只需要17个位存储(假设没有可为NULL的列)，这样MyISAM只适用3个字节就能存储17个BIT列。其他存储引擎例如Memory和InnoDB，为每个BIT列使用一个豿存储的最小整数类型来存放，所以不能节省存储空间。
  MySQL把BIT当作字符串类型，而不是数字类型。当检索BIT(1)的值时，结果是一个包含二进制0或1值得字符串，而不是ASCII码的"0"或"1".然而，在数字上下文的场景中检索时，结果将时位字符串转换成的数字。如果需要和另外的值比较结果，一定要记得这一点。例如，如果存储一个值b’00111001’(二进制值等于57)到BIT(8)的列并且检索它，得到的内容是字符码为57的字符串。也就是说得到00111001。但是在数字上下文场景中，得到的是数字57:

mysql> INSERT INTO bittest VALUES(b'00111001'