mysql数据库基础：数据类型介绍

mysql数据库基础

分类

常见数据类型

类型	分类
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT、INTEGER、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值类型：GEOMETRY、POINT、 LINESTRING、POLYGON； 集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

常见数据属性

属性名称	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，使用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

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~ 9223372036854775807*2+1

特点

1、如何设置无符号和有符号
通过UNSIGNED属性设置整形无符号

create table 表名(
	t1 INT, # 设置有符号（默认）
	t2 INT UNSIGNED # 设置无符号
	# 如果添加的是负数，那么结果查询后显示为0
)

2、如果插入的数据超过整型的取值范围，会报out of range异常。

3、如果不认为设置长度，那么系统会自动赋予默认长度

4、使用场景：
（1）TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
（2）SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
（3）MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。
（4）INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。
（5）BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等

2、小数

2.1 分类

浮点型

浮点数类型	字节	范围
float(M,D)	4	±1.75494351E^ 38~±3.402823466EE^38
double(M,D)	8	很大~~~

定点型

MySQL中的定点数类型只有 DECIMAL 一种类型

定点数类型	字节	范围
DECIMAL(M,D)	M+2	最大取值范围与double相同，给定decimal的有效取值范围由M和D决定

2.2 特点

（1）M和D的含义
M：整数部分位数+小数部分位数
D：保留的小数位数（四舍五入）
（2）M和D都可以省略
（3）如果时decimal定点型，则M默认为10，D默认为0
如果是float和double类型，则会根据插入的数值的精度来决定精度
（4）定点型的精确度较高，如果要求插入数值的精度较高，如货币运算等则考虑使用定点型。
（5）从MySQL 8.0.17开始，FLOAT(M,D) 和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用，将来可能被移除。另外，关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用了，将来也可能被移除。
（6）定点数在MySQL内部是以 字符串的形式进行存储，这就决定了它一定是精准的。

浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）;
定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景 （比如涉及金额计算的场景）

3、字符型

3.1 较短的文本

字符串类型	最多字符数	描述及存储需求	区别
char(M)	M	M为0~255之间的整数	固定长度的字符，比较耗费空间，效率高点
varchar(M)	M	M为0~65535之间的整数	可变长度的字符，没那么耗费空间，效率低点

3.2 较长的文本

字符串类型	描述及存储需求
text	存储长文本类型
blob	存储图片类型

4、日期型

4.1 分类

日期类型	字节数	最小值	最大值
date	4	100-1-01	9999-12-31
time	3	-838:59:59	838:59:59
year	1	1901	2155
datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	4	1970	2038年的某个时刻

补充：
datetime和timestamp的区别
（1）timestamp支持的时间范围较小，datetime的取值范围相对较大
（2）timestamp和实际时区有关，更能反映实际的日期，而datetime则只能反映出插入时的当地时区
（3）timestamp的属性受mysql版本和sqlMode的影响很大

5、枚举类型（Enum)

要求插入的值必须属于列表中指定的值之一。
例如：

# 建表,定义一个枚举类型的数据
create table tab_char(
	c1 ENUM('a','b','c')
);

# 插入值
insert into tob_char values('a'),('b'),('c');
# 如果插入列表意外的值，则会报错
insert into tob_char values('m'); # 报错

6、set（类型）

set用来保存集合
和Enum类型类似，里面可以保存0~64个成员。
和Enum类型最大的区别是：set类型一次可以选取多个成员，而Enum只能选一个。根据成员个数不同，存储所占的字节也不同

• 例如

create table tab_set(
	s1 SET('a','b','c','d')
)

insert into tab_set values('a');
insert into tab_set values('a,b');
insert into tab_set values('a,b,c');

结果

7、位类型介绍
BIT类型中存储的是二进制值

类型	长度	长度范围	占用空间
BIT(M)	M	1<=M<=64	约为（M+7)/8个字节

BIT类型，如果没有指定(M)，默认是1位。这个1位，表示只能存1位的二进制值。这里(M)是表示二进制的位数，位数最小值为1，最大值为64。