数据库-＞数据类型

🕗 发布于 2024-10-18 01:28 数据库

一、定义

安装完MYSQL之后就有了MYSQL服务 --> 管理着很多个数据库 --> 管理着很多个表 --> 管理着很多数据行 --> 数据行是由多个列组成的 --> 描述列用到了数据类型

定义列时，需要为每个列指定数据类型

二、常用数据类类型

1.数值类型

1.1类型列表

类型	大小	说明
BIT[(M)] （只能存0或1）	默认bit	位值类型。M表⽰每个值的位数，取值范围为 1 ~ 64 。如果省略M，则默认为1
TINYINT[(M)]	1byte	取值范围 -2^7 ~ 2^7-1 ，⽆符号取值范围 2^8-1
BOOL	1byte	TINYINT(1)的同义词。值为零被认为是假，⾮零值被认为是true
SMALLINT[(M)]	2byte	取值范围 -2^15 ~ 2^15-1 ，⽆符号取值范围 2^16-1
MEDIUMINT[(M)]	3byte	取值范围 -2^23 ~ 2^23-1 ，⽆符号取值范围 2^24-1
INT[(M)]	4byte	取值范围 -2^31 ~ 2^31-1 ，⽆符号取值范围 2^32-1
INTEGER[(M)]	4byte	INT[(M)]的同义词
BIGINT[(M)]	8byte	取值范围 -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])]	动态	不存在精度损失，M是总位数，D是⼩数点后的位数。DECIMAL的最⼤位数(M)为 65，最⼤⼩数位数(D)为30。如果省略M，则默认为10，如果省略D，则默认为 0。M中不计算⼩数点和负数的-号，如果D为0，则值没有⼩数点和⼩数部分

由于floay和double的精度问题一般在开发中不使用这两个数据类型

1.2 数据类型的取值范围

类型	⼤⼩	有符号最⼩值	有符号最⼤值	⽆符号最⼩值	⽆符号最⼤值
TINYINT	1Byte	-128	127	0	255
SMALLINT	2Byte	-32768	32767	0	65535
MEDIUMINT	3Byte	-8388608	8388607	0	16777215
INT	4Byte	-2147483648	2147483647	0	4294967295
BIGINT	8Byte	-2^63	2^63-1	0	2^64-1

2.字符串类型

2.1类型列表

类型	说明
CHAR[(M)]	固定⻓度字符串， M 表⽰⻓度，以字符为单位，取值范围 0 ~ 255 ， M 省略则⻓度为 1
VARCHAR(M)	可变⻓度字符串， M 表⽰字符最⼤⻓度，的范围 0 ~ 65535 ，有效字符个数取决于实际字符数和使⽤的字符集
TINYTEXT	⼩⽂本类型，最⼤⻓度为 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)]	固定⻓度⼆进制字节，于CHAR类似，但存储的是⼆进制字节⽽不是字符串。 M 表⽰⻓度，以字节为单位，取值范围 0 ~ 255 ， M 省略则⻓度为 1
VARBINARY(M)	可变⻓度⼆进制字节，于VARCHAR类似，但存储的是⼆进制字节⽽不是字符串。 M 表⽰⻓度，以字节为单位
TINYBLOB	⼩⼆进制字节类型，最⼤⻓度为 255 (2^8-1)个字节
BLOB[(M)]	⼆进制字节类型，最⼤⻓度为 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 是字符集中字符所需的最⼤字节数 • ENUM的值在内部表⽰为整数
SET('value1','value2',...）	• 集合 • 从值列表 'value1','value2' 中选零个或多个值 • 最多64个元素 • 单个元素的最⼤⻓度是 M <= 255 或 (M x w) <= 1020 ，其中 M 是元素字符⻓度， w 是字符集中字符所需的最⼤字节数 • SET值在内部表⽰为整数

M指的是字符的个数不是字节数

utf8mb4：1-4个字节表示一个字符

使用utf8mb4可以保存最多的字符数是16383个

2.2关于排序

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

2.3CHAR和VARCHAR的区别

CHAR 固定⻓度的字符串，M 表⽰以字符为单位的列⻓度，取值范围 0 ~ 255 ，省略则⻓度为
1 ，在存储时总是⽤空格向右填充到指定的⻓度，获取列的值时会从尾部删除空格。允许定义
CHAR(0)，此时列的值只能为NULL或空字符串，主要的⽬的是为了旧系兼容，⽐如类中必须有这个属性，但不使⽤这个属性的值，也就是说值并没有意义，但列⼜不能没有

VARCHAR 可变⻓度字符串，M 表⽰以字符为单位的最⼤列⻓度，取值范围 0 ~ 65,535 (在所
有列中共享)，有效⻓度取决于实际字符数和使⽤的字符集，并且⽤额外的⼀或两个字节记录实际使⽤的字节数，当实际字节数不超过255个字节⽤⼀个字节记录⻓度，超过255个字节时，使⽤两个字节记录⻓度，获取列的值时不会从尾部删除空格，插⼊数据时会删除超出⻓度的空格

2.4如何选择CHAR和VARCHAR

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

3.日期类型

3.1类型列表

类型	大小	说明	0值
TIMESTAMP[(fsp)]	4bytes	• 时间戳类型 •⽀持范围 1970-01-01 00:00:01.000000 ~ 20388-01-19 03:14:07.499999 • 0值为 0000-00-00 00:00:00	000000- 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:00⽀持	000000- 00 00:00:00
DATE	3bytes	• ⽇期类型 • ⽀持范围 1000-01-01 ~ 9999-12-31 • 显⽰格式为 YYYY-MM-DD • 0值为 0000-00-00
TIME[(fsp)]	3bytes	• 时间类型 • ⽀持范围 -838:59:59.000000 ~ 838:59:59.000000 • 显⽰格式为 hh:mm:ss[.fraction] • 0值为 00:00:00	00:00:00
YEAR[(4)]	1bytes	• 4位格式的年份 • ⽀持范围 1901 ~ 2155 • 显⽰格式为 YYYY • 0值为 0	0

3.2其它

• CURRENT_DATE 和 CURRENT_DATE() 是 CURDATE() 的同义词⽤于获取当前⽇期
• CURRENT_TIME 和 CURRENT_TIME([fsp]) 是 CURTIME() 的同义词⽤于获取当前时间
• CURRENT_TIMESTAMP 和 CURRENT_TIMESTAMP([fsp]) 是 NOW() 的同义词⽤于获取当前⽇期和时间