In OceanBase AI Database, each column in a table has a data type. Each data type corresponds to a unique storage format, constraints, and a range of values.
This topic describes the SQL data types supported by OceanBase AI Database.
Numeric data types
OceanBase AI Database supports standard SQL numeric data types, including exact numeric types, approximate numeric types, the BIT data type for storing bit values, and extended types.
Overview of numeric types
The numeric types supported by the current version of OceanBase AI Database can be categorized as follows:
Integer types:
BOOL/BOOLEAN/TINYINT,SMALLINT,MEDIUMINT,INT/INTEGER,BIGINT, andSERIAL.Fixed-point types:
DECIMALandNUMERIC.Floating-point types:
FLOATandDOUBLE.Bit-Value type:
BIT.
When defining numeric types, you can specify the precision (field length) and scale (number of decimal places). The meanings of precision and scale may vary for different numeric types. For details, see the descriptions of each type.
ZEROFILL attribute
When defining a numeric type, you can use the ZEROFILL keyword to specify the minimum display width, which implicitly defines the type as UNSIGNED. If the actual display width of the data is less than the minimum display width, the display width is padded to the minimum value by first zero-padding the fractional part up to the upper limit of the scale, and then zero-padding the integer part up to the upper limit of the precision.
Examples:
INT(5) ZEROFILLmeans that when the data value is 123, it will be displayed as 00123.DECIMAL(10, 5) ZEROFILLmeans that when the data value is 123.456, it will be displayed as 00123.45600.
Integer types
Integer types are fixed-length, exact numeric types. The value range depends on the type length and whether the type is unsigned. Precision only indicates the minimum display width.
The following table describes the storage length and value range of each integer type supported by OceanBase AI Database.
Type |
Length (bytes) |
Value range (signed) |
Value range (unsigned) |
|---|---|---|---|
BOOL/BOOLEAN/TINYINT |
1 | [-27, 27 - 1] | [0, 28 - 1] |
SMALLINT |
2 | [-215, 215 - 1] | [0, 216 - 1] |
MEDIUMINT |
3 | [-223, 223 - 1] | [0, 224 - 1] |
INT/INTEGER |
4 | [-231, 231 - 1] | [0, 232 - 1] |
BIGINT |
8 | [-263, 263 - 1] | [0, 264 - 1] |
SERIAL |
8 | N/A | [0, 264 - 1] |
TINYINT
TINYINT is used to store a very small integer. The syntax is as follows:
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
M specifies the maximum display width. The maximum display width is 255. The display width is unrelated to the value range that can be stored. If you specify the ZEROFILL option for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to the column.
BOOL/BOOLEAN
The BOOL/BOOLEAN type is a synonym for TINYINT. A zero value indicates an error, and a non-zero value indicates a correct value. Here is an example:
obclient> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false |
+------------------------+
1 row in set
obclient> SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true |
+------------------------+
1 row in set
obclient> SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true |
+------------------------+
1 row in set
obclient> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false |
+--------------------------------+
1 row in set
SMALLINT
SMALLINT is used to store a small integer. The syntax is as follows:
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
M specifies the maximum display width. The maximum display width is 255. The display width is unrelated to the value range that can be stored. If you specify the ZEROFILL option for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to the column.
MEDIUMINT
MEDIUMINT is used to store a medium-sized integer. The syntax is as follows:
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
M specifies the maximum display width. The maximum display width is 255. The display width is unrelated to the value range that can be stored. If you specify the ZEROFILL option for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to the column.
INT/INTEGER
INT or INTEGER is used to store a normal-sized integer. The syntax is as follows:
INT[(M)] [UNSIGNED] [ZEROFILL] INTEGER[(M)] [UNSIGNED] [ZEROFILL]
Here, M indicates the maximum display width. The maximum display width is 255. The display width has nothing to do with the range of values that can be stored. If you specify ZEROFILL for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to the column.
BIGINT
BIGINT is used to store a large integer. The syntax is as follows:
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
M specifies the maximum display width. The maximum display width is 255. The display width is unrelated to the value range that can be stored. If you specify the ZEROFILL option for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to the column.
Regarding BIGINT, note the following:
You must use signed
BIGINTorDOUBLEvalues for all operations. Therefore, do not use unsigned large integers greater than 9223372036854775807 (63 bits), except for theBITfunction. Otherwise, rounding errors will occur when you convert aBIGINTvalue to aDOUBLEvalue, and the last digit of the result may be incorrect.You can always store exact integer values in a
BIGINTcolumn by using strings. In this case, no intermediate conversion to double precision is involved when you convert a string to a number.When both operands are integers, the - and + operators and the * operator use
BIGINToperations. If you multiply two large integers (or the integer results returned by functions), the result may be incorrect if it exceeds 9223372036854775807.
SERIAL
The SERIAL data type is used to represent an auto-incrementing large-integer (wide-range number) column. By default, the SERIAL data type is equivalent to creating a BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE type column. Using the SERIAL data type makes it convenient to create columns with unique identifiers, especially suitable for use as table primary keys.
Fixed-point types
Fixed-point types are variable-length, precise numeric types. The value range and precision depend on Precision and Scale, and whether the type is unsigned.
DECIMAL is equivalent to NUMERIC. The syntax is as follows:
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
M specifies the total number of digits (Precision), and D specifies the number of digits to the right of the decimal point (Scale). The decimal point and the negative sign "-" are not included in M. If D is 0, the value has no decimal point or fractional part. The maximum number of significant digits for the integer part is equal to M minus D, that is, Precision minus Scale.
The maximum value of M for DECIMAL is 65, and the maximum value of D is 30. If D is omitted, it defaults to 0. If M is omitted, it defaults to 10. When neither M nor D is specified, all basic calculations (+, -, *, /) on columns with the DECIMAL data type use a precision of 65 bits.
If you specify ZEROFILL for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to that column. If UNSIGNED is specified, negative values are not allowed.
For example, DECIMAL(5,2) specifies a maximum of 3 digits in the integer part and 2 digits in the decimal part. Therefore, the range is [-999.99, 999.99]. If UNSIGNED is also specified, the range is [0, 999.99].
The following types are synonyms for DECIMAL. FIXED is used for compatibility with other database systems.
DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL],
FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
Floating-point types
Floating-point types are fixed-length, inexact numeric types. Their value range and precision depend on the type length, Precision, and Scale, as well as whether they are unsigned.
Precision and Scale represent the maximum total number of significant decimal digits and the maximum number of significant digits in the fractional part, respectively. The maximum number of significant digits in the integer part is equal to Precision minus Scale. The maximum value for Precision is 53, and the maximum value for Scale is 30.
Notice
The precision of floating-point types is only a theoretical value specified by the IEEE standard; actual performance may vary slightly due to hardware or operating system limitations.
The following table lists the storage length and value range required for floating-point types when Precision and Scale are not specified.
Type |
Length (bytes) |
Value range |
Precision |
|---|---|---|---|
FLOAT |
4 | [-3.402823466E+38, -1.175494351E-38], 0, and [1.175494351E-38, 3.402823466E+38] | 7 digits |
DOUBLE |
8 | [-1.7976931348623157E+308, -2.2250738585072014E-308], 0, and [2.2250738585072014E-308, 1.7976931348623157E+308] | 15 digits |
If Precision and Scale are specified, the value range is determined in the same way as for fixed-point types.
FLOAT
FLOAT is used to represent a small (single-precision) floating-point number. The syntax is as follows:
FLOAT [UNSIGNED] [ZEROFILL]
M is the total number of digits that can be stored, and D is the number of digits after the decimal point. If M and D are omitted, the value is stored within the hardware limit. A single-precision floating-point number is accurate to about 7 decimal places.
If ZEROFILL is specified for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to the column. If UNSIGNED is specified, negative values are not allowed.
Note
FLOAT(M,D)is an obsolete syntax in MySQL and is not recommended for use.- Since
FLOATis a single-precision floating-point number, its maximum effective precision does not exceed 7 digits. When the input precision is greater than 7, only the first 7 significant digits are guaranteed; the excess value cannot be guaranteed. This issue also exists in MySQL. If precise matching is required, it is recommended to use theDECIMALtype.
FLOAT also supports the following syntax:
FLOAT(p) [UNSIGNED] [ZEROFILL]
p specifies the precision in bits, but this value is used only to determine whether the result data type is FLOAT or DOUBLE. If p is 0 to 24, the data type becomes FLOAT without M or D values; if p is 25 to 53, the data type becomes DOUBLE without M or D values. The range of the result column is the same as that of the single-precision FLOAT or double-precision DOUBLE data type described earlier in this section.
DOUBLE
DOUBLE is used to represent a normal-sized (double-precision) floating-point number. The syntax is as follows:
DOUBLE [UNSIGNED] [ZEROFILL]
M is the total number of digits that can be stored, and D is the number of digits after the decimal point. If M and D are omitted, the value is stored within the hardware limit. A double-precision floating-point number is accurate to about 15 decimal places.
If ZEROFILL is specified for a numeric column, OceanBase AI Database automatically adds the UNSIGNED attribute to the column. If UNSIGNED is specified, negative values are not allowed.
Note
DOUBLE[(M,D)]is an obsolete syntax in MySQL and is not recommended. SinceDOUBLEis a double-precision floating-point number, its maximum effective precision does not exceed 15 digits. When the input effective precision is greater than 15, only the first 15 significant digits are guaranteed, and the accuracy of the excess part cannot be assured. MySQL also has this issue. If precise matching is required, it is recommended to use theDECIMALtype.- In OceanBase AI Database, the value of
Dcan only be 0. Setting it to a non-zero value will result in the error "Feature not supported".
DOUBLE PRECISION
DOUBLE PRECISION is a synonym for DOUBLE. The syntax is as follows:
DOUBLE PRECISION [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]
Note
DOUBLE PRECISION [(M,D)] is an obsolete syntax in MySQL and is not recommended. If precise matching is required, it is recommended to use the DECIMAL type.
BIT-Value type
The BIT data type is used to store bit values.
Bit values are specified in the form b'value', where value is represented by 0s and 1s. For example, b'111' represents 7, and b'10000000' represents 128.
The syntax is as follows:
BIT[(M)]
M indicates the number of bits for each value, with a range of [1, 64]. If M is omitted, the default is 1.
When inserting a value into a BIT(M) column, if the length of the inserted value is less than M, 0s are padded on the left. For example, inserting b'101' into a BIT(6) column is equivalent to inserting b'000101'.
Date and time data types
The current version of OceanBase AI Database supports the following date and time data types: DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Each type has a valid range.
When using date and time types, consider the following points:
OceanBase AI Database outputs dates and times in standard formats, but attempts to parse user input in multiple formats.
Although OceanBase AI Database tries to parse user-input literals in various formats, the date part must follow the "year-month-day" sequence, for example, '98-09-04'. It cannot be in the "month-day-year" or "day-month-year" sequence, such as '09-04-98' and '04-09-98'. To convert strings that do not follow the "year-month-day" sequence, you can use the
STR_TO_DATEfunction.An input year that is a two-digit number is ambiguous because it is uncertain which century it refers to. OceanBase AI Database converts one- or two-digit years in the range "70-99" to "1970-1999" and years in the range "00-69" to "2000-2069".
When converting between date and time types, if the source type is
DATEand the target type isDATETIMEorTIMESTAMP, the converted time part will be '00:00:00'. If the source type isTIMEand the target type isDATETIMEorTIMESTAMP, the converted date part will be the system's current date, which is affected by the system variabletimestamp.If date and time related types are used in numerical contexts, for example, as parameters for addition, OceanBase AI Database converts them to numeric types, and vice versa.
Typically, when a value exceeds the valid range of a date or time type or is invalid, OceanBase AI Database converts it to the zero value for that type. For example, for the
DATETIMEtype, the conversion result is '0000-00-00 00:00:00'; for theDATEtype, the conversion result is '0000-00-00'. An exception is that out-of-rangeTIMEvalues are converted to boundary values within the legal range of theTIMEtype.Setting the SQL mode controls which date values OceanBase AI Database supports. For example, adding
ALLOW_INVALID_DATESto the SQL mode allows the database to accept date values like '2009-11-31', in which case the database only checks whether the month is between 1 and 12 and the day is between 1 and 31. RemovingNO_ZERO_IN_DATEfrom the SQL mode allows the database to accept date values like '2020-00-01' and '2020-01-00', in which case functions such asDATE_SUBandDATE_ADDmay not produce correct results.OceanBase AI Database allows a date part of '0000-00-00'. If the type is
DATETIMEorTIMESTAMP, then the hour, minute, and second parts must also be 0.
The following table shows the zero values for each date and time type. You can explicitly use the values in the table to represent zero, or use 0 or '0', which is more convenient.
Type |
Zero-value format |
|---|---|
| DATE | '0000-00-00' |
| TIME | '00:00:00' |
| DATETIME | '0000-00-00 00:00:00' |
| TIMESTAMP | '0000-00-00 00:00:00' |
| YEAR | 0000 |
Character types
Character types store characters such as numbers and letters as strings. This section describes the most commonly used character data types, including CHAR and VARCHAR, as well as BINARY and VARBINARY.
CHAR and VARCHAR types
The CHAR and VARCHAR types are similar, but they differ in how values are stored and retrieved and in how trailing spaces are handled.
You can specify a maximum length for both CHAR and VARCHAR. For example, CHAR(30) can store up to 30 characters.
The length of a CHAR column can range from 0 to 256. CHAR stores the written value with consecutive trailing spaces removed. On retrieval, if the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled, the retrieved value is padded with spaces to the maximum length.
The length of a VARCHAR column can range from 0 to 262144, and its effective maximum length is 262144 bytes.
For CHAR and VARCHAR, if the inserted data exceeds the maximum length, an error is returned when sql_mode includes STRICT_TRANS_TABLES; otherwise, the data is automatically truncated to the maximum length and a warning is issued.
The comparison method for stored VARCHAR and CHAR values depends on the collation setting.
BINARY and VARBINARY types
The BINARY and VARBINARY types are similar to CHAR and VARCHAR types, except that they store binary data instead of strings.
The maximum lengths of BINARY and VARBINARY are the same as those of CHAR and VARCHAR, but their lengths are measured in bytes.
When storing BINARY values, 0x00 (that is, '\0') is padded on the right to reach the maximum length.
When strict mode is enabled, inserting data that exceeds the maximum length causes an error. In non-strict mode, the data is truncated and a warning is issued.
Comparison of BINARY values explicitly checks trailing null bytes. 0x00 and spaces are treated as different characters, with 0x00 sorted before spaces.
Large object data types
OceanBase AI Database supports large object data types, which fall into two categories: BLOB and TEXT.
The BLOB type includes TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. The TEXT type includes TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.
The BLOB type stores binary strings. Comparison and sorting of BLOB values are based on the numerical value of each byte. The TEXT type stores non-binary strings. Comparison and sorting of TEXT values are based on the collation of their character set. If the BINARY character set is specified for the TEXT type, it is equivalent to BLOB.
In non-strict mode, if you insert a string that exceeds the maximum length into a BLOB or TEXT column, the excess part is truncated. In strict mode, this operation returns an error. If the excess part consists only of spaces, no error is reported regardless of whether strict mode is enabled.
For TEXT and BLOB types, no trailing spaces are added on insert, nor are they removed on retrieval.
In most cases, you can treat BLOB columns as VARBINARY columns and TEXT columns as VARCHAR columns. The difference is that BLOB and TEXT columns cannot have a default value.
Notice
Indexes cannot be created on TEXT or BLOB columns.
Among the four types TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT, only TEXT allows you to explicitly specify its length. OceanBase AI Database determines the appropriate type from these four based on the user-specified length. This also applies to BLOB types. Example:
obclient> CREATE TABLE t(c1 TEXT(30), c2 TEXT(300), c3 TEXT(30000), c4 TEXT(10000000));
Query OK, 0 rows affected
obclient> DESC t;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c1 | tinytext | YES | | NULL | |
| c2 | text | YES | | NULL | |
| c3 | mediumtext | YES | | NULL | |
| c4 | longtext | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
Multimodel data types
In OceanBase AI Database, semi-structured data types such as JSON, spatial data, and XML are classified as multimodel data types.
Characteristics
- The composition of multimodel data types is standardized. For example, RFC8259 is the data exchange specification for JSON. OGC (Open Geospatial Consortium) provides a text markup language (WKT-CRS) to describe coordinate systems and their conversions, and W3C defines the standard for XML.
- Multimodel data can be directly accessed via SQL: Multimodel data is semi-structured data, and its internal basic types can all be mapped to SQL types. Structurally, multimodel data organizes a series of basic SQL types into complex composite data types through logical tree cascading.
- The computation functions for multimodel data are relatively fixed: Multimodel types have standardized formats. These formats not only define the data exchange format but also provide recommendations for the computation APIs of multimodel data.
Query computation
Multimodel types have flexible structures. Based on these standards, the database provides a rich set of functions to support the construction, querying, and computation of multimodel data.
- Data construction: For example, JSON and XML provide aggregate functions that extract basic data from relational tables and, combined with specific aggregation rules, construct JSON or XML objects.
- Data querying: For example, JSON and XML allow you to access subsets of a multimodel data object or a specific basic SQL element using paths.
- Data computation: For example, use spatial functions to calculate relationships between spatial objects.
Storage
Multimodel data types are composite types, and the data is generally variable length, allowing the creation of very large multimodel data. For storage convenience, multimodel data typically inherits the storage capabilities of large object data. Theoretically, the upper limit for storing large object data is also the upper limit for multimodel data. However, in practice, computing or storing extremely large multimodel data is highly inefficient, so creating ultra-large multimodel data is not recommended.
- Text/Varchar2 types: These can be stored directly in the database based on the standard-defined text format using Text/Varchar2. The downside is that each query on multimodel data requires parsing, so this method is not recommended for storing multimodel data.
- Native multimodel types: The stored data formats are optimized for database parsing. Once multimodel data is stored in the database, it does not need to be re-parsed for the next query, and there is also some optimization in storage space. Therefore, it is recommended to use native multimodel types for storage.
Data exchange
Multimodel data is exchanged with other systems based on standard-defined formats.
Note
- The current version of multimodel data types only supports spatial data and JSON.
- JSON-based virtual generation functions can create secondary indexes on any child element within a JSON structure.
Spatial types
The Geographic Information System (GIS) of OceanBase AI Database supports the construction, storage, and analysis of spatial data types.
OceanBase AI Database supports the following spatial data types:
GEOMETRYPOINTLINESTRINGPOLYGONMULTIPOINTMULTILINESTRINGMULTIPOLYGONGEOMETRYCOLLECTION
Single-value types
These types are used to store a single spatial value:
GEOMETRYcan store any type of geometric value.POINT,LINESTRING, andPOLYGONare the three most basic types and can only store values of specific geometric types.POINT: Represents a point with a single coordinate value. For example:POINT(50 60), where longitude comes first, followed by latitude, separated by a space.LINESTRING: Represents a line, consisting of a series of connected points. For example:LINESTRING(30 20,10 30,50 50), where points are separated by commas, and coordinates within a point are separated by a space, consistent with thePOINTformat.POLYGON: Represents a polygon, which can be a solid planar shape without an interior boundary or can have holes. For example: a polygon with only one outer boundary,POLYGON((0 0,10 0,10 10,0 10)).
GEOMETRY is the base class for all spatial collection types. Other types such as POINT, LINESTRING, and POLYGON are subclasses of GEOMETRY.
Collection types
These types are used to store multiple values:
MULTIPOINT,MULTILINESTRING, andMULTIPOLYGONare three collection types used to store sets of spatial data, but they can only represent collections of their specified base types.MULTIPOINT: Represents a collection of points. For example:MULTIPOINT(10 0),(5 10),(25 5),(20 5)orMULTIPOINT(10 0,5 10,25 5,20 5).MULTILINESTRING: Represents a collection of lines. For example:MULTILINESTRING((0 0,15 5,10 10),(30 20,10 30,40 40)).MULTIPOLYGON: Represents a collection of polygons. For example:MULTIPOLYGON(((30 20, 45 40, 10 40,30 20)),((15 5,40 10,10 20,5 10,15 5)))andMULTIPOLYGON(((40 40,20 45,45 30,40 40)),((20 35,10 30,10 10,30 5,45 20,20 35),(30 20,20 15,20 25,30 20))).
GEOMETRYCOLLECTIONcan store a collection of objects of any type. For example:GEOMETRYCOLLECTION(POINT(40 10),LINESTRING(10 10,20 20,10 40),POLYGON((40 40,20 45,45 30,40 40))).
The four types MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION are all collection types, composed of multiple POINT, LINESTRING, or POLYGON elements.
SRID attribute
The SRID attribute defines the Spatial Reference System (SRS) of a spatial type column. A column with an SRID attribute has the following two characteristics:
- The
SRIDof the spatial object written to this column must match theSRIDattribute defined in the column; otherwise, the write operation fails. - A spatial index defined on this column can take effect during spatial queries.
Examples
Spatial type fields support defining the nullable attribute and the spatial coordinate system SRID attribute. Example:
obclient [test]> CREATE TABLE tbl1_g(g GEOMETRY NOT NULL SRID 0);
Query OK, 0 rows affected
JSON data type
The JSON data type is a special data type for storing and processing JSON (JavaScript Object Notation) data in a database. A JSON value must consist of objects, arrays, strings, numbers, boolean values (false/true), or null. Among these, false, true, and null must be in lowercase.
JSON text structure
The JSON text structure includes characters, strings, numbers, and three literal names. Various separators, including spaces, tabs, line breaks, and carriage returns, can be used before or after any structural character.
Start array = [ Left square bracket
Start object = { Left curly brace
End array = ] Right square bracket
End object = } Right curly brace
Name separator = : Colon
Value separator = , Comma
Object
An object is structured as a pair of curly braces enclosing zero or more name/value pairs (or members). Names within an object should be unique. A name is a string, followed by a colon that separates the name from its value. A single comma separates multiple name/value pairs.
Example:
{ "NAME": "SAM", "Height": 175, "Weight": 100,"Registered" : false}
Array
An array is structured as a square bracket enclosing zero or more values (also called elements). Array elements are separated by commas, and the values in the array do not need to be identical.
Example:
["abc", 10, null, true, false]
Number
Numbers are represented in decimal format and include an integer component, which may be prefixed with an optional minus sign (-). A number can also have a fractional part and/or an exponent part. Leading zeros are not allowed. The fractional part is a decimal point followed by one or more digits. The exponent part starts with an uppercase or lowercase letter E, which can be followed by a plus sign (+) or a minus sign (-). After E and the optional sign, one or more digits follow.
Example:
[100, 0, -100, 100.11, -12.11, 10.22e2, -10.22e2]
String
A string begins and ends with double quotes ("). All Unicode characters can be placed within quotes, except for those that require escape sequences (including quotes, backslashes, and control characters).
JSON text must be encoded in UTF-8, UTF-16, or UTF-32. The default encoding is UTF-8.
Example:
{"Url": "http://www.example.com/image/481989943"}
Create a JSON value
OceanBase AI Database supports the following DDL operations on the JSON data type:
Create a table with a JSON column.
Add or drop a JSON column.
Create an index on a generated column that is of the JSON data type.
Limitations
You can create multiple columns of the JSON data type in each table, but note the following limitations:
A column of the JSON data type cannot be a
PRIMARY KEY,FOREIGN KEY, orUNIQUE KEY. However, you can add aNOT NULLorCHECKconstraint.A column of the JSON data type cannot have a default value.
A column of the JSON data type cannot be used as a partitioning key.
The length of JSON data cannot exceed that of
LONGTEXT. The maximum depth of a JSON object or array is 99.
Examples
- Basic DDL operations
obclient> CREATE TABLE tbl1 (id INT PRIMARY KEY, docs JSON NOT NULL, docs1 JSON);
Query OK, 0 rows affected
obclient> ALTER TABLE tbl1 MODIFY docs JSON CHECK(docs <'{"a" : 100}');
Query OK, 0 rows affected
obclient> CREATE TABLE json_tab(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Primary Key',
json_info JSON COMMENT 'JSON data',
json_id INT GENERATED ALWAYS AS (json_info -> '$.id') COMMENT 'Virtual field for JSON data',
json_name VARCHAR(5) GENERATED ALWAYS AS (json_info -> '$.NAME'),
index json_info_id_idx (json_id)
)COMMENT 'Sample table for JSON';
Query OK, 0 rows affected
obclient> ALTER TABLE json_tab ADD COLUMN json_info1 JSON;
Query OK, 0 rows affected
obclient> ALTER TABLE json_tab ADD INDEX (json_name);
Query OK, 0 rows affected
obclient> ALTER TABLE json_tab drop COLUMN json_info1;
Query OK, 0 rows affected
- Create an index using the generated column as the specified key
obclient> CREATE TABLE jn ( c JSON, g INT GENERATED ALWAYS AS (c->"$.id"));
Query OK, 0 rows affected
obclient> CREATE INDEX idx1 ON jn(g);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
obclient> INSERT INTO jn (c) VALUES
('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> SELECT c->>"$.name" AS name FROM jn WHERE g <= 2;
+-------+
| name |
+-------+
| Fred |
| Wilma |
+-------+
2 rows in set
obclient> EXPLAIN SELECT c->>"$.name" AS name FROM jn WHERE g <= 2\G
*************************** 1. row ***************************
Query Plan: =========================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------------
|0 |TABLE SCAN|jemp(idx1)|2 |92 |
=========================================
Outputs & filters:
-------------------------------------
0 - output([JSON_UNQUOTE(JSON_EXTRACT(jemp.c, '$.name'))]), filter(nil),
access([jemp.c]), partitions(p0)
1 row in set