每种编程语言都有自己所定义的数据类型,mysql也不例外,平时我们在创建表时,需要根据业务要求,结合存储、索引、字段临界值等条件来为字段定制不一样的类型。下面我们一起学习下mysql的几种常用的数据类型。 MySQL支持多种类型的SQL数据类型:数值,日期和时间类型,字符串(字符和字节)类型,空间类型和 JSON数据类型等
在 mysql 中,一个中文汉字所占的字节数与编码格式有关:
如果是GBK编码,则一个中文汉字占2个字节,英文占1个字节 如果是UTF8编码,则一个中文汉字占3个字节,而英文字母占1字节。 比如定义某个字段数据类型为:varchar(32),表示这个可以存储 32 个字符,此时表示的是字符,所以跟中英文无关,也就是该字段可以存储 32 个中文,或者是 32 个英文,或者是 32 个中文和英文的混搭都行。但如果字符数超过 32 个的话就会报错。
在 MySQL 中支持的 5 个主要整数类型是 TINYINT,SMALLINT,MEDIUMINT,INT 和 BIGINT。这些类型在很大程度上是相同的,只有它们存储的值的大小是不相同的。
长度: 整数类型可以被指定长度,例如:INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的,它不会限制值的合法范围,只会影响显示字符的个数,而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。 **例子:**假定类型设定为INT(5),属性为UNSIGNED ZEROFILL,如果用户插入的数据为12的话,那么数据库实际存储数据为00012。 应用场景: (1)TINYINT :一般用于枚举数据,比如系统设定取值范围很小且固定的场景。 (2)SMALLINT :可以用于较小范围的统计数据,比如统计工厂的固定资产库存数量等。 (3)MEDIUMINT :用于较大整数的计算,比如车站每日的客流量等。 (4)INT、INTEGER :取值范围足够大,一般情况下不用考虑超限问题,用得最多。比如商品编号。 (5)BIGINT :只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。
DECIMAL可以用于存储比BIGINT还大的整型,能存储精确的小数。 其中,FLOAT 表示单精度浮点数;DOUBLE 表示双精度浮点数;而FLOAT和DOUBLE是有取值范围的,并支持使用标准的浮点进行近似计算。
计算时FLOAT和DOUBLE相比DECIMAL效率更高一些,DECIMAL你可以理解成是用字符串进行处理。 数据精度说明 (1)对于浮点类型,在MySQL中单精度值使用 4 个字节,双精度值使用 8 个字节。 (2)MySQL允许使用 非标准语法 (其他数据库未必支持,因此如果涉及到数据迁移,则最好不要这么用): FLOAT(M,D) 或 DOUBLE(M,D) 。这里,M称为精度 ,D称为标度 。(M,D)中 M=整数位+小数位,D=小数位。 D<=M<=255,0<=D<=30。 例如,定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。如果超过这个范围会报错。 (3)FLOAT和DOUBLE类型在不指定(M,D)时,默认会按照实际的精度(由实际的硬件和操作系统决定)来显示。 (4)浮点类型,也可以加 UNSIGNED ,但是不会改变数据范围。 例如:FLOAT(3,2) UNSIGNED仍然只能表示0-9.99的范围。 (5)不管是否显式设置了精度(M,D),这里MySQL的处理方案如下:
如果存储时,整数部分超出了范围,MySQL就会报错,不允许存这样的值; 如果存储时,小数点部分若超出范围,就分以下情况: (1)若四舍五入后,整数部分没有超出范围,则只警告,但能成功操作并四舍五入删除多余的小数位后保存。 例如在FLOAT(5,2)列内插入999.009,近似结果是999.01。 (2)若四舍五入后,整数部分超出范围,则MySQL报错,并拒绝处理。 如FLOAT(5,2)列内插入999.995和-999.995都会报错。
(6)从MySQL 8.0.17开始,FLOAT(M,D) 和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用,将来可能被移除。另外,关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用了,将来也可能被移除。 对于DECIMAL: (1)MySQL中的定点数类型只有 DECIMAL 一种类型。 使用 DECIMAL(M,D) 的方式表示高精度小数。其中,M被称为精度,D被称为标度。0<=M<=65, 0<=D<=30,D<M。 例如,定义DECIMAL(5,2)的类型,表示该列取值范围是-999.99~999.99。 (2)DECIMAL(M,D)的最大取值范围与DOUBLE类型一样,但是有效的数据范围是由M和D决定的。DECIMAL 的存储空间并不是固定的,由精度值M决定,总共占用的存储空间为M+2个字节。也就是 说,在一些对精度要求不高的场景下,比起占用同样字节长度的定点数,浮点数表达的数值范围可以更大一些。 (3)定点数在MySQL内部是以 字符串的形式进行存储,这就决定了它一定是精准的。 (4)浮点数和定点数的对比:
浮点数相对于定点数的优点是在长度一定的情况下,浮点类型取值范围大,但是不精准,适用于需要取值范围大,又可以容忍微小误差的科学计算场景(比如计算化学、分子建模、流体动力学等); 定点数类型取值范围相对小,但是精准,没有误差,适合于对精度要求极高的场景 (比如涉及金额计算的场景)。
日期与时间是重要的信息,在我们的系统中,几乎所有的数据表都用得到。原因是客户需要知道数据的时间标签,从而进行数据查询、统计和处理。
MySQL有多种表示日期和时间的数据类型,不同的版本可能有所差异,MySQL8.0版本支持的日期和时间类型主要有:YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。
尽量使用timestamp,空间效率高于datetime, 用整数保存时间戳通常不方便处理。
注意: 为什么时间类型 TIME 的取值范围不是 -23:59:59~23:59:59 呢? 原因是 MySQL 设计的 TIME 类型,不光表示一天之内的时间,而且可以用来表示一个时间间隔,这个时间间隔可以超过 24 小时。 TIMESTAMP和DATETIME的区别: (1)TIMESTAMP存储空间比较小,表示的日期时间范围也比较小。 (2)底层存储方式不同,TIMESTAMP底层存储的是毫秒值,距离1970-1-1 0:0:0 0毫秒的毫秒值。 (3)两个日期比较大小或日期计算时,TIMESTAMP更方便、更快。 (4)TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同,显示不同的结果。而DATETIME则只能反映出插入时当地的时区,其他时区的人查看数据必然会有误差的。 总结: 用得最多的日期时间类型,就是 DATETIME 。虽然 MySQL 也支持 YEAR(年)、 TIME(时间)、DATE(日期),以及 TIMESTAMP 类型,但是在实际项目中,尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息,取值范围也最大,使用起来比较方便。毕竟,如果日期时间信息分散在好几个字段,很不容易记,而且查询的时候,SQL 语句也会更加复杂。 此外,一般存注册时间、商品发布时间等,不建议使用DATETIME存储,而是使用时间戳 ,因为DATETIME虽然直观,但不便于计算。
MySQL中,文本字符串总体上分为 CHAR 、VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、LONGTEXT 、 ENUM 、 SET 等类型。 (1)CHAR类型
1、CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M),则表示长度默认是1个字符。 2、如果保存时,数据的实际长度比CHAR类型声明的长度小,则会在右侧填充 空格以达到指定的长 度。当MySQL检索CHAR类型的数据时,CHAR类型的字段会去除尾部的空格。 3、定义CHAR类型字段时,声明的字段长度即为CHAR类型字段所占的存储空间的字节数
(2)VARCHAR类型
1、 VARCHAR(M) 定义时, 必须指定长度M,否则报错。 2、MySQL4.0版本以下,varchar(20):指的是20字节,如果存放UTF8汉字时,只能存6个(每个汉字3字节) ;MySQL5.0版本以上,varchar(20):指的是20字符。 3、检索VARCHAR类型的字段数据时,会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。
(3)CHAR和VARACHAR的比较及其应用场景
1、情况1:存储很短的信息。比如门牌号码101,201……这样很短的信息应该用char,因为varchar还要占个byte用于存储信息长度,本来打算节约存储的,结果得不偿失。 2、情况2:固定长度的。比如使用uuid作为主键,那用char应该更合适。因为他固定长度, varchar动态根据长度的特性就消失了,而且还要占个长度信息。 3、情况3:十分频繁改变的column。因为varchar每次存储都要有额外的计算,得到长度等工作,如果一个非常频繁改变的,那就要有很多的精力用于计算,而这些对于char来说是不需要的。 4、情况4:具体存储引擎中的情况: (1)MyISAM 数据存储引擎和数据列:MyISAM数据表,最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化,从而使 数据检索更快 ,用空间换时间。 (2)MEMORY 存储引擎和数据列:MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用CHAR或VARCHAR列都没有关系,两者都是作为CHAR类型处理的。 (4)InnoDB 存储引擎,建议使用VARCHAR类型。因为对于InnoDB数据表,内部的行存储格式并没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),而且主要影响性能的因素是数据行使用的存储总量,由于char平均占用的空间多于varchar,所以除了简短并且固定长度的,其他考虑varchar。这样节省空间,对磁盘I/O和数据存储总量比较好。
(1)ENUM类型也叫作枚举类型,ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时,ENUM类型只允许从成员中选取单个值,不能一次选取多个值。 (2)其所需要的存储空间由定义ENUM类型时指定的成员个数决定。
1、当ENUM类型包含1~255个成员时,需要1个字节的存储空间。 2、当ENUM类型包含256~65535个成员时,需要2个字节的存储空间。 3、ENUM类型的成员个数的上限为65535个。
(1)SET表示一个字符串对象,可以包含0个或多个成员,但成员个数的上限为64。设置字段值时,可以取取值范围内的0个或多个值。 (2)当SET类型包含的成员个数不同时,其所占用的存储空间也是不同的,具体如下:
(3)SET类型在存储数据时成员个数越多,其占用的存储空间越大。注意:SET类型在选取成员时,可以一次选择多个成员,这一点与ENUM类型不同。
(1)MySQL中的二进制字符串类型主要存储一些二进制数据,比如可以存储图片、音频和视频等二进制数据。 (2)MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、 TINYBLOB、 BLOB、MEDIUMBLOB 和LONGBLOB类型。 BINARY与VARBINARY类型介绍 (1)BINARY和VARBINARY类似于CHAR和VARCHAR,只是它们存储的是二进制字符串。 (2)BINARY (M)为固定长度的二进制字符串,M表示最多能存储的字节数,取值范围是0~255个字符。如果未指定(M),表示只能存储 1个字节 。 例如BINARY (8),表示最多能存储8个字节,如果字段值不足(M)个字节,将在右边填充’\0’以补齐指定长度。 (3)VARBINARY (M)为可变长度的二进制字符串,M表示最多能存储的字节数,总字节数不能超过行的字节长度限制65535,另外还要考虑额外字节开销,VARBINARY类型的数据除了存储数据本身外,还需要1或2个字节来存储数据的字节数。VARBINARY类型 必须指定(M) ,否则报错。
(1)JSON(JavaScript Object Notation)是一种轻量级的 数据交换格式 。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。它易于人阅读和编写,同时也易于机器解析和生成,并有效地提升网络传输效率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串,然后就可以在网络或者程序之间轻 松地传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式。 (2)在MySQL 5.7中,就已经支持JSON数据类型。在MySQL 8.x版本中,JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构,使得在MySQL中存储和读取JSON类型的数据更加方便和高效。 创建数据表,表中包含一个JSON类型的字段 js 。
(1)MySQL 空间类型扩展支持地理特征的生成、存储和分析。这里的地理特征表示世界上具有位置的任何东西,可以是一个实体,例如一座山;可以是空间,例如一座办公楼;也可以是一个可定义的位置,例如一个十字路口等等。MySQL中使用 Geometry(几何) 来表示所有地理特征。Geometry指一个点或点的集合,代表世界上任何具有位置的事物。
(2)MySQL的空间数据类型(Spatial Data Type)对应于OpenGIS类,包括单值类型:GEOMETRY、POINT、 LINESTRING、POLYGON以及集合类型:MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION 。