【MySQL】数据类型

发布时间:2023年12月17日


数据类型分类

在这里插入图片描述

  • 数值类型:用于存储数值,包括整型和浮点型,如 int、double、float等。
  • 字符串类型:用于存储字符串,包括固定长度的char和可变长度的varchar,以及blob和text类型等。
  • 日期和时间类型:用于存储日期和时间,包括date、time、datetime、timestamp等。
  • 枚举类型:用于存储枚举值,例如 enum型。
  • 集合类型:用于存储一组值,例如set类型。
  • 注意:枚举类型和集合类型都属于字符串,因此它们被归为了String类型。

在MySQL中,不同的数据类型有不同的存储需求和和存储限制,选择合适的数据类型可以提高数据存储和查询的效率,并保证数据的准确性和完善性。


数值类型

在这里插入图片描述

💕 tinyint类型

create table t1(num tinyint);

在这里插入图片描述

数值越界测试:

mysql> insert into t1 values (127);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (128);
1264 - Out of range value for column 'num' at row 1
mysql> insert into t1 values (-128);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (100);
Query OK, 1 row affected (0.00 sec)

在这里插入图片描述

这里我们可以看到,如果插入的数据超过数值类型所能表示的范围,MySQL将不允许该数据插入。因为无法得知该数据是截断后的数据还是未经截断的数据。这里体现出了MySQL保证了数据库中数据的可靠性。

同时,超过数据类型范围的数据无法插入到表中也是一种 约束。约束是指在创建表时规定的对表中的数据进行限制的条件。它可以对表中某列或某几列添加一些限制条件,以保证表中的数据符合要求。

注意:

  • 在MySQL中,整型可以指定是有符号的和无符号的,默认是有符号的。
  • 可以通过 unsigned 来说明某个字段是无符号的。
  • 尽量不适用unsigned,对于int类型可能存放不下的数据,int unsigned 同样可能存放不下,与其如此,还不如设计时将int类型提升为 bigint类型。
  • 如果索要存储的数据不会出现负数,可以使用unsigned。

unsigned tinyint 测试

create table t2 (num tinyint unsigned);

在这里插入图片描述


💕 bit类型

bit[(M)]:位字段类型。M表示每个值的位数,范围从1到64。如果M被忽略,默认为1。

create table t3(id int, ch bit(8));

在这里插入图片描述

这里我们发现插入10后,没有显现出来。其实这是因为bit字段在显示时,是按照ASCII码对应的字符显示,而有一些ASCII码对应的字符是不可显的。

在这里插入图片描述

这里我们可以看到,超过bit的数值范围时是无法插入的。同时我们也可以看到插入的97和65分别以ASCII码对应的字符的形式显示出来了。

如果我们有这样的值,只存放0或1,这时可以定义bit(1),因为这样可以节省空间。例如:

create table t4(name varchar(32), gender bit(1));

在这里插入图片描述


小数类型

💕 小数类型

float [(m, d)] [unsigned] : M指定显示长度,d指定小数位数,占用空间4个字节。

注意: float(4,2) 表示的范围是-99.99~99.99,MySQL在保存值时会进行四舍五入。

create table t5(id int, salary float(4,2));

这里的 4 表示数字的总个数,2 表示小数点后数字的个数。

在这里插入图片描述

如果我们定义的是 float(4,2) unsigned,因为把它指定为无符号数字时, 取值范围会变成0~99.99。它的取值范围相当于少了负数那一部分。

在这里插入图片描述

下面我们来验证一下:

在这里插入图片描述


💕 decimal

decimal(m, d) [unsigned]:定点数m指定长度,d表示小数点的位数。

  • decimal(5,2) 表示的范围是 -999.99 ~ 999.99。
  • decimal(5,2) unsigned 表示的范围 0 ~ 999.99。
  • decimal 和 float 很像,但是有区别:float 和 decimal 表示的精度不一样。
create table t6(id int, salary float(10,8), salary2 decimal(10,8));

在这里插入图片描述

注意:

  • decimal的精度更准确,因此如果我们希望某个数据表示高精度,可以选择decimal。
  • float表示的精度最大约是7位。
  • decimal整数最大位数m为65,支持小数最大位数d是30,如果d被省略,默认为0。如果m被省略,默认是10。

字符串类型

💕 char

char(L):固定长度字符串,L是可以存储的长度,单位为字符,最大长度值可以是255。

create table t7(id int, name char(2));

在这里插入图片描述

这里我们可以发现,这里 char(2) 中的 2 表示的其实是字符的最大数量,并不是字节数,而MySQL中的字符指定就是单纯的字符,字符可以是一个字母,也可以是一个汉字。而在C语言中,用的是两个字节来表示一个汉字字符。

这里我们需要注意的是:char 最多只能是255

在这里插入图片描述


💕 varchar

varchar(L):可变长度字符串,L表示字符长度,最大长度为65535个字节。

create table t8(id int, name varchar(6));

在这里插入图片描述

这里我们可以看到,varchar和char类型一样,超过长度上限就会插入失败。

注意:

  • 变长字符串是指长度可变的字符串,即字符串长度不固定,可以根据实际需要存储不同长度的字符数据。varchar 类型的字段会根据存储的实际数据长度来占用相应的存储空间,因此它比 char 类型更加灵活,能够在存储短字符串时占用更少的存储空间。
  • varchar(len) 所能够容纳的最大字符数取决于所使用的字符集和校对规则,也就是 len 的最大值跟编码形式有关。
  • varchar 长度可以指定为 0 到 65535 之间的值,但是有 1 - 3 个字节用于记录数据大小,所以说有效字节数是 65532。
  • 当我们的表的编码是 utf8 时,varchar(n) 的参数 n 最大值是 65532 / 3 = 21844(因为 utf 中,一个字符占用 3 个字节)。如果编码是 gbk,varchar(n) 的参数 n 最大是 65532 / 2 = 32766 (因为 gbk 中,一个字符占用 2 字节)。

在这里插入图片描述

charvarchar比较:

在这里插入图片描述

如何选择定长或变长字符串?

  • 如果数据确定长度都一样,就使用定长(char),比如:身份证,手机号,md5。\n如果数据长度有变化,就使用变长(varchar),比如:名字,地址,但是你要保证最长的能存的进去。
  • 定长的磁盘空间比较浪费,但是效率高。
  • 变长的磁盘空间比较节省,但是效率低。
  • 定长的意义是:直接开辟好对应的空间。
  • 变长的意义是:在不超过自定义范围的情况下,用多少开辟多少。

日期类型和时间类型

常用的日期有如下三个:

  • date:用于存储日期(年、月、日)。其格式为 ‘YYYY-MM-DD’,其中 YYYY 表示四位年份,MM 表示月份(01-12),DD 表示日期(01-31)。date 数据类型占用 3 个字节。
  • datetime:用于存储日期和时间(年、月、日、时、分、秒)。其格式为 ‘YYYY-MM-DD HH:MM:SS’,其中 YYYY 表示四位年份,MM 表示月份(01-12),DD 表示日期(01-31),HH 表示小时(00-23),MM 表示分钟(00-59),SS 表示秒(00-59)。datetime 数据类型占用 8 个字节。
  • timestamp:用于存储日期和时间(年、月、日、时、分、秒),且其值自动随系统时间的更改而更新。其格式与 datetime 相同,但是其取值范围比 datetime 更小。timestamp 数据类型占用 4 个字节。
  • 时间戳(timestamp) 是一种特殊的数据类型,用于表示某个时间点。在 MySQL 中,时间戳通常指 Unix 时间戳,它表示自 1970 年 1 月 1 日 0 时 0 分 0 秒(UTC 时区)起到某个时间点的秒数。
  • 在 MySQL 中,timestamp 类型可以自动更新为当前时间,比如在插入新记录时,可以将 timestamp 列设置为默认值 CURRENT_TIMESTAMP,这样每当插入一条新记录时,该列的值都会自动更新为当前时间。
create table t10(t1 date, t2 datetime, t3 timestamp);

在这里插入图片描述

注意: 插入和更新数据时,时间戳会自动地获取当前时间点的时间戳并更新。


enum 和 set

  • enum 是一种多选一的数据类型,它可以存储一个固定的选项列表中的一个值,支持最多 65535 个元素,每个元素的长度不超过 65535 个字节。在创建表时,可以使用 enum(column_name,‘option1’,‘option2’,…) 指定列名和选项列表,其中列名是必须的,选项列表中至少要有一个选项。出于效率考虑,这些值实际存储的是数字。因为这些选项的每个选项值依次对应如下数字:1,2,3,… 最多 65535 个;当我们添加枚举值时,也可以添加对应的数字编号。
  • set 是一种多选多的数据类型,它可以存储一个固定的选项列表中的多个值,支持最多 64 个元素,每个元素的长度不超过 64 个字节。在创建表时,可以使用 set(column_name,‘option1’,‘option2’,…) 指定列名和选项列表,其中列名是必须的,选项列表中至少要有一个选项。出于效率考虑,这些值实际存储的是数字,因为这些选项的每个选项值依次对应如下数字:1,2,4,8,16,32,… 最多 64 个。
  • 不建议在添加枚举值,集合值的时候采用数字的方式,因为不利于阅读。\n示例:\n有一个调查表 votes,需要调查人的喜好, 比如在集合(唱,跳,rap,打篮球)中去选择(可以多选),同时填表人还需要填写自己的性别男或女(单选)。

下面我们举一个例子:

创建一个调查表votes,需要调查人的喜好,比如在集合(唱、跳、rap、打篮球)中去选择(可以多选),同时填表人还需要填写自己的性别男或女(单选)。

create table Votes(name varchar(32), hobby set('唱', '跳','rap','打篮球'), gender enum('男','女'));

在这里插入图片描述

下面我们往表中插入一些数据观察一下:

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

查找数据:

# 针对类型不是集合的查找
select * from table_name where column_name=xxx;
# 针对类型是集合的查找
select * from table_name where find_in_set(xxx, column_name);

集合查询使用 find_in_set 函数

find_in_set(sub, str_list) :如果 sub 在 str_list 中,则返回下标;如果不在,返回 0;str_list 用逗号分隔的字符串。

在这里插入图片描述


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