Help Center/ GaussDB/ MySQL Compatibility(Distributed)/ MySQL Compatibility in MySQL-Compatible Mode/ Data Types/ Numeric Data Types
Updated on 2024-12-06 GMT+08:00
View PDF
Share

Numeric Data Types

Table 1 Integer types

No.

MySQL

GaussDB

Difference

1

BOOL

Not fully compatible

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.

2

BOOLEAN

Not fully compatible

3

TINYINT[(M)] [UNSIGNED]

Supported.

For details, see the following note.

4

SMALLINT[(M)] [UNSIGNED]

Supported.

For details, see the following note.

5

MEDIUMINT[(M)] [UNSIGNED]

Supported.
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 other differences, see the following note.

6

INT[(M)] [UNSIGNED]

Supported.

For details, see the following note.

7

INTEGER[(M)] [UNSIGNED]

Supported.

For details, see the following note.

8

BIGINT[(M)] [UNSIGNED]

Supported.

For details, see the following note.
  • Input formats:
    • MySQL

      For characters such as "asbd", "12dd", and "12 12", the system truncates them or returns 0 and reports a WARNING message. 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.
Table 2 Arbitrary precision types

No.

MySQL

GaussDB

Difference

1

DECIMAL[(M[,D])]

Supported.
  • 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'.

2

NUMERIC[(M[,D])]

Supported.

3

DEC[(M[,D])]

Supported.

4

FIXED[(M[,D])]

Not supported

-
Table 3 Floating-point types

No.

MySQL

GaussDB

Difference

1

FLOAT[(M,D)]

Supported.
  • 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 '').

2

FLOAT(p)

Supported.
  • 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 '').

3

DOUBLE[(M,D)]

Supported.
  • 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 '').

4

DOUBLE PRECISION[(M,D)]

Supported.
  • 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 '').

5

REAL[(M,D)]

Supported.
  • 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 '').
Table 4 Sequential integers

No.

MySQL

GaussDB

Difference

1

SERIAL

Not fully compatible

For details about SERIAL in GaussDB, see "Numeric Data 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
  • The default values of the SERIAL type in the INSERT scenario are different. For example: 
    -- The inserted default value 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)

-- The inserted default value 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)
  • The reference columns of the SERIAL type in the REPLACE scenario are different. For details about the GaussDB reference columns, see section "REPLACE" in Developer Guide of GaussDB. For example: 
    -- The inserted reference column value 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)

-- The inserted reference column value 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