数据库数据类型

oracle数据类型

一、字符类型

• char 定长字符类型（未达到指定长度时，自动在末尾用空格补全）；默认值为1；最大2000字节；非unicode。
• nchar 定长字符类型（未达到指定长度时，自动在末尾用空格补全）；默认值为1；最大1000字节；根据unicode，所有字符都占两个字节。
• varchar2 变长字符类型（未达到指定长度时，不自动补全空格）；定义时需指定长度；最大为4000字节；非unicode。
• nvarchar2 变长字符类型（未达到指定长度时，不自动补全空格）；定义时需指定长度；最大为2000字节；根据unicode，所有字符都占两个字节。

说明：（1）unicode字符集是为了解决字符集不兼容的问题而产生的，所有字符都用两个字节表示，即英文字符也用两个字节表示。（2）以上的最大长度都是指字节长度，而非字符个数，如char(1)就连一个汉字都不能存放。

举例：

create table test_char(
country_id char,
city_id nchar(2),
address varchar2(4000),
name nvarchar2(4)
);

插入数据并查询：

insert into test_char values ('1', '中国', '浙江省杭州市西湖区', '司马相如');

二、数值类型

oracle中的数值类型主要为三种：number，binary_float，binary_double，其他的类型基本上都是number类型的子类型。

1. number(p,s)类型

• p精度，表示包括小数在内的总共有效位数，p的取值范围为[138]（若没有指定，默认为38）；s表示精确到多少位，取值范围为[-84127]（若没有指定，默认为0），当s取负数时，将小数点左边的s位置为0，并四舍五入。
• 当小数位数大于s时，采用四舍五入的方式。当整数部分的长度大于p-s时，报错。
• 需要22个字节的存储空间。

官方文档给出的几个例子如下：

\2. number类型的子类型

• integer或int是number的子类型，等同于number(38)
• smallint是number的子类型，等同于number(38)
• decimal是number(p,s)的子类型，可以使用decimal(p,s)，若p，s未指定，等同于number(38)
  \3. float类型
• float(b)，数b表示二进制进度，b的取值范围为[1,126]，默认为126
• real是float(b)的子类型，等同于float(63)

上述的b是一个二进制精度（binary precision），而不是我们通常说的十进制精度（decimal precision），需要进行以下的转换：binary precision＝int(b*0.30103)

举例来说：当b=2，则对应的十进制精度为int(2*0.30103)=0，即小数点后精度为0。

eg：56.2存储到float(2)变为60。计算过程：56.2=5.62101，因为精度为0只能取到整数，5.62四舍五入后为6，最后取值为6101=60

\4. binary_float和binary_double类型

• binary_float 32位单精度浮点数数据类型，需要5个字节（4字节+1个长度字节）支持至少6位精度。
• binary_double 64位双精度浮点数数据类型，需要9个字节（8字节+1个长度字节）。

举例：

create table test_num(
n1 number,
n2 number(38),
n3 number(9,2),
n4 int,
n5 smallint,
n6 decimal(5,2),
n7 float,
n8 float(2),
n9 real,
n10 binary_float,
n11 binary_double
);

插入数据并查询结果：

insert into test_num values (1.23, 123, 7456123.89, 573, 34, 673.43, 34.1264, 56.2, 23.231, 12.34f, 34.56d);
select * from test_num;

数值类型总结：binary_float和binary_double不常用，因为其表示范围更大可用于科学计算，但精度没有number类型高（可用于金融数据），而float表示的是二进制精度，需要进行精度转换。综上，在使用Oracle的数值类型时我们最好都采用number(p,s)的方式。

三、日期类型

• date 存储以下信息：世纪，年，月，日，时，分，秒。占用七个字节存储空间，每个部分占用一个字节。
• timestamp(precision) ，precision的取值范围为[0,9]，默认为6（微秒），最大为9（纳秒）；7字节或12字节的定宽日期/时间数据类型；秒的小数部分位数超过precision但未超过9时，按精度四舍五入，超过9时报错
• timestamp(precision) with time zone 该类型是在timestamp(precison)的基础上加入了时区偏移量的值。
• timestamp with local time zone 存储时转化为数据库时区进行规范化存储，但不存储时区信息，客户端检索时，按客户端时区的时间数据返回给客户端
• interval year(precision) to month 可以用来表示几年几月的时间间隔，precision的取值范围为[0~9]，默认为2表示不超过两位数的一个数
• interval day(days_precision) to second(seconds_precision) 可以用来存储天、小时、分和秒的时间间隔，days_preciosn取值范围为[09]，默认是2，seconds_precision取值范围为[09]，默认是6

举例：

create table test_date(
t1 date,
t2 timestamp(6),
t3 timestamp(9) with time zone,
t4 timestamp with local time zone,
t5 interval year(3) to month,
t6 interval day(3) to second(6)
);

插入数据并查询结果

insert into test_date (t1, t2, t3, t4) values (sysdate, sysdate, sysdate, sysdate);
insert into test_date (t1) values (to_date('2013-1-21 5:23:01','yyyy-mm-dd hh24:mi:ss'));
insert into test_date (t1) values (to_date('20131112203256', 'yyyymmddhh24miss'));
insert into test_date (t2) values (to_timestamp('20191112203357.999997623', 'yyyymmddhh24miss.ff'));
insert into test_date (t3) values (to_timestamp_tz('20191112203357.999996623', 'yyyymmddhh24miss.ff'));
insert into test_date (t4) values (to_timestamp_tz('20191112203357.999996623', 'yyyymmddhh24miss.ff'));
insert into test_date (t5) values (interval '11' year);
insert into test_date (t5) values (interval '223-9' year(3) to month);
insert into test_date (t6) values (interval '12 10:23:01.1234568' day to second);

注意：第四条t2数据超过精度四舍五入，第六条t4数据超过精度四舍五入。这里先留一个问题：第八条t5记录，插入的月份没有正常显示（知道原因的可以和我讨论）。

四、long类型与lob大型对象数据类型

• long 文本类型，最多达2GB；long列不能作为主键或唯一约束；存储时需要进行字符集转换；一个表中只有一列可以为long或long raw；不支持分布式事务；限制较多
• long raw 可变长二进制数据，不用进行字符集转换的数据，最长2GB。
• blob二进制大型对象，存储图片，音乐，视频等信息，通常将文件转为二进制再存进去；存储的最大大小为4G；支持随机存储
• clob字符大型对象，存储文章或较长的文字。存储的最大大小为4G。
• nclob存储Unicode类型的数据，根据字符集而定的字符类型，最大长度4G。
• bfile二进制文件，存放指向操作系统文件的指针；指向的文件不是数据库的一部分，只能在数据库外维护；只读，数据库将该文件当二进制文件处理。

举例：

create table test_bigfile(
f1 long,
f2 blob,
f3 clob,
f4 nclob,
f5 bfile
);

数据插入与查询：

insert into test_bigfile (f1, f2, f3, f4) values ('12323232', '2312', '111', '212');
insert into test_bigfile (f1, f2, f3, f4) values ('12323232', '231234', '111', '212');

说明：bfile类型的插入需要指定文件所在的目录位置，类似于 insert into test_bigfile (f5) values (bfilename(‘bfiledir’, ‘bfile1.txt’));其中bfiledir为文件路径，这里就不在扩展；long类型因为其限制过多，我们通常用blob，clob类型来代替。

五、rowid & urowid类型

• rowid 行地址，十六进制串，表示行在所在的表中唯一的行地址，该数据类型主要用于返回ROWID伪列，常用在可以将表中的每一条记录都加以唯一标识的场合，使用rowid来建立内部索引。
• urowid支持逻辑的和物理的rowids。

我们以上述test_bigfile表为例：

select rowid from test_bigfile;

上述两条记录的查询结果为：

解释：从rowid伪列里查询出来的rowid是基于base64编码，一共有18位，分为4部分：OOOOOOFFFBBBBBBRRR

OOOOOO： 六位表示data object id，根据object id可以确定segment； FFF： 三位表示相对文件号；BBBBBB：六位表示data block number； RRR：三位表示row number，以此来确定唯一的行地址。

MySQL 数据类型

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。