mysql之mysq类型18

概述：
接下来按照以下来讲解mysql的主要数据类型。

#常见的数据类型
/*
数值型：
	整型
	小数：
		定点数
		浮点数
字符型：
	较短的文本：char、varchar
	较长的文本：text、blob（较长的二进制数据）

日期型：

*/

1 数值型

mysql的数值型包括整形和浮点型。

#一、整型
/*
分类：
tinyint(1)、smallint(2)、mediumint(3)、int/integer(4)、bigint(8)	//括号代表所占字节数

特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值(低于时插入最低临界，高于时插入最高临界).
注：(navicat报错,无法插入，我数据库版本5.7。而使用sqlyog报警告,数据库5.5能插入临界值)
③ 如果不设置长度，会有默认的长度长度代表了显示的最大宽度，如果不够会用0在左边填充，
但必须搭配zerofill使用！并且加上zerofill后，默认变成无符号。
注：我navicat，数据库版本5.7无法显示填充位，而sqlyog，数据库版本5.5能显示，具体原因暂没查询
*/

#①如何设置无符号和有符号
CREATE TABLE tab_int(
	t1 INT,
	t2 INT UNSIGNED 
);
DESC tab_int;

#②超过临界值或低于临界值时测试
INSERT INTO tab_int VALUES(-123456,-123456);
INSERT INTO tab_int VALUES(2147483648,4294967296);

#③测试类型长度代表的意思，即填充0的意思
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,
	t2 INT(7) ZEROFILL 
);
INSERT INTO tab_int VALUES(123,123);

#额外显示数据命令
SELECT * FROM tab_int;


#二、小数
/*
分类：
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M，D)
decimal(M,D)

特点：
①
M：整数部位+小数部位的长度
D：小数部位长度
如果超过范围(指超过M而不越界，不过越界也能插入按照上面的整形例子)，则插入临界值(数据库5.5,sqlyog操作)。
而我数据库5.7,navicat操作时：
当长度太长导致越界则会报错，当长度也太长但是没越界时，取规定的长度，即M(长度包括D小数点后的数)
例M=5,D=2.
插入123.4523，由于未越界但长度超过5，过插入结果为123.45
插入1234567890.12(假设字节越界)，则会报错。
具体看下面例子，理解这一点主要是区分越界与超过长度范围M，两者意思不一样。

②
M和D都可以省略。
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度

③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

*/

#测试M和D
DROP TABLE tab_float;
CREATE TABLE tab_float(
	f1 FLOAT,
	f2 DOUBLE,
	f3 DECIMAL
);
SELECT * FROM tab_float;
DESC tab_float;

INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523);#f1,f2正常，f3结果为123，因为DECIMAL默认小数点保留0位即D=0.M这里没超出10，所以不需要考虑
INSERT INTO tab_float VALUES(123.4,123.4,123.4);		 #全部正常插入
INSERT INTO tab_float VALUES(1523.4,1523.4,1523111133.4);#f1,f2正常，f3结果为1523111133，因为M=10，所以直接取整数部分，不考虑小数
INSERT INTO tab_float VALUES(1523.4,1523.4,11523111133.4);#直接越界报错，无法插入

#原则：
/*
所选择的类型越简单越好，能保存数值的类型越小越好
*/

其中整形和浮点型相关具体范围：

2 字符型

#三、字符型
/*
较短的文本：
1）主要：
char
varchar
2）其他：
位类型用于保存位，一个字节8位
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合

char，varchar特点：
类型	写法		M的意思										空间的耗费					效率
char	char(M)		最大的字符数(不是字节)，可以省略，默认为1	固定长度的字符，比较耗费	高
varchar varchar(M)	最大的字符数(不是字节)，不可以省略			可变长度的字符，比较节省	低


较长的文本：
text
blob(较大的二进制)
*/

#测试枚举如何插入
CREATE TABLE tab_char(
	c1 ENUM('a','b','c')
);
INSERT INTO tab_char VALUES('a');#成功
INSERT INTO tab_char VALUES('b');#成功
INSERT INTO tab_char VALUES('c');#成功
INSERT INTO tab_char VALUES('m');#失败，不在枚举c1中，有的直接报错(sql5.7)，有的插入成功显示空白(slq5.5)。
INSERT INTO tab_char VALUES('A');#成功，不区分大小写，自动转成小写插入
SELECT * FROM tab_char;

#测试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');#成功
INSERT INTO tab_set VALUES('a,c,d,e');#失败，报错(sql5.7)
SELECT * FROM tab_set;

其中字符型(这里将枚举，set这些也划分为字符型，方便记)的具体定义或范围：
1）主要的char，varchar。

2）其它类型。位类型，binary，varbinary类型，枚举类型，set集合类型。

3 日期类型

日期类型只有5个，实际上一般只用到datetime和timestamp，而timestamp用得居多。

#四、日期型
/*
分类：
date只保存日期
time 只保存时间
year只保存年

datetime保存日期+时间
timestamp保存日期+时间。(注意mysql时间戳类型会被转成显示年月日时分秒，而不是显示一个时间戳)

特点：
类型		字节		范围		时区等的影响
datetime	8			1000——9999	不受
timestamp	4	        1970-2038	受

*/
#测试timestamp受时区影响，而datetime不受
CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;
#结果，时间一样：
t1					t2
2021-02-24 12:08:26	2021-02-24 12:08:26

#改时区
SHOW VARIABLES LIKE 'time_zone';
#结果看到时区为系统时区，我们在中国，所以SYSTEM为东八区
Variable_name  Value
time_zone	   SYSTEM

#修改为东9区，此次修改只在本次连接的数据库有效，断开本次所连的数据库就会失效，例如test数据库，断开就会失效
SET time_zone='+9:00';
#结果：
Variable_name  Value
time_zone	   +09:00

#最后结果，timestamp受时区影响，而datetime不受：
t1					t2
2021-02-24 12:08:26	2021-02-24 13:08:26