MySQL 数据类型

2024-11-18 18:16:38

数值类型

int类型

类型	说明
tinyint	1字节，范围从-128到127（有符号），0到255（无符号）
smallint	2字节，范围从-2^15到2^15-1（有符号），0到2^16-1（无符号）
mediumint	3字节，范围从-2^23到2^23-1（有符号），0到2^24-1（无符号）
int	4字节，范围从-2^31到2^31-1（有符号），0到2^32-1（无符号）
bigint	8字节，范围从-2^63到2^63-1（有符号），0到2^64-1（无符号）

例如我们创建表 t2，其内有类型为 tinyint 的 n1 字段和类型为 tinyint unsigned 的 n2 字段可以插入一系列各自范围内数据，如果插入的数据超过范围就会报错不让插入。

bit类型

类型	说明
bit(M)	位字段类型， M表示比特位的数目，范围从1 到 64 。如果 M 被忽略，默认为 1 。

例如，我们创建表 t3，其内有类型为 int 的 id 字段和类型为 bit(1) 的 gender 字段，其中bit(1)表示了gender字段只占用一个比特位，所以范围为0到1，当我们插入超过此范围的数字时就会报错，我们也可以看到在表中 bit 类型显示时以十六进制的形式显示。

我们可以更改gender为bit(10)，这样以后gender的范围变为0到2^10-1。

小数类型

类型	大小
float(m,d)	4字节，单精度浮点数。m指定浮点数的总长度，d指定小数位数。用于存储大约7位有效数字的浮点数。
double(m,d)	8字节，双精度浮点数。用于存储大约15位有效数字的浮点数。
decimal(m,d)	根据指定的精度和小数位数占用不同字节数。m最大为65，d最大为30。

例如，我们创建表 t4，其内有类型为 float(4,2) 的 n1 字段，类型为 float 的 n2 字段，以及类型为 decimal 的 n3 字段，其中float(4,2)表示n1字段数据总长度为4，小数位数为2，所以范围为-99.99到99.99，当我们插入超过此范围的数字时就会报错。

并且我们发现插入小数位数超过2位时会发现四舍五入。

如果向类型为 float 的 n2 字段插入数据时则不会强制保留小数位数

但是当插入的数据长度过长则会影响精度，float一般用于存储大约7位有效数字的浮点数，double用于存储大约15位有效数字的浮点数。

但是向类型为 decimal 的 n3 字段插入数据时则不会影响精度。

这是因为，decimal类型占用的空间不是固定的，其会根据精度的需要，调整占用的内存，确保可以精确保存这个精度的所有值。

字符串类型

类型	说明
char(L)	固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255。
varchar(L)	可变长度字符串，占用的字节数根据存储的数据长度而变化，最多65,535字节。

使用案例：

我们定义了表t5，其内有类型为 char(6) 的 s 字段，类型为 varchar(6) 的 vs 字段，在 c/c++ 中，中文汉字占两个或三个字节，但是我们定义的vs可以放下 ' 我爱你，中国 ' 这一段话，说明其参数表示的是字符的个数而非字节数。

那么关于varchar(L),L到底是多大，这个L值，和表的编码密切相关：

varchar 长度可以指定为 0 到 65535 之间的值，但是有 1 - 3 个字节用于记录数据大小，所以说有效字

节数是 65532 。当我们的表的编码是utf8 时， varchar(n) 的参数 n 最大值是 65532/3=21844[ 因为 utf 中，一个字符占用3 个字节 ] ，如果编码是 gbk ， varchar(n) 的参数 n 最大是 65532/2=32766 （因为 gbk 中，一个字符占用2 字节）。

char 和 varchar 比较

即char的长度是定义时就固定好了，而varchar是是会随着使用而增加，但不会超过定义时的长度。

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

类型	说明
text	text类型是用于存储大量文本数据的一种数据类型。

TEXT类型有四种规格：tinytext、text、mediumtext和longtext，它们的主要区别在于存储容量的不同。

TEXT类型的存储容量

tinytext：最大可以存储255字节的文本数据。
text：最大可以存储65,535字节（约64KB）的文本数据。
mediumtex：最大可以存储16,777,215字节（约16MB）的文本数据。
longtext：最大可以存储4,294,967,295字节（约4GB）的文本数据。

TEXT类型的特点

存储容量大：适合存储文章、评论等大量文本内容。
占用空间小：相比于varchar类型，TEXT类型在存储大量文本数据时占用的空间更小。
不支持索引：TEXT类型的列不能被索引，这可能会影响查询性能。如果需要对TEXT类型的列进行索引，可以使用全文搜索引擎，如Elasticsearch，来提高文本数据的搜索性能。

使用案例：

日期和时间类型

类型	用途	格式	存储需求
date	用于存储日期，不包含时间部分	YYYY-MM-DD，其中YYYY表示年份，MM表示月份，DD表示日期	3个字节
datetime	用于存储日期和时间，精确到秒。	YYYY-MM-DD HH:MM:SS	8个字节
timestamp	时间戳，用于存储日期和时间，与DATETIME类型类似，但支持时区转换	YYYY-MM-DD HH:MM:SS	4个字节

例如我们定义了表time，其内有类型为 date 的 time1 字段，类型为 datetime 的 time2 字段，类型为 timestamp 的 time3 字段，我们可以先向 time1 和 time2 按格式插入数据，实际上 time3 一般使用和 time2 相同，但我们通常在定义 timestamp 类型的列时添加 default current_timestamp on update current_timestamp 属性使其有自动更新的特性，在插入或更新数据时，如果没有指定该列的值，它会自动以当前时间(CURRENT_TIMESTAMP)填充/更新。

例如，我们修改 time3 列属性。

我们再次只向 time1 和 time2 插入数据，而 time3 会自动以当前时间(CURRENT_TIMESTAMP)填充/更新。

enum和set

enum ：枚举， “ 单选 ” 类型；

enum('选项1','选项2','选项3',...);

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“ 数字 ” ，因为这些选项的每个选项值依次对应如下数字： 1,2,3,.... 最多 65535 个；当我们添加枚举值时，也可以添加对应的数字编号。

set ：集合， “ 多选 ” 类型；

set('选项值1','选项值2','选项值3', ...);

该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“ 数字 ” ，因为这些选项的每个选项值依次对应如下数字： 1,2,4,8,16,32 ， .... 最多64 个，即每个值对应一个比特位。

例如我们定义了表votes，其内有类型为 char 的 name 字段，类型为 enum 的 gender 字段，类型为 set 的 hobby 字段，我们依次插入元素。

如果我们想查找所有喜欢跳舞的人，使用如下查询语句

不能查询出所有，爱好为跳舞的人。

集合查询使用 find_ in_ set 函数：

find_in_set(sub,str_list)

如果 sub 在 str_list 中，则返回下标；如果不在，返回0； str_list 用逗号分隔的字符串。

SELECT FIND_IN_SET('b', 'a,b,c,d'); -- 结果:2，因为 'b' 在strlist集合中位置为2，'a' 是位置1
SELECT FIND_IN_SET('1', '1'); -- 结果:1，特殊情况，只有一个字符串
SELECT FIND_IN_SET('2', '1,2'); -- 结果:2
SELECT FIND_IN_SET('6', '1'); -- 结果:0，strlist中不存在 '6'，所以返回0

接下来我们使用以下语句查询

码农公寓