Numeric Data Types

Unless otherwise specified, considering the characteristics of the floating-point type in terms of precision, scale, and number of digits after the decimal point, the floating-point type is not supported in GaussDB by default. You are advised to use a valid integer type.

Difference 1: In MySQL 5.7, when a string like '1.2.3.4' that contains multiple decimal points is entered, the content of the first decimal point is incorrectly inserted in loose mode (when sql_mode does not contain the 'strict_trans_tables' option). This issue has been resolved in MySQL 8.0. GaussDB is consistent with 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)

Difference 2: When the parameter that specifies the precision is enabled (m_format_behavior_compat_options contains the 'enable_precision_decimal' option), in the CREATE TABLE AS scenario of UNION, GaussDB calculates the column length using the maximum length of a directly input integer (11 for INT and 20 for BIGINT), while MySQL calculates it based on the actual length of the integer.

-- 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)