MySQL数据类型

发布时间:2024年01月16日

在这里插入图片描述

要保持希望在每天清晨太阳升起。 --自己


数据类型分类

MySQL的数据类型大概可以分为这几类,分被为整形、浮点型、布尔类型、定点数类型、字符串类型、日期与时间类型、枚举类型和集合类型。这些数据类型用于存储不同类型的数据,例如整数、小数、字符串、日期、时间、布尔值等。选择合适的数据类型对于数据库性能和数据存储是非常重要的。

类型成员
整形TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
浮点型FLOAT、DOUBLE
定点数类型DECIMAL
字符串类型CHAR、VARCHAR、BINARY、VARBINARY、 BLOB(用于存储二进制数据)、TEXT(用于存储文本数据)
日期与时间类型DATE、TIME、DATETIME、TIMESTAMP、YEAR
布尔类型BOOL、BOOLEAN
枚举类型ENUM
集合类型SET
空间数据类型GEOMETRY、POINT、LINESTRING、POLYGON、etc.

整形

TINYINT

TINYINT 是 MySQL 中的一种整数数据类型,用于存储小范围的整数值。其中有符号 TINYINT 的范围是从 -128 到 127,无符号 TINYINT 的范围是从 0 到 255。TINYINT 存储空间为 1 字节,由于其小范围,常用于存储一些状态标志、开关、或者表示很小的整数值的字段。且在默认情况下,TINYINT 的默认值是 0。

示例:

  • 创建一个带有 TINYINT 类型的列的表:

    create table people (
        name varchar(255),
        age tinyint);
    

    在这里插入图片描述

  • 插入数据:

insert into people values('张三', 18);

在这里插入图片描述

在这里插入图片描述

  • 越界测试:
insert into people values('赵六', 129);
insert into people values('赵六', 128);
insert into people values('赵六', 127);

在这里插入图片描述

insert into people values('王二', -129);
insert into people values('王二', -128);

在这里插入图片描述

注意事项:

  • 当只需要存储较小范围的整数时,使用 TINYINT 可以节省存储空间。
  • 在设计数据库表结构时,根据实际需求选择合适的整数类型是很重要的,以兼顾存储效率和数据范围。
  • 在MySQL中,整型可以指定是有符号的和无符号的,默认是有符号的。
  • 可以通过UNSIGNED来说明某个字段是无符号的

总体而言,TINYINT 是一种轻量级的整数类型,适用于存储不需要太大范围的整数数据。

  • SMALLINT 用于存储较小范围的整数值。有符号 SMALLINT 的范围是从 -32768 到 32767,无符号 SMALLINT 的范围是从 0 到 65535。SMALLINT 存储空间为 2 字节,适用于需要存储中等范围的整数值的字段,默认情况下,SMALLINT 的默认值是 0。

  • MEDIUMINT 用于存储较大范围的整数值。有符号 MEDIUMINT 的范围是从 -8388608 到 8388607,无符号 MEDIUMINT 的范围是从 0 到 16777215。MEDIUMINT 存储空间为 3 字节,适用于需要存储相对较大范围整数值的字段。默认情况下,MEDIUMINT 的默认值是 0。

  • INT 用于存储较大范围的整数值。有符号 INT 的范围是从 -2147483648 到 2147483647,无符号 INT 的范围是从 0 到 4294967295。INT 存储空间为 4 字节,适用于需要存储较大范围整数值的字段。常用于自增主键、计数器等。默认情况下,INT 的默认值是 0。

  • BIGINT用于存储非常大范围的整数值。有符号 BIGINT 的范围是从 -9223372036854775808 到 9223372036854775807,无符号 BIGINT 的范围是从 0 到 18446744073709551615。BIGINT 存储空间为 8 字节,适用于需要存储非常大范围整数值的字段,例如处理大型数据集、大量计数等。默认情况下,BIGINT 的默认值是 0。

注意事项:

  • BIGINT 是用于存储非常大整数值的最大范围的整数类型。
  • 在设计数据库表结构时,要谨慎选择 BIGINT,因为它占用较大的存储空间,可能会影响查询性能。
  • 通常,只有当确实需要处理非常大整数值时才使用 BIGINT

浮点型

FLOAT

FLOAT 是 MySQL 中用于存储单精度浮点数的数据类型。FLOAT 使用 4 字节的存储空间。FLOAT 是单精度浮点数,提供大约 7 位有效数字的精度,范围为 -3.4028235E38 到 -1.17549435E-38、0,以及 1.17549435E-38 到 3.4028235E38,适用于需要较高精度但不需要太大范围的浮点数。常用于存储温度、价格等需要小数部分的数值。

示例:

  • 创建一个带有 FLOAT 类型的列的表:

    create table demo(
        id tinyint,
        price float);
    

在这里插入图片描述

  • 插入数据:

    insert into demo values(1, 12.90), (2, 27.89), (3, 67.111);
    

    在这里插入图片描述

除此之外,还可以指定显示的位数及小数点后几位,语法为:

float(a, b)  --其中a为显示的位数,b为小数位数

在这里插入图片描述

例如float(4,2)表示的范围是-99.99 ~ 99.99,而MySQL在保存值时会进行四舍五入。如果插入的数为23.346,在保存时就会四舍五入为23.35。如果定义的是float(4,2) unsigned 时,因为把它指定为无符号的数,范围是 0 ~ 99.99。

在这里插入图片描述

在结果中可以看到,当插入数据为23.345时,他保存的结果并没有进行四舍五入,但是如果插入的数据为23.3451时他就会进行进位。

在这里插入图片描述

由此可以看出,当插入的数据进位的第一位(b + 1位)为5且后面没有数据(或者为0)时,不会进行四舍五入。

注意事项:

  • FLOAT 是单精度浮点数,提供了相对较高的精度,但在处理非常大或非常小的数值时可能存在精度损失。
  • 在比较浮点数时,由于精度问题,最好使用范围或差值比较,而非直接相等比较。

总体而言,FLOAT 是一种适用于需要较高精度但不需要太大范围的浮点数的数据类型。在选择数据类型时,要考虑具体的应用场景和对精度的要求。

与FLOAT类似,在MySQL中,DOUBLE 是一种用于存储双精度浮点数的数据类型。它可以存储较大范围的数值,包括小数点后的精度。 DOUBLE 数据类型可以存储非常大的数值,约为1.8 × 10^308,同时也可以存储负数。DOUBLE 存储的是浮点数,因此它是近似值。在进行计算时可能存在舍入误差,如果未指定默认值,DOUBLE 类型的列默认为0。并且可以对 DOUBLE 类型的列执行基本的算术运算,如加法、减法、乘法和除法。

定点数类型

DECIMAL

在MySQL中,DECIMAL 是一种用于存储精确数值的数据类型,它用于表示固定精度和小数位数的十进制数。与 DOUBLE 类型不同,DECIMAL 类型不是浮点数,而是一种精确数值类型。DECIMAL 存储的是一个以字符串形式表示的十进制数,它以固定的精度和小数位数存储。在定义 DECIMAL 类型时,需要指定总的精度(总位数)和小数位数。例如,DECIMAL(10, 2) 表示总共10位数字,其中2位是小数位。

注意:

  1. 默认值: 如果未指定默认值,DECIMAL 类型的列默认为0。

  2. 小数点后的零值: DECIMAL 不会在存储时去除小数点后的零。例如,2.00 将被精确存储。

  3. 适用场景: DECIMAL 通常用于要求精确计算的场景,如货币、税率等。

语法:

DECIMAL(A, B) --定点数A指定长度,B表示小数点的位数

DECAML(5,2) 表示的范围是 -999.99 ~ 999.99,而DECIMAL(5,2) UNSIGNED 表示的范围为0 ~ 999.99。尽管DECIMAL和FLOAT很像,但是FLOAT和DECIMAL表示的精度不一样,FLOAT表示的精度大约是7位,DECIMAL整数最大位数为65,支持小数最大位数为30。如果B被省略,默认为0,如果A被省略,默认是10。

示例:

  • 创建数据库
create table demo5(
    -> id tinyint,
    -> price1 float(10, 8),
    -> price2 decimal(10,8));

在这里插入图片描述

  • 插入数据
insert into demo5 values(1, 10.123456789, 10.123456789);

在这里插入图片描述

从结果可以看出decimal的精度更高,如果希望小数的精度高,推荐优先使用decimal。

字符串类型

CHAR

在MySQL中,CHAR 是一种用于存储固定长度字符串的数据类型。因为CHAR 存储的字符串是固定长度的,这意味着无论实际字符串长度是多少,它都会被存储为指定的长度。如果实际字符串长度小于指定长度,则会用空格填充。在创建表时,需要指定 CHAR 类型的长度。例如,CHAR(10) 表示存储长度为10的字符串。由于是固定长度,CHAR 类型在存储和检索方面可能比可变长度的字符串类型(如 VARCHAR)更为高效。如果实际字符串长度小于指定长度,MySQL 会在字符串末尾填充空格。在比较字符串时,末尾的空格也会被考虑。CHAR适用于存储长度固定的数据,如固定长度的代码、标识符等。

之所以说CHAR 类型在存储和检索方面可能比 VARCHAR 类型更高效的原因主要与其固定长度的特性有关。

  1. 存储空间的效率: 由于 CHAR 类型是固定长度,MySQL 在存储数据时无需额外的长度前缀来存储实际字符串的长度信息。相比之下,VARCHAR 类型需要存储额外的长度信息,以便在检索时能够正确解释字符串的边界。

  2. 避免尾部空格的填充: 当使用 CHAR 类型时,MySQL 会在存储字符串时使用空格进行填充,以达到指定的固定长度。这意味着在检索时不需要去除尾部的填充空格,而 VARCHAR 类型则需要考虑实际字符串长度,可能需要进行额外的处理。

  3. 定长字段的索引效率: 对于某些查询和索引操作,固定长度的字段可能更容易进行优化。例如,当使用固定长度的 CHAR 字段作为索引时,MySQL 可能更容易进行预测和优化查询操作。

  4. 性能考虑: 由于 CHAR 类型的固定长度,数据库引擎可以更容易地预分配存储空间,而不需要动态调整空间大小。这在一些情况下可能带来性能优势。

需要注意的是,虽然 CHAR 类型在某些情况下可能更高效,但在其他情况下可能不如 VARCHAR。选择使用哪种类型取决于具体的应用场景和需求。如果字符串长度变化较大且可变,VARCHAR 类型可能更为合适,因为它可以动态调整存储空间,避免不必要的空间浪费。

语法:

char(n) --n表示存储的长度,最大为255

示例:

create table demo6(
    -> id tinyint,
    -> name char(255));

在这里插入图片描述

insert into demo6 values(1, '张三');
insert into demo6 values(2, 'Tom');

在这里插入图片描述

VARCHAR

在MySQL中,VARCHAR 是一种用于存储可变长度字符串的数据类型。VARCHAR 存储的字符串是可变长度的,它只使用实际字符串的存储空间,而不会填充额外的空格。这使得 VARCHAR 更为灵活,适用于存储不同长度的字符串。在创建表时,需要指定 VARCHAR 类型的最大长度。例如,VARCHAR(255) 表示最大长度为255的可变长度字符串。由于是可变长度,VARCHAR 类型在存储方面可以更为节省空间,尤其是对于较短的字符串。与 CHAR 不同,VARCHAR 不会在存储时填充额外的空格。它只存储实际的字符串内容。它适用于存储长度可变的数据,如变长文本、描述等。

语法:

varchar(n) --n表示字符长度,最大长度为65535个字节

varchar(len)的这个len值和表的编码密切相关,varchar长度可以指定为0到65535之间的值,但是有1 - 3 个字节用于记录数据大小,所以说有效字节数是65532。当我们的表的编码是utf8时,varchar(n)的参数n最大值是65532/3=21844(因为utf8中,一个字符占用3个字节),如果编码是gbk,varchar(n)的参数n最大是65532/2=32766(因为gbk中,一个字符占用2字节)。

在这里插入图片描述

示例:

create table demo8(
    -> id tinyint,
    -> name varchar(255));

在这里插入图片描述

insert into demo8 values(1, '张三'),(2, '李四');

在这里插入图片描述

TEXT

在MySQL中,TEXT 是一种用于存储大量文本数据的数据类型。它可以存储可变长度的字符串,适用于需要存储大段文本内容的场景。TEXT 类型用于存储可变长度的文本数据,包括较长的字符串、文档、文章等。与 VARCHAR 不同,TEXT 不需要指定最大长度。它可以存储非常大的文本数据,最多可达到 65,535 字节。TEXT 类型适合存储大块文本,但由于它是可变长度的,可能会在存储和检索时占用较多的存储空间。适用于存储较大的文本数据,如博客文章、评论、文档等。

示例:

create table demo9(
    -> id tinyint,
    -> comment text);

在这里插入图片描述

insert into demo9 values(1, '你干嘛哎呦');

在这里插入图片描述

BIT类型

BIT

在MySQL中,BIT 类型用于存储位字段,表示二进制位序列,通常用于表示布尔值或一组开关状态。BIT 类型用于存储固定长度的二进制位序列,其中每一位可以表示一个二进制位。你可以通过 BIT(len) 来指定位的数量,其中 len 表示位的数量。因此bit类型具有以下特点:

  1. 位的操作: BIT 类型支持对位进行操作,包括设置、清除、翻转等。这使得它适用于表示一组开关状态或标志位。

  2. 存储空间: BIT 类型的存储空间是固定的,不受存储的实际位数的影响。例如,BIT(1)BIT(8) 在存储空间上都占用一个字节。

  3. 适用场景: 适用于存储二进制数据,特别是当需要表示一系列布尔值或开关状态时。

语法:

bit(n) --n表示每个值的位数,范围从1到64。如果忽略,默认为1。

并且bit字段在显示时,是按照ASCII码对应的值显示 ,如下:

create table demo1(
    -> id int,
    -> flag bit(64));

在这里插入图片描述

insert into demo1 values(1, 1);
insert into demo1 values(1, 65);

在这里插入图片描述

日期与时间类型

DATE

在MySQL中,DATE 是一种用于存储日期值的数据类型。它用于表示年、月、日的日期信息。特性如下:

  1. 存储日期信息: DATE 类型用于存储年、月、日的日期信息,不包含具体的时分秒。

  2. 日期格式: DATE 类型的日期格式为 ‘YYYY-MM-DD’,其中 YYYY 表示四位数的年,MM 表示两位数的月,DD 表示两位数的日。

  3. 合法的日期范围: MySQL DATE 类型允许存储的日期范围为 ‘1000-01-01’ 到 ‘9999-12-31’。

  4. 日期操作: 可以对 DATE 类型的列执行各种日期操作,包括日期的比较、加减、格式化等。

示例:

create table demo1(
    -> id int,
    -> birth date);

在这里插入图片描述

insert into demo1 values(1, '1955-5-15');

在这里插入图片描述

DATETIME

在MySQL中,DATETIME 是一种用于存储日期和时间信息的数据类型。它包含年、月、日、时、分、秒等组成部分,可以表示精确到秒的日期时间。特性如下:

  1. 存储日期时间信息: DATETIME 类型用于存储包括年、月、日、时、分、秒的日期时间信息。

  2. 日期时间格式: DATETIME 类型的日期时间格式为 ‘YYYY-MM-DD HH:MM:SS’,其中 YYYY 表示四位数的年,MM 表示两位数的月,DD 表示两位数的日,HH 表示小时(24小时制),MM 表示分钟,SS 表示秒。

  3. 合法的日期时间范围: MySQL DATETIME 类型允许存储的日期时间范围为 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。

  4. 日期时间操作: 可以对 DATETIME 类型的列执行各种日期时间操作,包括日期时间的比较、加减、格式化等。

示例:

create table demo1(
    -> id int,
    -> birth datetime);

在这里插入图片描述

insert into demo1 values(1, '1988-8-18 18:08:18');

在这里插入图片描述

TIMESTAMP

在MySQL中,TIMESTAMP 是一种用于存储日期和时间信息的数据类型,类似于 DATETIME,但有一些区别。特性如下:

  1. 存储日期时间信息: TIMESTAMP 类型用于存储包括年、月、日、时、分、秒的日期时间信息,类似于 DATETIME

  2. 日期时间格式: TIMESTAMP 类型的日期时间格式为 ‘YYYY-MM-DD HH:MM:SS’,其中 YYYY 表示四位数的年,MM 表示两位数的月,DD 表示两位数的日,HH 表示小时(24小时制),MM 表示分钟,SS 表示秒。

  3. 合法的日期时间范围: MySQL TIMESTAMP 类型允许存储的日期时间范围为 ‘1970-01-01 00:00:01’ 到 ‘2038-01-19 03:14:07’。这是由于 TIMESTAMP 使用的是32位整数,表示秒数,因此有一个最大的可表示时间。

  4. 自动更新功能: TIMESTAMP 类型有一个特殊的自动更新功能。当插入新行时,如果 TIMESTAMP 列没有指定值,它将默认使用当前时间。

  5. 时区影响: TIMESTAMP 类型存储的时间戳是基于时区的,而 DATETIME 类型是无时区的。这意味着在使用 TIMESTAMP 时,可能需要考虑时区的影响。

示例:

create table demo1(
    -> id int,
    -> birth timestamp);

在这里插入图片描述

insert into demo1(id) values(1);
insert into demo1 values(2, '1988-8-18 18:8:28');

在这里插入图片描述

枚举类型

ENUM

在MySQL中,ENUM 是一种用于定义枚举类型的数据类型。枚举类型允许在列中存储一个固定集合的可能值。特性如下:

  1. 定义枚举类型: ENUM 类型允许在列中定义一个固定的、有限的可能值集合。

  2. 单一值选择: ENUM 列只能存储预定义的可能值之一。例如,如果 statusENUM('Active', 'Inactive', 'Pending'),则 status 列的值只能是这三个值中的一个。

  3. 存储效率: ENUM 类型在存储时使用整数来表示可能值的索引,这可以在存储和检索时提供一些性能优势。

  4. 默认值: 如果未指定默认值,ENUM 列的默认值将是第一个可能值。

示例:

create table demo1(
    -> name varchar(255),
    -> hobby enum('唱','跳','rap','篮球'));

在这里插入图片描述

insert into demo1 values('张三', '唱');
insert into demo1 values('李四', '鸡');

在这里插入图片描述

因为这些选项的每个选项值依次对应如下数字:1,2,3等等,最多65535个;当我们添加枚举值时,也可以添加对应的数字编号。

insert into demo1 values('李四', 2);

在这里插入图片描述

集合类型

SET

在MySQL中,SET 是一种用于定义集合(set)类型的数据类型。集合类型允许在列中存储多个可能值,这些值是预定义的。特性如下:

  1. 定义集合类型: SET 类型允许在列中定义一个预定义的集合,列可以包含多个可能值。

  2. 多值选择: SET 列可以存储预定义集合中的多个可能值。例如,如果 permissionsSET('Read', 'Write', 'Execute'),则 permissions 列的值可以是 ‘Read’、‘Write’、‘Read,Write’、‘Execute’、‘Read,Execute’ 等。

  3. 存储效率: SET 类型在存储时使用整数来表示可能值的位掩码,这可以在存储和检索时提供一些性能优势。

  4. 默认值: 如果未指定默认值,SET 列的默认值为空集。

示例:

create table demo1(
    -> name varchar(255),
    -> hobby set('唱','跳','rap','篮球'));

在这里插入图片描述

insert into demo1 values('张三', '唱,跳,rap');

在这里插入图片描述

因为这些选项的每个选项值依次对应如下数字:1,2,4,8,16,32以此类推,最多64个,所以也可以通过数字进行添加。但通常不建议在添加枚举值或者集合值的时候采用数字的方式,因为不利于阅读。

insert into demo1 values('李四', 3);
insert into demo1 values('王五', 8);

在这里插入图片描述

集合查询

find_ in_ set

在MySQL中,FIND_IN_SET 函数用于在逗号分隔的值列表中查找指定字符串的位置。它返回指定字符串在列表中第一次出现的位置,如果字符串未找到则返回0。FIND_IN_SET 的语法如下:

FIND_IN_SET(查找的字符串, 值列表)
  • 查找的字符串:要在列表中查找的字符串。
  • 值列表:逗号分隔的值列表。

如下图,直接查找是查找不出所有的打篮球的人。

在这里插入图片描述

而使用集合查询结果如下:

在这里插入图片描述

总结

文章介绍了MySQL中几种常用的数据类型,对这些常见的数据类型进行介绍分析,也标注了一些类型的特性,并结合示例演示,让读者理解起来更加容易,如果觉得文章对你有帮助的话就帮作者点一个👍呗!感谢支持。

在这里插入图片描述

文章来源:https://blog.csdn.net/wzh18907434168/article/details/135629488
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。