自学内容网 自学内容网
自学内容网 > 正文

MySQL之数据类型

🕗 发布于 2024-10-16 01:19 mysql  数据库  

学习每一门语言前，我们都会接触每个语言中的数据类型，在SQL语言中也存在许许多多的数据类型，我们今天来一探究竟。

1. 常⽤数据类型分类

我们学习Java语言在⾯向对象软件开发的过程中，通常会先进⾏需求分析从⽽得到类和属性，类是⾯向对象中的概念，对应到数据库中的概念就是实体，类中的属性对应实体中的属性。实体通常以表的形式存在，每个实体对应⼀张表，表中的每条记录(数据⾏)就是实体的⼀个实例，每条记录⼜包含若⼲字段(或称为列)，每个字段代表实体的⼀个属性。

如果要定义实体的属性，就要为属性命名并指定合适的数据类型。与其他编程语⾔类似，SQL中规定了⽤于描述属性的数据类型。常⽤的数据类型有以下⼏类：

  • 数据值类型
  • 字符串类型
  • ⼆进制类型
  • ⽇期类型

2. 数据值类型

2.1 类型列表

类型大小说明
BIT[(M)]默认bit位置类型，M表示每个值的位数，取值范围1~64，如果省略M默认为1
TINYINT[(M)]（tiny int）1byte取值范围是-2^7 - 2 ^ 7-1,无符号取值范围2^8-1
BOOL（bool）1byteTINYINT(1)的同义词。值为零被认为是假，⾮零值被认为是true
SMALLINT[(M)] (small int)2byte取值范围 -2^15 ~ 2^15-1 ，⽆符号取值范围 2^16-1 。
MEDIUMINT[(M)] (medium int)3byte取值范围 -2^23 ~ 2^23-1 ，⽆符号取值范围 2^24-1
INT[(M)]4byte取值范围 -2^31 ~ 2^31-1 ，⽆符号取值范围 2^32-1
INTEGER[(M)] (integer)4byteINT[(M)]的同义词
BIGINT[(M)]4byte取值范围 -2^63 ~ 2^63-1 ，⽆符号取值范围 2^64-1
FLOAT[(M,D)]4byte单精度浮点型，M是总位数，D是⼩数点后⾯的位数，⼤约可以精确到⼩数点后7位
DOUBLE[(M,D)]8byte双精度浮点型，M是总位数，D是⼩数点后⾯的位数，⼤约可以精确到⼩数点后15位。
DECIMAL[(M[,D])]（decimal）动态不存在精度损失，M是总位数，D是⼩数点后的位数。DECIMAL的最⼤位数(M)为65，最⼤⼩数位数(D)为30。如果省略M，则默认为10，如果省略D，则默认为0。M中不计算⼩数点和负数的-号，如果D为0，则值没有⼩数点和⼩数部分。

3. 字符串类型/二进制类型

3.1 类型列表

类型说明
CHAR[(M)]固定⻓度字符串， M 表⽰字符的⻓度，以字符为单位，取值范围 0 ~ 255个字符，占用的字节=字符数*字符集表示字符所占用的单个字符的字节，例如utf8mb4单个字符所占字节长度为1~4个字节，那么255个字符占用的总字节树就是255 * 4 ， M 省略则⻓度为 1，若给定了M的值为255，即使只存放一个数据，后面的254个字符用0补齐
VARCHAR(M)（varchar）可变⻓度字符串， M 表⽰字符最⼤⻓度，所占的字节范围 0 ~ 65535个字节 ，若使用字符集utf8mb4那么能存放的字符个数为65535/4=16386，所以有效字符个数取决于实际字符数和使⽤的字符集
TINYTEXT（tiny text）⼩⽂本类型，最⼤⻓度为 255 (2^8 - 1)个字节，有效字符个数取决于使⽤的字符集
TEXT[(M)]⽂本类型，最⼤⻓度为 65535 (2^16 - 1)个字节，有效字符个数取决于使⽤的字符集
MEDIUMTEXT中⽂本类型，最⼤⻓度为 16,777,215 (2^24 - 1)个字节，有效字符个数取决于使⽤的字符集
LONGTEXT⼤⽂本类型，最⼤⻓度为 4,294,967,295 即 4GB (2^32 - 1)个字节，有效字符个数取决于使⽤的字符集
BINARY[(M)] (binary)固定⻓度⼆进制字节，于CHAR类似，但存储的是⼆进制字节⽽不是字符串。 M 表⽰⻓度，以字节为单位，取值范围 0 ~ 255 ， M 省略则⻓度为1
VARBINARY（M）（varbinary）可变⻓度⼆进制字节，于VARCHAR类似，但存储的是⼆进制字节⽽不是字符串。M 表⽰⻓度，以字节为单位
TINYBLOB⼩⼆进制字节类型，最⼤⻓度为 255 (2^8 - 1)个字节
BLOB[(M)] （blob）⼆进制字节类型，最⼤⻓度为 65535 (2^16 - 1)个字节
MEDIUMBLOB中⼆进制字节类型，最⼤⻓度为 16,777,215 (2^24 - 1)个字节
LONGBLOB⼤⼆进制字节类型，最⼤⻓度为 4,294,967,295 即 4GB (2^8 - 1)个字节
ENUM(‘value1’,‘value2’,…)枚举， 从值列表 ‘value1’,‘value2’ 或 ‘’(空字符串) 和 NULL 中选⼀个值，最多可以有 65,535 个不同的元素， 单个元素的最⼤⻓度是 M <= 255 或 (M x w) <= 1020 ，其中 M 是元素字符⻓度， w 是字符集中字符所需的最⼤字节数 ， NUM的值在内部表⽰为整数
SET(‘value1’,‘value2’,…)集合• 从值列表 ‘value1’,‘value2’ 中选零个或多个值• 最多64个元素• 单个元素的最⼤⻓度是 M <= 255 或 (M x w) <= 1020 ，其中 M 是元素字符⻓度， w 是字符集中字符所需的最⼤字节数• SET值在内部表⽰为整数

3.2 关于排序

  • 字符串类型的列以字符为单位，并且可以单独指定字符集和排序规则，⽐如字符集是utf8mb4排序规则是utf8mb4_0900_ai_ci
  • ⼆进制的列以字节为单位，可以指定_bin结尾的排序规则，⽐如排序规则是utf8mb4_bin ，这时以⽐较和排序基于数字字符代码值

3.3 CHAR与VARCHAR的区别

  • CHAR固定⻓度的字符串，M表⽰以字符为单位的列⻓度，取值范围0 ~ 255 ，省略则⻓度为1，在存储时总是⽤空格向右填充到指定的⻓度，获取列的值时会从尾部删除空格。允许定义CHAR(0)，此时列的值只能为NULL或空字符串，主要的⽬的是为了旧系兼容，⽐如类中必须有这个属性，但不使⽤这个属性的值，也就是说值并没有意义，但列⼜不能没有。
  • VARCHAR 可变⻓度字符串。M表⽰以字符为单位的最⼤列⻓度，取值范围0 ~ 65,535 (在所有列中共享)，有效⻓度取决于实际字符数和使⽤的字符集，并且⽤额外的⼀或两个字节记录实际使⽤的字节数，当实际字节数不超过255个字节⽤⼀个字节记录⻓度，超过255个字节时，使⽤两个字节记录⻓度，获取列的值时不会从尾部删除空格，插⼊数据时会删除超出⻓度的空格。

3.4 如何选择CHAR与VARCHAR

  • 如果数据确定⻓度都⼀样，就使⽤定⻓CHAR 类型，⽐如：⾝份证，md5，学号，邮编
  • 如果数据⻓度有变化,就使⽤变⻓VARCHAR ,⽐如：名字，地址，但要规划好⻓度，保证最⻓的字符串能存的进去。
  • 定⻓CHAR类型⽐较浪费磁盘空间，但是效率⾼。
  • 变⻓VARCHAR 类型⽐较节省磁盘空间，但是效率低。
  • 定⻓CHAR类型会直接开辟好对应的存储空间。
  • 变⻓VARCHAR 类型在不超过定义⻓度范围的情况下⽤多少开辟多少存储空间。

3.5 VARCHAR与TEXT的区别

  • 容量⼤⼩：VARCHAR 最⼤⽀持65535 个字节；TEXT 最⼤⽀持65535 个字节，在指定TEXT时，当超过65535时 自动转换为MEDIUMTEXT 类型，当超过16,777,215 时⾃动转换为LONGTEXT 类型

  • 存储位置：VARCHAR 类型的列实际内容⼩于768个字节时存在当前⾏，⼤于768时存在溢出⻚，当前⾏保存溢出⻚的地址；TEXT类型的列整体保存在溢出⻚，当前⾏只保存溢出⻚地址

  • 查询性能：对于频繁查询的VARCHAR 列可以创建索引，提升查询性能；TEXT类型的列⽆法直接创建普通索引,但可以使⽤列的性能⾼于FULLTEXT 索引，由于索引的⽀持和存储位置的不同，VARCHAR列的性能⾼于TEXT类型的列

  • 适⽤场景：如果存储的数据⻓度较⼩且需要创建索引进⾏检索，可以选择VARCHAR类型，⽐如姓名，⽤⼾，邮箱等；如果存储的数据⻓度较⼤且不需要频繁以该列为条件进⾏检索可选择TEXT类型，⽐如⽂章内容等。

4. 日期类型

4.1 类型列表

类型大小说明0值
TIMESTAMP[(fsp)] (timestamp)4bytes• 时间戳类型 • ⽀持范围 1970-01-01 00:00:01.000000 ~ 20388-01-19 03:14:07.499999 • 0值为 0000-00-00 00:00:000000-00-00 00:00:00
DATETIME[(fsp)]8bytes⽇期类型和时间类型的组合 • ⽀持范围 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.499999 • 显⽰格式为 YYYY-MM-DD hh:mm:ss[.fraction] • 0值为 0000-00-00 00:00:000000-00-00 00:00:00
DATE3bytes⽇期类型⽀持范围1000-01-01 ~ 9999-12-31显⽰格式为YYYY-MM-DD0值为0000-00-00
TIME[(fsp)]3byte时间类型⽀持范围-838:59:59.000000 ~ 838:59:59.000000显⽰格式为hh:mm:ss[.fraction]0值为00:00:0000:00:00
YEAR[(4)]1byte4位格式的年份⽀持范围1901 ~ 2155显⽰格式为YYYY 0值为00

4.2 解释

  • fsp为可选设置，⽤来指定⼩数秒精度，范围从0到6，值为0表⽰没有⼩数部分，如果省略，默认精度为0
  • CURRENT_DATE和CURRENT_DATE()是CURDATE()的同义词⽤于获取当前⽇期
  • CURRENT_TIME和CURRENT_TIME([fsp])是CURTIME()的同义词⽤于获取当前时间
  • CURRENT_TIMESTAMP和CURRENT_TIMESTAMP([fsp])是NOW()的同义词⽤于获取当前⽇期和时间

原文地址：https://blog.csdn.net/2302_81707171/article/details/142921211

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024