2-14-2 MySQL数据类型

MySQL数据类型:

对数据进行分类,针对不同分类进行不同的处理.

1. 使系统能够根据数据类型来操作数据.

2. 预防数据运算时出错.

3. 更有效的利用空间。

数据分类，可以使用最少的存储，来存放数据，同时提高了性能。

定义数据类型的本质上是在定义列

MySQL数据类型分类:

两大类: 整型和浮点型;

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127) • 2的n-1次方-1（有符号的范围） 2^7-1 • 2的n次方-1（无符号的范围）2^8-1	(0，255) 2^8-1	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535) 2^16-1	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)2^32-1	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值

##########################################################

类型	大小范围	用途	存储需求
CHAR(M)	0-255 bytes	定长字符串	M bytes 1<=M<=255
VARCHAR(M)	0~65535 bytes	变长字符串	L+1 bytes L<=M且1<=M<=255
TINYBLOB小二进制	0-255 bytes	不超过 255 个字符的二进制字符串	L+1 bytes L<2⁸
TINYTEXT	0-255 bytes	短文本字符串	L+1 bytes L<2⁸
BLOB大二进制	0-65 535 bytes	二进制形式的长文本数据	L+2 bytes L<2¹⁶
TEXT	0-65 535 bytes	长文本数据	L+2 bytes L<2¹⁶
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据	L+3 bytes L<2²⁴
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据	L+3 bytes L<2²⁴
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据	L+4 bytes L<2³²
LONGTEXT	0-4 294 967 295 bytes	极大文本数据	L+4 bytes L<2³²
ENUM	1~2 bytes	枚举类型	1 or 2 bytes
SET	1~8 bytes	一个设置	1/2/3/4/8 bytes，取决于集合成员数量

整型<INT 类型>

在MySQL中支持的5个主要整数类型是 TINYINT，SMALLINT，MEDIUMINT， INT和BIGINT。这些类型在很大程度是相同的，只有他们存储的值的大小是不同的

整型属性<修饰符>：

unsigned 无符号整数，修饰符：规定字段只能保存正的数据。它可以增大这个字段的正数支持的范围。

tinyint unsigned 可以存储的范围是0-255，而tinyint的存储范围是-128-127

zerofill 修饰符：规定0（不是空格）填补输出的值。使用这个值可以防止 mysql存储负值。

int(M) 在 integer 数据类型中，M 表示最大显示宽度。

在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。其实，除了显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的。M值最大255

如果int的值为10 ，使用zerofill修饰后：

int（10）显示结果为0000000010

int（3）显示结果为010

就是显示的长度不一样而已都是占用四个字节的空间，可以使用的空间也一样。

默认使用空格填充，不方便显示出来。

注：当我们产成固定长度的序列号时，可以使用zerofill。如：卡号

浮点型

主要有float<单精度>, double<双精度>,decimal<精确指定>

Float和double是浮点型，数值越大越不精确

Decimal是定点数，常用来存储财务工资方面，高精度

float(3,1) ：表示此字段有效位数为3位，整数占两位，小数点后面占1位。小数点后超过1位，mysql自动四舍五入。占4个字节存储

double ：8个字节来存储

decimal: 用来存储精确的小数，消耗空间，运算慢，一般想办法使用bigint替代它

9823912.382 mm = X * 1000 = 9823912382

Select mm/1000 from table

字符串类型

Char（m）

保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。

Varchar（n）

保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。

TEXT类型存放最大长度为 65,535 个字符的字符串

区别：

char 比varchar占空间，但效率要好

空间：char固定长度，小于长度，空格自动补齐,大于长度,自动截短.没有varchar节省空间

效率：varchar修改数据时，由于长度是动态的，会导致数据迁移，增加IO

字符串类型修饰符：

binary 修饰符: 区分字符大小。比较char类型以一个二进制方式起作用。

总结：char和varchar区别：

CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。

VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。

VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。

例：

VARCHAR(10)，实际长度是它的值的实际长度+1。为什么"+1"呢？这一个字节用于保存实际使用了多大的长度。如果列声明的长度超过255，则使用两个字节。

从空间上考虑，用varchar合适；从效率上考虑，用char合适。

对于不同存储引擎有不同的说法：

对于MyISAM表，尽量使用Char，对于那些经常需要修改而容易形成碎片的myisam和isam数据表就更是如此，它的缺点就是占用磁盘空间；

　　对于InnoDB表，因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分（所有数据行共用一个表头部分，这个表头部分存放着指向各有关数据列的指针），所以使用char类型不见得会比使用varchar类型好。事实上，因为char类型通常要比varchar类型占用更多的空间，所以从减少空间占用量和减少磁盘i/o的角度，使用varchar类型反而更有利.

TEXT类型

•TEXT类型及其子类型用于存储比较长的非二进制字符串。例如：

•文章

•评论

•你可以把TEXT类型理解为VARCHAR的加长增强版。

•TEXT类型不区分大小写。

复合类型

它们字段的值，必须从预先定义好的字符串集合中选取。

ENUM（枚举）： ENUM类型存储的数据好比是单项选择题里的选项。其存储的值为表创建时在列规定中枚举的一列值。

只能取一个。用于互斥。男人，女人。

create table ckdata8(sex enum('M','F'));

Set类型：能选取多个

create table ckdata9(type set('a','b','c','d','e'));

# 不能有一样的元素，有的话，无效。

# 如果不存在些元素，以空白来表示

注：set 类型：最包64类项。在set中，相关的元素不能同时存在。

日期和时间类型

date类型

日期。格式：YYYY-MM-DD

注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'

Create table ckdata10 (riqi date)

mysql> insert into ckdata10 values('2015-10-23'),(20180925);

mysql> insert into ckdata10 value('2015-10-23'),(20180925);

两者区别不大，一般还是用values

time类型

year类型

datetime类型timestamp类型

datetime类型能保存大范围的值，从1001年到9999年，精度为秒，它把日期和时间封装到格式为YYYY-MM-DD HH:MM:SS的整数中，与时区无关,使用8个字节的存储空间

timestamp类型保存了从1970年1月1日午夜（格林尼治时间）以来的秒数

它和linux时间戳相同，timestamp只使用了4个字节的存储空间，因此它的范围比datetime小的多，只能表示从1970-2038，通常我们还是用它的，提高空间利用率

常用总结:

整型

tinyint,占1字节,有符号：-128~127,无符号位:0~255

smallint,占2字节,有符号：-32768~32767,无符号位:0~65535

mediumint,占3字节,有符号：-8388608~8388607,无符号位:0~16777215

int,占4字节,有符号：-2147483648~2147483647,无符号位:0~4284967295

bigint,占8字节

bool 等价于tinyint(1) 布尔型

浮点型

float([m[,d]]) 占4字节,1.17E-38~3.4E+38

double([m[,d]]) 占8字节

decimal([m[,d]]) 以字符串形式表示的浮点数

字符型

char([m]):定长的字符,占用m字节

varchar[(m)]:变长的字符,占用m+1字节，大于255个字符：占用m+2

tinytext,255个字符(2的8次方)

text,65535个字符(2的16次方)

mediumtext,16777215字符(2的24次方)

longtext,(2的32次方)

enum(value,value,...)占1/2个字节最多可以有65535个成员

set(value,value,...)占1/2/3/4/8个字节，最多可以有64个成员

扩展：linux时间戳

显示时间戳：

[root@server01 ~]# date +%s

1444124814

显示指定时间的时间戳

[root@server01 ~]# date -d "2015-10-06 17:48:23" +%s

1444124903

[root@server01 ~]# date -d "1970-01-01 08:00:00" +%s

时间戳转日期

[root@server01 ~]# date -d"@1455455465"

Sun Feb 14 21:11:05 CST 2016

[root@server01 ~]# date -d"@1455455465" "+%Y/%m/%d %H:%M:%S"

2016/02/14 21:11:05

[root@server01 ~]# date -d"@1455455465" "+%Y-%m-%d %H:%M:%S"

2016-02-14 21:11:05