Oracle 和 Mysql 的整数类型

1. MySQL整数数据类型

   MySQL有丰富的数据类型，就整数而言，MySQL提供了多达5种整数类型，见下表

类型	字节	最小值	最大值
		(带符号的/无符号的)	(带符号的/无符号的)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

2. Oracle 数据类型

  不像MySQL，表面上看起来，Oracle的数据类型不够丰富。就对应于MySQL整数类型而言，Oracle仅仅提供了number一种数据类型，但事实上，这是一种万用数据类型，不但可覆盖MySQL的所有整数类型，而且表示的数的范围更大。Oracle的number类型的最大精度为38位10进制数，范围为1.0*pow(10,-130)到9.99*pow(10,125)。 下面的例子用于测试Oracle的number类型的精度。值得注意的时，PL/SQL developer对超大精度的支持不够，当定义精度为38位时，在PL/SQL Developer中不能显示表中的数据，下面的命令运行于Oracle客户端sqlplus.

创建表，指定精度为38位十进制整数

create table numtype( n number(38,0));

当插入的数据小于10^38以下时，可正确工作

insert into numtype values(9);
insert into numtype values(99);
insert into numtype values(999);
insert into numtype values(9999);
insert into numtype values(99999999);
insert into numtype values(9999999999999999);
insert into numtype values(99999999999999999999999999999999);
insert into numtype values(99999999999999999999999999999999999);
insert into numtype values(999999999999999999999999999999999999);
insert into numtype values(9999999999999999999999999999999999999);
insert into numtype values(99999999999999999999999999999999999999);

当插入数据超过10^38，报错

insert into numtype values(999999999999999999999999999999999999999);
insert into numtype values(999999999999999999999999999999999999999)
                           *
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度

插入3个38位数，检测精度有无丢失

insert into numtype values(99999999999999999999999999999999999998);
insert into numtype values(99999999999999999999999999999999999997);
insert into numtype values(99999999999999999999999999999999999996);
insert into numtype values(1);
insert into numtype values(0);

显示数据，可以看到,默认的输出精度丢失

SQL> select * from numtype;
         N
----------
          N
----------
         9
        99
       999
      9999
  99999999
1.0000E+16
1.0000E+32
1.0000E+35
1.0000E+36
1.0000E+37
1.0000E+38
1.0000E+38
1.0000E+38
1.0000E+38
         1
         0
已选择16行。

换一种方法，转化为字符串显示，显示38位10进制数字，可以看到，没有精度丢失。

SQL> select to_char(n,'99999999999999999999999999999999999999') from numtype;
TO_CHAR(N,'9999999999999999999999999999
---------------------------------------
                                      9
                                     99
                                    999
                                   9999
                               99999999
                       9999999999999999
       99999999999999999999999999999999
    99999999999999999999999999999999999
   999999999999999999999999999999999999
  9999999999999999999999999999999999999
 99999999999999999999999999999999999999
 99999999999999999999999999999999999998
 99999999999999999999999999999999999997
 99999999999999999999999999999999999996
                                      1
                                      0
已选择16行。

使用DUMP函数，查看其内部格式，可以看到，其内部表示为变长格式，占用空间最小为1字节，最大为20字节

SQL> select n, DUMP(n) from numtype;
         N
----------
DUMP(N)
---------------------------------------------------------------------------------------------------
         9
Typ=2 Len=2: 193,10

        99
Typ=2 Len=2: 193,100

       999
Typ=2 Len=3: 194,10,100

      9999
Typ=2 Len=3: 194,100,100

  99999999
Typ=2 Len=5: 196,100,100,100,100

1.0000E+16
Typ=2 Len=9: 200,100,100,100,100,100,100,100,100

1.0000E+32
Typ=2 Len=17: 208,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100

1.0000E+35
Typ=2 Len=19: 210,10,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100


1.0000E+36
Typ=2 Len=19: 210,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100

1.0000E+37
Typ=2 Len=20: 211,10,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100

1.0000E+38
Typ=2 Len=20: 211,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100

1.0000E+38
Typ=2 Len=20: 211,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,99

1.0000E+38
Typ=2 Len=20: 211,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,98

1.0000E+38
Typ=2 Len=20: 211,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,97
         1
Typ=2 Len=2: 193,2
         0
Typ=2 Len=1: 128
已选择16行。