MYSQL中如何选择合适的数据类型

MySQL数据类型分为以下几类：

数值，典型代表为tinyint、int、h3int

浮点/定点，典型代表为float、double、小数及相关同义词

字符串，典型代表为char、varchar

时间和日期，典型代表为date、datetime、time、timestamp

Binary，典型代表有binary、varbinary

bit类型

枚举类型

集合类型

大对象，例如text、blob

json文档类型

1数字类型（不是数据类型，别误会）如果是用来存储整数的话，根据不同字段，选择其他类型。

以上是整数选择的几个例子。
整数的应用范围最广。
它们可用于存储数字、时间戳和转换为数字的其他类型的代码，例如IPv4。
示例1使用int32存储IPv4地址，这比仅使用字符串节省了空间。
表x1，字段ipaddr，使用函数inet_aton，检索使用函数inet_ntoa。

查看磁盘空间使用情况，t3占用量最大，t1占用量最小。
因此，如果整数存储区域有固定的上限，并且以后不需要扩展容量，建议选择最小的类型。
当然，这也适用于其他类型。
root@ytt-pc:/var/lib/mysql/3305/ytt#ls-sihl总使用量3.0G3541825861M-rw-r-----1mysqlmysql860MD1011年12月:36t1.ibd354182098M-qlm-lm-lm-lm-lm-lm-lm-lm-l988M十二月1011:38t2.ibd35418231.2G-rw-r-----1mysqlmysql1.2GDember1011:39t3.ibd

2浮点数/定点数我们来谈谈浮点数，包括float和double表示一个浮点数。
简单的区别就是float默认占用4Byte。
float(p)中的P表示整数位的最小精度。
如果p>24，则直接转换为double，占用8Bytes。
p的最大值为53，但是存在最大值计算不准确的问题。
我们来谈谈定点数，包括十进制数和同义数。
固定数的整数位和小数位是分开存储的，最大有效精度不能超过65。
因此，与float的区别在于精度存储。
它必须精确存储或精确计算，最好定义为小数。
示例3创建表y1并为字段f1、f2和f3指定不同的类型。
mysql-(ytt/3305)->createtabley1(f1float,f2double,f3decimal(10,2));QueryOK,0rowsaffected(0.03sec)

3多次使用。
用于存储字符、字符串以及MySQL未知的所有类型。
简直可以说是万能型！

Char(10)表示最多存储10个字符虽然varhar(10)可以存储与char(10)相同数量的字符，但不同的是varchar类型存储的是实际大小理论上是固定大小的存储。
具体字节数与字符集有关。
示例4例如下面的表t4中，两个字段c1和c2分别为char和varchar。
mysql-(ytt/3305)->createtablet4(c1char(20),c2varchar(20));QueryOK,0rowsaffected(0.02sec)

所以在选择char和varchar的时候要注意是否是合适的值范围。
例如，对于固定长度值，选择char；对于不确定值，选择varchar。

4.日期类型日期类型包括日期、时间、日期、时间戳和年份。
年占1个字节，日期占3个字节。

时间、时间戳和日期时间在不包含小数时分别占用3Byte、4Byte和8Byte，另外供磁盘使用，见下表。

点击输入图片描述

点击输入图片描述

点击输入输入图片描述

注：timestamp表示的时间戳是存储在int32中的整数，取值范围为'1970-01-0100:00:01.000000'至'2038-01-1903:14:07.999999';我的值范围是'1000-01-0100:00:00.000000'到'9999-12-3123:59:59.999999'。

综上所述，日期类型的选择遵循以下原则：

1如果时间有可能超过时间戳，则优先选择日期时钟。
2、如果需要单独获取年份值，比如按年份拆分、按年份检索等，最好在表中添加年份类型来参与。
3.如果需要单独获取日期或时间，最好单独存储，而不是仅仅使用日期和时间。
后期抓取时，添加特征过滤。
后期添加SQL写入，会增加额外的消耗。

4如果有类似保存毫秒的需求，最好使用时间类型的属性，而不是直接使用字符类型。
还必须考虑MySQL内部类型转换的额外资源消耗。

示例5

创建表t5，将可能需要的字段全部省略，这样以后写SQL语句就方便了。

当然，这种情况会占用额外的磁盘空间。
如果想在易用性和大空间使用之间折衷，可以使用MySQL的虚拟列进行实时计算。
例如，假设c5字段不存在，并且您想要获取c5的结果。
mysql-(ytt/3305)->altertablet5dropc5,addc5yeargeneratedalwaysas(year(c1))virtual;QueryOK,1rowaffected(2.4)6sec)记录:1重复:0警告:0

5二进制类型

Binary和varbinary分别对应char和varchar的二进制存储，相关属性相同。
区别如下：

binary(10)/varbinary(10)表示的不是字符数，而是字节数。

行结尾不同。
char的行终止符是\0，二进制的行终止符是0x00。

由于是二进制存储，字符编码和校对规则直接无效。

示例6

让我们看一下这个简单的二进制访问示例，它使用与之前相同的变量@a。

记住！这里需要提前计算@a占用的字节数，防止存储溢出。

6位类型

Bit是MySQL中存储的位类型。
它最多支持64位并直接以二进制状态存储。
例如性别、真假等。
它有如下属性：

1对于bit(8)，如果简单存储1位，则00000001会在左侧补02.可以直接按十进制过滤数据。
3、如果该字段被索引，MySQL不会自行进行类型转换，只能使用二进制来过滤。

示例7

创建表c1，字段性别定义一位。
mysql-(ytt/3305)->createtablec1(genderbit(1));QueryOK,0rowsaffected(0.02sec)

selectcast(g)enderasunsigned)'f1'fromc1;+-----+|f1|+-----+|0||1|+-----+2rowsinset(0.00sec)

这同样适用于过滤数据，无论是二进制还是直接十进制。
mysql-(ytt/3305)->selectconv(性别,16,10)asgender\->fromc1wheregender=b'1'+--------+|性别|+--------+|1|+--------+1rowinset(0.00sec)mysql-(ytt/3305)->selectconv(性别,16,10)asgender\->fromc1wheregender='1';+--------+|性别|+--------+|1|+-----------+1rowinset(0.00sec)

其实这样的场景也可以定义为char(0)，这也是类似于bit的一种非常优化的用法。

createtablec2(genderchar(0));QueryOK,0rowsaffected(0.03sec)

现在我将为表c1创建一些测试数据。

selectcount(*)fromc1;+-----------+|count(*)|+----------+|33554432|+----------+1rowinset(1.37sec)

将c1的所有数据插入到c2中。

insertintoc2selectif(gender=0,'',null)fromc1;QueryOK,33554432rowsaffected(2min18.80sec)记录：33554432重复项：0warnings:0

两个表的磁盘使用情况几乎相同。
root@ytt-pc:/var/lib/mysql/3305/ytt#ls-sihl总使用量1.9G4085684933M-rw-r-----1mysqlmysql932MD1。
12月110:16c1.ibd4082686917M-rw-r-----1mysqlmysql916M12月1110:22c2.ibd

检索方式略有不同，但效率相似。
因此，字符类型不愧为通用类型。

7.枚举类型

枚举类型，又称enum。
适合所有已知值都提前规划好并且以后最好不要添加新值的情况。
枚举类型具有以下属性：

1最多占用2个字节。
2.最多支持65535个不同的项目。
3、MySQL后端存储使用下标，即tinyint或smallint，下标从1开始。
4、排序时，按下标排序，而不是按里面元素的数据类型排序。
所以要特别注意这一点。

示例8

创建表t7。
mysql-(ytt/3305)->createtablet7(c1enum('mysql','oracle','dble','postgresql','mongodb','redis','db2','sqlserver'));QueryOK,0rowsaffected(0.03sec)

8集合类型

集合类型SET与枚举类似，你也必须事先知道有多少个元素。
SET具有以下属性：

1最多占用8个字节，int64。
它在内部以二进制位的形式存储。
分别为4和8，...，pow(2,63)。
3.支持多达64种不同的元素。
可以直接插入和删除重复项来删除重复项。
4、元素可以组合插入例如订阅者1和2可以一起插入，3可以直接插入。

例9

定义表c7字段c1为set类型，包含8个值，即下表的最大值为pow(2,7)。

createtablec7(c1set('mysql','oracle','dble','postgresql','mongodb','redis','db2','sqlserver'));QueryOK,0rowsaffected(0.02sec)

插入从1到128的所有组合。

INSERTINTOc7WITHRECURSIVEytt_number(cnt)AS(SELECT1AScntUNIONALLSELECTcnt+1FROMytt_numberWHEREcnt

9在保存的数据类型中使用函数

函数中除了显式声明变量之外，会话变量的默认数据类型很弱，可以根据给定的值随意转换。

示例10

定义一个返回两个给定参数的乘积的函数。
定义中有两个变量，一个是v_tmp显式定义为int64，另一个@vresult根据给定值的类型随意改变其类型。

简单的对话。

selectytt_sample_data_type(1111,222)'结果';+------------------------+|结果|+--------------------------+|这是：'246642'。
|+---------------------------------+1rowinset(0.00sec)

摘要

本文简单介绍一下MySQL的基本数据类型，并用一些通俗易懂的例子来梳理一下这些类型。
在实际场景中，我们建议选择最合适的类型，而不建议对所有数据类型都采用简单的最大化原则。
例如，您可以使用varchar(100)，但不能使用varchar(1000)。

技术分享|在磁盘上查找MySQL表的大小

由于复杂的原因，在MySQL中查询磁盘上表的物理大小并不容易。
MySQL支持多种存储引擎，可以以不同的方式存储数据。
例如，InnoDB存储引擎在MySQL5.7中提供了三种“核心”格式，包括行格式和两种可压缩类型。
这使得问题变得更加复杂。
在MySQL5.7中，INFORMATION_SCHEMA.TABLES中的数据长度（data_length）和索引长度（index_length）用于估计表的大小，但这可能不准确。
MySQL并不实时维护这些值，而是定期更新它们。
该图显示数据长度和索引长度更新不一致，并且在服务器启动时更新统计信息可能会对查询计划产生不稳定的影响。
为了获得更准确的实时信息，可以通过INNODB_SYS_TABLESPACES表查看表空间信息。
该表是实时更新的，不需要任何特殊配置。
与INFORMATION_SCHEMA.TABLES中的数据不同，INNODB_SYS_TABLESPACES表还处理InnoDB页面压缩功能，并正确显示文件大小和分配大小之间的差异。
使用不同类型的InnoDB压缩会影响INFORMATION_SCHEMA中显示的信息。
对于传统的InnoDB压缩（InnoDB表压缩），data_length和index_length显示压缩数据的大小。
当使用MySQL5.7中新的InnoDB压缩（InnoDB页压缩）时，显示的值对应于文件大小，而不是分配大小。
总之，查询MySQL中某个表占用的磁盘空间并不是一件容易的事。
要获得准确的实时信息，您必须查看INNODB_SYS_TABLESPACES表以了解实际文件大小。
了解不同的InnoDB压缩方法如何影响结果也很关键。