Search code examples
mysqldatabase-schemaunsigned-integer

Representing n-bit unsigned integers in mysql

How do I represent this data in mysql?

16 bit unsigned integer -----Range: 0x0000 - 0xFFF7
64 bit unsigned int.  Depicted as xx:xx:xx:xx:xx:xx:xx:xx -----Range: 0x0000 - 0xFFFFFFFFFFFFFFFF
2 bits ----- 00 - None,  01 - Residential  Security,  10 - High Security
32 bit unsigned int

Should I convert everything to string and convert it at application layer?

Solution

  • According to MySQL's Overview of Numeric Types:

    • UNSIGNED SMALLINT: range is 0 to 65535. This would be sufficient for 16-bit unsigned ints.
    • UNSIGNED TINYINT: range is 0 to 255. Sufficient for 2-bit unsigned int. It appears you would need to preserve leading zeroes, so use ZEROFILL too. To keep the value to just two characters wide, you can specify UNSIGNED ZEROFILL TINYINT(2).
    • UNSIGNED INT: range is 0 to 4294967295. Sufficient for 32-bit unsigned int.
    • UNSIGNED BIGINT: range is 0 to 18446744073709551615. See below:

    The last one, the 64-bit unsigned int, has a couple of caveats, from the above linked page:

    • All arithmetic is done using signed BIGINT or DOUBLE values, so you should not use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting a BIGINT value to a DOUBLE.
    • You can always store an exact integer value in a BIGINT column by storing it using a string. In this case, MySQL performs a string-to-number conversion that involves no intermediate double-precision representation.
    • The -, +, and * operators use BIGINT arithmetic when both operands are integer values. This means that if you multiply two big integers (or results from functions that return integers), you may get unexpected results when the result is larger than 9223372036854775807.