MySQL 8.0 数据类型小结

最新推荐文章于 2024-05-15 13:14:13 发布
最新推荐文章于 2024-05-15 13:14:13 发布
阅读量4.4k 收藏 9
点赞数 3
分类专栏： # MySQL运维 MySQL从小工到专家之路 文章标签： mysql 数据库 数据类型 占用空间
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010520724/article/details/107553913
版权

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL支持的数据类型

文章目录

一.数字类型

类型存储(字节)最小(有符号)最大(有符号)最小(无符号)最大(无符号)描述
BIT(M)(m+7)/8————位值类型。M表示每个值的位数，从1到64.如果M省略，默认是1。比如bit(8)存储888变为00000111
TINYINT(M)1-1281270255
SMALLINT(M)2-3276832767065535
MEDIUMINT(M)3-83886088388607016777215
INT,INTEGER(M)4-2147483648214748364704294967295
BIGINT(M)8-2^632^63 -102^64
DECIMAL变长（0-4个字节）M为总位数(精度)，D为小数点后的位数(刻度)。如果D为0，则值没有小数部分。最大(M)是65。最大(D)为30.如果省略D，D的默认值为0,。如果省略M，M的默认值为10. NUMBERIC的实现是DECIMAL
NUMBERIC变化同上
FLOAT(M,D)4M是总位数，D是小数点后面的位数。如果M和D省略，则将值存储到硬件允许的限制。单精度浮点精确到7位小数。
正区间- [ –3.402823466E38 , –1.175494351E-38 ]
负区间-[ 1.175494351E-38 , 3.402823466E38]
DOUBLE(M,D)8M是总位数，D是小数点后面的位数。如果M和D省略，则将值存储到硬件允许的限制。单精度浮点精确到15位小数。
正区间-[ –1.7976931348623157E308,–2.2250738585072014E-308 ]
负区间-[ 2.2250738585072014E-308 , 1.7976931348623157E308 ]
BOOL,BOOLEAN1TINYINT(1)的同义词

总结:
1.不允许负数
整数类型有可选的UNSIGNED属性，表示不允许存负值，这大概可以使整数的上限提高一倍。
如: TINYINT UNSIGNED

2.如何选择合适的数值类型
1.整数类型
例如枚举类的， 选择 tinyint、smallint即可，节省磁盘空间就是优化。
其它的业务相关表，例如用户表、订单表 可以选择用 int类型。
虽然int类型不支持小数，但是例如金额这个，可以通过调整单位，例如单位为分，这样就可以存小数金额了
对于一些大的日志表、分布式ID之类的，可以选择bigint类型

2.小数类型
对于需要存储小数的场景而言，使用decimal(m,d)
m代表总的位数，d代表小数位，整数位为m-d

create table t_decimal(id  decimal(7,4));
-- 小数位不够会自动填0
insert into t_decimal values (123.456);
-- 这个是正常的插入方法
insert into t_decimal values (123.4561);
-- 整数为超过限制 报错
insert into t_decimal values (1230.4561);
-- 小数位超过限制 报错
insert into t_decimal values (1230.45612);

执行记录:

mysql> create table t_decimal(id  decimal(7,4));
Query OK, 0 rows affected (0.03 sec)

mysql> -- 小数位不够会自动填0
mysql> insert into t_decimal values (123.456);
Query OK, 1 row affected (0.00 sec)

mysql> -- 这个是正常的插入方法
mysql> insert into t_decimal values (123.4561);
Query OK, 1 row affected (0.00 sec)

mysql> -- 整数为超过限制 报错
mysql> insert into t_decimal values (1230.4561);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> -- 小数位超过限制 报错
mysql> insert into t_decimal values (1230.45612);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql>
mysql> select * from t_decimal;
+----------+
| id       |
+----------+
| 123.4560 |
| 123.4561 |
+----------+
2 rows in set (0.00 sec)

3.浮点数
浮点数会存在一定的精度的缺失，但是可以应用于科学计算，性能会比整数和小数类都要快。

二.日期时间类型

类型存储(字节)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/‘838:59:59’HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038YYYYMMDD HHMMSS混合日期和时间值，时间戳

关于时间类型的选择:
1.如果只存年，用YEAR类型
2.如果只存年月日，用DATE
3.如果需要存年月日时分秒，用TIMESTAMP
不要被这个2038年给吓到了，而不用TIMESTAMP，其实更节约存储空间，且能容纳时区信息
4.不用将TIMESTAMP转换为数值
FROM_UNIXTIME() – 把数值转换为时间戳
UNIX_TIMESTAMP() – 把时间戳转换为数值

转换感觉是节省了空间，不过处理起来非常的不方便，不推荐使用

在这里插入图片描述

三.字符类型

CHAR和VARCHAR类型

类型存储(字节)范围用途
CHAR(M)M0 - 255存储定长的字符
VARCHAR(M)VARCHAR(10) 实际存储3个字符，1个字节来存储长度，总共占4字节
VARCHAR(1000) 实际存储3个字符，2个字节来存储长度，总共占5字节
不同的存储引擎可能存在一定的差异		0-65536存储可变长度的字符串

1.类型选择问题
很多时候，开发同事为了方便，直接用varchar(200) 来存储字符，不考虑实际需求。
这样做，存在诸多弊端。
如果是md5密码这样的定长字段，如果用varchar类型，会浪费一定的存储空间。
如果存储的字符只有5个，而这时都用varchar(200)，感觉存储空间是一样的。但是程序端读取的时候，varchar(200)会消耗更多的内存。

2.变长字符的更新问题
InnoDB存储引擎
varchar由于是变长，遇到更新的时候，如果比原先的长度长很多，这个时候页的空间不够，会分裂页，此时会比较消耗性能

3.定长字符神奇的空格问题

-- char类型，string3末尾的空格莫名的不见了
create table char_test( char_col char(10));
insert into char_test(char_col) values ('string1'),('   string2'),('string3   ');
select * from char_test;
select concat("'",char_col,"'") from char_test;
-- 换成varchar类型，就不会出现这个问题
drop table char_test;
create table char_test( char_col varchar(10));
insert into char_test(char_col) values ('string1'),('   string2'),('string3   ');
select concat("'",char_col,"'") from char_test;

执行记录:

mysql> create table char_test( char_col char(10));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into char_test(char_col) values ('string1'),('   string2'),('string3   ');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from char_test;
+------------+
| char_col   |
+------------+
| string1    |
|    string2 |
| string3    |
+------------+
3 rows in set (0.00 sec)

mysql> select concat("'",char_col,"'") from char_test;
+--------------------------+
| concat("'",char_col,"'") |
+--------------------------+
| 'string1'                |
| '   string2'             |
| 'string3'                |
+--------------------------+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table char_test;
Query OK, 0 rows affected (0.03 sec)

mysql> create table char_test( char_col varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into char_test(char_col) values ('string1'),('   string2'),('string3   ');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select concat("'",char_col,"'") from char_test;
+--------------------------+
| concat("'",char_col,"'") |
+--------------------------+
| 'string1'                |
| '   string2'             |
| 'string3   '             |
+--------------------------+
3 rows in set (0.00 sec)

BLOB和TEXT类型

类型描述
TINYBLOB最大长度255(2^8-1)，使用1字节前缀存储长度信息
BLOB最大长度65，535(2^16-1),使用2字节前缀存储长度信息
MEDIUMBLOB最大长度16，777,215（2^24-1）,使用3字节前缀存储长度信息
LONGBLOB最大长度（2^32-1）或4GB,使用4字节前缀存储长度信息
TINYTEXT最大长度255(2^8-1)，使用1字节前缀存储长度信息
TEXT最大长度65，535(2^16-1),使用2字节前缀存储长度信息
MEDIUMTEXT最大长度16，777,215（2^24-1）,使用3字节前缀存储长度信息
LONGTEXT最大长度（2^32-1）或4GB,使用4字节前缀存储长度信息

BLOB是SMALLBLOB的同义词
TEXT是SMALLTEXT的同义词

MySQL把每个BLOB和TEXT当做一个独立的对象处理。
当BLOB和TEXT值太大时，InnoDB会使用专门的外部存储区域来进行存储，此时每个值在行内需要1-4个值存储一个指针，然后在外部存储区域存储实际的值

BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。

ENUM和SET类型

类型描述
ENUM(‘v1’,‘v2’…)一个enum最多可包含65,535个不同的元素。单个ENUM元素的最大支持长度是255(文字长度)。枚举值内部对应一个索引，从1开始。
SET(‘v1’,‘v2’…)一个SET列最多可包含64个不同的元素。单个SET元素的最大支持长度是255(文字长度)
CREATE TABLE test_enum_set (
  a SET('A','B','C'),
  b ENUM('张三','李四')
);


insert into test_enum_set values ('A','张三');

insert into test_enum_set values ('A,B','张三');

insert into test_enum_set values ('A,B','张三,李四');

执行记录:

mysql> CREATE TABLE test_enum_set (
    ->   a SET('A','B','C'),
    ->   b ENUM('张三','李四')
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_enum_set values ('A','张三');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_enum_set values ('A,B','张三');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_enum_set values ('A,B','张三,李四');
ERROR 1265 (01000): Data truncated for column 'b' at row 1
mysql>
mysql> select * from test_enum_set;
+------+--------+
| a    | b      |
+------+--------+
| A    | 张三   |
| A,B  | 张三   |
+------+--------+
2 rows in set (0.00 sec)
只是甲
关注
  • 3
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL 8 常用的数据类型
学无止境
10-16 490
以下是一些常用的 MySQL 8 数据类型：
关系数据库-1-[mysql8]中的数据类型
qq_20466211的博客
01-22 1368
数据类型
MySQL 8.0 全新特性详解
最新发布
weixin_40381772的博客
05-15 1173
MySQL 8.0的这些新特性在数据管理、安全性、性能优化等多个方面进行了改进，为用户提供了更加高效、灵活和安全的数据库管理系统。无论是处理大规模数据、应对高并发场景还是满足多样化的数据处理需求，MySQL 8.0都能提供出色的支持。
Mysql8.0 数据类型介绍
xharvard的专栏
02-28 1622
无论是数值类型、日期类型、普通的文本类型，可取值的范围都非常大，但是有时候我们指定在固定的几个值范围内选择一个或多个，那么就需要使用ENUM枚举类型和SET集合类型了。DATETIME：日期时间，8字节。TIMESTAMP的取值范围小，并且TIMESTAMP类型的日期时间在存储时会将当前时区的日期时间值转换为时间标准时间值，检索时再转换回当前时区的日期时间值。插入日期，时间的分隔符不限于"-"，也可以是“%”，“#”等。可以看到，insert的时候，值必须是创建表的时候预定义的成员，输入其他的会报错。
MySQL8 全部类型
u012632105的博客
03-27 518
数字类型 日期类型 字符串类型 CHAR和VARCHAR 表列数和行大小限制 MySQL每个表的硬限制为4096列，但对于给定的表，有效最大值可能更小。确切的列限制取决于几个因素： 表的最大行大小限制列的数量（也可能是size），因为所有列的总长度不能超过此大小。 各列的存储要求限制了适合给定最大行大小的列数。某些数据类型的存储要求取决于存储引擎，存储格式和字符集等因...
mysql的数据类型_MySql的回顾八数据类型介绍
weixin_42452892的博客
01-20 211
漆黑的夜空，总能让人产生无数的遐想，不知屏幕前的你，这周过得怎么样？玩耍也好，"修炼"也好，总之时间在不断从我们各处穿梭而过，但你既然都点进来了，那在这短短的5分钟内我就来带你领来温习一下Mysql数据类型吧！#常见的数据类型数值型:整型小数:定点数浮点型字符型:较短的文本:char,varchr较长的文本:text,blob(较长的二进制数据)一.整型分类:tinyint,smallint,me...
mysql8.0.19基础数据类型详解
09-08
主要介绍了mysql8.0.19基础数据类型的相关知识，本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值 ,需要的朋友可以参考下
mysql8.0 附带一键安装脚本
12-15
这个“mysql8.0 附带一键安装脚本”可能包含了一个简化 MySQL 8.0 安装过程的自动化脚本，使得在各种操作系统上部署数据库服务器变得更加便捷。 ### MySQL 8.0 的关键特性： 1. **增强的性能**：MySQL 8.0 通过...
MySQL8.0认证 ocp(175题)
08-30
2. **数据类型与运算符**：掌握各种数据类型，如整型、浮点型、字符串和日期时间类型，以及逻辑、比较和算术运算符的使用。 3. **SQL语句**：熟练编写SELECT查询，理解JOIN、子查询、分组和聚合函数、排序和限制、...
MySQL8.0参考手册中文版
09-26
打开百度网盘，免费下载不要钱 链接：https://pan.baidu.com/s/1loAoyceuxnOLVmsW41o72g?pwd=mnzg 提取码：mnzg
MySQL 8.0 Reference Manual MySQL 8.0 Reference Manual
04-09
* 支持多种数据类型，包括数字、字符串、日期时间、枚举、集合等 * 强大的 SQL 语言支持，包括 SELECT、INSERT、UPDATE、DELETE 等语句 * Supports transactions, stored procedures, and views * Support for ...
mysql数据类型及语法_mysql8 参考手册--Numeric数据类型语法
weixin_39601056的博客
01-18 459
MySQL支持所有标准的SQL数值数据类型。这些类型包括精确数字数据类型(整数、SMALLINT、DECIMAL和numeric)以及近似数字数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC和FIXED是DECIMAL的同义词。MySQL将DOUBLE视为DOUBLE PRECISION(非标准扩展)的同义词。MySQL还将RE...
MySQL8.0学习记录08 - 数据类型默认值
wisfy_21的博客
04-21 1628
MySQL 数据类型默认值
MySQL 枚举类型如何定义比较好 tinyint?enum?varchar?
程序员面试精选【codeoffer】
02-10 3910
前言 当项目中遇到比较多的枚举字段时怎么选择MySQL的类型呢?tinyint,varchar还是enum?据我观察大家还是用tinyint的比较多，少数也会直接用varchar。 说到枚举，这个类型真的是有点坑，写的时候又不想校验(尤其是枚举值比较多的时候),又想直接在表里可以显示出原字符（说到底还是懒）。tinyint可以校验插入的值为自己定义的数值且索引友好但是显示不友好，varvhar可以直接在表中显示字段值但对索引又不友好。 两种都不是最优选择，后来MySQL出现了e...
mysql8.0 数据类型和运算符
S_zhangmin的博客
01-24 1459
MySQL 数据类型介绍 MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。 Part 1. 整数类型 类型名称 说明 存储需求 有符号范围 无符号范围 TINYINT 很小的整数 1字节 -2^7~2^7-1 0~2^8 SMALLINT 小整数 2字节 -2^15~2^15-1 0~2^16 MEDIUMINT 中等大小的整数 3字节 -2^23~2^23-1 0~2^
MySQL8.0 初级学习笔记（二）数据类型
twelve_ddx的博客
07-23 366
数据类型的定义： 是指数据列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。 MySQL支持所有标准SQL的数值数据类型，大致可以分成三类：数值，日期/时间和字符串（字符）类型。 一：常用数值类型有整型（int)和浮点型(float) 整型INT 有五种数据集类型：TINYINT 、SMALLINT、 MEDIUMINT、 INT or INTEGER、BIGINT 浮点型FLOAT有三种数据集类型：FLOAT、DOUBLE、DECIMAL 表格截图.
Mysql 关于 int（1） 和 int(11) , 我必须要说一下了。
热门推荐
默默不代表沉默
03-07 2万+
前言 首先在吐槽这个现象前，我先说下我们该篇的内容。 1. int 类型表字段 在mysql数据库里面，到底存值的范围是多少 ？ 什么时候是-2147483648到2147483647 什么时候是 0 到4294967295 2. int （1） int（11） 这个括号后面的玩意，对于int来说 作用是什么？ int（1） 就只能存入 0，1，2，3，4，5，6，7，8，9 吗？ 是这样理解？？？ 我存个10不行？ 可能对这些概念很清晰的人会觉...
MySQL8.0学习记录04 - 数据类型之字符串类型
wisfy_21的博客
04-12 734
MySQL字符串类型
mysql8.0时间类型
10-25
MySQL 8.0时间类型包括：DATE、TIME、DATETIME和TIMESTAMP。其中，DATE表示日期，格式为'YYYY-MM-DD'；TIME表示时间，格式为'HH:MM:SS'；DATETIME表示日期和时间，格式为'YYYY-MM-DD HH:MM:SS'；TIMESTAMP也表示日期和时间，但是它的范围比DATETIME更小，只能表示从1970年1月1日到2038年1月19日之间的时间。此外，MySQL 8.0还支持指定时间类型的微秒的精度，最多可以精确到6位。不同的时间类型所占的字节数和表示范围也不同，具体可以参考引用中的内容。在存储时间类型时，如果指定了精度，则需要根据精度来确定所需的存储空间，具体可以参考引用中的内容。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值