When I tried to fill in a big number, error. How to add the maximum limit of INT?

UPDATE `test`.`number` SET `idNumber` = '36552124313028521236524313028' WHERE (`idNumber` = '365521');

CodePudding user response：

You could try use a BigInt

If you want a number larger than the largest 64-bit unsigned integer 18,446,744,073,709,551,615 then you will need to store it as a varchar or some other Text form

refer to the MySQL data types for further info

CodePudding user response：

It depends on how the column is to be used. For calculations or auto_increment attribute, numerics should be used. As you say you would like to add a maximum limit, by ADD I suppose you would like to define a length value to your liking. However, the whole number types such as small int, int, big int have a predefined maximum range , which can not be changed.（MySQL 8.0 users may try the check option, which is ignored in previous versions） If you need to define the limit for the whole number, there is a workaround by using decimal(n,0) to make the number always appear as a whole number.
For idenfiers which do not require calculations, varchar is suggested for strings that have a dynamic range, and char is more suitable for those having a static length,such as province acronym e.g AZ (Arizona) AR (Arkansas) CA (California)
Last but not least. Please refrain from using a varchar for string-looking values that are prone to calculations,such as IP ADDRESS. It appears as a string in its dotted format, but deep inside it has an inherent nature of numerics. For instance, IPV4 has a range from 0.0.0.0 to 255.255.255.255 , which can be treated as a formula of (256 * 256 * 256 * 256) . Thus it is a perfect fit for the unsigned integer type in terms of length and can be calculated when necessary. To display it in its dotted format , use the inet_ntoa() function. e.g select inet_ntoa(3232235777);