数据库

mysql学习总结02 — 数据类型

csxiaoyao · 6月24日 · 2018年 本文4057字 · 阅读11分钟201

mysql学习总结02 — 数据类型

Write By CS逍遥剑仙
我的主页: csxiaoyao.com
GitHub: github.com/csxiaoyaojianxian
Email: sunjianfeng@csxiaoyao.com
QQ: 1724338257

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个选项

mysql学习总结02 — 数据类型-禅林阆苑

0 条回应

×