There are different datatypes used to build a column in MySQL table. For example SMALLINT, MEDIUMINT, and INT. I understand that different datatype will affect table size according to column types. Therefore, its unnecessary to assign UNSIGNED INT to a value which ranges between 0-1000.
What happens if you multiply values of different datatypes in MySQL (e.g. SMALLINT * INT)? What MySQL datatype do you need to store the result?
MySQL defines some rules for arithmetic operations.
In case of +, -, * result is calculated to BIGINT (64-bit) precision if both operands are integers (e.g. SMALLINT, INT)
If both operands are integer and any of them are UNSIGNED, the result is an unsigned integer. For subtraction (-) if NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result will be signed.
If any of the operands of a +, -, /, *, % is real (e.g. FLOAT or DOUBLE) or string value (e.g. '10.13e-3') the precision of the result is the precision of the operand with the maximum precision.
It is safe to do arithmetic operations between columns with different datatypes. MySQL will automatically convert into appropriate format.
As for storing its result, there are some constraints.
If out-of-range value is inserted, largest endpoint will be stored (e.g. inserting 130 to TINYINT will store 127 and raise warning). If strict mode is enabled, insertion will fail and raise error.
Integer overflow results in silent wraparound (e.g. 9223372036854775807 + 1 = -9223372036854775808. This is because its the largest possible operation using BIGINT datatype).
When a floating-point or fixed-point column is assigned a value that exceeds the range implied by the specified (or default) precision and scale, MySQL stores the value representing the corresponding endpoint of that range
Floating-point overflow produces NULL result. Some operation can result in +INF, -INF or `NaN'.
DECIMAL datatype, if overflowed, will be truncated. And raise warning.
References (from dev.mysql.com):
