Numeric Data Types

**Table 1** Integer types
No.	MySQL	GaussDB	Difference
1	BOOL	Supported, with differences	Output format: The output of SELECT TRUE/FALSE in GaussDB is t or f, and that in MySQL is 1 or 0. MySQL: The BOOL/BOOLEAN type is actually mapped to the TINYINT type.
2	BOOLEAN	Supported, with differences
3	TINYINT[(M)] [UNSIGNED] [ZEROFILL]	Supported, with differences	For details, see the following note.
4	SMALLINT[(M)] [UNSIGNED] [ZEROFILL]	Supported, with differences	For details, see the following note.
5	MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]	Supported, with differences	MySQL requires 3 bytes to store MEDIUMINT data. The signed range is –8388608 to +8388607. The unsigned range is 0 to +16777215. GaussDB is mapped to the INT type. Four bytes are required for storage. The value range is determined based on boundary values. The signed range is –8388608 to +8388607. The unsigned range is 0 to +16777215. For other differences, see the following note.
6	INT[(M)] [UNSIGNED] [ZEROFILL]	Supported, with differences	For details, see the following note.
7	INTEGER[(M)] [UNSIGNED] [ZEROFILL]	Supported, with differences	For details, see the following note.
8	BIGINT[(M)] [UNSIGNED] [ZEROFILL]	Supported, with differences	For details, see the following note.

Input formats:

MySQL:
If a character string with multiple decimal points (such as "1.2.3.4.5") is entered, MySQL will misparse the character string in loose mode, throw a warning, and insert the character string into the table successfully. For example, after "1.2.3.4.5" is inserted into the table, the value is 12.
GaussDB:
If a character string with multiple decimal points (such as "1.2.3.4.5") is entered in loose mode, the characters after the second decimal point are truncated as invalid characters, a warning is thrown, and the character string is inserted into the table successfully. For example, after "1.2.3.4.5" is inserted into the table, the value is 1. After "1.6.3.4.5" is inserted into the table, the value is 2.

Example: When CREATE TABLE AS with UNION is used, GaussDB uses the default value of max_length for an integer CONST node, while MySQL calculates the max_length value based on the actual situation.

-- GaussDB
m_db=# CREATE TABLE test_int AS SELECT 1234567 UNION ALL SELECT '456789';
m_db=# DESC test_int;
  Field   |    Type     | Null | Key | Default | Extra 
----------+-------------+------+-----+---------+-------
 ?column? | varchar(11) | YES  |     |         | 
(1 row)
m_db=# DROP TABLE test_int;

-- MySQL
mysql> CREATE TABLE test_int AS SELECT 1234567 UNION ALL SELECT '456789';
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;

**Table 2** Arbitrary precision types
No.	MySQL	GaussDB	Difference
1	DECIMAL[(M[,D])] [ZEROFILL]	Supported, with differences	MySQL decimal uses a 9 x 9 array to store values. The integer part and decimal part are stored separately. If the length exceeds the value, the decimal part is truncated first. GaussDB truncates an integer that contains more than 81 digits.
2	NUMERIC[(M[,D])] [ZEROFILL]	Supported, with differences
3	DEC[(M[,D])] [ZEROFILL]	Supported, with differences
4	FIXED[(M[,D])] [ZEROFILL]	Supported, with differences

**Table 3** Floating-point types
No.	MySQL	GaussDB	Difference
1	FLOAT[(M,D)] [ZEROFILL]	Supported, with differences	The FLOAT data type does not support partitioned tables with the key partitioning policy.
2	FLOAT(p) [ZEROFILL]	Supported, with differences	The FLOAT data type does not support partitioned tables with the key partitioning policy.
3	DOUBLE[(M,D)] [ZEROFILL]	Supported, with differences	The DOUBLE data type does not support partitioned tables with the key partitioning policy.
4	DOUBLE PRECISION[(M,D)] [ZEROFILL]	Supported, with differences	The DOUBLE PRECISION data type does not support partitioned tables with the key partitioning policy.
5	REAL[(M,D)] [ZEROFILL]	Supported, with differences	The REAL data type does not support partitioned tables with the key partitioning policy.