MySQL数据库基本功-常用数据类型介绍和优化技巧

2023-02-13 00:00:00 字符串 字节 类型 数据类型 位数

背景

介绍下MySQL数据库的基础数据库类型。方便开发同学对使用和设计MySQL数据库表结构有一个初步正确的认识。因为MySQL数据类型随着官方也在不断演进中，下面介绍的数据类型和特性是基于Mysql 5.6+版本来介绍的。

MySQL基础数据类型分类：

  1. 数字类型
  2. 时间日期型
  3. 字符串类型

1.数字类型

整形数字

上表就是常用5种数字类型。列是类型名称，第二列存储代表此类型需要的存储空间。第三四列为该类型有符号的小大的取值范围。第五六列为该类型无符号的小大的取值范围。

tinyint适合存个数有限的一类的类别字段，例如男女，是否可用，各种有限几个状态一类的。smallint适合存稍大一类的类别字段，例如网络端口。

用int和bigint做自增主键时候别忘记加无符号修饰符。不存负数记得把无符号修饰符加上。

浮点数类型

DECIMAL中M代表总位数，D是小数点后的位数。小数点和负数符号(-)不计入M。DECIMAL的大位数(M)为65，支持的大小数位(D)为30。每个值的整数和小数部分的存储分别确定。

FLOAT中M代表总位数，D是小数点后的位数。如果省略M和D，则将值存储到硬件允许的限制。单精度浮点数支持的大小数(D)大约为7位。使用FLOAT可能会给你一些意想不到的问题, 因为MySQL中的所有计算都是以双倍精度完成的。

DOUBLE中M代表总位数，D是小数点后的位数。如果省略M和D，则将值存储到硬件允许的限制。双精度浮点数支持的大小数(D)大约为15位。

注意：浮点型数据本身不是一个的数字，如果要将FLOAT或DOUBLE列与具有小数的数字进行比较，则不能使用相等（=）比较。此问题在大多数计算机语言中很常见，因为并非所有浮点值都可以存储。在某些情况下，将FLOAT更改为DOUBLE可以解决此问题。

附录：科学记数法是一种记数的方法。把一个数表示成a与10的n次幂相乘的形式（1≤a<10，n为整数），这种记数法叫做科学记数法。 例如：19971400000000=1.99714×10^13。计算器或电脑表达10的幂一般是用E或e，也就是1.99714E13=19971400000000。


浮点数类型DECIMAL到底需要多大存储空间？

每个值的整数和小数部分的存储分别确定。每部分每九位数的倍数需要四个字节存储, 剩余数字位数则需要0-4个字节存储。下边表给出了剩余数字位数和所需存储空间对应关系。

数字类型知识点巩固小测试

浮点数DECIMAL(20,6)字段到底需要多大空间存储？

2.时间日期类型

DATETIME和TIMESTAMP区别：MySQL将TIMESTAMP值从当前时区转换为UTC（世界标准时间，不属于任意时区。中国大陆、中国香港、中国澳门、中国台湾、蒙古国、新加坡、马来西亚、菲律宾、西澳大利亚州的时间与UTC的时差均为+8，也就是UTC+8）进行存储，进行检索时再从UTC转换回当前时区。（对于其他类型DATETIME，不会发生这种情况。）

DATETIME，TIMESTAMP和TIME类型可以设置包括（0-6位）秒的小数精度，(3)到毫秒(ms)，(6)到微秒(μs)。默认不设置秒的小数精度。

有些同学可能会疑惑，为什么上边TIME类型小时部分有负数还有超过24的那么大数呢？这主要是因为TIME类型不仅可用于表示一天中的时间（必须小于24小时），还可以用于表示两个事件之间经过的时间或时间间隔（所以可能远大于24小时，甚至是负数形式）

附录：秒的小数精度(fsp)和存储字节数对应关系表

时间类型知识点巩固小测试

下面时间日期类型的字段需要多大空间存储？

  1. DATETIME(3)？
  2. TIME(6)？

3.字符串类型

上面的M指的是字符个数。L表示给定字符串值的实际长度 (以字节为单位)。上边+的某某bytes表示记录变长字段字节长度需要的存储空间。

VARCHAR列中的值是可变长度的字符串。长度可以指定为从0-65535的任意值。VARCHAR的有效大长度还受大行大小 (在所有列之间共享的65535个字节) 和使用的字符集的限制。

Binary 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，只是它们包含二进制字符串而不是非二进制字符串。 也就是说，它们包含字节字符串而不是字符字符串。 这意味着它们具有二进制字符集和排序规则，并且比较和排序基于值中字节的数值。

Binary 和 VARBINARY 允许的大长度与 CHAR 和 VARCHAR 相同，只是 BINARY 和 VARBINARY 的长度是以字节而不是字符为单位的长度。

utf8mb4字符编码如果不考虑列之间共享的65535个字节，大可以设置16383个字符个数。

字符串类型知识点巩固小测试

varchar(80)字段大需要多大空间存储(utf8mb4字符编码)？

总结

如何选择合适的数据类型？

  1. 合适的数据类型。
  2. 更小的数据类型。
  3. 尽可能用数字类型替换字符类型。

选用更小的数据类型减少存储空间，因为更小的数据类型需要的IO资源更低。 能用数字类型替代字符串类型的尽量替换，因为数字类型存储空间更小，而且数字类型没有字符串类型的字符集各种排序规则判断效率至少快3倍以上。

为什么会有上边数据类型的选择原则呢？

那就要从数据库数据存储结构讲起了。请看下边的简略的图。


  1. 磁盘空间是被划分为许多大小相同的块(Block)或者页(Page)的，Mysql默认页的大小是16KB。
  2. 一个表的这些数据块以链表的方式串联在一起。
  3. 数据是以行（Row）为单位一行一行的存放在磁盘上的块中，如上边简图所示。
  4. 在访问表中数据时，一次从磁盘中读出或者写入至少一个完整的块Block/页Page。换句话说MySql数据库小读写单元就是一个块或页，哪怕你只需要页中的一条数据，mysql也需要把整个页读进内存再把它从内存中找出来。

因为数据定位操作是所有数据操作的基础，因此数据定位的效率也就是直接影响所有数据库数据操作的效率核心点。MySql数据库读写又是以一个块或页为小单元。因此应用越小的数据类型，相应的每个块(Block)内能容纳的行（Row）数量也就越多。相应的数据库数据定位操作所需要IO量就会减少，效率自然会更高效。

结合刚才学的知识看看下边的建表语句有哪些字段的数据类型可以改进？ 

CREATE TABLE `ac_account` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`account` varchar(32) NOT NULL COMMENT '账户号',
`ac_amount` float(10, 2) NOT NULL COMMENT '账户金额',
`account_status` int(1) NOT NULL DEFAULT 1 COMMENT '账户状态 : 0:,1:有效',
`create_user` varchar(255) NOT NULL COMMENT '创建人',
`create_time` varchar(20) NOT NULL DEFAULT '2010-01-01 00:00:00' COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET = utf8mb4 COMMENT '账户表';

相关文章