数值数据类型

除特别说明外，GaussDB数据库中的数据类型精度、标度、位数大小等默认不支持用浮点型数值定义，建议使用合法的整型数值定义。

差异1：MySQL 5.7在形如'1.2.3.4'这类字符串中含有多个小数点的输入，在宽松模式（sql_mode未包含'strict_trans_tables'选项）下会错误的将第一个小数点的内容也插入进去。MySQL 8.0版本修复了该问题， GaussDB与MySQL 8.0保持一致。

-- GaussDB
m_db=# SET SQL_MODE='';
SET
m_db=# CREATE TABLE test_int(a int);
CREATE TABLE
m_db=# INSERT INTO test_int VALUES('1.2.3');
WARNING:  Data truncated for column
LINE 1: INSERT INTO test_int VALUES('1.2.3');
                                    ^
CONTEXT:  referenced column: a
INSERT 0 1
m_db=# SELECT * FROM test_int;
 a 
---
 1
(1 row)

m_db=# DROP TABLE test_int;
DROP TABLE

-- MySQL 5.7
mysql> SET SQL_MODE='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE test_int(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test_int VALUES('1.2.3');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM test_int;
+------+
| a    |
+------+
|   12 |
+------+
1 row in set (0.00 sec)

mysql> DROP TABLE test_int;
Query OK, 0 rows affected (0.01 sec)

差异2：开启精度开关参数（m_format_behavior_compat_options包含'enable_precision_decimal'选项）时，在UNION的CREATE TABLE AS场景中，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)