MySQL用户定义变量的存储限制 [英] Storage limits of MySQL user-defined variables

问题描述

MySQL手册文档用户定义的变量下:

Under User-Defined Variables, the MySQL manual documents:

可以从一组有限的数据类型中为用户变量分配一个值:整数，十进制，浮点数，二进制或非二进制字符串或NULL值.十进制和实数值的分配不会保留数值的精度或小数位数.除允许的类型之一之外的其他类型的值将转换为允许的类型.例如，将具有时间或空间数据类型的值转换为二进制字符串.

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value. Assignment of decimal and real values does not preserve the precision or scale of the value. A value of a type other than one of the permissible types is converted to a permissible type. For example, a value having a temporal or spatial data type is converted to a binary string.

如果为用户变量分配了非二进制(字符)字符串值，则它具有与字符串相同的字符集和排序规则.用户变量的强制性是隐式的. (这与表列值具有相同的强制性.)

If a user variable is assigned a nonbinary (character) string value, it has the same character set and collation as the string. The coercibility of user variables is implicit. (This is the same coercibility as for table column values.)

但是该手册未注明:

• 支持的整数(例如4字节INT或8字节BIGINT)的整数 size ，它们是带符号的还是无符号的，以及是否为(如果使用，则使用显示宽度)；

• the size of supported integers (e.g. 4-byte INT, or 8-byte BIGINT), whether they are signed or unsigned, and whether ZEROFILL is applied (if so, using what display width);

此sqlfiddle 出现表示建议使用MySQL 5.5.25最多为BIGINT，并根据分配进行了签名，并且不会应用任何ZEROFILL.

This sqlfiddle appears to suggest that MySQL 5.5.25 will use up to BIGINT, signed according to the assignment, and will not apply any ZEROFILL.

定点和浮点值中使用的精度和小数位数，因为这不能从赋值中保留；和

字符串的最大长度(以字符和/或字节为单位).

sqlfiddle受2个 ²⁰ 字节的max_allowed_packet限制(我不能更改):我猜猜 2个^{的LONGTEXT支持32} 字节(4GiB).

sqlfiddle is limited by max_allowed_packet of 2²⁰ bytes (which I can't change): I'd guess that LONGTEXT of 2³² bytes (4GiB) is supported.

可以安全地依靠哪些限制，尤其是在不同服务器版本和配置之间?

What limits can be safely relied upon, especially across different server versions and configurations?

推荐答案

用户变量使用与MySQL用于计算列值的内部数据类型相同的内部数据类型.这些内部类型足以容纳MySQL支持的 all 数据类型.

User variables use the same internal data types that MySQL uses for computations on column values. These internal types are big enough for all data types supported by MySQL.

• 整数没有ZEROFILL，因为这是列的附加属性，而不是数据类型本身.此外，整数用户变量没有宽度.转换为文本时，它们会根据需要使用任意多个数字.
• 浮点值没有刻度. 64位IEEE浮点数是二进制值，并且具有足够的精度(约18个十进制数字).
理论上，如果max_allowed_packet足够大并且您有足够的内存，则
• 字符串应保留任何LONGTEXT值.但是，在 bug 31898 .

• Integers do not have ZEROFILL, as that is an additional property of the column, not of the data type itself. Furthermore, integer user variables do not have a width; when converted to text, they use just as many digits as needed.
• Floating-point values do not have a scale. 64-bit IEEE floats are binary values, and have enough precision for about 18 decimal digits.
• Strings should, in theory, hold any LONGTEXT value, if max_allowed_packet is large enough and you have enough memory. However, there is an undocumented limit of 16 MB for user variables, mentioned in bug 31898.

这篇关于MySQL用户定义变量的存储限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！