Updated on 2024-07-19 GMT+08:00

Set Types

Overview

  1. In MySQL, an ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation.
  2. A SET is a string object that can have zero or more values, each of which must be chosen from a list of permitted values specified when the table is created.

Type Mapping

Table 1 Set type mapping

MySQL Set Type

MySQL INPUT

GaussDB(DWS) OUTPUT

ENUM

ENUM

VARCHAR

SET

SET

VARCHAR

  • The ENUM type is transformed into the VARCHAR type, with a precision quadruple the length of the longest enumeration. The CHECK() function validates the accuracy of the entered values.
  • The SET type is converted to the VARCHAR type. The precision is four times the sum of the length of each enumerated value field and the number of separators.

Input: ENUM

1
2
3
4
CREATE TABLE IF NOT EXISTS `runoob_dataType_test`(
     id   int(2) PRIMARY KEY,
    `dataType_17` ENUM('dws-1', 'dws-2', 'dws-3')
);

Output

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"
(
  "id" INTEGER(2) PRIMARY KEY,
  "datatype_17" VARCHAR(20) CHECK (dataType_17 IN('dws-1','dws-2','dws-3','', null))
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("id");

Input: SET

1
2
3
CREATE TABLE IF NOT EXISTS `runoob_tbl_test`(
    `dataType_18` SET('dws-1', 'dws-2', 'dws-3')
);

Output

1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS "public"."runoob_tbl_test"
(
  "datatype_18" VARCHAR(68)
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("datatype_18");