3.Mysql支持的数据类型
数据类型用来指定一定的存储格式、约束和有效范围。
数据类型主要有：数值类型、字符串类型、日期和时间类型。
3.1 数字类型
类型名 字节数 有符号最小值 有符号最大值 无符号最小值 无符号最大值 有效范围 有效位数 备注
tinyint 1B -128 127 0 255 2^8bit 2位 整数
smallint 2B -32768 32767 0 65535 2^16bit 4位 整数
mediumint 3B -8388608 8388607 0 16777215 2^24bit 6位 整数
int\integer 4B -2147483648 2147483647 0 4294967295 2^32bit 9位 整数
bigint 8B -9223372036854775808 9223372036854775807 0 18446744073709551615 2^64bit 18位 整数
float 4B +-1.175494351E-38 +-3.402823466E+38 6位 浮点数
double 8B +-2.2250738585072014E-308 +-1.7976931348623157E+308 15位 浮点数
dec(m,d)/decimal(m,d) m+2B dec(-65) dec(65) 0 dec(65,0~30) 65位 定点数
bit(m) 1~8B BIT(1) BIT(64) 位类型
数字类型分为整型、浮点型、定点型、位类型。
1）整型
整型包括：tinyint、smallint、mediumint、integer(int)、bigint 5类。
超出类型范围时报1264越界，
指定显示宽度，如int(5),默认显示宽度为11。超出显示宽度时不会报错。
指定显示填充zerofill,即将显示宽度不足的数据用0来在左边填充。
指定无符号属性UNSIGNED,如果指定了填充zerofill，则默认为无符号UNSIGNED数。无符号属性UNSIGNED包含非负约束，当插入负数给无符号数时将报错。
指定自增属性AUTO_INCREMENT,每个表最多只能有一个自增列，自增列必须定义为非空not null,且需要定义为主键(PRIMARY KEY)或唯一键(UNIQUE),自增列从1开始每次增加1。
例子：
create table test1(id1 int auto_increment not null PRIMARY KEY,id2 int(5));
desc test1;
insert into test1(id2) values (111111); -- ok 超出显示宽度时不会报错
select * from test1;
alter table test1 modify id2 int(5) zerofill; -- ok int(5) unsigned zerofill 如果指定了填充zerofill，则默认为无符号UNSIGNED数。
insert into test1(id2) values (111112); -- ok
insert into test1(id2) values (113); -- ok 00113 将显示宽度不足的数据用0来在左边填充
insert into test1(id2) values (-114); -- 1264 越界 无符号属性UNSIGNED包含非负约束，当插入负数给无符号数时将报错
insert into test1(id2) values (4294967296); -- 1264 越界 超出类型范围时报1264越界
2）小数
小数包括：浮点型（单精度float和双精度double）和定点型（decimal）
定点数在Mysql内部以字符串形式存放，比浮点数更精确，适用于货币等高精度数据。
float(M,D)、double(M,D)、decimal(M,D)：M等于整数位数+小数位数；称为精度，D等于小数位数，称为标度；整数位数=M-D。
当整数位数越界时，系统将报错；当小数位数越界时，系统不会报错，当会将小数部分进行四舍五入。
float、double不指定精度时，按实际的精度显示；decimal不指定精度时默认为decimal(10,0)。
例子：
create table test2(id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
select * from test2;
insert into test2 values (1.23,1.23,1.23); -- ok
insert into test2 values (1.234,1.234,1.234); -- ok warning1265 truncated id3
alter table test2 modify id1 float;
alter table test2 modify id2 double;
alter table test2 modify id3 decimal; -- 小数部分全部被四舍五入 warning1265 truncated id3
desc test2; -- decimal恢复默认精度decimal(10,0)
insert into test2 values (1.234,1.234,1.234); -- ok id1和id2显示实际精度 id3 小数部分全部被四舍五入 warning1265 truncated id3
3）位类型bit(M)
用于存放多位二进制数，M取值1~64，默认为1。
对于位类型bit(M)字段可以用select查询结果，也可以用二进制函数bin()或十六进制函数hex()读取。
例子：
create table test3 (id bit);
desc test3;
insert into test3 values (1);
select * from test3; -- ok 显示
select id,bin(id),hex(id) from test3; -- ok 显示
insert into test3 values (2); -- 1406 data too long
alter table test3 modify id bit(2);
insert into test3 values (2); -- ok
select id,bin(id),hex(id) from test3; -- ok
3.2 日期时间类型
类型名 字节数 最小值 最大值 零值 备注
date 4B 1000-01-01 9999-12-31 0000-00-00 年月日
datetime 8B 1000-01-01 00:00:00 9999-12-31 00:00:00 0000-00-00 00:00:00 年月日时分秒
timestamp 4B 19700101080001 2038年某时刻 00000000000000 显示为'YYYY-MM-DD HH:MI:SS'固定19个字符
time 3B -838:59:59 838:59:59 00:00:00 时分秒
year 1B 1901 2155 0000 4位格式允许值1901~2155,2位格式允许值70~69(1970~2069)
每种日期时间类型都有一个有效值范围，如果超出范围系统可能报错，并使用零值进行存储。
now()函数用来获取当前日期时间。
CURRENT_TIMESTAMP函数用来获取当前日期时间(和时区相关)。
查看当前时区：show variables like 'time_zone'; -- SYSTEM表示和主机的时区值一致，中国是东八区+8:00
修改本地时区：set time_zone='+9:00';
3.2.1 date、time、datetime类型
date显示年月日，time显示时分秒，datetime显示年月日时分秒。
例子：
create table test21 (d1 date,d2 time,d3 datetime);
desc test21;
insert into test21 values (now(),now(),now()); -- warnings 1292
select * from test21;
3.2.2 timestamp类型
表中第一个timestamp类型的列会被系统自动设置为非空，并设置默认值为CURRENT_TIMESTAMP；
表中第二个timestamp类型的列会被系统自动设置为非空，并设置默认值为'0000-00-00 00:00:00',可以将默认值手动修改为CURRENT_TIMESTAMP；
给timestamp类型的列插入null时，自动更新为当前时间（包括第二个timestamp类型的列）；
timestamp和时区相关，插入时会将时间转换为本地时区的时间保存，查询时同样将时间转为本地时区的时间再显示。
例子：
create table test22 (d1 timestamp,d2 timestamp,d3 datetime);
desc test22; -- 'd1', 'timestamp', 'NO', '', 'CURRENT_TIMESTAMP', 'on update CURRENT_TIMESTAMP'
insert into test22 values (null,null,now()); -- 给timestamp类型的列插入null时，自动更新为当前时间（包括第二个timestamp类型的列）；
select * from test22;
alter table test22 modify d2 timestamp default current_timestamp; -- ok 表中第二个timestamp类型列的默认值可以手动修改为CURRENT_TIMESTAMP
show variables like 'time_zone'; -- 查看时区
set time_zone='+9:00'; -- 修改时区
select * from test22; -- 发现的d1和d2比d3早了一个小时
3.3 字符串类型
类型名 字节数 存储需求
char(M) M M为0~255之间的整数
varchar(M) M+1 M为0~65535之间的整数，值的长度+1个字节
tinyblob 允许长度0~255字节，值的长度+1个字节
blob 允许长度0~65535字节，值的长度+2个字节
mediumblob 允许长度0~16777215字节，值的长度+3个字节
longblob 允许长度0~4294967295字节，值的长度+4个字节
tinytext 允许长度0~255字节，值的长度+1个字节
text 允许长度0~65535字节，值的长度+2个字节
mediumtext 允许长度0~16777215字节，值的长度+3个字节
longtext 允许长度0~4294967295字节，值的长度+4个字节
varbinary(M) 允许长度0~M个字节的变长字节字符串，值的长度+1个字节
binary(M) M 允许长度0~M个字节的定长字节字符串
3.3.1 CHAR和VARCHAR类型
CHAR为定长字符串，长度取值0~255字节，按定义长度分配存储空间，在插入数据时，会自动删除尾部的空格；
VARCHAR为变长字符串，长度取值0~65535字节，按照实际长度+1分配存储空间，在插入数据时，保留尾部的空格。
例子：
create table test31 (v1 varchar(4),v2 char(4));
insert into test31 values ('abcd','abcd');
select * from test31;
desc test31;
insert into test31 values ('abcde','abcd'); -- 超长时都会提示 1406
insert into test31 values ('ab ','ab ');
select * from test31 where v2='ab'; -- CHAR类型会自动删除尾部的空格
select concat(v1,'+'),concat(v2,'+') from test31; -- VARCHAR类型保留尾部的空格
3.3.2 BINARY和VARBINARY类型
BINARY和VARBINARY类似于char和varchar，
区别在于：BINARY和VARBINARY用来存储二进制字符串，char和varchar存储文本字符串。
例子：
create table test32 (v1 VARBINARY(2),v2 BINARY(2));
insert into test32 values ('a','a');
select *,hex(v1),hex(v2),bin(v1),bin(v2) from test32;
insert into test32 set v1='b',v2='b';
select *,hex(v1),hex(v2),bin(v1),bin(v2) from test32;
3.3.3 ENUM类型
ENUM枚举类型，枚举类型值的取值范围需要在建表时显示指定，
对于小于等于255种取值的枚举类型需要1个字节存储；
对于大于255种取值且小于等于65535种取值的枚举类型需要2个字节存储；
最多只允许65535种取值，否则将不能存储。
例子：
create table test33 (gender enum('M','F'));
insert into test33 values ('M'), ('F'),('m'),('f'),('1'),('2'),(1),(2),(null); -- 字母不区分大小写，数字代表取值的顺序，数字可加引号也可不加
select * from test33;
3.3.4 SET类型（位图存储）
SET集合类型包含0~64个成员，每个成员用一个bit表示，每8个成员占用一个字节，共占用8个字节。
SET集合类型一次可以选择多个成员，ENUM枚举类型则只能选择一个。
例子：
create table test34 (s set('a','b','c','d'));
insert into test34 values ('a,b'), ('a,c,d'); -- ok
insert into test34 values ('A,C'),('D'); -- ok 字母不区分大小写
insert into test34 values ('1,3,2'),('2,3'); -- 1265 不能使用数字代表取值的顺序
insert into test34 values (1,4),(2,1); -- 1136 列数量不匹配
insert into test34 values (null); -- ok
select * from test34;
insert into test34 values ('a,f'); -- 1265 当元素不在允许值集合中就不能保存
insert into test34 values ('a,b,a'); -- ok 保存结果'a,b'
SET类型可以从允许值集合中选择任意1个或多个元素进行组合；
元素只要在允许值集合中就可以正常保存；
当元素不在允许值集合中就不能保存；
当元素在允许值集合中且重复时就去重后保存。
3.4 decimal详解
decimal(m,d)类型是定点数，
m最大取值65，默认取值10；
d最大取值30，默认取值0；
decimal最大值为decimal(65)；
select func_decimal_65(12345678901234567890123456789012345678901234567890123456789012345); -- ok
select func_decimal_65(123456789012345678901234567890123456789012345678901234567890123456); -- 1264 越界
select func_decimal_65(-12345678901234567890123456789012345678901234567890123456789012345); -- ok，65位不包括负号
select func_decimal_65(-12345678901234567890123456789012345678901234567890123456789012345.6789); -- ok，小数部分定义的长度为0，将被四舍五入
select func_decimal_default(1234567890); -- ok
select func_decimal_default(12345678901); -- 1264 越界
select func_decimal_default(-1234567890); -- ok，负号不影响位数
select func_decimal_default(-1234567890.123); -- ok,小数将被四舍五入
select func_decimal_m65_d30(12345678901234567890123456789012345.123456789012345678901234567890); -- ok
select func_decimal_m65_d30(123456789012345678901234567890123456.123456789012345678901234567890); -- 1264 越界 整数位数=m-d=65-30=35位
select func_decimal_m65_d30(-12345678901234567890123456789012345.123456789012345678901234567890); -- ok，负号不影响位数
select func_decimal_m65_d30(-12345678901234567890123456789012345.1234567890123456789012345678905); -- ok，小数越界将被四舍五入
use scott;
DROP function IF EXISTS `func_decimal_65`;
DROP function IF EXISTS `func_decimal_default`;
DROP function IF EXISTS `func_decimal_m65_d30`;
DELIMITER $$
create function func_decimal_65(p_type decimal(65)) RETURNS decimal(65)
begin
/*
功能：测试decimal数据的有效范围
*/
DECLARE l_cnt decimal(65);
select p_type into l_cnt;
return l_cnt;
end $$
create function func_decimal_default(p_type decimal) RETURNS decimal
begin
/*
功能：测试decimal数据的有效范围
*/
DECLARE l_cnt decimal;
select p_type into l_cnt;
return l_cnt;
end $$
create function func_decimal_m65_d30(p_type decimal(65,30)) RETURNS decimal(65,30)
begin
/*
功能：测试decimal数据的有效范围
*/
DECLARE l_cnt decimal(65,30);
select p_type into l_cnt;
return l_cnt;
end $$
DELIMITER ;
