【Mysql系列】 Mysql数据类型

1.1. CHAR与VARCHAR类型

CHAR()类型表示长度固定的字符串，n表示字符的最大数量，取值范围从0到255。如果输入的字符串长度不够，将会使用空格进行填充。默认情况下没有设置SQL模式PAD CHAR TO FULL LENGTH,MySQL读取 CHAR字段时自动截断了尾部的空格

VARCHAR字段的实际最大长度受限于最大的行大小(65536字节，所有字段长度之和)以及字符集。例如， utf8b4字符集中的一个字符最多占用4个字节，因此这种字符集的VARCHAR字段可以声明的最大长度为 16383。

1.2. BINARY.与VARBINARY

BINARY(M)和/ARBINARY(M)类型与CHAR和ARCHAR类型类似，但是存储的内容为二进制字节串，而不是普通字符串。其中M表示最大的字节长度，分别为255和65535。这两种类型使用binary字符集和排序规则，基于字节数值进行比较和排序。 I存储BINARY数据时，在尾部使用0x00(字节0)填充到指定长度，查询时不会删除尾部的O字节。所有的字节对应比较操作都有意义，包括ORDER BY和DI1 STINCT:操作，OxO0和空格比较的结果不相等，OxOO的排序在空格前面。

1.3. TEXT与BLOB

TEXT类型可以用于存储长文本字符串，长度支持1字节到4GB。M小ySQL不会在服务器内存中缓存TEXT数据，而是从磁盘中读取，所有访问时比CHAR和VARCHAR类型更慢一些。MySQL插入或者查询时不会对TEXT数据尾部空格进行任何处理。 MySQL提供了4种形式的TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT以及LONGTEXT。● TINYTEXT,最大长度为255个字节，类似于VARCHAR(255)。每个TINYTEXT值需要1字节额外的存储表示长度 TEXT(SAMLLTEXT),最大长度为64KB,类似于VARCHAR(65535)。每个TEXT值需要2字节额外的存储表示长度； MEDIUMTEXT,最大长度为16MB。每个MEDIUMTEXT值需要3字节额外的存储表示长度；●·LONGTEXT,最大长度为4GB。每个LONGTEXT值需要4字节额外的存储表示长度。?TEXT数据类型一般用于存储文章内容、产品文档等信息。只有在普通字符串类型的长度无法满足时才会考虑使用TEXT类型，推荐单独使用一个表存储这种字段。 BLOB类型可以用于存储二进制大对象，长度支持1字节到4GB;BLOB类型使用binarya字符集和排序规则，基于字节数值进行比较和排序。MySQL不会在服务器内存中缓存BLOB数据，而是从磁盘中读取，所有访问时比 BINARY和/ARBINARY:类型更慢一些。MySQL插入或者查询时不会对BLOB数据尾部空格进行任何处理。 MySQL提供了4种形式的BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB以及LONGBLOB。

MySQL使用独立的"外部存储"处理TEXT和BLOB数据，而且只对数据的max_sort_.lerigth:字节进行排序，不支持完整数据的索引和排序。△BLOB数据类型一般用于存储图片、文档、视频等信息，但是不建议这样使用。推荐使用单独的对象存储二进制内容，并且在数据库中保存文件路径。

1.4. ENUM类型

ENUM('value1',value2',…)类型定义了一个枚举，即取值限定为value1'、value.2'、、NULL或者"之一的字符串对象。ENUM数据在内部使用整数表示，最多包含65535个不同的值。每个枚举元素最大的长度为M<=255并且(M*W)<=1020,其中M是元素的字面长度，W是字符集中字符可能占用的最大字节数。使用枚举类型的优势在于：·在字段的取值有限时提供紧凑的数据存储，枚举在内部使用整数表示，需要1字节或2字节存储：查询结果的可读性，内部整数在查询结果中显示为相应的字符串。

如果插入100万条'medium数据，需要100万字节存储；如果直接使用/ARCHAR:类型，需要6倍存储。使用枚举类型时需要注意枚举值的排序使用内部的索引数字，而不是字符串。例如，对于ENUM(b',)字符b排在ā之前。枚举类型和字符串类型的连接查询性能更慢。

1.5. SET类型

SET(value1',value.2',.…)类型定仪了一个集合，即取值限定为value1'、value.2'、.中零个或多个的字符串对象。SET数据在内部使用整数表示，最多包含64个不同的成员。每个集合元素最大的长度为M<=255并且(M*W)<=1020,其中M是元素的字面长度，w是字符集中字符可能占用的最大字节数。

SET对象的存储空间由集合成员的个数决定；如果个数为N,对象占用(N+7)/8字节，向上取整为1、2、3、4或者8字节。I MySQL提供了FIND_IN_SET、FIELD等函数，方便了SET数据类型的使用。?SET类型的一个典型应用时访问控制列表(ACL)

1.6. 日期和时间

MySQL提供了以下存储日期时间值的数据类型：DATE、TIME、DATETIME、TIMESTAMP以及YEAR。其中， TIME、DATETIME、TIMESTAMP:支持小数秒，最多6位小数（微秒）。

1.6.1. 日期类型

DATE表示日期类型，支持的范围从'1000-01-01'到'9999-12-31'，占用3个字节。DATE数据的显示格式为 YY-MM-DD'。例如：

MySQL使用4位数字存储日期数据中的年份，如果输入2位年份，将会使用以下规则：·00~69之间的年份转换为2000~2069：·70~99之间的年份转换为1970~1999。

1.6.2. 时间类型

MySQL使用TIME类型表示一天中的时间，格式为'HH:MM:SS',范围小于24小时。另外，也可以使用TIME表示两个事件之间的时间间隔，格式为hhh:mm:ss',范围从'-838：59：59'到'838：59：59'。TME类型需要3字节存储空间。 MySQL使用TIME(N)表示包含小数部分的时间，最多包含6位小数（微秒），默认为0位。如果包含了小数秒 TIME需要额外的存储，TIME(1)和TIME(2)需要4字节，TIME(3)和TIME(4)需要5字节，TIME(5)和TIME(6)需要6字节存储。

1.6.3. 时间戳类型

DATETIME(N)和TIMESTAMP(N)类型可以同时存储日期(DATE)和时间(TIME)信息，也就是时间戳。 DATETIME类型使用YYY-MM-DDhh:mm:ss[.fraction]'格式显示，支持范围'1000-01-0100：00：00.000000'到9999-12-3123：59：59.999999'，默认0位小数秒，需要5字节存储。如果支持小数秒，额外的存储和 TIME(N)类似。1 TIMESTAMP类型使用UTC时区进行存储，支特范围'1970-01-0100：00：01.000000'UTC到2038-01-19 03:14:07.999999'UTC,默认0位小数秒，需要4字节存储。如果支特小数秒，额外的存储和T1ME(N)类似。

结果显示，TIMESTAMP类型会随着当前时区进行调整。 DATETIME和TIMESTAMP类型支持自动初始化或者更新为当前日期时间，在字段定义时分别使用DEFAU儿T CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性进行设置。

另一种存储时间戳的方式是使用UNX时间戳，即当前时间距离1970年1月1日的秒数来表示时间。例如，使用 INT可以支持到2038年，无符号的NT可以支持到2106年。这种存储方式的缺点就是可读性不强。MySQL提供了FORM_UNIXTIME和UNIX_TIMESATMP函数处理整数和时间戳的转换，也可以依赖应用程序进行格式处理。

1.7. Json数据类型

MySQL5.7.8开始支持原生JSON数据类型，可以支持更加高效的SON文档存储和管理。原生SON数据类型提供了自动的格式验证以及优化的存储格式，可以快速访问文档中的元素节点。例如：

JSON文档的最大长度不能超过系统变量max_allowed_packet(默认64MB)的限制。除了)SON数据类型之外，MySQL还提供了许多SON处理函数和操作符，例如：构造SON对象的SON_OBJECT、JSON_ARRAY;·查询指定元素的->(JSON_EXTRACT)、>>(SON_UNQUOTE+JSON_EXTRACT)·将)SON数据转换为SQL数据的SON_TABLE;·更新SON数据的SON SET、JSON_INSERT、JSON_REPLACE、JSON_REMOVE格式验证的SON_VALID函数。一般来说，JSON字段所需的存储和LONGBLOB或者LONGTEXT差不多。不过，JSON文档的二进制编码需要额外的存储，包括元数据和字典信息。举例来说，JS0N文档中的字符串需要额外的4到10个字节存储。

一般来说，JSON字段所需的存储和LONGBLOB或者LONGTEXT:差不多。不过，SON文档的二进制编码更额外的存储，包括元数据和字典信息。举例来说，S0N文档中的字符串需要额外的4到10个字节存储。 JSON数据类型的优势是结构更灵活，不需要预定义字段。M小ySQL支持基于虚拟列的索引，可以为SON节点创建索引，优化查询性能。