MySQL数据类型

a. 整型

整型	占字节大小（单位byte）	数值范围（unsigned）	数值范围（signed）
tinyint	1	(0，255)	(-128，127)
smallint	2	(0，65 535)	(-32 768，32 767)
mediumint	3	(0，16 777 215)	(-8 388 608，8 388 607)
int	4	(0，4 294 967 295)	(-2 147 483 648，2 147 483 647)
bigint	8	(0，18 446 744 073 709 551 615)	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)

整型应用细节：

。生产中应该选择哪种类型

在满足业务所需数值范围的情况下，选择占用字节更少的类型。可以节省内存空间

。int(11) 、tinyint(4)与 zerofill(0填充)的问题

只是显示属性，没有实际意义，只表示宽度，不会影响字段的值

alter table t1 modify a tinyint zerofill not null;

a tinyint(3) unsigned zerofill NOT NULL

负数加zerofill是不被允许的

 

。int类型转换为bigint注意事项

如果该列是主键，则无法使用onlineddl

如果不是主键，支持onlineddl

onlineddl：执行ddl语句，可以支持dml语句，不会阻塞dml语句

 

。ipv4地址选择什么类型存放

建议使用int类型存储。

inet_aton('ip') 将ip地址转为整型select inet_aton('255.255.255.255');

inet_ntoa('int') 将整型转为ip地址 select inet_ntoa('4294967295');

 

 

b.浮点型

浮点型	占字节大小（单位byte）	精度
float	4	单精度
double	8	双精度
decimal	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	高精度

浮点型应用细节：

。生产中浮点数如何选择？

凡是涉及到资金的，建议选择decimal。

。测试环境中插入随机字符？

floor() :向下取整 select floor('1.23');

ceil()：select ceil('1.23');

round()：select round(0.54); select round(0.5354, 2);

rand()：取0-1随机值 select rand();

公式：floor(i+rand()(j-i)) 随机取i-j整数 select floor(1+rand()(10-1));随机取1-10

insert into t2 values(repeat('a', floor(1+rand()*(30-1))));

 

。float长度细节

float没有定义长度，默认占4字节

float(p) , 0<=p<=24,占4字节

25<=p<=53,占8字节

 

。float列不指定精度时，不能做等值查询

 

等值查询结果为空。

。decimal最大长度

decimal(M,D) M最大值65，D最大值30。

 

c.字符串类型

字符串类型	说明	N的含义	是否有字符集	最大长度
char(n)	定长字符	字符	是	255
varchar(n)	变长字符	字符	是	65535
binary(n)	定长二进制字节	字节	否	255
varbinary(n)	变长二进制字节	字节	否	65535
tinyblob	二进制大对象	字节	否	256
blob	二进制大对象	字节	否	16k
mediumblob	二进制大对象	字节	否	16M
longblob	二进制大对象	字节	否	4G
tinytext	大对象	字节	是	256
text	大对象	字节	是	16k
mediumtext	大对象	字节	是	16M
longtext	大对象	字节	是	4G

字符串类型应用细节：

。varchar是变长类型，需要额外的1-2个字节

varchar(30) <255 额外占用1字节，>255额外占2字节

 

。char(30)、varchar(30)，在utf8字符集下，占字节数计算

char(30) 占用字节数=30*3

varchar(30) 占用字节数=30*3+1

 

。varchar实际长度超过255字节时，会被作为text处理

 

。varchar长度越长排序时使用更多的内存空间，所以varchar类型的字段长度越短越好

 

。字段超长可能会造成页溢出（off page）

page 16k，字段超过page大小的一半，就会发生off page

 

。大文本大字段尽量不要去使用，业务上必须有的话，可以存放到独立子表中

 

。对text列进行排序，只会用到max_sort_length字节去作排序

 

。enum() 枚举类型，细节

enum(v_list), v_list最多可以是65535个，如果不超过255占1字节，超过255占2字节。

 

d.日期类型

日期类型	大小	范围
datetime	8	1000-01-01 00:00:00/9999-12-31 23:59:59
timestamp	4	结束时间是北京时间 2038-1-19 11:14:07
date	3	1000-01-01/9999-12-31
time	3	'-838:59:59'/'838:59:59'
year	1	1901/2155

日期类型应用细节：

。timestamp，当行被更改时，会自动更新为当前时间

5.6.5之后datetime也可以设置自动更新为当前时间

alter table t3 modify d datetime default current_timestamp on update current_timestamp;

 

。5.6.4之后底层都是以整型存储，效率高，推荐使用datetime

。日期函数

now() 当前时间select now();

cast(now() as date) 时间转换 select cast(now() as date);