5.1. 数据类型

本节主要介绍统一SQL支持原生Oracle数据库中的SQL数据类型的对比信息

序号	数据类型分类	Oracle 数据类型	统一SQL	TDSQL-MySQL 数据类型
1	数字	BINARY_FLOAT	支持	FLOAT
2	数字	BINARY_DOUBLE	支持	DOUBLE
3	大对象	BFILE	不支持
4	大对象	BLOB	支持	LONGBLOB
5	字符	CHAR(n),CHARACTER(n)	支持	CHAR(n)
6	大对象	CLOB	支持	LONGTEXT
7	时间&日期	DATE	支持	DATETIME
8	数字	DECIMAL(p,s),DEC(p,s)	支持	如下文所示
9	数字	DOUBLE PRECISION	支持	DOUBLE
10	数字	FLOAT(p)	支持	FLOAT(p)
11	数字	INTEGER,INT	支持	BIGINT
12	时间&日期	INTERVAL YEAR(p) TO MONTH	不支持
13	时间&日期	INTERVAL DAY(p) TO SECOND(s)	不支持
14	其他类型	LONG	支持	MEDIUMTEXT
15	其他类型	LONG RAW	支持	LONGBLOB
16	字符	NCHAR(n)	支持	NCHAR(n)
17	字符	NCHAR VARYING(n)	支持	NVARCHAR(n)
18	大对象	NCLOB	支持	LONGTEXT
19	数字	NUMBER(p,s),NUMERIC(p,s)	支持	DECIMAL(p,s)
20	字符	NVARCHAR2(n)	支持	NVARCHAR(n)
21	其他类型	RAW(n)	支持	VARBINARY(n)
22	其他类型	REAL	支持	REAL
23	其他类型	ROWID	不支持
24	数字	SMALLINT	支持	BIGINT
25	时间&日期	TIMESTAMP(p)	支持	TIMESTAMP(p)
26	时间&日期	TIMESTAMP(p) WITH TIME ZONE	支持	TIMESTAMP(p)
27	时间&日期	TIMESTAMP(p) WITH LOCAL TIME ZONE	不支持
28	其他类型	UROWID(n)	不支持
29	字符	VARCHAR(n)	支持	VARCHAR(n)
30	字符	VARCHAR2(n)	支持	VARCHAR(n)
31	XML类型	XMLType	不支持

警告

在Oracle中，FLOAT数据类型支持参数的范围：

FLOAT(n): [1,126]

TDSQL-MySQL中FLOAT数据类型支持范围：

FLOAT(n): [1,53]

所以统一SQL在转换时进行了参数校验，限定了FLOAT参数的范围应该在[1,53]之间。

在Oracle中，对于数据类型NUMBER，DECIMAL，DEC，NUMERIC的精度标度范围：

精度范围(p)：1~38
标度范围(s)：-84~127

统一SQL转换时支持的精度标度范围如下(且p>=s)：

精度范围(p)：1~38
标度范围(s)：0~38

在使用上述数据类型时，请确保数据类型精度标度范围在支持范围内。
对于目标库数据类型无法处理的数据长度，在运行时会报错。
在处理数字类型数据时，源库和目标库会存在截取或四舍五入的情况，可能会导致精度的损失(参考备注示例内容)。
在TDSQL-MySQL中decimal默认可以处理的整数位为10位，对转换后是decimal且整数位超过10位时，在运行时会报错。推荐使用时根据需要指定精度和标度。
对于decimal/dec/numeric,decimal/dec/numeric(*),number/decimal/dec/numeric(,0),number/decimal/dec/numeric(,s)转换到目标库后默认精度(p=38)，对于超过38位的数据，在目标库执行将报错，此种情况下请谨慎使用。

备注

1.在oracle中，对于以下类型语法，在处理数据时存在数字截取的情况,举例比较：
  1.1. number/decimal/dec/numeric
  1.2. number(*)/decimal(*)/dec(*)/numeric(*)
  1.3. number(*,0)/decimal(*,0)/dec(*,0)/numeric(*,0)
-- Oracle SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(id int,c NUMBER,cx NUMBER(*));
-- 插入数据
INSERT INTO unisql_number_t(id,c) values(0,999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(1,999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(2,999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(3,999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,c) values(4,999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
-- 执行报错，数字溢出
INSERT INTO unisql_number_t(id,c) values(5,999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000);

INSERT INTO unisql_number_t(id,cx) values(10,999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(11,999999999999999999999999999999999999999910000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(12,999999999999999999999999999999999999999920000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(13,999999999999999999999999999999999999999930000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
INSERT INTO unisql_number_t(id,cx) values(14,999999999999999999999999999999999999999940000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
-- 执行报错，数字溢出
INSERT INTO unisql_number_t(id,cx) values(15,999999999999999999999999999999999999999950000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
--查询数据
SELECT * FROM unisql_number_t;
ID|C                                                                                                                             |CX                                                                                                                            |
--+------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+
 0|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
 1|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
 2|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
 3|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
 4|999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|                                                                                                                              |
10|                                                                                                                              |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
11|                                                                                                                              |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
12|                                                                                                                              |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
13|                                                                                                                              |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
14|                                                                                                                              |999999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000|

-- TDSQL-MySQL SQL

DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(id int,c decimal,cx decimal);
-- 插入数据
INSERT INTO unisql_number_t(id,c) values(0,9999999995);
INSERT INTO unisql_number_t(id,c) values(1,9999999999);
-- 执行报错，数字溢出
INSERT INTO unisql_number_t(id,c) values(2,10000000000);


INSERT INTO unisql_number_t(id,cx) values(10,9999999995);
INSERT INTO unisql_number_t(id,cx) values(11,9999999999);
-- 执行报错，数字溢出
INSERT INTO unisql_number_t(id,cx) values(12,10000000000);

SELECT * FROM unisql_number_t;
id|c         |cx        |
--+----------+----------+
 0|9999999995|          |
 1|9999999999|          |
10|          |9999999995|
11|          |9999999999|


  1. 插入数据时四舍五入的场景,举例说明：
-- Oracle SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(c1 NUMBER(*,0),c2 NUMBER(2,0),c3 NUMBER(3,2));

INSERT INTO unisql_number_t(c1) values(1.11);
INSERT INTO unisql_number_t(c1) values(1.51);
INSERT INTO unisql_number_t(c2) values(1.11);
INSERT INTO unisql_number_t(c2) values(1.51);
INSERT INTO unisql_number_t(c3) values(1.111);
INSERT INTO unisql_number_t(c3) values(1.115);

SELECT * FROM unisql_number_t;
C1|C2|C3  |
--+--+----+
1|  |    |
2|  |    |
 | 1|    |
 | 2|    |
 |  |1.11|
 |  |1.12|

-- TDSQL-MySQL SQL
DROP TABLE unisql_number_t;
CREATE TABLE unisql_number_t(c1 decimal(38,0),c2 decimal(2),c3 decimal(3,2));

INSERT INTO unisql_number_t(c1) values(1.11);
INSERT INTO unisql_number_t(c1) values(1.51);
INSERT INTO unisql_number_t(c2) values(1.11);
INSERT INTO unisql_number_t(c2) values(1.51);
INSERT INTO unisql_number_t(c3) values(1.111);
INSERT INTO unisql_number_t(c3) values(1.115);

SELECT * FROM unisql_number_t;
c1|c2|c3  |
--+--+----+
1|  |    |
2|  |    |
 | 1|    |
 | 2|    |
 |  |1.11|
 |  |1.12|

5.1.1. NUMBER

Oracle 数据类型	精度(p)	标度(s)	转换后 TDSQL-MySQL 数据类型	示例
number			decimal	number —– decimal
number(*)			decimal	number(*) —– decimal
number(*,0)			decimal(38,0)	number(*,0)—-decimal(38,0)
number(*,s)		s > 0	decimal(38,s)	number(*,2)—-decimal(38,2)
number(p) / number(p,0)	p>0	空或 0	decimal(p)	number(4,0)—–decimal(4)
number(p,s)	p>0	s>0	decimal(p,s)	number(10,2)—–decimal(10,2)

示例

-- 转换前Oracle SQL:
CREATE TABLE unisql_number_t(c1 NUMBER,c2 NUMBER(*),c3 NUMBER(*,0),c4 NUMBER(*,2),c5 NUMBER(4),c6 NUMBER(4,0),c7 NUMBER(10,2));

-- 转换后TDSQL-MySQL：
CREATE TABLE unisql_number_t (c1 decimal,c2 decimal,c3 decimal(38,0),c4 decimal(38,2),c5 decimal(4),c6 decimal(4),c7 decimal(10,2))

5.1.2. DECIMAL

Oracle 数据类型	精度(p)	标度(s)	转换后 TDSQL-MySQL 数据类型	示例
decimal			decimal(38,0)	decimal —– decimal(38,0)
decimal(*)			decimal(38,0)	decimal(*) —– decimal(38,0)
decimal(*,0)			decimal(38,0)	decimal(*,0)—-decimal(38,0)
decimal(*,s)		s > 0	decimal(38,s)	decimal(*,2)—-decimal(38,2)
decimal(p) / decimal(p,0)	p>0	空或 0	decimal(p)	decimal(4,0)—–decimal(4)
decimal(p,s)	p>0	s>0	decimal(p,s)	decimal(10,2)—–decimal(10,2)

示例

-- 转换前Oracle SQL:
CREATE TABLE unisql_decimal_t(c1 decimal,c2 decimal(*),c3 decimal(*,0),c4 decimal(*,2),c5 decimal(4),c6 decimal(4,0),c7 decimal(10,2));

-- 转换后TDSQL-MySQL：
CREATE TABLE unisql_decimal_t (c1 decimal(38,0),c2 decimal(38,0),c3 decimal(38,0),c4 decimal(38,2),c5 decimal(4),c6 decimal(4),c7 decimal(10,2))

5.1.3. DEC

Oracle 数据类型	精度(p)	标度(s)	转换后 TDSQL-MySQL 数据类型	示例
dec			dec(38,0)	dec —– dec(38,0)
dec(*)			dec(38,0)	dec(*) —– dec(38,0)
dec(*,0)			dec(38,0)	dec(*,0)—-dec(38,0)
dec(*,s)		s > 0	dec(38,s)	dec(*,2)—-dec(38,2)
dec(p) / dec(p,0)	p>0	空或 0	dec(p)	dec(4,0)—–dec(4)
dec(p,s)	p>0	s>0	dec(p,s)	dec(10,2)—–dec(10,2)

示例

-- 转换前Oracle SQL:
CREATE TABLE unisql_dec_t(c1 dec,c2 dec(*),c3 dec(*,0),c4 dec(*,2),c5 dec(4),c6 dec(4,0),c7 dec(10,2));

-- 转换后TDSQL-MySQL：
CREATE TABLE unisql_dec_t (c1 dec(38,0),c2 dec(38,0),c3 dec(38,0),c4 dec(38,2),c5 dec(4),c6 dec(4),c7 dec(10,2))

5.1.4. NUMERIC

Oracle 数据类型	精度(p)	标度(s)	转换后 TDSQL-MySQL 数据类型	示例
numeric			numeric(38,0)	numeric —– numeric(38,0)
numeric(*)			numeric(38,0)	numeric(*) —– numeric(38,0)
numeric(*,0)			numeric(38,0)	numeric(*,0)—-numeric(38,0)
numeric(*,s)		s > 0	numeric(38,s)	numeric(*,2)—-numeric(38,2)
numeric(p) / numeric(p,0)	p>0	空或 0	numeric(p)	numeric(4,0)—–numeric(4)
numeric(p,s)	p>0	s>0	numeric(p,s)	numeric(10,2)—–numeric(10,2)

示例

-- 转换前Oracle SQL:
CREATE TABLE unisql_numeric_t(c1 numeric,c2 numeric(*),c3 numeric(*,0),c4 numeric(*,2),c5 numeric(4),c6 numeric(4,0),c7 numeric(10,2));

-- 转换后TDSQL-MySQL：
CREATE TABLE unisql_numeric_t (c1 numeric(38,0),c2 numeric(38,0),c3 numeric(38,0),c4 numeric(38,2),c5 numeric(4),c6 numeric(4),c7 numeric(10,2))

5.1.5. CHAR(size [BYTE | CHAR])

参数说明

参数	说明
size	表示指定的固定长度。
BYTE	表示以字节为单位提供列的长度。
CHAR	表示以字符为单位提供列的长度。

警告

因为目标库不支持BYTE、CHAR参数，当前BYTE、CHAR仅作为语法糖支持转换，非语义一致。

示例

-- 转换前Oracle SQL：
CREATE TABLE unisql_char_byte_test (col char(10 BYTE));
CREATE TABLE unisql_char_char_test (col char(10 CHAR));

-- 转换后TDSQL-MySQL：
CREATE TABLE `unisql_char_byte_test` (`col` char(10))
CREATE TABLE `unisql_char_char_test` (`col` char(10))

5.1.6. VARCHAR2(size [BYTE | CHAR])

参数说明

参数	说明
size	表示存储的字节数或字符数的长度大小。
BYTE	表示以字节为单位提供列的长度。 VARCHAR2 的默认类型为 BYTE。
CHAR	表示以字符为单位提供列的长度。

警告

因为目标库不支持BYTE、CHAR参数，当前BYTE、CHAR仅作为语法糖支持转换，非语义一致。

示例

-- 转换前Oracle SQL：
CREATE TABLE unisql_varchar2_byte_test (col varchar2(10 BYTE));
CREATE TABLE unisql_varchar2_char_test (col varchar2(10 CHAR));


-- 转换后TDSQL-MySQL：
CREATE TABLE `unisql_varchar2_byte_test` (`col` varchar(10))
CREATE TABLE `unisql_varchar2_char_test` (`col` varchar(10))