04_MySQL数据类型篇

1. 数据类型

数据库表中每个字段都在MySQL中有对应的数据类型。

常见的数据类型分为三大类：数值型、字符型和日期型。

其中数值型又可以分为整型和小数等。

注：选择数据类型的原则是所选择的类型越简单越好，能保存数值的类型越小越好。

较常用:int,double,date,datetime,timestamp,char,varchar

1.1 数值型

数值型又可以分为整型和小数型，小数型又可以分为定点数和浮点数。

整数类型	字节数	无符号( unsigned ) 范围	有符号( signed ) 范围( 默认）	用途
TINYINT	1	0~255	-128~127	小整数值
SMALLINT	2	0~65535	-32768~32767	大整数值
MEDIUMINT	3	0~16777215	-8388608~8388607	大整数值
INT	4	0~4294967295	-2147483648~2147483647	大整数值
INTEGER	4	0~4294967295	-2147483648~2147483647	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值

1.1.1 整型

常见的整型有：tinyint、smallint、mediumint、int/integer、bigint等。

它们的特点是：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配ZEROFILL使用！

示例如下：

-- 设置无符号和有符号的整数
CREATE TABLE t1(
	id1 INT, # 有符号整数
	id2 INT UNSIGNED # 无符号整数
)
INSERT INTO t1 VALUES(-1234,123456);
-- 设置整型长度，并搭配ZEROFILL
CREATE TABLE t2(
	id1 INT(7) ZEROFILL# 如果输入的整数不足7位，那么高位会用0填满7位
)
INSERT INTO t2 VALUES(123);

1.1.2 小数

小数在MySQL中分为定点数和浮点数，其中浮点数由于精度关系又分为float和double。如下所示：

• 小数
  • 浮点型
    • float(M,D)
    • double(M,D)
  • 定点型
    • decimal(M,D)

小数的特点是：

• M表示整数部位+小数部位，D表示小数部位，如果超过范围则插入临界值。
• M和D都可以省略，如果是decimal类型，那么M默认位10，D默认位0；如果是float和double类型，则会根据插入的数值的精度来决定精度。
• 定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用。

示例如下：

CREATE TABLE tab_float(
	f1 FLOAT,# float类型
	f2 DOUBLE,# double类型
	f3 DECIMAL# decimal类型，它们的M和D都是省略的
);
INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523);

1.2 字符型

字符型数据类型可以大概分为：

• 较短的文本
  • char
  • varchar
• 较长的文本
  + text
  + blob（较大的二进制）
• 其他
  + binary和varbinary用于保存较短的二进制
  + enum用于保存枚举
  + set用于保存集合

char和varchar的特点如下：

	char	varchar
写法	char(n)	varchar(n)
n的含义	最大的字符数，可以省略，默认为1	最大的字符数，不可以省略
特点	固定长度的字符	可变长度的字符
空间的耗费	比较耗费，因为分配了指定长度的空间，如果赋予的值没有占满，那么其他空间浪费了	比较节省
效率	高	低

注意下enum和set的区别

-- enum数据类型的使用：可以放一个列表
-- 但插入时只能选择列表中的一个值
-- 可以通过enum实现检查约束
CREATE TABLE tab_char(
	c1 ENUM('a','b','c');
)
INSERT INTO tab_char VALUES('a');
-- 插入值只能在枚举类型中给定的值中选择一个,只能选择一个
-- 如果值不是其中的一个也不能插入成功。
 
-- set数据类型的使用：也可以放一个列表
-- 但插入时可以选择列表中多个值，用英文逗号分隔，不区分大小写
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,c,d');

1.3 日期型

日期数据类型可以分为：

• date ：只保存日期（即年月日),yyyy-MM-dd
• time：只保存时间（即时分秒）
• year：只保存年
• datetime：保存日期+时间（即年月日+时分秒）,yyyy-MM-dd HH:mm:ss
• timestamp：保存日期+时间（即年月日+时分秒）,但如果将来不给这个字段赋值或赋值为null,则使用当前系统时间赋值,可以用来作为注册时间的数据类型

datetime和timestamp的区别：

	detetime	timestamp
字节	8	4
范围	1000-9999	1970-2038
是否受时区影响	不受	受

示例:

CREATE TABLE tab_date(
    t1 DATETIME,
    t2 TIMESTAMP
);