Numeric Data Types

Updated on 2024-12-06 GMT+08:00

View PDF

**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.

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.
Explicit 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 message 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 message 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 message 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 message 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 message 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, see Developer Guide in GaussDB. 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)

Table 4 Sequential integers

No.

MySQL

GaussDB

Difference

SERIAL

Not fully compatible.

For details about SERIAL, see Developer Guide in GaussDB.

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

Previous topic: Data Types

Next topic: Date and Time Data Types

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot