Common data types|V3.2.4|OceanBase Database| docs|Distributed Database

Common data types

Last Updated：2023-10-24 09:23:03 Updated

In OceanBase Database in MySQL mode, the common 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.

Classification	Type	Value range	Description
Numeric data type - integer	BIGINT	Signed:[-2⁶³, 2⁶³ - 1] Unsigned:[0, 2⁶⁴ - 1]	Represents a large integer.
	INT INTEGER	Signed:[-2³¹, 2³¹ - 1] Unsigned: [0, 2³² - 1]	Represents a normal-sized integer.
	SMALLINT	Signed:[-2¹⁵, 2¹⁵ - 1] Unsigned:[0, 2¹⁶ - 1]	Represents a small integer.
	BOOL BOOLEAN TINYINT	Signed:[-2⁷, 2⁷ - 1] Unsigned:[0, 2⁸ - 1]	The `TINYINT` data type is used to represent a very small integer. The `BOOL` or `BOOLEAN` data type is a synonym for the `TINYINT` data type. A zero value indicates false, whereas a non-zero value indicates true.
Numeric data type - fixed-point	DECIMAL	The scale and precision depend on D and M, and whether the value is unsigned. M specifies the precision (total number of digits in the value that can be stored) and D specifies the scale (the number of digits after the decimal point). The decimal point (.) and minus sign (-) are not counted in M. If D is 0, the value does not have a decimal point or fractional part. The result of subtracting D from M is the maximum number of valid digits in the integer part. When neither M nor D is specified, the precision is 65 digits for the results of all basic operations (addition, subtraction, multiplication, and division) involving a `DECIMAL` column.	DECIMAL is equivalent to NUMERIC.
Numeric data type - floating-point	FLOAT	Signed:[-2¹²⁸, 2¹²⁸] Unsigned:[-2¹⁰²⁴, 2¹⁰²⁴] Precision: 7 digits	Represents a small (single-precision) floating-point number.
Numeric data type - floating-point	DOUBLE	Signed:[-2¹⁰²⁴, 2¹⁰²⁴] Unsigned:[0, 2¹⁰²⁴] Precision: 15 digits	Represents a normal-sized (double-precision) floating-point number.
Numeric data type - integer/fixed-point/floating-point	NUMBER NUMBER(P) NUMBER(P, S)	P stands for precision, and S stands for scale, the number of digits after the decimal point. The maximum precision is 38 and the scale ranges from -84 to 127. A value with both precision and scale is a fixed-point value. A value with a scale of 0 is an integer value. A value without precision and scale is a floating-point value with a maximum precision of 38.	Represents integers, fixed-point numbers, or floating-point numbers.
Bit-value data type	BIT	A bit value consists of zeros and ones. The length is 1 bit.	Stores bit values.

Date and time data types

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

Classification	Type	Value range	Description
Date and time	DATE	A DATE value contains only the date part and is displayed in the YYYY-MM-DD format.	Represents a value with only the date part but no time part.
	TIME	A TIME value contains only the time part and is displayed in the HH:MM:SS[.fraction] format.	Represents a point in time in a day, the elapsed time, or the time interval between two events, which may be far greater than 24 hours.
	DATETIME	A DATETIME value contains both the date and time (not time zone specific) parts, and is displayed in the YYYY-MM-DD HH:MM:SS[.fraction] format.	Represents a value that contains both the date and time parts.
	TIMESTAMP	A TIMESTAMP value contains both the date and time (time zone specific) parts.	Represents a value that contains both the date and time parts.
	YEAR	YYYY,[1901, 2155]	Represents a 4-digit year.

Character data types

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

Classification	Type	Value range	Description
Character data type - variable-length	VARCHAR(N)	Maximum length: 256 KB. Character set: UTF8MB4.	The declared length is the maximum number of characters that can be stored.
Character data type - variable-length	VARBINARY	Maximum length: 256 KB. Character set: BINARY.	It is similar to the `VARCHAR` data type but stores binary byte strings.
Character data type - fixed-length	CHAR(N)	Maximum length: 256. Character set: UTF8MB4.	The declared length is the maximum number of characters that can be stored.
Character data type - fixed-length	BINARY	Maximum length: 256. Character set: BINARY.	It is similar to the `CHAR` data type but stores binary byte strings.

BLOB data types

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

Classification	Type	Value range	Description
BLOB data types	TINYBLOB	Binary text that contains a maximum of 255 characters or is up to 255 bytes in size.	Represents a `BLOB` type with a maximum length of 255 bytes.
	BLOB	Long binary text that is up to 65 KB in size.	Represents a BLOB type with a maximum length of 65,536 bytes.
	MEDIUMBLOB	Longer binary text that is up to 16 MB in size.	Represents a `BLOB` type with a maximum length of 16,777,216 bytes.
	LONGBLOB	Ultra-long binary text that is up to 4 GB in size.	Represents a BLOB type with a maximum length of 4,294,967,296 bytes.

Text data types

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

Classification	Type	Value range	Description
Text data types	TINYTEXT	Up to 255 bytes in size,[2⁸ - 1].	Represents a `TEXT` type with a maximum length of 256 characters. If a value contains multi-byte characters, the maximum valid length becomes smaller.
	TEXT	Up to 65 KB in size,[2¹⁶ - 1].	Represents a `TEXT` type with a maximum length of 65,536 characters. If a value contains multi-byte characters, the maximum valid length becomes smaller.
	MEDIUMTEXT	Up to 16 MB in size, [2²⁴-1].	Represents a `TEXT` type with a maximum length of 16,777,216 characters. If a value contains multi-byte characters, the maximum valid length becomes smaller.
	LONGTEXT	Up to 48 MB in size.	Represents a `TEXT` type with a maximum length of 50,331,648 characters or 48 MB in size. If a value contains multi-byte characters, the maximum valid length becomes smaller. The maximum valid length of a `LONGTEXT` column also depends on the maximum package size and available memory configured in the client/server protocol.

Enumeration data type

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

Classification	Type	Value range	Description
Enumeration data type	ENUM	Maximum number of elements: 65,535. Maximum length of each element: 255 characters. Character set: UTF8MB4.	Represents a string object with only one value. The value must be sourced from the list `'value1', 'value2', ...` Enumerated values must be strings with quotation marks.

Set data type

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

Classification	Type	Value range	Description
Set data type	SET	Maximum number of elements: 65,535. Maximum length of each element: 255 characters. Character set: UTF8MB4.	Represents a string object with zero or more values, and each value is sourced from the list `'Value1', 'Value2', ...`

JSON data type

Classification	Type	Value range	Description
JSON data type	JSON	JSON data can be in the form of objects or arrays.	The JSON data type uses a lightweight text data format to store and represent data.

Examples

Example 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';

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

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

Create a table named t_bool.

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

Insert data into the t_bool table.

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

Query data in the t_bool table.

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

Example 3

Create a table named t_boolean.

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

Insert data into the t_boolean table.

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

Query data in the t_boolean table.

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

Example 4

Create a table named t_bit.

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

Insert data into the t_bit table.

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

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

Create a table named t_enum.

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

Insert data into the t_enum table.

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

Query data in the t_enum table.

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

Example 6

Create a table named t_enum.

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

Insert data into the t_enum table.

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

Query data in the t_enum table.

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

Example 7

Create a table named t_json.

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

Insert data into the t_json table.

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

Query data in the t_json table.

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