mysql数据类型学习笔记

最近在学习mysql。数据类型部分，知识点比较散，在此整理一下：

1、数据类型的选择原则

      （1）选择占用空间更小的数据类型。 例如：如果只需要存0-200，tinyint unsigned（占1个字节，0-255）更好。

      （2）选择更简单直接的。例如整型比字符串操作代价更低，使用Mysql内建的类型（date,time,datetime,timestamp）存储日期和时间，而不是字符串。

       （3)  尽量避免null。可为null的列使得索引、值比较都更复杂。会使用更多的存储空间，在MYSQL里需要特殊处理。可为null的列被索引时，每个索引记录需要一个额外的字节。

2、整数类型

     （1）TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间。能够表达的值的范围从-2的(N-1)方到2的(N-1)次方-1.N为存储空间位数。

     （2）整数类型有可选的UNSIGNED属性。可以使正数的上限提高一倍。从0到2的N次方-1.

     （3）MYSQL可以为整数类型指定宽度，如INT(11)，对大多数应用没有意义。不会限制值的合法范围，只是规定MYSQL的一些交互工具（客户端）显示的字符的个数。对于存储和计算,INT(1)和INT(20)是一样一样的。

3、实数类型（带有小数部分的数字和BIGINT无法表达的整数）

     （1）DECIMAL用于存储精确的小数。在MYSQL5.0和更高版本，DECIMAL类型支持精确计算，在MYSQL4.1以及更早的版本则使用浮点运算来实现DECIMAL的计算，会因精度损失出现一些奇怪的结果。（注：CPU支持原生浮点计算，所以浮点运算明显更快）

     （2）DECIMAL类型可以指定精度。这回影响DECIMAL列的空间消耗。在MYSQL5.0以后，每4个字节存9个数字。如：DECIMAL(18,9),一共使用9个字节。小数点前4个字节，小数点后4个字节，小数点本身1个字节。

     （3）MYSQL5.0以及更高版本中，DECIMAL类型允许最多65个数字。而早期的版本中此限制是254个数字，并保存为字符串（每个数字一个字节）。然而并不能在计算中使用这么大的数字的。原因是在当时版本DECIMAL只是存储格式，计算的时候会转为DOUBLE类型进行计算。

     （4）浮点类型在存储同样范围的值时，通常比DECIMAL占用更少的空间。FLOAT使用4个字节，DOUBLE占用了8个字节。MYSQL使用DOUBLE作为内部浮点计算的类型。

     （5）由于空间和计算开销的考量，应该尽量只在对小数进行精确计算时，才使用DECIMAL（如财务数据）。但在数据量比较大的时候可以考虑BIGINT代替DECIMAL(如果没有超过BIGINT表达范围的话)，思路是将小数乘以相应的倍数，使之成为整数。

4、字符串类型

     （1）VARCHAR存储可变字符串，比定长类型更节省空间。但有一种情况是例外，如果MYSQL的表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储。

     （2）VARCHAR需要1或2个额外字节记录字符串的长度。如果列的最大长度<=255字节，那么只使用一个字节表示。否则使用2个字节。假设采用latin1字符集，一个VARCHAR(10)的列需要11个字节的存储空间，VARCHAR(1000)的列需要1002个字节的存储空间。

     （3）VARCHAR类型的字段在UPDATE时可能需要做额外的工作。如果磁盘页内没有更多的空间可以存储，就需要扩容。不同的存储引擎的处理方式不一样。MyISAM会将行拆成不同的片段存储，InnoDB会需要分裂页使行可以放进页内。

     （4）适用VARCHAR类型的场景：字符串列的最大长度比平均长度大很多；列的更新很少，碎片问题比较少；使用了UTF-8字符集，每个字符占用的存储空间是可变的。

     （5）MYSQL5.0以后的版本，在存储和检索VARCHAR时会保留末尾空格，之前的版本会剔除掉末尾空格。

     （6）CHAR类型是定长的。存储CHAR时，MYSQL会删除掉末尾空格，这是因为CHAR类型会使用空格来填充存储空间中剩下的空间，以方便字符串比较。如果存储长度固定或者基本一致的列，使用CHAR比较合适，因为不会产生空间碎片。

     （7）在MYSQL中，数据如何存储决定于存储引擎，并非所有的存储引擎会按照相同的方式处理定长和边长的字符串。Memory引擎只支持定长的行，即使有变长字段，也会根据最大长度分配每一行的空间。不过填充空格和截取空格的行为在不同存储引擎都是一样的。这是在MYSQL服务器层进行处理的。

     （8）与CHAR和VARCHAR类类似，还有BINARY和VARBINARY，存储的是二进制字符串，即存储的是字节码而非字符。使用\0填充而不是空格。二进制的优势在比较的时候按字节比较。简单、快速。

5、BLOB和TEXT

     （1）TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB。BLOB是SMALLBLOB的同义词。

     （2）TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT。TEXT是SMALLTEXT的同义词。

     （3）和其他数据类型不同，BLOB和TEXT是作为独立的对象处理，当BLOB和TEXT值太大时，InnoDB会使用外部存储区域进行存储。此时每个值在行内需要1-4个字节存储指向真正数据的指针。

     （4）BLOB存储的是二进制字节数据，没有排序规则（COLLATION）和字符集（CHARSET)，而TEXT则相反。

     （5）对BLOB和TEXT进行排序时，只对每个列的最前MAX_SORT_LENGTH字节进行排序，而不是整个字符串。

     （6）Memory引擎不支持BLOB和TEXT类型。如果查询使用到了BLOB或者TEXT列，并需要使用隐式临时表，将不得不是一样MyISAM磁盘临时表，这回导致严重的性能开销。最好的解决方案是尽量避免使用BLOB和TEXT。如果非用不可，可以在查询中将使用到BLOB或者TEXT的地方使用SUBSTRING(COLUMN,LENGTH)将BLOB转为字符串，这样就可以使用内存临时表了。但要确保截取的字符串足够短，不会使临时表大小超过MAX_HEAP_TABLE_SIZE或TMP_TABLE_SIZE,超过后，仍会使用磁盘临时表。

6、ENUM

    （1）MYSQL在存储枚举类型数据时，实际上保存的是每个值在枚举列表中的位置（该整数在1-2个字节内即可表达，除非枚举项非常多）。并且在表的.frm文件中保存‘数字---字符串’的映射关系。

    （2）对枚举字段进行排序时，是按照内部存储的整数（即在枚举列表中位置）排序，而不是对枚举列表的含义字符串进行排序。如果想绕过此限制，需要使用FIELD函数。

    （3）由于MYSQL把每个枚举值存为整数，并且需要进行查询映射关系表来转化为字符串，因此枚举列有一些额外开销。在关联表的情况下，VARCHAR/CHAR类型和ENUM类型进行关联会比VARCHAR和VARCHAR关联更慢。但ENUM的优势在于存储占用的空间变小了。

7、日期和时间类型

   （1）MYSQL能存储的最小时间粒度为秒。（MariaDB支持微秒）。

   （2）DATETIME支持1001----9999年。精度为秒。它把日期封装到格式为YYYYMMDDHHMMSS的整数中。与时区无关。使用了8个字节的存储空间。

   （3）TIMESTAMP保存了从1970年1月1日午夜（格林泥治标准时间）以来的秒数。只能表示到1970---2038年。使用了4个字节的存储空间。MYSQL提供了FROM_UNIXTIME将UNIX时间戳转为日期，UNIX_TIMESTAMP将日期转为时间戳。

   （4）TIMESTAMP列默认NOT NULL，DEFAULT值为当前时间。

   （5）通常应尽量使用TIMESTAMP，它比DATETIME空间效率更高。不推荐将unix时间戳存储为整数，不会带来任何收益。

   （6）如果需要存储更小粒度的日期和时间：使用BIGINT存储时间；使用DOUBLE存储秒之后的小数部分；使用MariaDB替代MYSQL.