Numeric Data Types

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

View PDF

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

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.

NOTE:

In the scenario where the driver adopts FLOAT and DOUBLE types with a precision scale, no error is reported when the input data exceeds the range.

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