mysql 不能定义用户变量名,MySQL用户定义变量的存储限制

Under User-Defined Variables, the MySQL manual documents:

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.)

However the manual does not state:

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);

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.

the precision and scale used in fixed- and floating-point values, given that this is not preserved from the assignment; and

the maximum length (in characters and/or bytes) of strings.

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

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

解决方案

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.

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.