General data types

In OceanBase Database in MySQL mode, the general data types include numeric, date and time, character, LOB, text, enumeration, set, and JSON. This topic describes these data types.

Numeric data types

Numeric data types include:

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

• Fixed-point types: DECIMAL and NUMERIC

• Floating-point types: FLOAT and DOUBLE

• Bit-value type: BIT

Date and time data types

Date and time data types: DATETIME, TIMESTAMP, DATE, TIME, and YEAR

Character data types

Character data types: CHAR, VARCHAR, BINARY, and VARBINARY

BLOB data types

BLOB data types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB

Text data types

Text data types: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT

ENUM data type

Enumeration data type: One value is selected within a given range, for example, male or female for gender.

SET data type

Set data type: One or more values can be selected within a given range.

JSON data type

Examples

Example 1

1. Create a table named All types.

   CREATE TABLE `All types` (
     `col1` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Column 1',
     `col2` integer DEFAULT NULL COMMENT 'Column 2',
     `col3` decimal(10,2) DEFAULT NULL COMMENT 'Column 3',
     ` col4` number DEFAULT NULL COMMENT 'Column 4',
     `col5` tinyint(4) DEFAULT NULL COMMENT 'Column 5',
     `col6` smallint(6) DEFAULT NULL COMMENT 'Column 6',
     `col7` mediumint(9) DEFAULT NULL COMMENT 'Column 7',
     `col8` bigint(20) DEFAULT NULL COMMENT 'Column 8',
     `col9` float(10,2) DEFAULT NULL COMMENT 'Column 9',
     `col10` double(10,2) DEFAULT NULL COMMENT 'Column 10',
     `col11` varchar(10) DEFAULT NULL COMMENT 'Column 11',
     `col12` char(10) NOT NULL DEFAULT 'Chinese field' COMMENT 'Column 12',
     `col13` text DEFAULT NULL COMMENT 'Column 13',
     `col14` tinytext DEFAULT NULL COMMENT 'Column 14',
     `col15` mediumtext DEFAULT NULL COMMENT 'Column 15',
     `col16` longtext DEFAULT NULL COMMENT 'Column 16',
     `col17` blob DEFAULT NULL COMMENT 'Column 17',
     `col18` tinyblob DEFAULT NULL COMMENT 'Column 18',
     `col19` longblob DEFAULT NULL COMMENT 'Column 19',
     `col20` mediumblob DEFAULT NULL COMMENT 'Column 20',
     `col21` binary(16) DEFAULT NULL COMMENT 'Column 21',
     `col22` varbinary(16) DEFAULT NULL COMMENT 'Column 22',
     `col23` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Column 23',
     `col24` time DEFAULT NULL COMMENT 'Column 24',
     `col25` date DEFAULT NULL COMMENT 'Column 25',
     `col26` datetime DEFAULT NULL COMMENT 'Column 26',
     `col27` year(4) DEFAULT NULL COMMENT 'Column 27',
     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 = 'All types';
   

2. Insert data into the All types 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 data in the All types table.

   obclient> select * from `All types`;
   +------+------+------+-------+------+------+------+------+------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+------------------+-----------+---------------------+----------+------------+---------------------+-------+
   | 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 affected
   

2. Insert data into the t_bool table.

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

3. Query data in 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 data in 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 data in 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 data in the t_enum table.

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

Example 6

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 data in the t_enum table.

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

Example 7

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_json table.

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

3. Query data in the t_json table.

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