到目前为止,我们已经看到了DB2在体系结构和特性方面的一些差异。现在就来研究这些数据库服务器在数据类型方面的差异。 MySQL、PostgreSQL和 DB2 之间的数据类型对比 SQLANSI标准规定了关系数据库系统中使用的数据类型的规则。但是,并非每种数据库平台都支持标准委员会定义的每个数据类型。而且,特定数据类型的厂商实现可能与标准的规定不同,甚至在所有数据库厂商之间互不相同。因此,尽管许多MySQL、PostgreSQL和 DB2数据类型在名称和/或含义方面是相似的,但是也有许多需要注意的差异。 表 2 列出最常用的 DB2数据类型。我们在后面的小节中提供 MySQL和 PostgreSQL 数据类型与DB2 最接近的匹配。 尽管 DB2对 SQL有一些限制(比如对约束名的长度限制、数据类型限制等等),但是各个新版本正在系统化地消除这些限制。 表2. DB2 数据类型 |
|
|
数据类型 |
说明 |
BIGINT |
存储有符号或无符号整数,使用 8字节的存储空间。 |
BLOB BLOB(n) |
存储长度可变的二进制数据,长度最大为 2GB。超过 1 GB的长度不进行日志记录。 |
CHAR(n) CHARACTER(n) |
存储固定长度的字符数据,长度最大为 254字节。使用 ‘n’字节的存储空间。 |
CHAR(n) FOR BIT DATA |
存储固定长度的二进制值。 |
CLOB CLOB(n) |
存储长度可变的字符数据,长度最大为 DATE |
存储日历日期,不包含天内的时间。使用 4字节的存储空间。 |
DEC(p,s) DECIMAL(p,s) NUM(p,s) NUMERIC(p,s) |
采用精度(p)1到 31 和刻度(s)0到 31 来存储数值。使用(p/2) +1 字节的存储空间。 |
DOUBLE DOUBLEPRECISION FLOAT |
存储浮点数,使用 8字节的存储空间。 |
FLOAT(p) |
采用精度(53 来存储数值。如果p <= 24,那么相当于REAL。如果 p>= 25,那么相当于 DOUBLEPRECISION。 |
GRAPHIC(n) |
用于 National LanguageSupport(NLS)和长度固定的字符串(常常是DBCS),长度最大为127 字节。对于双字节字符集,使用n*2 字节的存储空间;对于单字节字符集,使用n 字节的存储空间。 |
INT INTEGER |
存储有符号或无符号整数,使用 4字节的存储空间。 |
REAL |
存储浮点数,使用 SMALLINT |
存储有符号和无符号整数,使用 2字节的存储空间。 |
TIME |
存储天内的时间,使用 3字节的存储空间。 |
TIMESTAMP |
存储日期(年、月、日)和时间(小时、分钟、秒),最大精度6 毫秒。使用 10字节的存储空间。 |
VARCHAR(n) CHARVARYING(n) CHARACTER VARYING(n) |
存储长度可变的字符数据,长度最大为 32,672字节。使用 n+2字节的存储空间。 |
VARCHAR(n) FOR BIT DATA |
存储长度可变的二进制数据。使用 n字节的存储空间。 |
VARGRAPHIC(n) |
存储长度可变的双字节字符数据,长度最大为16,336 字符。使用(n*2)+2 字节的存储空间。 |
DB2
|
理解MySQL 和DB2 之间的数据类型问题
-
从性能方面考虑,将少于 32K的 BLOB 和CLOB 迁移为VARCHAR(n) WITH BIT DATA 或VARCHAR(n)。迁移工具通过研究表中的实际数据处理这一转换。
-
迁移工具处理 UNSIGNED数据类型。
-
将 Boolean 数据类型迁移到SMALLINT 或CHAR(1)。
-
如果小数精度大于 31,那么迁移工具将列转换为双精度数据类型。
-
自动递增的列迁移为数值数据类型并使用IDENTITY 子句。
|
|
下面的表中描述 MySQL 和 DB2 数据类型的定义和差异。表3 描述最常用的 MySQL 数据类型。表 4 将 MySQL数据类型映射到最接近的 DB2 数据类型。 MySQL可以使用 SERIAL别名作为数据类型,这相当于 BIGINTUNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 。 BOOL或 BOOLEAN 是TINYINT(1) 的同义词。在MySQL 中,DECIMAL的最大位数是 65,支持的最大小数位是30。如果为 DECIMAL指定 UNSIGNED,那么不允许负数。 时间戳列不支持毫秒。 3. MySQL 数据类型 |
数据类型 |
说明 |
BIT |
固定长度的位串。 |
BOOLEAN |
存储逻辑布尔值(true/false/unknown),可以是TRUE、true和 1;FALSE、false和 0。 |
TINYBLOB |
用于存储二进制对象(比如图形)的原始二进制数据,最大255 字节。 |
BLOB |
用于存储二进制对象(比如图形)的原始二进制数据,最大65,535 字节。 |
MEDIUMBLOB |
用于存储二进制对象(比如图形)的原始二进制数据,最大 LONGBLOB |
用于存储二进制对象(比如图形)的原始二进制数据,最大4GB。 |
CHAR(n) CHARACTER(n) |
包含固定长度的字符串,用空格填充到长度 n。 |
DATE |
用 3 字节的存储空间存储日历日期(年、月、日)。 |
DATETIME |
用 8 字节的存储空间存储日历日期和天内的时间。 |
YEAR |
用 1 字节的存储空间存储两位或四位格式的年份。 |
DECIMAL(p,s) |
存储精确的数值,精度(p)最高为65,刻度(s)为30 或更高。 |
FLOAT |
存储浮点数,限制由硬件决定。单精度浮点数精确到大约7 位小数。UNSIGNED属性不允许负数。 |
DOUBLE REAL |
存储双精度浮点数,限制由硬件决定。双精度浮点数精确到大约15 位小数。TINYINT |
存储有符号或无符号 1字节整数。 |
SMALLINT |
存储有符号或无符号 2字节整数。 |
MEDIUMINT |
存储有符号或无符号 3字节整数。 |
INTEGER |
存储有符号或无符号 4字节整数。 |
BIGINT |
存储有符号或无符号 8字节整数。 |
TINYTEXT |
用于存储最多 255字节的字符串数据。 |
TEXT |
用于存储最多 MEDIUMTEXT |
用于存储最多 LONGTEXT |
用于存储最多 4GB的字符串数据。 |
TIME |
用 3 字节的存储空间存储天内的时间。 |
TIMESTAMP |
用 4字节的存储空间存储日期和时间。如果没有提供有效值的话,TIMESTAMP列会自动设置为最近操作的日期和时间。 |
VARCHAR(n) CHARACTERVARYING(n) CHARACTER VARYING |
存储长度可变的字符串,最大长度由 n指定。末尾的空格不存储。 |
ENUM |
一种串对象,它的值只能是从值列表 ‘value1’,‘value2’,...,NULL 中选择的一个值。 |
SET |
一种串对象,它可以具有零个或更多的值,这些值必须从值列表‘ BINARY |
与 CHAR类型相似,但是存储二进制字节串而不是字符串。 |
VARBINARY |
与 VARCHAR类型相似,但是存储二进制字节串而不是字符串。 |
表4. MySQL 数据类型到DB2 的映射 |
MYSQL |
DB2 |
说明 |
BIT |
CHAR(n) FOR BIT DATA |
关于用来简化迁移的 UDF的细节,请参阅 参考资料。 |
BOOLEAN |
SMALLINT 或 CHAR(1) |
使用检查约束来实施规则。 |
TINYBLOB |
VARCHAR(255) FOR BIT DATA |
可以使用 BLOB(255) 或VARCHAR(255) FOR BITDATA。在这种情况下,使用 VARCHAR效率比较高。 |
BLOB |
BLOB(64K) |
如果长度小于 32K,那么考虑使用VARCHAR(n) FOR BIT DATA。 |
MEDIUMBLOB |
BLOB(16M) |
可以使用 NOT LOGGED改进性能。 |
LONGBLOB |
BLOB(2G) |
支持的 BLOB 最大长度是2GB。 |
CHAR(n) CHARACTER(n) |
CHAR(n) CHARACTER(n) |
在 DB2 中,‘n’的最大值为 254。 |
DATE |
DATE |
- |
DATETIME |
TIMESTAMP |
可以使用特殊寄存器 CURRENTTIMEZONE 对日期进行转换。 |
YEAR |
SMALLINT |
可以使用检查约束实施 YEAR规则。 |
如果 p 大于31,那么使用DOUBLE。 |
FLOAT |
REAL |
_ |
DOUBLE REAL |
DOUBLE |
SMALLINT |
SMALLINT |
使用检查约束限制值小于 256。 |
SMALLINT |
MEDIUMINT |
INTEGER |
如果需要,使用检查约束限制最大长度。 |
INTEGER |
INTEGER INT |
BIGINT |
BIGINT |
TINYTEXT |
VARCHAR(255) |
对于少于 32K 的数据,使用VARCHAR 比较高效。 |
TEXT |
CLOB(64K) |
DB2 允许为 CLOB或 BLOB指定长度参数。指定需要的长度,而不要使用TINY、MEDIUM或 LONG CLOB。 |
MEDIUMTEXT |
CLOB(16M) |
LONGTEXT |
CLOB(2G) |
最大长度是 2GB。如果使用LOGGED,那么 BLOB或 CLOB 的最大长度为1GB。使用 NOTLOGGED 选项可以提高性能。 |
TIME |
TIME |
TIMESTAMP |
TIMESTAMP |
VARCHAR(n) CHARACTERVARYING(n) |
VARCHAR(n) CHARACTERVARYING(n) |
如果长度小于 32K,那么使用VARCHAR。 |
ENUM |
VARCHAR(n) |
使用检查约束来实施规则。 |
SET |
BINARY |
CHAR(n) FOR BIT DATA |
如果 n 小于254,那么使用CHAR(n) FOR BIT DATA;否则使用VARBINARY |
VARCHAR(n) FOR BIT DATA |
如果 ‘n’ 小于VARCHAR;否则使用BLOB。 |
DB2
|
DB2的数据类型问题
-
在 PostgreSQL中可以使用 TEXT数据类型替代 CHAR(n)或 VARCHAR(n)。如果存储的值的最大长度小于32K,那么将TEXT 列迁移为CLOB数据类型可能不是最好的选择。迁移工具通过检查数据对TEXT 列进行正确的转换。
-
PostgreSQL 可以使用精度大于31 的NUMERIC。将精度大于31 的 NUMERIC迁移为 DB2 的DOUBLE 数据类型。
-
将 Boolean 数据类型迁移为CHAR(1)。这可能要求在应用程序中做一些修改。
-
BIGSERIAL 数据类型映射到DB2 的 BIGINT并加上 IDENTITY属性。
-
PostgreSQL支持对混合表达式中的数据类型进行隐式转换。DB2支持对混合表达式中的数据类型进行显式转换。
|
|
下面两个表描述 DB2 和 PostgreSQL 数据类型的定义和差异。表5 描述最常用的 PostgreSQL 数据类型。表 6 将PostgreSQL 数据类型映射到最接近的 DB2 数据类型。 PostgreSQL使用特殊的网络地址类型,比如inet 、cidr 、macaddr 。这些数据类型迁移到DB2 中的 VARCHAR数据类型。 PostgreSQL还支持几何数据类型。迁移工具不处理几何数据类型。目前,我们假设不太需要支持对这种数据类型进行转换。如果您使用几何数据类型,那么请告诉我们,我们将在工具中提供补丁。 处理 PostgreSQL中的位串数据类型需要在应用程序中做一些修改。目前,工具不提供这种支持。如果需要这种支持,请告诉我们。 PostgreSQL还支持多维数组,它们最好迁移成 DB2中的子表。但是,工具目前不支持多维数组。 5. PostgreSQL 数据类型 |
数据类型 |
说明 |
BIGSERIAL SERIAL8 |
存储自动递增的惟一整数,最多 8字节。 |
BIT |
固定长度的位串。 |
BIT VARYING(n) VARBIT(n) |
可变长度的位串,长度为 n位。 |
BOOLEAN |
存储逻辑布尔值(t、true、y、yes和 1,或者f、false、n、no和 0。 |
BYTEA |
用于存储大型二进制对象(比如图形)的原始二进制数据。使用的存储空间是4 字节加上二进制串的长度。 |
CHAR(n) CHARACTER(n) |
包含固定长度的字符串,用空格填充到长度 n。 |
DATE |
用 4 字节的存储空间存储日历日期(年、月、日)。 |
DATETIME |
存储日历日期和天内的时间。 |
存储精确的数值,精度(p)和刻度(0 或更高。 |
FLOAT4 REAL |
存储浮点数,精度为 8或更低和 6 个小数位。 |
FLOAT8 DOUBLE PRECISION |
存储浮点数,精度为 16或更低和 15 个小数位。 |
SMALLINT |
存储有符号或无符号 2字节整数。 |
INTEGER |
存储有符号或无符号 4字节整数。 |
INT8 BIGINT |
存储有符号或无符号 8字节整数。 |
SERIAL SERIAL4 |
存储自动递增的惟一整数值,最多 4字节存储空间。 |
TEXT |
存储长度可变的大型字符串数据,最多 1GB。PostgreSQL 自动压缩TEXT 字符串。 |
TIME (WITHOUT TIME ZONE| WITH TIME ZONE) |
存储天内的时间。如果不存储数据库服务器的时区,就使用8 字节的存储空间;如果存储时区,就使用12 字节。 |
TIMESTAMP (WITHOUT TIMEZONE | WITH TIME ZONE) |
存储日期和时间。可以存储或不存储数据库服务器的时区,使用8 字节存储空间。 |
VARCHAR(n) CHARACTERVARYING(n) CHARACTER VARYING |
存储可变长度的字符串,最大长度为 n。不存储末尾的空格。 |
6. PostgreSQL 数据类型到DB2 的映射 |
POSTGRESQL |
DB2 |
说明 |
BIGSERIAL SERIAL8 |
BIGINT |
使用 IDENTITY 属性模拟自动递增特性。 |
BIT |
CHAR(n) FOR BIT DATA |
对于长度最大为 254字节的字符串。 |
BIT VARYING(n) VARBIT(n) |
VARCHAR(n) FOR BIT DATA |
用于 BYTEA |
BLOB |
用于 32K 和 2GB字节之间的数据。 |
BOOLEAN |
无布尔类型 |
使用 CHAR(1) 或SMALLINT。 |
CHAR(n) CHARACTER (n) |
CHAR(n) |
最多 254 字节。 |
DATE |
DATE |
可以使用特殊寄存器 CURRENTTIMEZONE 对日期进行转换。 |
DATETIME |
TIMESTAMP |
可以使用特殊寄存器 如果精度大于 DOUBLE。 |
FLOAT4 REAL |
REAL |
可以使用 NUMERIC 或FLOAT。 |
FLOAT8 DOUBLE PRECISION |
DOUBLE PRECISION |
对于大数值使用 DOUBLEPRECISION,如果精度小于 NUMERIC。 |
SMALLINT |
SMALLINT |
_ |
INTEGER |
INTEGER |
INT8 BIGINT |
BIGINT |
VARCHAR(n) CHARACTERVARYING(n) CHARACTER VARYING |
VARCHAR(n) |
如果 ‘n’ 小于等于32K。DB2要求指定 ‘n’,而PostgreSQL 不要求指定‘n’ 的值。 |
SERIAL SERIAL4 |
INTEGER |
使用 IDENTITY 属性。 |
TEXT |
VARCHAR(n) CLOB |
如果长度小于 VARCHAR;如果大于BLOB。 |
TIME (WITHOUT TIME ZONE |WITH TIME ZONE) |
TIME |
没有时区。 |
TIMESTAMP (WITHOUT TIMEZONE | WITH TIME ZONE) |
TIMESTAMP |
没有时区。 |
在 PostgreSQL中,即使在引用的表中数据类型不同,也可以创建外键约束。例如,如果父表的惟一键的数据类型是整数,那么可以在子表中数据类型为char(10)的列上创建外键。工具将转换这个约束,但是会失败,因为DB2 不允许数据类型不同。 |