目录
1.整数类型
序号 | 名称 | 所占字节数 | 有符号数取值范围 | 无符号数取值范围 |
---|---|---|---|---|
1 | TINYINT | 1 | ||
2 | SMALLINT | 2 | ||
3 | MEDIUMINT | 3 | ||
4 | INT / INTEGER | 4 | ||
5 | BIGINT | 8 |
1.1.整形数据的三个可选修饰符
- int(M),M表示显示宽度,M的取值范围是该数据类型的取值范围。
如果设置了显示宽度,那么插入的数据宽度超过显示宽度限制,会不会截断或插入失败?
答案:不会对插入的数据有任何影响,还是按照类型的实际宽度进行保存,即显示宽度与类型可以存储的值范围无关 。从MySQL 8.0.17开始,整数数据类型不推荐使用显示宽度属性。整型数据类型可以在定义表结构时指定所需要的显示宽度,如果不指定,则系统为每一种类型指定默认的宽度值。
- UNSIGNED,表示无符号类型。
- ZEROFILL,表示0填充,如果指定了ZEROFILL只是表示不够M位时,用0在左边填充,如果超过M位,只要不超过数据存储范围即可。
2.浮点数类型
2.1.浮点数类型可选修饰符
- FLOAT(M,D) 或 DOUBLE(M,D) 。这里,M称为精度 ,D称为标度 。(M,D)中 M=整数位+小数位,D=小数位。 D<=M<=255,0<=D<=30。
- UNSIGNED,浮点类型,也可以加 UNSIGNED ,但是不会改变数据范围,例如:FLOAT(3,2) UNSIGNED仍然只能表示0-9.99的范围。
3.定点数类型
3.1.DECIAML注意事项
- 使用 DECIMAL(M,D) 的方式表示高精度小数。其中,M被称为精度,D被称为标度。0<=M<=65,0<=D<=30,D<M。例如,定义DECIMAL(5,2)的类型,表示该列取值范围是-999.99~999.99。
- 定点数在MySQL内部是以 字符串 的形式进行存储,这就决定了它一定是精准的。
- 当DECIMAL类型不指定精度和标度时,其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时,则MySQL同样会进行四舍五入处理。
3.2.浮点数和定点数的比较
- 浮点数相对于定点数的优点是在长度一定的情况下,浮点类型取值范围大,但是不精准,适用于需要取值范围大,又可以容忍微小误差的科学计算场景(比如计算化学、分子建模、流体动力学等)。
- 定点数类型取值范围相对小,但是精准,没有误差,适合于对精度要求极高的场景 (比如涉及金额计算的场景)。
4.位类型
4.1.BIT类型注意事项
- BIT类型,如果没有指定(M),默认是1位。这个1位,表示只能存1位的二进制值。这里(M)是表示二进制的位数,位数最小值为1,最大值为64。
5.日期与时间类型
序号 | 名称 | 描述 | 字节 | 日期格式 | 最小值 | 最大值 |
---|---|---|---|---|---|---|
1 | YEAR | 年份 | 1 | YYYY或YY | 1901 | 2155 |
2 | TIME | 时间 | 3 | HH:MM:SS | -838:59:59 | 838:59:59 |
3 | DATE | 日期 | 3 | YYYY-MM-DD | 1000-01--1 | 9999-12-03 |
4 | DATETIME | 日期时间 | 8 | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
5 | TIMESTAMP | 日期时间 | 4 | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC | 2038-01-19 03:14:07 UTC |
5.1.TIMESTAMP类型注意事项
-
UTC表示世界标准时间
-
存储数据的时候需要对当前时间所在的时区进行转换,查询数据的时候再将时间转换回当前的时区。因此,使用TIMESTAMP存储的同一个时间值,在不同的时区查询时会显示不同的时间。
5.2.TIMESTAMP和DATETIME的区别
- TIMESTAMP存储空间比较小,表示的日期时间范围也比较小。
- 底层存储方式不同,TIMESTAMP底层存储的是毫秒值(时间戳),距离1970-1-1 0:0:0 0毫秒的毫秒值。
- 两个日期比较大小或日期计算时,TIMESTAMP更方便、更快。
- TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同,显示不同的结果。而DATETIME则只能反映出插入时当地的时区,其他时区的人查看数据必然会有误差的。
6.字符串类型
序号 | 名称 | 描述 | 长度 | 范围 | 所占字节 |
---|---|---|---|---|---|
1 | CHAR(M) | 定长字符串 | M | M | |
2 | VARCHAR(M) | 可变长字符串 | M | M + 1 |
6.1.CHAR类型
- CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M),则表示长度默认是1个字符。
- 如果保存时,数据的实际长度比CHAR类型声明的长度小,则会在 右侧填充 空格以达到指定的长度。当MySQL检索CHAR类型的数据时,CHAR类型的字段会去除尾部的空格。
- 定义CHAR类型字段时,声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
6.2.VARCHAR类型
- VARCHAR(M) 定义时, 必须指定 长度M,否则报错。
- 检索VARCHAR类型的字段数据时,会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。
6.3.CHAR和VARCHAR如何选择
情况1:存储很短的信息。比如门牌号码101,201……这样很短的信息应该用char,因为varchar还要占个byte用于存储信息长度,本来打算节约存储的,结果得不偿失。
情况2:固定长度的。比如使用uuid作为主键,那用char应该更合适。因为他固定长度,varchar动态根据长度的特性就消失了,而且还要占个长度信息。
情况3:十分频繁改变的column。因为varchar每次存储都要有额外的计算,得到长度等工作,如果一个非常频繁改变的,那就要有很多的精力用于计算,而这些对于char来说是不需要的。
情况4:具体存储引擎中的情况:
MyISAM 数据存储引擎和数据列:MyISAM数据表,最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化,从而使 数据检索更快 ,用空间换时间。
MEMORY 存储引擎和数据列:MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用CHAR或VARCHAR列都没有关系,两者都是作为CHAR类型处理的。
InnoDB 存储引擎,建议使用VARCHAR类型。因为对于InnoDB数据表,内部的行存储格式并没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),而且主要影响性能的因素是数据行使用的存储总量,由于char平均占用的空间多于varchar,所以除了简短并且固定长度的,其他考虑varchar。这样节省空间,对磁盘I/O和数据存储总量比较好。
7.文本类型
序号 | 名称 | 描述 | 长度 | 范围 | 所占字节 |
---|---|---|---|---|---|
1 | TINYTEXT | 小型文本 | L | L + 2 | |
2 | TEXT | 文本 | L | L + 2 | |
3 | MEDIUMTEXT | 中型文本 | L | L + 3 | |
4 | LONGTEXT | 长文本 | L | L + 4 |
7.1.文本类型注意事项
- 由于实际存储的长度不确定,MySQL不允许文本类型的字段作为主键
- TEXT文本类型,可以存比较大的文本段,搜索速度稍慢,因此如果不是特别大的内容,建议使用CHAR,VARCHAR来代替。还有TEXT类型不用加默认值,加了也没用。而且text和blob类型的数据删除后容易导致“空洞”,使得文件碎片比较多,所以频繁使用的表不建议包含TEXT类型字段,建议单独分出去,单独用一个表。
8.ENUM类型
序号 | 名称 | 描述 | 长度 | 范围 | 所占字节 |
---|---|---|---|---|---|
1 | ENUM | 枚举 | L | 1或2个字节 |
ENUM类型也叫作枚举类型,ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时,ENUM类型只允许从成员中选取单个值,不能一次选取多个值。
其所需要的存储空间由定义ENUM类型时指定的成员个数决定。当ENUM类型包含1~255个成员时,需要1个字节的存储空间;
当ENUM类型包含256~65535个成员时,需要2个字节的存储空间;
ENUM类型的成员个数的上限为65535个。
9.SET类型
序号 | 名称 | 描述 | 长度 | 范围 | 所占字节 |
---|---|---|---|---|---|
1 | SET | 集合 | L | 1、2、3、4或8个 |
SET表示一个字符串对象,可以包含0个或多个成员,但成员个数的上限为 64 。设置字段值时,可以取取值范围内的 0 个或多个值。当SET类型包含的成员个数不同时,其所占用的存储空间也是不同的,具体如下:
SET类型在存储数据时成员个数越多,其占用的存储空间越大。注意:SET类型在选取成员时,可以一次选择多个成员,这一点与ENUM类型不同。
10.二进制字符串类型
MySQL中的二进制字符串类型主要存储一些二进制数据,比如可以存储图片、音频和视频等二进制数据。
序号 | 名称 | 特点 | 长度 | 范围 | 所占字节 |
---|---|---|---|---|---|
1 | BINARY(M) | 固定长度 | M | M | |
2 | VARBINARY(M) | 可变长度 | M | M + 1 |
- BINARY与VARBINARY类型:BINARY和VARBINARY类似于CHAR和VARCHAR,只是它们存储的是二进制字符串。
- BINARY (M)为固定长度的二进制字符串,M表示最多能存储的字节数,取值范围是0~255个字符。如果未指定(M),表示只能存储 1个字节 。如果字段值不足(M)个字节,将在右边填充’\0’以补齐指定长度。
- VARBINARY (M)为可变长度的二进制字符串,M表示最多能存储的字节数,总字节数不能超过行的字节长度限制65535,另外还要考虑额外字节开销,VARBINARY类型的数据除了存储数据本身外,还需要1或2个字节来存储数据的字节数。VARBINARY类型 必须指定(M) ,否则报错。
11.JSON类型
JSON(JavaScript Object Notation)是一种轻量级的 数据交换格式 。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。它易于人阅读和编写,同时也易于机器解析和生成,并有效地提升网络传输效率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串,然后就可以在网络或者程序之间轻松地传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式。
向表中插入json数据
INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing","city":"beijing"}}');
12.空间类型
MySQL 空间类型扩展支持地理特征的生成、存储和分析。这里的地理特征表示世界上具有位置的任何东西,可以是一个实体,例如一座山;可以是空间,例如一座办公楼;也可以是一个可定义的位置,例如一个十字路口等等。MySQL中使用 Geometry(几何) 来表示所有地理特征。Geometry指一个点或点的集合,代表世界上任何具有位置的事物。