数值数据类型
除特别说明外,GaussDB中的数据类型精度、标度、位数大小等默认不支持用浮点型数值定义,建议使用合法的整型数值定义。
| 数据类型 | 与MySQL的差异 |
|---|---|
| BOOL | 输出格式:GaussDB中SELECT TRUE/FALSE输出结果为t/f,MySQL为1/0。 MySQL:BOOL/BOOLEAN类型实际映射为TINYINT类型。 |
| BOOLEAN | |
| TINYINT[(M)] [UNSIGNED] [ZEROFILL] | 具体差异请参见表格下方的示例内容。 |
| SMALLINT[(M)] [UNSIGNED] [ZEROFILL] | |
| MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] | 具体差异请参见表格下方的示例内容。 |
| INT[(M)] [UNSIGNED] [ZEROFILL] | 具体差异请参见表格下方的示例内容。 |
| INTEGER[(M)] [UNSIGNED] [ZEROFILL] | |
| BIGINT[(M)] [UNSIGNED] [ZEROFILL] |
差异:当启用精度控制参数(即m_format_behavior_compat_options包含'enable_precision_decimal'选项)时,在CREATE TABLE AS语句的UNION场景中,GaussDB对于直接输入的整型会取其最大长度(INT为11,BIGINT为20)来计算列的长度,而MySQL则会根据整型的实际长度来计算列的长度。
-- GaussDB m_db=# set m_format_behavior_compat_options= 'enable_precision_decimal'; SET m_db=# CREATE TABLE test_int AS SELECT 1234567 UNION ALL SELECT '456789'; INSERT 0 2 m_db=# DESC test_int; Field | Type | Null | Key | Default | Extra ----------+-------------+------+-----+---------+------- ?column? | varchar(11) | YES | | | (1 row) m_db=# DROP TABLE test_int; DROP TABLE m_db=# CREATE TABLE test_int AS SELECT 1234567890 UNION ALL SELECT '456789'; INSERT 0 2 m_db=# DESC test_int; Field | Type | Null | Key | Default | Extra ----------+-------------+------+-----+---------+------- ?column? | varchar(20) | YES | | | (1 row) m_db=# DROP TABLE test_int; DROP TABLE -- MySQL mysql> CREATE TABLE test_int AS SELECT 1234567 UNION ALL SELECT '456789'; Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> DESC test_int; +---------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------+------+-----+---------+-------+ | 1234567 | varchar(7) | NO | | | | +---------+------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> DROP TABLE test_int; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE test_int AS SELECT 1234567890 UNION ALL SELECT '456789'; Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> DESC test_int; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | 1234567890 | varchar(10) | NO | | | | +------------+-------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> DROP TABLE test_int; Query OK, 0 rows affected (0.00 sec)
| 数据类型 | 与MySQL的差异 |
|---|---|
| DECIMAL[(M[,D])] [ZEROFILL] | - |
| NUMERIC[(M[,D])] [ZEROFILL] | |
| DEC[(M[,D])] [ZEROFILL] | |
| FIXED[(M[,D])] [ZEROFILL] |
| 数据类型 | 与MySQL的差异 |
|---|---|
| FLOAT[(M,D)] [ZEROFILL] |
|
| FLOAT(p) [ZEROFILL] | |
| DOUBLE[(M,D)] [ZEROFILL] | |
| DOUBLE PRECISION[(M,D)] [ZEROFILL] | |
| REAL[(M,D)] [ZEROFILL] |