mysql 查询某个字段总和小于多少 mysql查询语句字段长度限制

转载

网络安全侠 2023-08-02 12:17:43

文章标签 mysql 查询某个字段总和小于多少 mysql 字符串数据类型 MySQL 文章分类 MySQL 数据库

一，背景

Mysql是存在表字段 总长度限制 的：
每行允许最多 65535 字节 长度，注意这里单位是字节！，不是单纯数字，超过了最大长度是不行的，但总长度的计算并不是单纯的所有长度相加，具体看下面说明

二，char ，varchar 计算

不同数据类型占用的字节长度不同，当类型为 char 或者 varchar 时你可能需要将你的长度乘以2 或 3 来得到总大小，即 char 和 varchar 里面存储的内容被识别为字符，字符从字面意思来将就是文字，数字，以及符号等，用于意思表达。

我们都知道我们拥有很多字符集，比如最常见的 UTF-8，以及我们的 GBK ，采用了不同字符集意味着采用了不同长度的字节去存储这些语言文字也就是我们的字符。

计算

当字符集为 UTF-8 时 char 和 varchar 后面的数字例如 varchar(20) 我们计算时就需要乘3，varchar(20) 占用字节长度就是 20 * 3 也就是 60字节

当字符集为 GBK 时 char 和 varchar 后面的数字例如 varchar(20) 我们计算时就需要乘2，varchar(20) 占用字节长度就是 20 * 2 也就是 40字节

当然字符集远远不止这些，如果要计算长度，先弄清楚你当前环境的字符集，然后查阅一下当前字符集一个字符与字节的换算比例。然后开始计算 char ,以及varchar 类型的总长度。

但也可能受到版本和大环境的影响，建议最好看下官网每个对应的说明，如果英文实在看不懂用谷歌翻译一下网页即可。

三，其他类型占用长度

其他类型的占用长度一般都是固定的字节，而跟在类型后的数字仅仅指定了数据的宽度，例如
int（5）
这里的 5 指的是数值的宽度，并不是字节，这些类型当你给定的长度超过了他本身限制时，当即就会向你抛出异常。

以下内容参考Mysql 官方数据类型说明，具体计算稍后会举例：
数字类型存储要求

数据类型	存储需求
TINYINT	1 个字节
SMALLINT	2 个字节
MEDIUMINT	3 个字节
INT, INTEGER	4字节
BIGINT	8 个字节
FLOAT§	如果 0 <= p<= 24则为 4 个字节，如果 25 <= p<= 53则为 8 个字节
FLOAT	4字节
DOUBLE [PRECISION], REAL	8 个字节
DECIMAL(M,D), NUMERIC(M,D)	变化; 见以下讨论
BIT(M)	大约 ( M+7)/8 字节

对值DECIMAL（和 NUMERIC）列使用二进制格式，包9个十进制（基体10）的数字成四个字节表示。
每个值的整数和小数部分的存储分别确定。
每个九位数字的倍数需要四个字节，而“剩余” 数字需要四个字节的一部分。下表给出了多余数字所需的存储空间。

剩余数字	字节数
0	0
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4

日期和时间类型存储要求
对于TIME、 DATETIME和 TIMESTAMP列，在 MySQL 5.6.4 之前创建的表所需的存储空间与从 5.6.4 开始创建的表不同。这是由于 5.6.4 中的更改允许这些类型具有小数部分，这需要 0 到 3 个字节。

数据类型	MySQL 5.6.4 之前需要存储	MySQL 5.6.4 所需的存储空间
YEAR	1 个字节	1 个字节
DATE	3 个字节	3 个字节
TIME	3 个字节	3 字节 + 小数秒存储
DATETIME	8 个字节	5 字节 + 小数秒存储
TIMESTAMP	4字节	4 字节 + 小数秒存储

从 MySQL 5.6.4 开始，存储 YEAR和 DATE保持不变。
但是， TIME、 DATETIME和 TIMESTAMP的表示方式不同。DATETIME更有效地打包，非小数部分需要 5 个字节而不是 8 个字节，并且所有三个部分都有一个小数部分，需要 0 到 3 个字节，具体取决于存储值的小数秒精度。

小数秒精度	存储需要
0	0 字节
1, 2	1 个字节
3、4	2 个字节
5、6	3 个字节

例如，TIME(0)、 TIME(2)、 TIME(4)和分别 TIME(6)使用 3、4、5 和 6 个字节。TIME并且 TIME(0)是等效的并且需要相同的存储空间。

有关时间值的内部表示的详细信息，请参阅MySQL 官方文档：重要算法和结构

其他类型的计算

当存在多个int 时直接使用 int 的个数乘以 4 得到所有int 占用总字节大小，如果存在 9 个int 那么占用字节为 9 * 4 即 36个字节。

datatime , bigint 等类似找到是该类型的字段数量，然后用该数量乘以单个该类型占用的字节大小，如果存在范围取值，即占用长度不唯一时，需要进一步判断。

四，字符串类型存储

在下表中，M表示非二进制字符串类型的声明列长度和二进制字符串类型的字节。 L表示给定字符串值的实际长度（以字节为单位）

数据类型	存储需求
CHAR(M)	紧凑的 InnoDB 行格式系列优化了可变长度字符集的存储。请参阅 COMPACT 行格式存储特性。否则，M× w字节，255，其中是字符集中最大长度字符所需的字节数。<= M <=w
BINARY(M)	M字节，0 255<= M <=
VARCHAR(M), VARBINARY(M)	L如果列值需要 0 - 255 个字节，则L+ 1 个字节，如果值可能需要超过 255 个字节，则 + 2 个字节
TINYBLOB, TINYTEXT	L+ 1 个字节，其中 L< 2 ^8 (2的8次方)
BLOB, TEXT	L+ 2 个字节，其中 L< 2 ^16 (2的16次方以下类似，不赘述)
MEDIUMBLOB, MEDIUMTEXT	L+ 3 个字节，其中 L< 2^24
LONGBLOB, LONGTEXT	L+ 4 个字节，其中 L< 2 ^ 32
ENUM(‘value1’,‘value2’,…)	1 或 2 个字节，取决于枚举值的数量（最多 65,535 个值）
SET(‘value1’,‘value2’,…)	1、2、3、4 或 8 个字节，取决于集合成员的数量（最多 64 个成员）

可变长度字符串类型使用长度前缀加数据存储。长度前缀根据数据类型需要一到四个字节，前缀的值是 L（字符串的字节长度）。例如，一个MEDIUMTEXT值的存储需要 L字节来存储值加上三个字节来存储值的长度。

要计算用于存储特定 CHAR、 VARCHAR或 TEXT列值的字节数，您必须考虑用于该列的字符集以及该值是否包含多字节字符。
特别是，在使用utf8Unicode 字符集时，您必须牢记并非所有字符都使用相同的字节数。
utf8mb3 和 utf8mb4 字符集每个字符最多分别需要三个和四个字节。有关用于不同类别utf8mb3或 utf8mb4字符的存储细分，请参阅 Mysql官方文档 “Unicode 支持” 章节。

VARCHAR, VARBINARY, BLOB and TEXT 类型是变长类型。对于每个，存储要求取决于以下

列值的实际长度
列的最大可能长度
列使用的字符集，因为有些字符集包含多字节字符

五，超长时的解决方式

较长字段可以修改为 text（文本和数字混合存储）类型或者Blob（字节存储）类型，二者长度最多都是 65,535 单位分别为字符或者字节。
需要注意的是存入 text 的都被识别为普通的字符来对待，而存入 blob 中的数据为字节，不能直接识别出来，当你获取出来后可能需要做转义处理。