MySQL各种行格式应用场景_MySQL数据类型及使用场景

MySQL数据类型介绍

整数类型

类型名称

说明

存储需求

取值范围有符号

取值范围符号

TINYINT

很小的整数

1个字节

-128~127

0-255

SMALLINT

小的整数

2个字节

32768~32767

0-65535

MEDIUMINT

中等大小的整数

3个字节

-8388608~8388607

0-16777215

INT(INTERGER)

普通大小的整数

4个字节

-2147483648

0-4294967295

BIGINT

大整数

8个字节

-9223372036854775808~9223372036854775807

0-18446744073709551615

CREATE TABLE tb_empl

(

id INT(11)

)

上面的11指的是这个字段中数据数字显示的宽度，与取值范围是无关的。

数值的位数小于指定宽度时会由空格填充，如果大于显示宽度，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。显示宽度只用于显示，不能限制取值范围和占用空间

浮点类型

类型名称

说明

存储需求

取值范围有符号

取值范围符号

FLOAT

单精度浮点

4个字节

-3.402823466E+38~-1.175494351E-38

0和1.175494351E-38~3.402823466E+38

DOUBLE

双精度浮点

8个字节

-1.7976931348623157E-308~1.7976931348623157E+308

0和2.2250738585072014E-308~1.7976931348623157E+308

DECIMAL(M,D)

压缩的'严格'定点数，就是指定了小数点后几位

M+2个字节

注意不论是定点还是浮点类型，如果指定的精度超出范围，则会四舍五入进行处理。

日期与时间类型

类型名称

日期格式

日期范围

存储需求

YEAR

YYYY

1901-2155

1个字节

TIME

HH:MM:SS

-838:59~838:59:59

3字节

DATE

YYYY-MM-DD

1000-01-01~9999-12-3

3字节

DATETIME

YYYY-MM-DD HH:MM:SS

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

8字节

TIMESTAMP

YYYY-MM-DD HH:MM:SS

1970-01-01 00:00:01 UTC~2038-01-19

4字节

文本字符串类型

类型名称

说明

CHAR

固定长度非二进制字符串

VARCHAR

变长非二进制字符串

TINYTEXT

非常小的非二进制字符串

TEXT

小的非二进制字符串

MEDIUMTEXT

中等大小的非二进制字符串

LONGTEXT

大的非二进制字符串

ENUM

枚举类型,只能有一个枚举字符串

SET

一个设置,字符串对象可以有零个或多个SET成员

CHAR为固定长度字符串

VARCHAR是长度可变的字符串

如何选择数据类型

整数和浮点数

需要小数部分，则使用浮点数类型，列入值的范围为1-99999，若使用整数，则MEDIUMINT UNSIGNED是最好的类型，若需要存储小数，则使用FLOAT类型。

浮点类型包括FLOAT和DOUBLE类型,DOUBLE类型精度比FLOAT类型高，因此，如果要求存储精度较高时，应该选择DOUBLE类型。

浮点数和定点数

浮点数FLOAT、DOUBLE相对于定点数DECIMAL的优势是:在一定长度情况下，浮点数能表示更大的数据范围，但是由于浮点数容易产生误差，因此精度高时，建议使用DECIMAL来存储。

日期与时间类型

如果只记录时间，TIME类型

如果记录日期和时间 TIMESTAMP和DATETIME

TIMESTAMP类型当插入一条记录但并没有指定TIMESTAMP这个列值时，MySQL会把TIMESTAMP列值设为当前的时间。

CREATE TABLE test1

(

names VARCHAR(20),

times TIMESTAMP NOT NULL DEFAULT NOW()

)

CHAR和VARCHAR之间的特点与选择

CHAR是固定长度字符，VARCHAR是可变长度字符

CHAR会自动删除插入数据的尾部空格，VARCHAR不会删除尾部空格

CHAR处理速度快，但是缺点就是浪费存储空间

对于MyISAM 使用CHAR可以整个表静态化，从而使数据检索更快，用空间换时间。

对于InnoDB使用VARCHAR,因为它的存储格式不分固定长度和可变长度，VARCHAR比较节省空间

ENUM和SET

ENUM只取单值，但要注意，他的索引是从1开始，加了引号就是值，不加就是索引。

设定enum的格式：

enum("选项1","选项2","选项3",...)；

实际上，enum的选项都会对应一个数字，依次是1，2，3，4，5...，最多有65535个选项

加了引号

CREATE TABLE `enum2tinyint` (

`switchs` enum('none','success','fail','delete','skip','1') DEFAULT NULL,

`switch` tinyint(1) NOT NULL,

KEY `switchs` (`switchs`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES ('1', '1');

1 1

不加引号

INSERT INTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES (1, '1');

none 1

INSERT INTO `test`.`enum2tinyint` (`switchs`, `switch`) VALUES (2, '1');

success 1

设定set的格式：

set("选项1","选项2","选项3",...)

同样的，set的每个选项值也对应一个数字，依次是1，2，4，8，16...，最多有64个选项

create table enum_set_table(

id int auto_increment primary key,

hobby set('music','movie','swimming','footbal')

)

#15=1+2+4+8 <=> 'music,movie,swimming,footbal' */

insert into enum_set_table(id,hobby) values(null,15);

# 等同于

insert into enum_set_table(id,hobby) values(null,'music,movie,swimming,footbal');

BLOB和TEXT

BLOB是二进制字符串

主要用于存储图片，音频信息

TEXT是非二进制字符串

只能存储纯文本文件