2016. 2. 1. 18:45

MySQL 수치형 데이타 최대값 구하기

MySQL 연산자 중에 ~ 연산자가 있다.

이 연산자는 bit 열의 1의보수를 구하는 말그대로 비트를 거꾸로 뒤집는 연산자(invert)이다.

이 연산자를 사용하면 결과값으로 unsigned 64-bit integer. 즉 BIGINT UNSIGNED 값이 나온다.

그럼 0을 1의 보수로 구하면 어떻게 될까? 바로 BIGINT UNSIGNED의 최대값이 나오게된다.

이값을 shift 연산이나 Bit and 연산을 하면 쉽게 구할수 있다.


mysql> select ~0;

+----------------------+

| ~0                   |

+----------------------+

| 18446744073709551615 |

+----------------------+

1 row in set (0.00 sec)



mysql> SELECT ~0 as bigint_unsigned

    -> ,      ~0 >> 32 as int_unsigned

    -> ,      ~0 >> 40 as mediumint_unsigned

    -> ,      ~0 >> 48 as smallint_unsigned

    -> ,      ~0 >> 56 as tinyint_unsigned

    -> ,      ~0 >> 1  as bigint_signed

    -> ,      ~0 >> 33 as int_signed

    -> ,      ~0 >> 41 as mediumint_signed

    -> ,      ~0 >> 49 as smallint_signed

    -> ,      ~0 >> 57 as tinyint_signed

    -> \G

*************************** 1. row ***************************

      bigint_unsigned: 18446744073709551615

         int_unsigned: 4294967295

 mediumint_unsigned: 16777215

    smallint_unsigned: 65535

      tinyint_unsigned: 255

         bigint_signed: 9223372036854775807

             int_signed: 2147483647

    mediumint_signed: 8388607

       smallint_signed: 32767

         tinyint_signed: 127

1 row in set (0.00 sec)



mysql> SELECT ~0 as bigint_unsigned

    -> ,      ~0 & 0xFFFFFFFF as int_unsigned

    -> ,      ~0 & 0xFFFFFF as mediumint_unsigned

    -> ,      ~0 & 0xFFFF as smallint_unsigned

    -> ,      ~0 & 0xFF as tinyint_unsigned

    -> ,      ~0 & 0x7FFFFFFFFFFFFFFF as bigint_signed

    -> ,      ~0 & 0x7FFFFFFF as int_signed

    -> ,      ~0 & 0x7FFFFF as mediumint_signed

    -> ,      ~0 & 0x7FFF as smallint_signed

    -> ,      ~0 & 0x7F as tinyint_signed

    -> \G

*************************** 1. row ***************************

   bigint_unsigned: 18446744073709551615

      int_unsigned: 4294967295

mediumint_unsigned: 16777215

 smallint_unsigned: 65535

  tinyint_unsigned: 255

     bigint_signed: 9223372036854775807

        int_signed: 2147483647

  mediumint_signed: 8388607

   smallint_signed: 32767

    tinyint_signed: 127

1 row in set (0.00 sec)