【MySQL篇】第四篇——数据类型

数值类型

整数类型

TINYINT类型

小数类型

FLOAT

DECIMAL

日期和时间类型

字符串类型

CHAR

VARCHAR

二进制类型

BIT

---

数据类型是指系统中所允许的数据类型。MySQL数据类型定义了列中可以存储什么数据以及该数据怎么样存储的规则。

数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。

如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。
MySQL 的数据类型有大概可以分为 5 种，分别是整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。

注意：整数类型和浮点数类型可以统称为数值数据类型。

• 数值类型:整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点数类型包括 FLOAT 和 DOUBLE，定点数类型为 DECIMAL。

• 日期/时间类型:包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

• 字符串类型:包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。

• 二进制类型:包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

数值类型

整数类型

MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其属性字段可以添加 AUTO_INCREMENT 自增约束条件。下表中列出了 MySQL 中的数值类型。

 从上表中可以看到，不同类型的整数存储所需的字节数不相同，占用字节数最小的是 TINYINT 类型，占用字节最大的是 BIGINT 类型，占用的字节越多的类型所能表示的数值范围越大。

不同的整数类型有不同的取值范围，并且需要不同的存储空间，因此应根据实际需要选择最合适的类型，这样有利于提高查询的效率和节省存储空间。

TINYINT类型

数值越界测试：

 说明:

• 在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的
• 可以通过UNSIGNED来说明某个字段是无符号的

无符号案例

•  其他类型自己推导

注意：尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。 

小数类型

浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；定点类型只有一种，就是 DECIMAL。

浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。

浮点数类型的取值范围为 M（1～255）和 D（1～30，且不能大于 M-2），分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的，FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。

下表中列出了 MySQL 中的小数类型和存储需求。

FLOAT

语法 

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节

案例：

小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。

案例:

如果定义的是float(4,2) unsigned 这时，因为把它指定为无符号的数，范围是 0 ~ 99.99

DECIMAL

语法:

decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

•  decimal(5,2) 表示的范围是 -999.99 ~ 999.99
• decimal(5,2) unsigned 表示的范围 0 ~ 999.99decimal和float很像，但是有区别:float和decimal表示的精度不一样

 发现decimal的精度更准确，因此如果我们希望某个数据表示高精度时，选择decimal.

说明：float表示的精度大约是7位。

• decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10。

日期和时间类型

YEAR、TIME、DATE、DTAETIME、TIMESTAMP。当只记录年信息的时候，可以只使用 YEAR 类型。

每一个类型都有合法的取值范围，当指定确定不合法的值时，系统将“零”值插入数据库中。

常用的日期有如下三个：

• date :日期 'yyyy-mm-dd' ，占用三字节
• datetime 时间日期格式 'yyyy-mm-dd HH:ii:ss' 表示范围从 1000 到 9999 ，占用八字节
• timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节

案例:

更新数据 

 

字符串类型

字符串类型用来存储字符串数据，还可以存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较，还可以进行正则表达式的匹配查找。

字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。

 VARCHAR 和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。
例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

CHAR

语法:

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255

案例:

说明:

 char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个， 最多只能是255

VARCHAR

语法:

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节

 案例:

说明:

关于varchar(len),len到底是多大，这个len值，和表的编码密切相关：

• varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。
• 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。

【实例】下面将不同的字符串保存到 CHAR(4) 和 VARCHAR(4) 列，说明 CHAR 和 VARCHAR 之间的差别，如下表所示。

对比结果可以看到，CHAR(4) 定义了固定长度为 4 的列，无论存入的数据长度为多少，所占用的空间均为 4 个字节。VARCHAR(4) 定义的列所占的字节数为实际长度加 1。

那么问题来了，如何选择定长或变长字符串?

• 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
• 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去
• 定长的磁盘空间比较浪费，但是效率高
• 变长的磁盘空间比较节省，但是效率低。
• 定长的意义是，直接开辟好对应的空间
• 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少

二进制类型

MySQL 中的二进制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

下表中列出了 MySQL 中的二进制数据类型，括号中的M表示可以为其指定长度。

BIT

位字段类型。M 表示每个值的位数，范围为 1～64。如果 M 被省略，默认值为 1。如果为 BIT(M) 列分配的值的长度小于 M 位，在值的左边用 0 填充。例如，为 BIT(6) 列分配一个值 b'101'，其效果与分配 b'000101' 相同。
BIT 数据类型用来保存位字段值，例如以二进制的形式保存数据 13，13 的二进制形式为 1101，在这里需要位数至少为 4 位的 BIT 类型，即可以定义列类型为 BIT(4)。大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。

BIT 数据类型用来保存位字段值，例如以二进制的形式保存数据 13，13 的二进制形式为 1101，在这里需要位数至少为 4 位的 BIT 类型，即可以定义列类型为 BIT(4)。大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。

基本语法

bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。

举例：

bit使用的注意事项:

•  bit字段在显示时，是按照ASCII码对应的值显示。

•  如果我们有这样的值，只存放0或1，这时可以定义bit(1)。这样可以节省空间