Updated on 2024-05-14 GMT+08:00

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]

Supported, with differences

For details, see the following note.

4

SMALLINT[(M)] [UNSIGNED]

Supported, with differences

For details, see the following note.

5

MEDIUMINT[(M)] [UNSIGNED]

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]

Supported, with differences

For details, see the following note.

7

INTEGER[(M)] [UNSIGNED]

Supported, with differences

For details, see the following note.

8

BIGINT[(M)] [UNSIGNED]

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.

Table 2 Arbitrary precision types

No.

MySQL

GaussDB

Difference

1

DECIMAL[(M[,D])]

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

Supported, with differences

3

DEC[(M[,D])]

Supported, with differences

4

FIXED[(M[,D])]

Supported, with differences

Table 3 Floating-point types

No.

MySQL

GaussDB

Difference

1

FLOAT[(M,D)]

Supported, with differences

The FLOAT data type does not support partitioned tables with the key partitioning policy.

2

FLOAT(p)

Supported, with differences

The FLOAT data type does not support partitioned tables with the key partitioning policy.

3

DOUBLE[(M,D)]

Supported, with differences

The DOUBLE data type does not support partitioned tables with the key partitioning policy.

4

DOUBLE PRECISION[(M,D)]

Supported, with differences

The DOUBLE PRECISION data type does not support partitioned tables with the key partitioning policy.

5

REAL[(M,D)]

Supported, with differences

The REAL data type does not support partitioned tables with the key partitioning policy.