{question}
How can we avoid inserting a number that is out of the range limit of the datatype?
{question}
{answer}
The global sync variable data_conversion_compatibility_level controls the way certain data conversions are performed. This variable can have the following possible values: '6.0', '6.5', '7.0'. Higher values introduce stricter checking of values, and will error for conversions that may have worked at lower levels. E.g., the '7.0' level will fail the conversion of 'abc' to an integer value, while the '6.0' level will silently convert it to a NULL or 0. It’s recommended to set this variable to the highest available value for new application development.
The following example is for SMALLINT. We will check it for signed and unsigned smallint.
Each integer type can be “unsigned”, doubling the positive range of values and disallowing values under 0. The SMALLINT signed range is: -32768 to 32767
The SMALLINT unsigned range is 32767+32768=65535.
If your variable data_conversion_compatibility_level is set to 6.0, you will be able to insert a value that is out of range without any error message, but the actual value will be up to the range limit value. Check the example below which has a table named tab1 with columns nsmall signed (the default) smallint, usmall unsigned.
mysql> set global data_conversion_compatibility_level = "6.0";
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tab1 values(32800, 65540);
Query OK, 1 row affected (0.04 sec)mysql> select * from tab1;
+--------+--------+
| nsmall | usmall |
+--------+--------+
| 32767 | 65535 |
+--------+--------+
To avoid this behavior and enforce an error message once you insert an out of range value, we need to set data_conversion_compatibility_level to 6.5 or 7.0.
Command:
set global data_conversion_compatibility_level="6.5";
set global data_conversion_compatibility_level="7.0";
Error:
mysql> insert into tab1 values(32800, 65536);
ERROR 1264 (22003): Leaf Error (10.0.3.226:3307): Out of range value for column 'nsmall'
mysql> insert into tab1 values(32767, 65540);
ERROR 1264 (22003): Leaf Error (10.0.3.226:3307): Out of range value for column 'usmall'
In value range:
mysql> insert into tab1 values(32767, 65535);
Query OK, 1 row affected (0.00 sec)mysql> select * from tab1;
+--------+--------+
| nsmall | usmall |
+--------+--------+
| 32767 | 65535 |
+--------+--------+
Note: It is recommended to test the settings before implementing them in production as it might impact your applications due to the stricter check.
{answer}