(2.5)Mysql之SQL基础——数据类型
(2.5)Mysql之SQL基础——数据类型
关键词:mysql数据类型
一、整数型
额外参数示例:
int [(n)] [unsigned] [zerofill]
【1】int(4) :显示的宽度,默认为11位,这个并不影响正常情况的显示和存储(不会影响宽度大于这个定义的值)。
当int字段类型设置为无符号且填充零(UNSIGNED ZEROFILL)时,当数值位数未达到设置的显示宽度时,会在数值前面补充零直到满足设定的显示宽度。
为什么会有无符号的限制呢,是因为ZEROFILL属性会隐式地将数值转为无符号型,因此不能存储负的数值。
【2】UNSIGNED :无符号数据类型
【3】ZEROFILL:0填充,当数据位不满定义长度时,会进行0填充,如int(4),如果列数据值为1,则会显示为0001.(该选项必定是无符号的)
二、小数型(以下均不能使用无符号)
【1】定点:decimal (每位小数占一个字节,必须写MD) 【2】浮点:float(4字节)、double(8字节)(可以不写MD)
使用:decimal/float/double(M,D) ,M为总位数,D为小数位数。比如当M为10D为2时,则为 8位整数即2位小数。
float(M,D) :如果不写MD,则自动处理M为7左右
double(M,D) :如果不写MD,则自动处理M为15左右
注意:
【1】如果数据不满足定义的小数位D,则后置补0。
例如:float(5,3) ,数值为1.1 则自动填充为 1.100
【2】如果前置不满足M且使用了zerofill,则只有1-9会前置填充1个0,否则不填充(定点与浮点有区别);
浮点数:float(6,2) zerofill 当值为1.1 -》 01.10 当值为12.1 -》 12.10
定点数:decimal(6,2) zerofill 当值为1.1 -》 0001.10 当值为12.1 -》 00012.10
【3】如果数值的小数位大于D的定义,则会四舍五入;
例如:float(5.2) 当值为1.236 -》 1.24
三、日期时间型
四、字符串型
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255字节 | 定长字符串 |
VARCHAR | 0-65535 字节 | 变长字符串 |
TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255字节 | 短文本字符串 |
BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
TEXT | 0-65 535字节 | 长文本数据 |
MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
ENUM | 0~65535 | 枚举类型 |
SET | 集合类型 |
注意
(1)字符集的区别:【1】UTF8:1个字符占用3个字节 【2】GBK:1个字符占用2个字节
(2)char与varchar:【1】char:会自动删除后置空格(但会保留前置)
五、char与varchar的区别
【5.1】概念
char是一种固定长度的类型,varchar则是一种可变长度的类型
char(M)类型的数据列里,每个值都占用M个字节,如果某个长度小于M,MySQL就会在它的右边用空格字符补足.(在检索操作中那些填补出来的空格字符将被去掉)
在varchar(M)类型的数据列里,每个值只占用刚好够用的字节再加上一个用来记录其长度的字节(即总长度为L+1字节)
举例:
char是固定长度的,而varchar会根据具体的长度来使用存储空间。比如char(255)和varchar(255)。
在存储字符串"hello world"的时候,char会用一块255的空间放那个11个字符。
而varchar就不会用255个,他先计算长度后只用11个再加上计算的到字符串长度信息,一般1-2个byte来,这样varchar在存储不确定长度的时候会大大减少存储空间。
【5.2】到底应该怎么选择char与varchar呢
上面介绍看来varchar比char聪明多了,那char有用武之地吗?还是很不少优势的。
一,存储很短的信息,比如门牌号码101,201……这样很短的信息应该用char,因为varchar还要占个byte用于存储信息长度,本来打算节约存储的现在得不偿失。
二,固定长度的。比如使用uuid作为主键,那用char应该更合适。因为他固定长度,varchar动态根据长度的特性就消失了,而且还要占个长度信息。
三,十分频繁改变的column。因为varchar每次存储都要有额外的计算,得到长度等工作,如果一个非常频繁改变的,那就要有很多的精力用于计算,而这些对于char来说是不需要的。
【5.3】varchar() 里面的宽度是不是越大越好?
还有一个关于varchar的问题是,varchar他既然可以自动适应存储空间,那我varchar(8)和varchar(255)存储应该都是一样的,那每次表设计的时候往大的方向去好了,免得以后不够用麻烦。
这个思路对吗?答案是否定的。mysql会把表信息放到内存中(查询第一次后,就缓存住了,linux下很明显,但windows下似乎没有,不知道为啥),这时内存的申请是按照固定长度来的,如果varchar很大就会有问题。所以还是应该按需索取。
较大的列使用更多的内存,因为MySQL通常会分配固定大小的内存块来保存值,这对排序或使用基于内存的临时表尤其不好。同样的事情也会发生在使用文件排序或者基于磁盘的临时表的时候。
比如:MySQL建立索引时如果没有限制索引的大小,索引长度会默认采用的该字段的长度,也就是说varchar(100)建立的索引存储大小要比varchar(10)建立索引存储大小大的多,加载索引使用的内存也更多。
【6】数据类型转换
select -- 数值 -> 字符 -- char(n) n 个长度的字符,超过截取 convert(2022, char(3)) c1, -- 202 convert(2022, char(4)) c2, -- 2022 convert(2022, char(5)) c3, -- 2022 -- 字符 -> 数值 convert('-12.56', SIGNED) s1, -- -12 有符号整数 convert('12.56', UNSIGNED) u1, -- 12 无符号整数 convert('12.56', decimal(3, 1)) dc1, -- 12.6 浮点数,四舍五入 -- 字符 -> 日期 convert('2021-05-20 13:14:00', datetime) d1, -- 日期+时间 convert('2021-05-20 13:14:00', date) d2, -- 仅日期 convert('2021-05-20 13:14:00', time) d3, -- 仅时间 str_to_date('2021-05-20 13:14:00', '%Y-%m-%d %H:%i:%S') d4, str_to_date(now(), '%Y-%m-%d') d5, -- 日期截取 -- 日期 -> 字符 convert(now(), char) c4, date_format(now(), '%Y-%m-%d %H:%i:%S') c5 -- 注意大小写 from dual;