Numeric Types

Overview

A data type is a basic data attribute. Occupied storage space and allowed operations vary according to data types. In a database, data is stored in tables, in which a data type is specified for each column. Data in the column must be of its allowed data type. The following table lists an example of converting MySQL numeric types to GaussDB(DWS) numeric types.

Type Comparison

**Table 1** Numeric type mapping
MySQL Numeric Type	MySQL INPUT	GaussDB(DWS) Output
DEC	DEC DEC[(M[,D])] [UNSIGNED] [ZEROFILL]	DECIMAL DECIMAL[(M[,D])]
DECIMAL	DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]	DECIMAL[(M[,D])]
DOUBLE PRECISION	DOUBLE PRECISION DOUBLE PRECISION [(M[,D])] [UNSIGNED] [ZEROFILL]	DOUBLE PRECISION DOUBLE PRECISION
DOUBLE	DOUBLE[(M[,D])] [UNSIGNED] [ZEROFILL]	DOUBLE PRECISION
FIXED	FIXED FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]	DECIMAL DECIMAL[(M[,D])]
FLOAT	FLOAT FLOAT [(M[,D])] [UNSIGNED] [ZEROFILL] FLOAT(p) [UNSIGNED] [ZEROFILL]	REAL REAL REAL
INT	INT INT(p) [UNSIGNED] [ZEROFILL]	INTEGER INTEGER(p)
INTEGER	INTEGER INTEGER(p) [UNSIGNED] [ZEROFILL]	INTEGER INTEGER(p)
MEDIUMINT	MEDIUMINT MEDIUMINT(p) [UNSIGNED] [ZEROFILL]	INTEGER INTEGER(p)
NUMERIC	NUMERIC NUMERIC [(M[,D])] [UNSIGNED] [ZEROFILL]	DECIMAL DECIMAL[(M[,D])]
REAL	REAL[(M[,D])]	REAL/DOUBLE PRECISION
SMALLINT	SMALLINT SMALLINT(p) [UNSIGNED] [ZEROFILL]	SMALLINT
TINYINT	TINYINT TINYINT(n) TINYINT(n) ZEROFILL TINYINT(n) UNSIGNED ZEROFILL	SMALLINT SMALLINT SMALLINT TINYINT

When the TINYINT type is converted, if it is unsigned (UNSIGNED), it is converted to TINYINT. Otherwise, it is converted to SMALLINT.
The REAL type is converted to DOUBLE PRECISION by default. If table.database.realAsFlag in the features-mysql.properties configuration file is set to true (false by default), the type is converted to REAL.

Input: TINYINT

    
         CREATE TABLE IF NOT EXISTS `runoob_dataType_test`(
    `dataType_1` TINYINT,
    `dataType_2` TINYINT(0),
    `dataType_3` TINYINT(255),
    `dataType_4` TINYINT(255) UNSIGNED ZEROFILL,
    `dataType_5` TINYINT(255) ZEROFILL
);

Output

    
         CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"
(
  "datatype_1" SMALLINT,
  "datatype_2" SMALLINT,
  "datatype_3" SMALLINT,
  "datatype_4" TINYINT,
  "datatype_5" SMALLINT
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("datatype_1");