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 toBIGINT(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 (-) ifNO_UNSIGNED_SUBTRACTIONSQL mode is enabled, the result will be signed.If any of the operands of a
+,-,/,*,%is real (e.g.FLOATorDOUBLE) 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
TINYINTwill 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
BIGINTdatatype).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
NULLresult. Some operation can result in+INF,-INFor `NaN'.DECIMALdatatype, if overflowed, will be truncated. And raise warning.References (from dev.mysql.com):