Menu
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
NULLvalue. 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?
262
You can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another. User variables are written as @ var_name , where the variable name var_name consists of alphanumeric characters, . , _ , and $ .
The scope of the local variable is in a stored program block in which it is declared. MySQL uses the DECLARE keyword to specify the local variable. The DECLARE statement also combines a DEFAULT clause to provide a default value to a variable.
User-defined variables are variables that you define when you write a policy. You can use any combination of letters and numbers as variable names as long as the first variable starts with a letter: You do not need to initialize variables used to store single values, such as strings or integers.
There are two ways to assign a value to a user-defined variable. You can use either := or = as the assignment operator in the SET statement. For example, the statement assigns number 100 to the variable @counter. The second way to assign a value to a variable is to use the SELECT statement.
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, 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.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.
112
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
