MySQL中的奇特之处：数据类型

初用MySQL，发现一些和Oracle等数据库不一样的地方，写下来方便查阅参考。

1. 整数数据类型

表达范围从小到大依次为tinyint,smallint,mediumint,int,bigint，占用的存储字节数分别为1，2，3，4，8。

类型后面可以在括号中标注显示宽度，例如int(5)，其中5就是显示宽度，表示该数据在显示时应该使用5个十进制数字。

显示宽度只适用于应用程序。它的取值不影响DBMS如何存储数据，也不限制什么取值范围的数据可以被存储。在查询时，它和数据一起被从DBMS返回给应用程序，由应用程序决定如何使用它。

mysql中没有原生的bool类型。当在DDL中使用boolean类型时，实际上会被解释为tinyint(1)。

所以在mysql中，如果想表示只有两个取值的bool类型，使用boolean或者tinyint(1)即可。如果想表示10以内的小整数，不要使用tinyint(1)类型，因为可能会被解释为boolean类型。替代方法是使用tinyint(2)或其它整数类型。

2. 时间戳类型

mysql中有两个类型可以表达时间戳：timestamp，datetime。两者的数值表达能力是一样的，都可以表达年月日时分秒微秒，即下面的格式：

'YYYY-MM-DD HH:MM:SS[.fraction]'

注：在5.6.4版本之前，是不存储微秒部分的。

它们的主要区别是：

1）表达范围不同

datetime的表达范围是：'1000-01-01 00:00:00.000000' - '9999-12-31 23:59:59.999999'

timestamp的表达范围是：'1970-01-01 00:00:01.000000' - '2038-01-19 03:14:07.999999'

2）存储方式不同

datetime的存储数据中不包含时区信息。datetime的各部分分别存储，所以需要空间比较大。在5.6.4版本之前，占用8个字节。从5.6.4版本起，会占用5个字节再额外加微秒部分。猜测5个字节的分配为：

年份：14个bit

月份：4个bit

日期：5个bit

时：5个bit

分：6个bit

秒：6个比特

合计40个bit，刚好是5个字节。

timestamp的存储数据隐含了时区信息（是UTC时区）。非UTC时区的时间戳在存储时会被转换为UTC时区的时间，而在查询时则被转换为查询会话的时区。不同时区的客户端查询同一个timestamp得到的结果可能看起来不同。

timestamp的存储数据是一个32bit的整数，占用4个字节，表示自1970-1-1 00:00:00以来的秒数。在5.6.4版本之前，只是这4个字节。 从5.6.4版本起 ，再额外加微秒部分。

3）空值

与其它数据类型不同，timestamp类型缺省为非空。如果赋予null值，会自动转化为current_timestamp。

datetime类型缺省则为可空（和其它数据类型一样）。

4）初始化方式不同（这里的讨论仅限初始化为当前时间戳）

MySQL中有两种时间戳初始化方式：

DEFAULT current_timestamp：表示在行创建时为该时间戳列自动赋值

ON UPDATE current_timestamp：表示在行中其它列发送改变时为该时间戳列自动赋值

在5.6.5版本之前，只有timestamp类型的时间戳可以被自动初始化数值，而且至多只能给一列timestamp指定自动初始化。可以给同一个timestamp列同时指定两种初始化，但不能给不同的timestamp列分别指定不同的初始化方式。而datetime类型则根本不能被自动初始化。

从5.6.5版本起，timestamp和datatime都可以被自动初始化。如果第一个timestamp列类型为非空并且没有指定任何自动初始化属性（类似于 "c1 timestamp,"这样的最简洁的写法），那么该列会缺省具有"default current_timestamp on update current_timestamp"属性。

另外需注意，只有timestamp或者datetime类型可以被初始化为非常数值。如果想将一个date列初始化为current_date，mysql是不接受的。