mysql学习总结02 — 数据类型

mysql学习总结02 — 数据类型

toc

1. 整数型

tinyint		最小整型	0-255(-128 ~ +127)	1个字节(8位)
smallint	小型整数	0-65535				2个字节
mediumint	中型整数	0-1677万				3个字节
int			标准整型	0-21亿				4个字节
bigint		最大整数	0-42亿				8个字节

1.1 无符号标识设定

注意：mysql默认为整形增加负数，例如 tinyint 实际表示的区间为 -128 ～ 127

无符号：表示存储的数据在当前字段中，没有负数(只有正数，例如 tinyint 区间为 0～255)

mysql> alter table sunshine add field1 tinyint unsigned;

1.2 显示长度 & zerofill

显示长度：指数据(整型)在数据显示的时候，可以显示的位数。显示长度只表示数据可以达到指定的长度，但不会自动满足到指定长度。如果想要数据的显示保持最高位(显示长度)，需要给字段增加 zerofill 属性。

tinyint(3)：表示最长可以显示3位，unsigned说明只能是正数，0~255不会超过三个长度tinyint(4)：表示最长可以显示4位，-128~127

zerofill：从左侧开始填充0 (左侧不会改变数值大小)，所以负数不能使用zerofill，即该字段为unsigned。

mysql> alter table sunshine add field2 tinyint zerofill;

显示长度可以自己设定，超出长度(但是不超出范围)不会影响，只会配合zerofill对长度不够的进行补充

mysql> alter table sunshine add field3 tinyint(2) unsigned zerofill;

2. 小数型

2.1 浮点型

float	单精度		4个字节(精确到小数点后7位)		大约10^38
double	双精度		8个字节(精确到小数点后15位)	大约10^308

如果数据精度丢失，浮点型按四舍五入计算

float		表示不指定小数位的浮点数
# 举例：输入9e5，浮点数可以采用科学计数法来存储数据
float(M,D)	表示共存储M个有效数字，其中小数部分占D位
# 举例1：float(6,2) 总长度为6位，小数位数为2位，小数点不算。存的最大值为9999.99
# 举例2：float(10,2) 插入12345678.90，实际存入12345679.00，精度大约为7位
# 举例3：float(10,2) 插入123456789.00，报错，超出大小，用户不能直接插入超过指定整数部分长度的数据
# 举例4：float(10,2) 插入99999999.99，实际存入100000000.00，输入数据长度刚好满足条件，但是会超出精度，此时导致的系统自动进位系统可以承担

double(M,D)	双精度(精确到小数点后15位)	M代表长度，D代表小数位数

2.2 定点数

定点数能够保证数据精度，整数部分一定精确，小数部分可能不精确(超出长度会四舍五入)。系统自动根据存储的数据分配存储空间，每9个数会分配4个字节进行存储，同时小数和整数部分分开

decimal(M,D)	M(<=65)表示总长度，D(<=30)表示小数部分长度
# 举例1：decimal(10,2) 插入12345678.90，正确插入
# 举例2：decimal(10,2) 插入99999999.99，正确插入
# 举例3：decimal(10,2) 插入99999999.999，报错，定点数如果整数部分进位超出长度会报错

3. 日期时间型

date 日期型 3个字节 插入格式"YYYY-mm-dd" 1000-01-01~9999-12-12 初始值0000-00-00

time 时间型 3个字节 插入格式"HH:ii:ss" -838:59:59~838:59:59

datetime 日期时间型 8个字节 插入格式"YYYY-mm-dd HH:ii:ss"

1000-01-01 00:00:00 ~ 9999-12-12 23:59:59

可为0值 0000-00-00 00:00:00

timestamp 时间戳表示从格林威治时间开始，但格式依然为"YYYY-mm-dd HH:ii:ss"

year 年类型 1个字节 1900~2155

注意点：

timestamp 不能为空，默认为当前时间 CURRENT_TIMESTAMP，当数据更新时这个字段自动更新为当前最新时间 year 有两种数据插入方式：0~99 和 四位数具体年份，两位数插入时有一个区间划分( year <=69 ? '20'+year : '19'+year ) time 本质是用来表示时间区间(xxx个小时)，所以可表示范围较大 -838:59:59~838:59:59 time 录入时可使用一个简单的日期(可为负数，*24)代替时间，例如：'5 12:12:12' => '132:12:12' PHP中有强大的时间日期转换函数：date将时间戳转换成各种格式，strtotime将很多格式转换成时间戳，PHP通常不需要数据库来处理复杂的时间日期，所以通常配合PHP的时候，通常使用整型来保存时间戳

4. 字符和文本型

4.1 字符型

char(L)	  	0-255	 固定长度的字符串   如：邮编、手机号码、电话号码等
varchar(L) 	0-65535  可变长度的字符串   如：新闻标题、家庭地址、毕业院校等

注意点：

如果数据长度超过255个字符，不论是否固定长度，都会使用text，不再使用char和varchar char的长度L不区分中英文 varchar需要记录数据长度(系统根据数据长度自动分配空间)，所以每个varchar数据产生后，系统都会在数据后面增加1-2个字节的额外开销用来保存数据所占用的空间长度。如果数据本身小于127个字符：额外开销一个字节，如果大于127个，开销两个字节 char的数据查询效率比varchar高

char 和 varchar 数据存储对比 (utf8字符占用3字节)

4.2 文本型

数据长度超过255个字符不再使用char和varchar，使用text

text	存储普通字符文本
blob	存储二进制文本(图片/文件)，一般不使用blob存储文件本身，通常存链接

text分四种，一般使用text

tinytext	0-255		2^8+1		1个字节	小型文本
text        0-1670万		2^16+2		2个字节	普通文本
mediumtext  0-2^24+3	2^24+3		3个字节	中型文本
longtext    0-42亿     	2^32+4		4个字节	大型文本

注意点：

在选择对应的存储文本时，不用刻意选择text类型，系统会自动根据存储的数据长度选择合适的文本类型 在选择字符存储时，如果数据超过255字符，一定选择text存储

mysql记录长度

mysql中规定记录长度 (record == row) 总长度不能超过65535字节，varchar能够存储的理论值为65535字符，字符在不同的字符集下占用多个字节，且需要额外的字节来保存长度

utf8 ==> (65535 - 2) / 3 = 21844

gbk ==> (65535 - 2) / 2 = 32766

5. enum & set

5.1 enum枚举类型 (单选)

基本语法：enum(数据值1,数据值2,…)

系统提供1-2字节存储枚举数据，通过计算enum列举的具体值选择实际存储空间：如果数据值列表 <=255 个，分配1字节，如果 > 255 && <=65535，分配2字节

mysql> create table tbTest (
          gender enum('男','女','保密')
       ) charset utf8;
mysql> insert into tbTest values('男');
mysql> insert into tbTest values('女');

实际字段上存储的值并不是真正的字符串，而是字符串对应的下标。设定枚举类型时，会给枚举中每个元素定义一个下标，这个下标规则从1开始

Enum(1=>‘男’,2=>’女’,3=>’保密’)

select * from tbTest;

| gender |

| :----: |

| 男 |

| 女 |

| 男 |

由于实际enum字段存储的结果是数值，数据插入时可以使用对应的数值

特性：mysql自动进行类型转换，如果数据遇到 " + - * /" 会自动将数据转成数值，而普通字符串会转换成数值0

select <字段名> + 0 from <表名>;

select gender + 0 from tbTest;

| gender + 0 |

| :--------: |

| 1 |

| 2 |

| 1 |

5.2 set集合类型 (多选)

基本语法：set( '值1', '值2', '值3', … )

mysql> create table tbTest (
          hobby set('小提琴','小号','乒乓球','游泳','羽毛球','编程','轮滑','烹饪')
       ) charset utf8;
mysql> insert into tbTest values('小提琴,乒乓球,编程');

本质是存储使用二进制位控制选取 (0未选中 1选中) 后的十进制值

==> 10100100 ==> 164

select * from tbTest;

| hobby |

| :----------------: |

| 小提琴,乒乓球,编程 |

select hobby + 0 from tbTest;

| hobby + 0 |

| :-------: |

| 164 |

系统为set提供了多个字节进行保存，系统自动选择具体的存储单元

1字节 => 8个选项

2字节 => 16个选项

3字节 => 24个选项

8字节 => 64个选项