MySQL学习之数据类型

MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型

1、数值数据类型：整数类型(TINYINT/SAMLLINT/MEDIUMINT/INT/BIGINT)、浮点小数类型（float、double）、定点小数类型（decimal）——对精度要求高时用Decimal

2、日期/时间类型：YEAR/TIME/DATE/DATETIME/TIMESTAMP

3、字符串类型：CHAR/VARCAHR/BINARY/VARBINARY/BLOG/TEXT/ENUM/SET等。

补充：id int(11),这里的11表示的是数据类型指定的显示宽度，数值中的数字个数，显示宽度与数据类型无关。数值位数小于宽度空格补齐，数值宽度大于显示宽度，依旧可以插入，总之只要不超过数据类型的范围，就可以进行插入。

时间日期类型

类型名称	日期格式	存储需求
YEAR	YYYY	1字节
TIME	HH:MM:SS	3字节
DATE	YYYY-MM-DD	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	4字节

非法的year会转换为0000

1、year

1）以4位字符串或者4位数字格式表示的year，范围‘1901’~‘2155’

2）以2位字符串格式表示的year,f范围‘00’~‘69’、‘70’~‘99’的范围分别被转换为2000~2069、1970~1999

3）以2位数字表示的year，范围1~99,1~69/70~99分别被转换为2001~2069/1970~1999

Create table table_name(y yeart);

Insert into table_name values(2010),(‘2010’);//两者都是插入了2010年

Delete from table_name;

Insert into table_name values(0),(78),(11);

 

 

2、TIME(用于时间信息值）

1) ’D HH:MM:SS’格式的字符串。还有’非严格’的语法’HH:MM:SS’/’HH:MM’/’D HH:MM’/’D HH’/’SS’(这里D表示日，取值范围0~34，通常转换为小时保存即D*24+HH。

2) TIME列的分配表示简写应注意：没有冒号时靠右对齐，有冒号时靠左对齐。（1112表示00:11:12,11:12表示11:12:00）

实例：

Create table tmp4(t time);

Insert into  tmp4 values(‘10:50:05’),(‘23:23’),(‘2 10:10’),(‘3 02’),(‘10’);

Insert into tmp4 values(current_time),(now());

 

3、DATE类型

Date 类型用在日期时，没有时间部分，在存储的时候仅需三个字节。日期格式’YYYY-MM-DD’,可以使用字符串类型输入或者数字类型的数据输入。

1）’YYYY-MM-DD’/’YYYYMMDD’

2）‘YY-MM-DD’/’YYMMDD’

3）YY-MM-DD/YYMMDD(第二种情况和第三种情况00~69转换为2000~2039,70~79转换为1970~1979.

4）使用current_date/now()指定当前系统的时间。

Create table tmp5( d date);

Insert into tmp5 values(‘1996-11-27’),(‘19970124’),(‘19950218’);

Select *  from tmp5;

 

4、DATETIME（同时包含日期和时间信息）

1）’YYYY-MM-DD HH:MM:SS’、’YYYYMMDDHHMMSS’字符串格式，取值范围：‘1000-01-01 00:00:00’~‘9999-12-3 23:59:59’

2）‘YY-MM-DD HH:MM:SS’/’YYMMDDHHMMSS’

3）YYYYMMDDHHMMSS/YYMMDDHHMMSS数字格式表示的日期和时间

 

5、TIMESTAMP

日期格式：YYYY-MM-DD HH:MM:SS

Create table tmp6(ts timestamp);

Insert into tmp6

values(‘19961127010101’),(‘950218010101’),(‘970124010101’),(now()),(121212121212);

 

文本字符串类型

1、char(固定长度字符串类型）和varchar（变长字符串类型）

 

2、TEXT类型（保存非二进制字符串）

 

3、ENUM类型（字段名 enum(‘值1’,‘值2’,‘值3’,.......‘值n’));）

（补充：ENUM有默认值。如果enum列声明为null则默认值为null；如果声明为not null则默认值为第一个元素；enum值在内部用整数表示，每个枚举值就有个索引值，列表所允许的编号从1开始）

Create table table_ name(score int,level enum(‘excelent’,’good’,’bad’));

Insert into table_name values(70,’excelent’),(90,1),(73,2),(23,3);

 

4、set类型（一个字符串对象，可以有零个或者多个值）也采用内部整数表示，每个枚举就有个索引值。如果插入重复列值，会自动删除重复的列值，set字段的顺序按定义的顺序显示，如果插入的不正确的值，默认忽视并发出警告。

 

二进制字符串类型(存储文本的字符串类型）

BIT/BINARY/VARBINARY/INTBINARY/TINYBLOB/BLOB/MEIDUMBLOB/LONGBLOB

1) BIT 位字段类型（bit(m)）m表示每个值的位数

Create table table_name(b bit(4));

Insert into table_name values(2),(9),(15);

Select bin(b+0) from table_name;//b+0是将对应的二进制转换为对应的数字值，bin()函数将数字转换为二进制。默认情况下，MySQL不可插入超出范围的值。

 

2) BINARY和VARBINARY类型

BINARY类型长度固定，不足右边填充’\0’补齐。

VARBINARY类型长度可变，实际占用的长度为字符串实际长度加1.

Create table table_name (

b binary(3),vb varbinary(30));

Insert into table_name values(5,5);

Select lenght(b),length(vb) from tmp13;

 

b字段不足会填充，vb字段没有填充。

4）BLOB类型（一个二进制大对象，用于存储可变数量的数据）

TINYBLOB/BLOB/MEDIUMBLOB,它们可以容纳值得最大长度不同。

BLOB列没有字符集，并且排序和比较基于列值字节的数值。

TEXT列有一个字符集，根据字符集对值进行排序和比较。

 

如何选择数据类型

1、整数和浮点数（double比float更加精确）

2、浮点数和定点数

float、double相对于定点数decimal的优势：浮点数能表示更大的范围却容易产生误差，如果对精确度要求比较高时/进行数值比较时，建议用decimal来存储

3、日期与时间类型

如果只需纪录年份用YEAR类型即可，如果只记录时间，用TIME即可

如果同时纪录日期和时间，用TIMESTAMP/DATETIME.当存储范围比较大是用DATETIME.

(TIMESTAMP在默认情况下默认值会设为当前时间值，在插入纪录时比DATETIME方便有效。

4、char 和varchar

区别：一个固定长度和一个变长；char会自动删除插入数据尾部空格和varchar不会;对于引擎而言：MyISAM采用char较好，而InnoDB采用varchar较好。

5、enum和set

区别：enum只取单值，而set可以取多值/单值；

同：均是以字符串的形式出现，内部以数值的形式存储他们。

6、BLOB和TEXT

BLOB是二进制字符串，TEXT是非二进制字符串，两者均可存放大量的信息。

BLOB主要存储图片、音频信息等，而TEXT只能存储纯文本文件。