MySQL、PostgreSQL和DB2之间的数据类型对比
SQL ANSI标准规定了关系数据库系统中使用的数据类型的规则。但是,并非每种数据库平台都支持标准委员会定义的每个数据类型。而且,特定数据类型的厂商实现可能与标准的规定不同,甚至在所有数据库厂商之间互不相同。因此,尽管许多MySQL、PostgreSQL和DB2数据类型在名称和/或含义方面是相似的,但是也有许多需要注意的差异。
表2列出最常用的DB2数据类型。我们在后面的小节中提供MySQL和PostgreSQL数据类型与DB2最接近的匹配。
尽管DB2对SQL有一些限制(比如对约束名的长度限制、数据类型限制等等),但是各个新版本正在系统化地消除这些限制。
表2. DB2数据类型
数据类型说明
BIGINT
存储有符号或无符号整数,使用8字节的存储空间。
BLOB
BLOB(n)
存储长度可变的二进制数据,长度最大为2 GB。超过1 GB的长度不进行日志记录。
CHAR(n)
CHARACTER(n)
存储固定长度的字符数据,长度最大为254字节。使用 ‘n’字节的存储空间。
CHAR(n) FOR BIT DATA
存储固定长度的二进制值。
CLOB
CLOB(n)
存储长度可变的字符数据,长度最大为2 GB。超过1 GB的长度不进行日志记录。
DATE
存储日历日期,不包含天内的时间。使用4字节的存储空间。
DEC(p,s)
DECIMAL(p,s)
NUM(p,s)
NUMERIC(p,s)
采用精度(p)1到31和刻度(s)0到31来存储数值。使用(p/2) +1字节的存储空间。
DOUBLE
DOUBLE PRECISION
FLOAT
存储浮点数,使用8字节的存储空间。
FLOAT(p)
采用精度(p)1到53来存储数值。如果p <= 24,那么相当于REAL。如果p >= 25,那么相当于DOUBLE PRECISION。
GRAPHIC(n)
用于National Language Support(NLS)和长度固定的字符串(常常是DBCS),长度最大为127字节。对于双字节字符集,使用n*2字节的存储空间;对于单字节字符集,使用n字节的存储空间。
INT
INTEGER
存储有符号或无符号整数,使用4字节的存储空间。
REAL
存储浮点数,使用4字节的存储空间。
SMALLINT
存储有符号和无符号整数,使用2字节的存储空间。
TIME
存储天内的时间,使用3字节的存储空间。
TIMESTAMP
存储日期(年、月、日)和时间(小时、分钟、秒),最大精度6毫秒。使用10字节的存储空间。
VARCHAR(n)
CHAR VARYING(n)
CHARACTER VARYING(n)
存储长度可变的字符数据,长度最大为32,672字节。使用n+2字节的存储空间。
VARCHAR(n) FOR BIT DATA
存储长度可变的二进制数据。使用n字节的存储空间。
VARGRAPHIC(n)
存储长度可变的双字节字符数据,长度最大为16,336字符。使用(n*2)+2字节的存储空间。
MySQL和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别名作为数据类型,这相当于BIGINT UNSIGNED 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
用于存储二进制对象(比如图形)的原始二进制数据,最大16,777,215字节。
LONGBLOB
用于存储二进制对象(比如图形)的原始二进制数据,最大4GB。
CHAR(n)
CHARACTER(n)
包含固定长度的字符串,用空格填充到长度n。
DATE
用3字节的存储空间存储日历日期(年、月、日)。
DATETIME
用8字节的存储空间存储日历日期和天内的时间。
YEAR
用1字节的存储空间存储两位或四位格式的年份。
DECIMAL(p,s)
NUMERIC(p,s)
存储精确的数值,精度(p)最高为65,刻度(s)为30或更高。
FLOAT
存储浮点数,限制由硬件决定。单精度浮点数精确到大约7位小数。UNSIGNED属性不允许负数。
DOUBLE
REAL
存储双精度浮点数,限制由硬件决定。双精度浮点数精确到大约15位小数。UNSIGNED属性不允许负数。
TINYINT
存储有符号或无符号1字节整数。
SMALLINT
存储有符号或无符号2字节整数。
MEDIUMINT
存储有符号或无符号3字节整数。
INTEGER
存储有符号或无符号4字节整数。
BIGINT
存储有符号或无符号8字节整数。
TINYTEXT
用于存储最多255字节的字符串数据。
TEXT
用于存储最多65,535字节的字符串数据。
MEDIUMTEXT
用于存储最多16,777,215字节的字符串数据。
LONGTEXT
用于存储最多4GB的字符串数据。
TIME
用3字节的存储空间存储天内的时间。
TIMESTAMP
用4字节的存储空间存储日期和时间。如果没有提供有效值的话,TIMESTAMP列会自动设置为最近操作的日期和时间。
VARCHAR(n)
CHARACTER VARYING(n)
CHARACTER VARYING
存储长度可变的字符串,最大长度由n指定。末尾的空格不存储。
ENUM
一种串对象,它的值只能是从值列表 ‘value1’, ‘value2’, ..., NULL中选择的一个值。
SET
一种串对象,它可以具有零个或更多的值,这些值必须从值列表 ‘value1’, ‘value2’, ...中选择。
BINARY
与CHAR类型相似,但是存储二进制字节串而不是字符串。
VARBINARY
与VARCHAR类型相似,但是存储二进制字节串而不是字符串。
表4. MySQL数据类型到DB2的映射
MYSQLDB2说明
BIT
CHAR(n) FOR BIT DATA
关于用来简化迁移的UDF的细节,请参阅参考资料。
BOOLEAN
SMALLINT或CHAR(1)
使用检查约束来实施规则。
TINYBLOB
VARCHAR(255) FOR BIT DATA
可以使用BLOB(255)或VARCHAR(255) FOR BIT DATA。在这种情况下,使用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
可以使用特殊寄存器CURRENT TIMEZONE对日期进行转换。
YEAR
SMALLINT
可以使用检查约束实施YEAR规则。
DECIMAL(p,s)
NUMERIC(p,s)
DECIMAL(p,s)
NUMERIC(p,s)
如果p大于31,那么使用DOUBLE。
FLOAT
REAL
_
DOUBLE
REAL
DOUBLE
_
SMALLINT
SMALLINT
使用检查约束限制值小于256。
SMALLINT
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。使用NOT LOGGED选项可以提高性能。
TIME
TIME
_
TIMESTAMP
TIMESTAMP
_
VARCHAR(n)
CHARACTER VARYING(n)
VARCHAR(n)
CHARACTER VARYING(n)
如果长度小于32K,那么使用VARCHAR。
ENUM
VARCHAR(n)
使用检查约束来实施规则。