Common data types

In MySQL mode of OceanBase Database, the common data types include: numeric, date and time, character, large object, text, enumeration, set, JSON, and spatial data types. This topic describes each data type.

Numeric data types

Numeric data types include:

• Integer types: BOOL/BOOLEAN/TINYINT, SMALLINT, MEDIUMINT, INT/INTEGER, and BIGINT

• Fixed-point types: DECIMAL and NUMERIC

• Floating-point types: FLOAT/DOUBLE

• Bit-value type: BIT

Date and time types

The date and time types are: DATETIME, TIMESTAMP, DATE, TIME, and YEAR.

Character types

The character types are: CHAR, VARCHAR, BINARY, and VARBINARY.

Large object types

The large object types are: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.

Text types

The text types are: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.

Enumeration type

An enumeration type is a data type that contains a predefined set of values. For example, the sex field is of the enumeration type, and its values are M for male and F for female.

Set type

A set type: allows you to select one or more values within a specified range.

JSON data types

Spatial data types

A spatial data type is a data type that represents spatial data, such as geographical locations, coordinates, and rectangles.

For more information, see Overview of spatial data types.

Example

Example 1

1. Create a full-type table.

   CREATE TABLE `full-type table` (
     `col1` int(11) NOT NULL AUTO_INCREMENT COMMENT 'First column',
     `col2` integer DEFAULT NULL COMMENT 'Second column',
     `col3` decimal(10,2) DEFAULT NULL COMMENT 'Third column',
     col4` number DEFAULT NULL COMMENT 'Fourth column',
     `col5` tinyint(4) DEFAULT NULL COMMENT 'Fifth column',
     `col6` smallint(6) DEFAULT NULL COMMENT 'Sixth column',
     `col7` mediumint(9) DEFAULT NULL COMMENT 'Seventh column',
     `col8` bigint(20) DEFAULT NULL COMMENT 'Eighth column',
     `col9` float(10,2) DEFAULT NULL COMMENT 'Ninth column',
     `col10` double(10,2) DEFAULT NULL COMMENT 'Tenth column',
     `col11` varchar(10) DEFAULT NULL COMMENT 'Eleventh column',
     `col12` char(10) NOT NULL DEFAULT 'Chinese field' COMMENT 'Twelfth column',
     `col13` text DEFAULT NULL COMMENT 'Thirteenth column',
     `col14` tinytext DEFAULT NULL COMMENT 'Fourteenth column',
     `col15` mediumtext DEFAULT NULL COMMENT 'Fifteenth column',
     `col16` longtext DEFAULT NULL COMMENT 'Sixteenth column',
     `col17` blob DEFAULT NULL COMMENT 'Seventeenth column',
     `col18` tinyblob DEFAULT NULL COMMENT 'Eighteenth column',
     `col19` longblob DEFAULT NULL COMMENT 'Nineteenth column',
     `col20` mediumblob DEFAULT NULL COMMENT 'Twentieth column',
     `col21` binary(16) DEFAULT NULL COMMENT 'Twenty-first column',
     `col22` varbinary(16) DEFAULT NULL COMMENT 'Twenty-second column',
     `col23` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Twenty-third column',
     `col24` time DEFAULT NULL COMMENT 'Twenty-fourth column',
     `col25` date DEFAULT NULL COMMENT 'Twenty-fifth column',
     `col26` datetime DEFAULT NULL COMMENT 'Twenty-sixth column',
     `col27` year(4) DEFAULT NULL COMMENT 'Twenty-seventh column',
     PRIMARY KEY (`col1`)
   ) AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = 'Full-type table';
   

2. Insert data into the full-type table.

   obclient> INSERT INTO `full-type table` VALUES(101,2,3,4,5,6,7,8,9.1,10.1,'Eleven','Twelve','Thirteen','Fourteen','Fifteen','Sixteen','Seventeen','Eighteen','Nineteen','Twenty','Twenty-one','Twenty-two','19700101','00:00:00','19700101','19700101','1970');
   Query OK, 1 row affected
   

3. Query the full-type table.

   obclient> select * from `full-type table`;
   +------+------+------+-------+------+------+------+------+------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+------------------+-----------+---------------------+----------+------------+---------------------+-------+
   | col1 | col2 | col3 |  col4 | col5 | col6 | col7 | col8 | col9 | col10 | col11  | col12  | col13  | col14  | col15  | col16  | col17  | col18  | col19  | col20  | col21            | col22     | col23               | col24    | col25      | col26               | col27 |
   +------+------+------+-------+------+------+------+------+------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+------------------+-----------+---------------------+----------+------------+---------------------+-------+
   |  101 |    2 | 3.00 |     4 |    5 |    6 |    7 |    8 | 9.10 | 10.10 | Eleven | Twelve | Thirteen | Fourteen | Fifteen | Sixteen | Seventeen | Eighteen | Nineteen | Twenty | Twenty-one        | Twenty-two | 1970-01-01 00:00:00 | 00:00:00 | 1970-01-01 | 1970-01-01 00:00:00 |  1970 |
   +------+------+------+-------+------+------+------+------+------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+------------------+-----------+---------------------+----------+------------+---------------------+-------+
   1 row in set
   

Example 2

1. Create a table named t_bool.

   obclient> CREATE TABLE t_bool(c1 bool);
   Query OK, 0 rows affecte
   

2. Insert data into the t_bool table.

   obclient> INSERT INTO t_bool VALUES(1);
   Query OK, 1 row affected
   

3. Query the t_bool table.

   obclient> SELECT * FROM t_bool;
   +------+
   | c1   |
   +------+
   |    1 |
   +------+
   1 row in set
   

Example 3

1. Create a table named t_boolean.

   obclient> CREATE TABLE t_boolean(c1 boolean);
   Query OK, 0 rows affected
   

2. Insert data into the t_boolean table.

   obclient> INSERT INTO t_boolean VALUES(1);
   Query OK, 1 row affected
   

3. Query the t_boolean table.

   obclient> SELECT * FROM t_boolean;
   +------+
   | c1   |
   +------+
   |    1 |
   +------+
   1 row in set
   

Example 4

1. Create a table named t_bit.

   obclient> CREATE TABLE t_bit(c1 bit(8));
   Query OK, 0 rows affected
   

2. Insert data into the t_bit table.

   obclient> INSERT INTO t_bit VALUES(101);
   Query OK, 1 row affected
   

3. Query the t_bit table.

   obclient> SELECT c1,bin(c1),hex(c1) FROM t_bit;
   +------+---------+---------+
   | c1   | bin(c1) | hex(c1) |
   +------+---------+---------+
   | e    | 1100101 | 65      |
   +------+---------+---------+
   1 row in set
   

Example 5

1. Create a table named t_enum.

   obclient> CREATE TABLE t_enum(c1 enum('RED','GREEN','BLUE'));
   Query OK, 0 rows affected
   

2. Insert data into the t_enum table.

   obclient> INSERT INTO t_enum VALUES('RED');
   Query OK, 1 row affected
   

3. Query the t_enum table.

   obclient>  SELECT * FROM t_enum;
   +------+
   | c1   |
   +------+
   | RED  |
   +------+
   1 row in set
   

Example 6

1. Create a table named t_json.

   obclient> CREATE TABLE t_json(c1 json);
   Query OK, 0 rows affected
   

2. Insert data into the t_set table.

   obclient>  INSERT INTO t_json VALUES( '{"name":"ny", "gender":"Male","age":22}' );
   Query OK, 1 row affected
   

3. Query the t_set table.

   obclient> SELECT * FROM t_json;
   +--------------------------------------------+
   | c1                                         |
   +--------------------------------------------+
   | {"age": 22, "name": "ny", "gender": "Male"}  |
   +--------------------------------------------+
   1 row in set
   

Example 7

1. Create a table named t_gis.

   obclient> CREATE TABLE t_gis(c1 geometry);
   Query OK, 0 rows affected
   

2. Insert data into the t_gis table.

   obclient> INSERT INTO t_gis VALUES(ST_GeomFromText('POINT(100 100)'));
   Query OK, 1 row affected
   

3. Query the t_gis table.

   SELECT st_astext(c1) FROM t_gis;
    +----------------+
    | st_astext(c1)  |
    +----------------+
    | POINT(100 100) |
    +----------------+
    1 row in set