Help Center/ GaussDB/ MySQL Compatibility(Distributed)/ MYSQL-compatible Mode/ Data Types/ Numeric Data Types
Updated on 2025-06-30 GMT+08:00
View PDF
Share

Numeric Data Types

Integer

Unless otherwise specified, the precision, scale, and number of bits cannot be defined as the floating-point values in MYSQL-compatible mode by default. You are advised to use a valid integer type.

Differences in terms of the integer types:

  • Input format:
    • MySQL

      For characters such as "asbd", "12dd", and "12 12", the system truncates them or returns 0 and reports a WARNING. Data fails to be inserted into a table in strict mode.

    • GaussDB
      • For integer types (TINYINT, SMALLINT, MEDIUMINT, INT, INTEGER, and BIGINT), if the invalid part of a character string is truncated, for example, "12@3", no message is displayed. Data is successfully inserted into a table.
      • If the whole integer is truncated (for example, "@123") or the character string is empty, 0 is returned and data is successfully inserted into a table.
  • Operators:
    • +, -, and *

      GaussDB: When INT, INTEGER, SMALLINT, or BIGINT is used for calculation, a value of the original type is returned and is not changed to a larger type. If the return value exceeds the range, an error is reported.

      MySQL: The value can be changed to BIGINT for calculation.

    • |, &, ^, and ~

      GaussDB: The value is calculated in the bits occupied by the type. In GaussDB, ^ indicates the exponentiation operation. If the XOR operator is required, replace it with #.

      MYSQL: The value is changed to a larger type for calculation.

  • Type conversion of negative numbers:

    GaussDB: The result is 0 in loose mode and an error is reported in strict mode.

    MySQL: The most significant bit is replaced with a numeric bit based on the corresponding binary value, for example, (-1)::uint4 = 4294967295.

  • Other differences:

    The precision of INT[(M)] controls formatted output in MySQL. GaussDB supports only the syntax but does not support the function.

  • Aggregate function:
    • variance: indicates the sample variance in GaussDB and the population variance in MySQL.
    • stddev: indicates the sample standard deviation in GaussDB and the overall standard deviation in MySQL.
  • Display width:
    • If ZEROFILL is not specified when the width information is specified for an integer column, the width information is not displayed in the table structure description.
    • When the INSERT statement is used to insert a column of the character type, GaussDB pads 0s before inserting the column.
    • The JOIN USING statement involves type derivation. In MySQL, the first table column is used by default. In GaussDB, if the result is of the signed type, the width information is invalid. Otherwise, the width of the first table column is used.
    • For GREATEST/LEAST, IFNULL/IF, and CASE WHEN/DECODE, MySQL does not pad 0s. In GaussDB, 0s are padded when the type and width information is consistent.
    • MySQL supports this function when it is used as the input or output parameter or return value of a function or stored procedure. GaussDB neither reports syntax errors nor supports this function.

For details about the differences between integer types in GaussDB and MySQL, see Table 1.

Table 1 Integer types

MySQL

GaussDB

Difference

BOOL

Supported, with differences

MySQL: The BOOL/BOOLEAN type is actually mapped to the TINYINT type.

GaussDB: BOOL is supported.

  • Valid literal values for the "true" state include: TRUE, 't', 'true', 'y', 'yes', '1', 'TRUE', true, 'on', and all non-zero values.
  • Valid literal values for the "false" state include: FALSE, 'f', 'false', 'n', 'no', '0', 0, 'FALSE', false, and 'off'.

TRUE and FALSE are standard expressions, compatible with SQL statements.

BOOLEAN

Supported, with differences

TINYINT[(M)] [UNSIGNED]

Supported, with differences

For details, see Differences in terms of the integer types.

SMALLINT[(M)] [UNSIGNED]

Supported, with differences

For details, see Differences in terms of the integer types.

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 maps data to the INT type and requires 4 bytes for storage.
  • The signed range is –2147483648 to +2147483647.
  • The unsigned range is 0 to +4294967295.

For details about other differences, see Differences in terms of the integer types.

INT[(M)] [UNSIGNED]

Supported, with differences

For details, see Differences in terms of the integer types.

INTEGER[(M)] [UNSIGNED]

Supported, with differences

For details, see Differences in terms of the integer types.

BIGINT[(M)] [UNSIGNED]

Supported, with differences

For details, see Differences in terms of the integer types.

Arbitrary Precision Types

Table 2 Arbitrary precision types

MySQL

GaussDB

Difference

DECIMAL[(M[,D])]

Supported, with differences
  • Operator: In GaussDB, "^" indicates the exponentiation operation. If the XOR operator is required, replace it with "#". In MySQL, "^" indicates the XOR operation.
  • Value range: The precision M and scale D support only integers and do not support floating-point values.
  • Input format: No error is reported when all input parameters of a character string (for example, "@123") are truncated. An error is reported only when it is partially truncated, for example, "12@3".

NUMERIC[(M[,D])]

Supported, with differences

DEC[(M[,D])]

Supported, with differences

FIXED[(M[,D])]

Not supported

-

Floating-Point Types

Table 3 Floating-point types

MySQL

GaussDB

Difference

FLOAT[(M,D)]

Supported, with differences
  • Partitioned table: The FLOAT data type does not support partitioned tables with the key partitioning policy.
  • Operator: In GaussDB, "^" indicates the exponentiation operation. If the XOR operator is required, replace it with "#". In MySQL, "^" indicates the XOR operation.
  • Value range: The precision M and scale D support only integers and do not support floating-point values.
  • Output format: An ERROR message is reported for invalid input parameters. No WARNING is reported in loose mode (that is, sql_mode is set to '').

FLOAT(p)

Supported, with differences
  • Partitioned table: The FLOAT data type does not support partitioned tables with the key partitioning policy.
  • Operator: The ^ operator is used for the numeric types, which is different from that in MySQL. In GaussDB, the ^ operator is used for exponential calculation.
  • Value range: When the precision p is defined, only valid integer data types are supported.
  • Output format: An ERROR message is reported for invalid input parameters. No WARNING is reported in loose mode (that is, sql_mode is set to '').

DOUBLE[(M,D)]

Supported, with differences
  • Partitioned table: The DOUBLE data type does not support partitioned tables with the key partitioning policy.
  • Operator: In GaussDB, "^" indicates the exponentiation operation. If the XOR operator is required, replace it with "#". In MySQL, "^" indicates the XOR operation.
  • Value range: The precision M and scale D support only integers and do not support floating-point values.
  • Output format: An ERROR message is reported for invalid input parameters. No WARNING is reported in loose mode (that is, sql_mode is set to '').

DOUBLE PRECISION[(M,D)]

Supported, with differences
  • Operator: In GaussDB, "^" indicates the exponentiation operation. If the XOR operator is required, replace it with "#". In MySQL, "^" indicates the XOR operation.
  • Value range: The precision M and scale D support only integers and do not support floating-point values.
  • Output format: An ERROR message is reported for invalid input parameters. No WARNING is reported in loose mode (that is, sql_mode is set to '').

REAL[(M,D)]

Supported, with differences
  • Partitioned table: The REAL data type does not support partitioned tables with the key partitioning policy.
  • Operator: In GaussDB, "^" indicates the exponentiation operation. If the XOR operator is required, replace it with "#". In MySQL, "^" indicates the XOR operation.
  • Value range: The precision M and scale D support only integers and do not support floating-point values.
  • Output format: An ERROR message is reported for invalid input parameters. No WARNING is reported in loose mode (that is, sql_mode is set to '').

Sequential Integers

Table 4 Sequential integers

MySQL

GaussDB

Difference

SERIAL

Supported, with differences

For details about SERIAL in GaussDB, see "SQL Reference > Data Types > Value Types" in Developer Guide.

The differences in specifications are as follows: 
CREATE TABLE test(f1 serial, f2 CHAR(20));
  • The SERIAL of MySQL is mapped to BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, and the SERIAL of GaussDB is mapped to INTEGER NOT NULL DEFAULT nextval('test_f1_seq'::regclass). For example: 
    -- Definition of MySQL SERIAL:
mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `f1` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `f2` char(20) DEFAULT NULL,
  UNIQUE KEY `f1` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

-- Definition of GaussDB SERIAL
gaussdb=# \d+ test
                                                Table "public.test"
 Column |     Type      |                     Modifiers                     | Storage  | Stats target | Description
--------+---------------+---------------------------------------------------+----------+--------------+-------------
 f1     | integer       | not null default nextval('test_f1_seq'::regclass) | plain    |              |
 f2     | character(20) |                                                   | extended |              |
Has OIDs: no
Options: orientation=row, compression=no, storage_type=USTORE
  • Differences in using INSERT to insert default values of the SERIAL type. For example: 
    -- Inserting default values of the SERIAL type in MySQL
mysql> INSERT INTO test VALUES(DEFAULT, 'aaaa');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES(10, 'aaaa');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES(DEFAULT, 'aaaa');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+----+------+
| f1 | f2   |
+----+------+
|  1 | aaaa |
| 10 | aaaa |
| 11 | aaaa |
+----+------+
3 rows in set (0.00 sec)

-- Inserting default values of the SERIAL type in GaussDB
gaussdb=# INSERT INTO test VALUES(DEFAULT, 'aaaa');
INSERT 0 1
gaussdb=# INSERT INTO test VALUES(10, 'aaaa');
INSERT 0 1
gaussdb=# INSERT INTO test VALUES(DEFAULT, 'aaaa');
INSERT 0 1
gaussdb=# SELECT * FROM test;
 f1 |          f2
----+----------------------
  1 | aaaa
  2 | aaaa
 10 | aaaa
(3 rows)
  • Differences in performing REPLACE on referencing columns of the SERIAL type. For details about GaussDB referencing columns, see "SQL Reference > SQL Syntax > R > REPLACE" in Developer Guide. For example: 
    -- Inserting values of the referencing columns of the SERIAL type in MySQL
mysql> REPLACE INTO test VALUES(f1, 'aaaa');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO test VALUES(f1, 'bbbb');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+----+------+
| f1 | f2   |
+----+------+
|  1 | aaaa |
|  2 | bbbb |
+----+------+
2 rows in set (0.00 sec)

-- Inserting values of the referencing columns of the SERIAL type in GaussDB
gaussdb=# REPLACE INTO test VALUES(f1, 'aaaa');
REPLACE 0 1
gaussdb=# REPLACE INTO test VALUES(f1, 'bbbb');
REPLACE 0 1
gaussdb=# SELECT * FROM test;
 f1 |          f2
----+----------------------
  0 | aaaa
  0 | bbbb
(2 rows)
Parent topic: Data Types