【MySQL】第十六部分 MySQL数据类型详解
文章目录
16. MySQL数据类型详解
16.1 整数类型
整数类型 | 字节 | 有符号数取值范围 | 无符号数取值范围 |
---|---|---|---|
TINYINT | 1 | -128~127 | 0~255 |
SMALLINT | 2 | -32768~32767 | 0~65535 |
MEDIUMINT | 3 | -8388608~8388607 | 0~16777215 |
INT,INTEGER | 4 | -2147483648~2147483647 | 0~4294967295 |
BIGINT | 8 | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
上述类型使用的场景:
TINYINT
:一般用于枚举数据,比如系统设定特定的身份。
SMALLINT
:可以用于较小范围的统计数据,比如统计工厂的固定资产库存数量等。
MEDIUMINT
:用于较大整数的计算,比如车站每日的客流量等。
INT、INTEGER
:取值范围足够大,一般情况下不用考虑超限问题,用得最多。
BIGINT
:只有当你处理特别巨大的整数时才会用到。比如: 双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。
在实际的工作中,先确保数据不会超过取值范围,再去考虑如何节省空间,否则得不偿失.
16.2 浮点类型
浮点数和定点数类型的特点是可以处理小数
,可以把整数看成小数的一个特例。因此,浮点数和定点数的使用场景,比整数大多了。 MySQL支持的浮点数类型,分别是 FLOAT
、DOUBLE
。
FLOAT
表示单精度浮点数;DOUBLE
表示双精度浮点数;
FLOAT
和 DOUBLE
它们之间主要的区别是: FLOAT 占用字节数少,取值范围小;DOUBLE 占用字节数多,取值范围也大.
浮点数类型精度会有误差:
CREATE TABLE test1(
f1 DOUBLE
);
INSERT INTO test1
VALUES(0.47),(0.44),(0.19);
SELECT SUM(f1)
FROM test1;
16.3 定点数类型
数据类型 | 字节数 | 含义 |
---|---|---|
DECIMAL(M,D),DEC,NUMERIC | M+2字节 | 有效范围由M和D决定 ( 0<=M<=65,0<=D<=30) M表示总位数,D表示小数点保留几位 |
例如: 定义DECIMAL(6,2)的类型,表示该取值范围是 -9999.99
到 9999.99
。
浮点数 vs 定点数
浮点数相对于定点数的优点是在长度一定的情况下,浮点类型取值范围大,但是精度会缺失,适用场景: 需要取值范围大,但是可以容许微小误差的业务场景.
定点数类型取值范围相对小,但是精准,没有误差,适合于对精度要求极高的场景 比如涉及金额计算的场景.
ALTER TABLE test1 ADD f2 DECIMAL(6,2)
INSERT INTO test1(f2)
VALUES(0.47),(0.44),(0.19);
SELECT SUM(f2)
FROM test1;
16.4 位类型 BIT
BIT类型中存储的是二进制值,010101。
二进制字符串类型 | 长度 | 长度范围 | 占用空间 |
---|---|---|---|
BIT(M) | M | 1 <= M <= 64 | 约为(M + 7)/8个字节 |
BIT类型,如果没有指定(M),默认是1位,表示只能存1位的二进制值。在向BIT类型的字段中插入数据时,一定要确保插入的数据在BIT类型支持的范围内。
CREATE TABLE test1(
f1 BIT(8),
f2 BIT
)
INSERT INTO test1
VALUES(12,0)
SELECT * FROM test1
16.5 日期和时间类型
YEAR
类型通常用来表示年
DATE
类型通常用来表示年、月、日
TIME
类型通常用来表示时、分、秒
DATETIME
类型通常用来表示年、月、日、时、分、秒
TIMESTAMP
类型通常用来表示带时区的年、月、日、时、分、秒
类型 | 名称 | 字节 | 日期格式 | 最小值 | 最大值 |
---|---|---|---|---|---|
YEAR | 年 | 1 | YYYY或YY | 1901 | 2155 |
TIME | 时间 | 3 | HH:MM:SS | -838:59:59 | 838:59:59 |
DATE | 日期 | 3 | YYYY-MM-DD | 1000-01-01 | 9999-12-03 |
DATETIME | 日期时间 | 8 | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 日期时间 | 4 | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC | 2038-01-19 03:14:07UTC |
TIMESTAMP和DATETIME的区别:
-
TIMESTAMP存储空间比较小,表示的日期时间范围也比较小
-
底层存储方式不同,TIMESTAMP底层存储的是毫秒值,距离1970-1-1 0:0:0 0毫秒的毫秒值。
-
两个日期比较大小或日期计算时,TIMESTAMP更方便、更快。
-
TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同,显示不同的结果。而DATETIME则只能反映出插入时当地的时区,其他时区的人查看数据必然会有误差的。
在工作中,用得最多的日期时间类型,就是 DATETIME
。
CREATE TABLE test_time(
f1 YEAR,
f2 DATE,
f3 TIME,
f4 DATETIME,
f5 TIMESTAMP
)
INSERT INTO test_time
VALUES (CURRENT_DATE,CURRENT_DATE,CURRENT_TIME,NOW(),NOW())
SELECT * FROM test_time
16.6 文本字符串类型
文本字符串总体上分为CHAR
、VARCHAR
、TINYTEXT
、TEXT
、MEDIUMTEXT
、LONGTEXT
、ENUM
、SET
等类型。
16.6.1 CHAR VS VARCHAR类型
字符串(文本)类型 | 特点 | 长度 | 长度范围 | 占用的存储空间 |
---|---|---|---|---|
CHAR(M) | 固定长度 | M | 0 <= M <= 255 | M个字节 |
VARCHAR(M) | 可变长度 | M | 0 <= M <= 65535 | (实际长度 + 1) 个字节 |
CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M),则表示长度默认是1个字符,如果数据的实际长度比CHAR类型声明的长度小,则会在右侧填充空格, 定义CHAR类型字段时,声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
VARCHAR(M)类型必须指定长度M,否则报错,MySQL4.0版本以下,varchar(20):指的是20字节,如果存放UTF8汉字时,只能存6个(每个汉字3字节) ;MySQL5.0版本以上,varchar(20):指的是20字符。
类型 | 特点 | 空间上 | 时间上 | 适用场景 |
---|---|---|---|---|
CHAR(M) | 固定长度 | 浪费存储空间 | 效率高 | 存储不大,速度要求高 |
VARCHAR(M) | 可变长度 | 节省存储空间 | 效率低 | 存储大,速度要求不高 |
情况1:存储很短的信息。比如门牌号码101,201……这样很短的信息应该用char,因为varchar还要占个byte用于存储信息长度。
情况2:固定长度的。比如使用uuid作为主键,那用char应该更合适。因为他固定长度,varchar动态根据长度的特性就消失了,而且还要占个长度信息。
情况3:十分频繁改变的column。因为varchar每次存储都要有额外的计算,得到长度等工作,如果一个非常频繁改变的,那就要有很多的精力用于计算,而这些对于char来说是不需要的。
16.6.2 TEXT类型
文本字符串类型 | 特点 | 长度 | 长度范围 | 占用的存储空间 |
---|---|---|---|---|
TINYTEXT | 小文本、可变长度 | L | 0 <= L <= 255 | L + 2 个字节 |
TEXT | 文本、可变长度 | L | 0 <= L <= 65535 | L + 2 个字节 |
MEDIUMTEXT | 中等文本、可变长度 | L | 0 <= L <= 16777215 | L + 3 个字节 |
LONGTEXT | 大文本、可变长度 | L | 0 <= L<= 4294967295(相当于4GB) | L + 4 个字节 |
TEXT文本类型,可以存比较大的文本段,搜索速度稍慢,因此如果不是特别大的内容,建议使用CHAR,VARCHAR来代替。TEXT和BLOB类型的数据删除后容易导致“空洞”,使得文件碎片比较多,所以频繁使用的表不建议包含TEXT类型字段,建议单独分出去,单独用一个表。
16.6.3 ENUM类型
ENUM类型也叫作枚举类型,ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时,ENUM类型只允许从成员中选取单个值,不能一次选取多个值。
文本字符串类型 | 长度 | 长度范围 | 占用的存储空间 |
---|---|---|---|
ENUM | L | 1 <= L <= 65535 | 1或2个字节 |
CREATE TABLE test_enum
(
gender ENUM("男","女"),
season ENUM("春","夏","秋","冬")
)
INSERT INTO test_enum
VALUES("男","秋")
# 当ENUM类型的字段没有声明为NOT NULL时,插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL,NULL);
# 允许按指定索引位置的枚举值
INSERT INTO test_enum
VALUES(1,2)
SELECT * FROM test_enum
16.6.4 SET类型
SET类型,SET表示一个字符串对象,可以包含0个或多个成员,但成员个数的上限为64
。设置字段值时,可以取值范围内的 0 个或多个值。
成员个数范围(L表示实际成员个数) | 占用的存储空间 |
---|---|
1 <= L <= 8 | 1个字节 |
9 <= L <= 16 | 2个字节 |
17 <= L <= 24 | 3个字节 |
25 <= L <= 32 | 4个字节 |
33 <= L <= 64 | 8个字节 |
CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);
INSERT INTO test_set (s) VALUES ('A'), ('A,B');
#插入重复的SET类型成员时,MySQL会自动删除重复的成员
INSERT INTO test_set (s) VALUES ('A,B,C,A');
#向SET类型的字段插入SET成员中不存在的值时,MySQL会抛出错误。
INSERT INTO test_set (s) VALUES ('A,B,C,D');
SELECT *
FROM test_set;
16.7 二进制字符串类型
MySQL中的二进制字符串类型主要存储一些二进制数据,比如可以存储图片、音频和视频等二进制数据。MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB类型。
16.7.1 BINARY和VARBINARY
二进制字符串类型 | 特点 | 值的长度 | 占用空间 |
---|---|---|---|
BINARY(M) | 固定长度 | M (0 <= M <= 255) | M个字节 |
VARBINARY(M) | 可变长度 | M(0 <= M <= 65535) | M+1个字节 |
类似于CHAR和VARCHAR,区别只是它们存储的是二进制字符串
BINARY (M)为固定长度的二进制字符串,M表示最多能存储的字节数,取值范围是0~255个字符。如果未指定(M),表示只能存储1个字节
。例如BINARY (8),表示最多能存储8个字节,如果字段值不足(M)个字节,将在右边填充’\0’以补齐指定长度。
VARBINARY (M)为可变长度的二进制字符串,M表示最多能存储的字节数,总字节数不能超过行的字节长度限制65535,另外还要考虑额外字节开销,VARBINARY类型的数据除了存储数据本身外,还需要1或2个字节来存储数据的字节数。VARBINARY类型必须指定(M)
,否则报错。
16.7.2 BLOB类型
BLOB是一个二进制大对象
,可以容纳可变数量的数据。
二进制字符串类型 | 值的长度 | 长度范围 | 占用空间 |
---|---|---|---|
TINYBLOB | L | 0 <= L <= 255 | L + 1 个字节 |
BLOB | L | 0 <= L <= 65535(相当于64KB) | L + 2 个字节 |
MEDIUMBLOB | L | 0 <= L <= 16777215 (相当于16MB) | L + 3 个字节 |
LONGBLOB | L | 0 <= L <= 4294967295(相当于4GB) | L + 4 个字节 |
需要注意的是,在实际工作中,往往不会在MySQL数据库中使用BLOB类型存储大对象数据,通常会将图片、音频和视频文件存储到服务器的磁盘上
,并将图片、音频和视频的访问路径存储到MySQL中。
16.8 JSON 类型
JSON是一种轻量级的数据交换格式
, JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串,然后就可以在网络或者程序之间轻松地传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式。
CREATE TABLE test_json(
js json
);
INSERT INTO test_json (js)
VALUES ('{"name":"lilei", "age":18, "address":{"province":"shanghai", "city":"shanghai"}}');
综上
- 定义整数的时候,就用INT
- 定义浮点数的时候,就用DECIMAL(M,D)
- 定义时间和日期的时候,就用DATETIME
- 定义字符串的时候,根据实际的需求选择CHAR或者VARCHAR
- 定义字段为TEXT,独立出一张表,用主键来对应,避免影响其他字段索引的效率
总结
以上就是今天要讲的内容,希望对大家有所帮助!!!