【基础】数据类型

时间:2022-10-19 16:54:38

Mysql版本众多,每个版本支持的数据类型繁多且不一样,本篇文章中主要基于MySQL Community Server 5.7.22介绍常用的数据类型,包括其特点以及区别。

一.数据类型

正确的定义表结构:数据类型、字段长度,对于数据库性能至关重要。Mysql中支持众多数据类型,很多类型又支持不同的长度。

【基础】数据类型

从以上的图中可以看出在Mysql中主要支持三种大类型的数据:

  • Numeric数值类型
  • Date and Time日期时间类型
  • String字符串类型

其中每种大类型中又细分特定的数据类型

Numeric Date and Time String
TINYINT DATE CHAR
SMALLINT DATETIME VARCHAR
INT TIMESTAMP BLOB
MEDIUMINT TIME TEXT
BIGINT YERA ENUM
FLOAT    
DOUBLE    
DECIMAL    

二.区别及应用场景

1.Numeric

数值类型的细分标准主要是根据数值表示的精度划分,其中整形又根据表示范围进一步划分。

  1. 精度划分:整形、单精度浮点类型、双精度浮点类型、DECIMAL
  2. 范围划分:TINYINT、SMALLINT、MEDIUMINT、BIGINT

下表中表示了各种INT类型的范围和存储所需要的字节长度:

【基础】数据类型

根据上表,对于表结构的列字段数据类型选型一定要精确匹配,能够节省存储空间,体现设计的严谨。比如订单状态,可以使用TINYINT类型。

DECIMAL-Fixed-Point Types (Exact Value):当需要明确表示数值精度时,就需要使用DECIMAL,比如货币数据,金钱是无法四舍五入近似处理的数据。比如薪资,存款。

FlOAT/DOUBLE-Floating-Point Types (Approximate Value):浮点类型大多数用于近似处理场景。

Type Storage(Bytes) Precision
FLOAT 4 23
DOUBLE 8 53

2.DATE and TIME

  • DATE表示日期,无时间部分。mysql检索和展示日期使用'YYYY-MM-DD'格式,范围在:'1000-01-01' to '9999-12-31'。
  • DATETIME表示日期时间,既有日期部分又有时间部分。mysql检索和展示日期时间使用'YYYY-MM-DD HH:MM:SS'格式,范围在:'1000-01-01 00:00:00' to '9999-12-31 23:59:59'。
  • TIMESTAMP表示时间戳,既有日期也有时间部分。范围在:1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC。由此可以看出DATETIME和TIMESTAMP的区别在于时间表示的范围上。除此,mysql在存储TIMESTAMP类型时将TIMESTAMP值从当前时区转换到UTC时区,检索时再将其从UTC转换到当前时区。这个时区设置是基于数据库连接的。默认情况下,每个连接的时区被设置为server端的时区,也可以单独设置连接的时区。所以如果改变连接的时区,检索时获取到的TIMESTAMP将表现不同。
  • TIME表示时间。mysql检索和展示时使用'HH:MM:SS'格式或者HHH:MM:SS,范围在:'-838:59:59' to '838:59:59'。TIME类型不仅可以表示一天中的时间点,还可以表示过去的时间或者间隔的时间段,所以可以是三位H。

3.String

String类型中常用的是CHAR和VARCHAR类型,TEXT和BLOB在大型字段中存储可能会使用,BLOB是存储String的二进制,比如图片二进制字节,TEXT存储文本。枚举在常量列表数据中可能会使用,主要是为了提高性能。

Char和VARCHAR类型十分相似,都是存储字符。但是从底层检索和存储角度分析,表现又不一样,且最大长度和尾部遗留空间是否保留的策略上表现也不尽相同。

比较 描述
相同点 1.都是存储字符类型数据; 2.类型申明时,都指定长度;
不同点 1.表示的长度范围不一。CHAR能表示0-255长度的字符, VARCHAR可以表示0-65535长度; 2.CHAR申明后,存储数据时长度是固定的,长度不足时 从右边补空格。VARCHAR申明后,存储时长度是可变的, VARCHAR最大有效长度取决于行数据的大小和字符集, VARCHAR在存储时有额外的1-2byte长度数据表示, 额外长度的数据表示存储的数据的字节个数; 3.CAHR在检索时,将去除后面的空格;

从以上的不同点描述CHAR和VARCHAR的存储原理中,可以看出CHAR和VARCHAR在底层的存储和检索时大不一样。

在非严格模式下,对于超过列申明长度的CHAR或者VARCHAR,mysql会截取以适合其申明的长度进行存储。例如下表:


【基础】数据类型