mysql学习总结02 — 数据类型

mysql学习总结02 — 数据类型

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


注意点：

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

4. 字符和文本型

4.1 字符型

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

注意点：

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

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

存储数据	char(2)	varchar(2)	char所占字节	varchar所占字节
A	A	A	2 * 3 = 6	1 * 3 + 1 = 4
AB	AB	AB	2 * 3 = 6	2 * 3 + 1 = 7

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个字节    大型文本

注意点：

1. 在选择对应的存储文本时，不用刻意选择text类型，系统会自动根据存储的数据长度选择合适的文本类型
2. 在选择字符存储时，如果数据超过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选中) 后的十进制值

小提琴	小号	乒乓球	游泳	羽毛球	编程	轮滑	烹饪
1	0	1	0	0	1	0	0

==> 10100100 ==> 164

select * from tbTest;

hobby
小提琴,乒乓球,编程

select hobby + 0 from tbTest;

hobby + 0
164

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

1字节 => 8个选项

2字节 => 16个选项

3字节 => 24个选项

8字节 => 64个选项