【数据库】mysql常用的数据类型

ゝ一世哀愁。 2021-12-19 04:25 502阅读 0赞

数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。  
有一些数据是要存储为数字的，数字当中有些是要存储为整数、小数、日期型等...  
MySQL支持多种类型，大致可以分为四类：数值型、浮点型、日期/时间和字符串(字符)类型。

**一、Text 类型（字符类型）：**  
char(size)：保存固定长度的字符串（可包含字母、数字以及特殊字符）  
varchar(size)：保存可变长度的字符串（可包含字母、数字以及特殊字符）  
text：存放最大长度为 65,535 个字符的字符串。

**1.1 字符与字节的区别**  
一个字符由于所使用的字符集的不同，会并存储在一个或多个字节中，所以一个字符占用多少个字节取决于所使用的字符集。  
注意：char与varchar后面接的数据大小为存储的字符数，而不是字节数  
在MySQL中，char、varchar和text类型的字段都可以用来存储字符类型的数据，char、varchar都可以指定最大的字符长度，但text不可以。  
它们的存储方式和数据的检索方式也都不一样。  
数据的检索效率是：char > varchar > text

**1.2 存数据时的区别**  
char：char定义的是固定长度，长度范围为0-255，存储定长数据很方便，CHAR字段上的索引效率级高，必须在括号里定义长度，可以有默认值，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间（自动用空格填充），且在检索的时候后面的空格会隐藏掉，所以检索出来的数据不需要记得用什么trim之类的函数去过滤空格。  
varchar：varchar是变长长度，长度范围为0-65535，但存储效率没有CHAR高，必须在括号里定义长度，可以有默认值。保存数据的时候，不进行空格自动填充，而且如果数据存在空格时，当值保存和检索时尾部的空格仍会保留。另外，varchar类型的实际长度是它的值的实际长度+1，这一个字节用于保存实际使用了多大的长度。  
text：存储可变长度的非Unicode数据，最大长度为2^31-1个字符。text列不能有默认值，存储或检索过程中，不存在大小写转换，后面如果指定长度，不会报错误，但是这个长度是不起作用的，意思就是你插入数据的时候，超过你指定的长度还是可以正常插入。

**1.3 取数据时的区别**  
数据库取char的数据时，会把后面的空格全部丢弃掉，也就是说，在char中的尾部存入空格时，最后取出来都会被丢弃；当然指定PAD\_CHAR\_TO\_FULL\_LENGTH时，在取数据时让尾部的空格保留。  
而数据库在取varchar数据时，尾部空格会保留。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM3OTY3ODY1_size_16_color_FFFFFF_t_70][]

可以用上表来表示，当定义char时，不管你存入多少字符，都会占用到你定义的字符数，而用varchar时，则和你输入的字符数有关，会多一到两个字节来记录字节长度，当数据位占用的字节数小于255时，用1个字节来记录长度，数据位占用字节数大于255时，用2个字节来记录长度，还有一位来记录是否为nul值。

**1.4 关于存储空间：**  
在使用UTF8字符集的时候，MySQL手册上是这样描述的：  
基本拉丁字母、数字和标点符号使用一个字节；  
大多数的欧洲和中东手写字母适合两个字节序列：扩展的拉丁字母（包括发音符号、长音符号、重音符号、低音符号和其它音符）、西里尔字母、希腊语、亚美尼亚语、希伯来语、阿拉伯语、叙利亚语和其它语言；  
韩语、中文和日本象形文字使用三个字节序列。  
mysql每一行的最大字节数为65535，当你使用utf8，一个字符有可能占用三个字节的时候，varchar如果定义允许空的话能定义的最大长度为(65535-1-2)/3=21844.  
Mysql在对比char，varchar，text类型的数据时，是不会把尾部的空格考虑在内的，这对所有字符集都适用，但在这里是除了like比较符的。

**1.5 结论：**  
1、经常变化的字段用varchar；  
2、知道固定长度的用char；  
3、超过255字节的只能用varchar或者text；  
4、能用varchar的地方不用text；  
5、能够用数字类型的字段尽量选择数字类型而不用字符串类型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了；

**二、Number 类型（数字类型）：**  
tinyint(3)：-128 到127常规。0到255无符号\*。在括号中规定最大位数。  
smallint(3)：-32768到32767常规。0到65535无符号\*。在括号中规定最大位数。  
mediumint(3)：-8388608到8388607普通。0to16777215无符号\*。在括号中规定最大位数。  
int(3)：-2147483648到2147483647常规。0到4294967295无符号\*。在括号中规定最大位数。  
bigint(10)：-9223372036854775808到9223372036854775807 常规。0到18446744073709551615无符号\*。在括号中规定最大位数。  
float(size,d))：带有浮动小数点的小数字。在括号中规定最大位数。在d参数中规定小数点右侧的最大位数。  
double(size,d)：带有浮动小数点的大数字。在括号中规定最大位数。在d参数中规定小数点右侧的最大位数。  
decimal(size,d)：作为字符串存储的double类型，允许固定的小数点。

**三、Date 类型（日期类型）：**  
date()：日期格式：YYYY-MM-DD；注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'  
datetime()：日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS；注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'  
timestamp()：时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS；注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC  
time()：时间格式：HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59'  
year()：2位或4位格式的年。注释：4位格式所允许的值：1901到2155；2位格式所允许的值：70到69，表示从1970到2069。

**4、BLOB (binary large object)，用来存储二进制大对象的字段类型。**  
BLOB往往是一个大文件，典型的BLOB是一张图片、一个声音或一个视频文件，由于它们的尺寸，必须使用特殊的方式来处理（例如：上传、下载或者存放到一个数据库）。  
处理BLOB的主要思想就是让文件处理器（如数据库管理器）不去理会文件是什么，而是关心如何去处理它。但也有专家强调，这种处理大数据对象的方法是把双刃剑，它有可能引发一些问题，如存储的二进制文件过大，会使数据库的性能下降。  
MySQL中，BLOB是个类型系列，共包括四种BLOB类型：TinyBlob、Blob、MediumBlob、LongBlob，这几个类型之间的唯一区别是在存储文件的最大尺寸不同。

字段类型 最大长度（字节）    存储需求  
TinyBlob 255    值的长度加上用于记录长度的1个字节(8位)  
Blob 65K        值的长度加上用于记录长度的2个字节(16位)  
MediumBlob 16M  值的长度加上用于记录长度的3个字节(24位)  
LongBlob 4G     值的长度加上用于记录长度的4个字节(32位)

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM3OTY3ODY1_size_16_color_FFFFFF_t_70]: /images/20211218/27a02e4783d2440ca67d2c7c48ecdc49.png