ApsaraDB for OceanBase supports the following data types:

- Numeric types
- Date and time types
- Text types

### Numeric types

Numeric types can be divided into three categories:

- Integer types:
`BOOL`

/`BOOLEAN`

,`TINYINT`

,`SMALLINT`

,`MEDIUMINT`

,`INT`

/`INTEGER`

, and`BIGINT`

. - Fixed point types:
`DECIMAL`

/`NUMERIC`

. - Floating-point types:
`FLOAT`

and`DOUBLE`

. - Bit-Value types:
`BIT.`

You can use the `UNSIGNED`

keywords to declare all data types as the unsigned type, which causes the range of the data types to change.

You can specify the precision and scale parameters for the numeric types when they are defined. The meanings of precision and scale may differ for different types. For more information, see the detailed description of each type.

#### Integer type

The integer type is a fixed-length and precise numeric type. The value range depends on the type length and the sign. The precision only indicates the minimum display width. For more information, see the “ZEROFILL keyword” section. The following table provides the details.

Type | Length (bytes) | Range (signed) | Range (unsigned) |
---|---|---|---|

`BOOL` , `BOOLEAN` , and`TINYINT` |
1 | [-2 ^ 7 , 2 ^ 7 - 1] | [0, 2 ^ 8 - 1] |

`SMALLINT` |
2 | [-2 ^ 15, 2 ^ 15 - 1] | [0, 2 ^ 16 - 1] |

`MEDIUMINT` |
3 | [-2 ^ 23, 2 ^ 23 - 1] | [0, 2 ^ 24 - 1] |

`INT` and `INTEGER` |
4 | [-2 ^ 31, 2 ^ 31 - 1] | [0, 2 ^ 32 - 1] |

`BIGINT` |
8 | [-2 ^ 63, 2 ^ 63 - 1] | [0, 2 ^ 64 - 1] |

`BOOL`

is equivalent to `BOOLEAN`

, and these two types are equivalent to `TINYINT(1)`

.

`INT`

is equivalent to `INTEGER`

.

#### Fixed-point type

The fixed-point type has a variable length and an exact value. The value range and degree of precision depend on the precision and scale parameters and the sign. Precision specifies the total number of valid digits in a decimal number, and scale specifies the number of digits to the right of the decimal point in a decimal number. The maximum number of digits in the integer part is equal to precision minus scale. The maximum values of precision and scale are 65 and 30. The default values for precision and scale are 10 and 0. Example:

`NUMBER(5, 2)`

has three significant digits for the integer part and two significant digits for the fractional part. So the value range of the number is [-999.99, 999.99].- If the number is defined as
`UNSIGNED`

at the same time, the range is[0, 999.99].

`DECIMAL`

is equivalent to `NUMERIC`

.

#### Floating-point type

The floating-point type is a fixed-length, non-exact numeric type. The floating-point type is defined by using the length, precision, scale, and sign. Precision specifies the total number of valid digits in a decimal number, and scale specifies the number of digits to the right of the decimal point in a decimal number. The maximum number of digits in the integer part is equal to precision minus scale. The maximum values of precision and scale are 53 and 30.

The precision of the floating-point type is only a theoretical value according to the IEEE standard. The actual precision may vary due to hardware or operating system limitations.

The following table lists default settings when the precision and scale parameters are not specified.

Type | Length (bytes) | Range (signed) | Range (unsigned) | Precision |
---|---|---|---|---|

`FLOAT` |
4 | [-2 ^ 128, 2 ^ 128] | [0, 2 ^ 128] | 7 digits |

`DOUBLE` |
8 | [-2 ^ 1024, 2 ^ 1024] | [0, 2 ^ 1024] | 15 digits |

If the precision and scale are specified, the range is determined the same way as the fixed-point types.

#### ZEROFILL keyword

When you define a numeric type, you can use the ZEROFILL keyword to specify the minimum display width, while implicitly define the type as UNSIGNED. If the actual display width of the value is less than the minimum display width, the value is zero-padded until it reaches the minimum display width. The fractional part is padded with zeroes on the right to reach the width specified by the scale. The integer part is padded with zeroes on the left to reach the width specified by the precision parameter. Example:

`INT(5) ZEROFILL`

:`123`

is displayed as`00123`

.`DECIMAL(10, 5) ZEROFILL`

:`123.456`

is displayed as`00123.45600`

.

#### BIT-value type

The BIT data type is used to store bit values. A BIT(M) can store M-bit values. The range of M is from 1 to 64.

To specify bit values, b’value’ notation can be used. **value** is a binary number that consists of 0 and 1. For example, b’111’ means 7, and b’10000000 ‘means 128.

When you insert a value into the BIT(M) column, if the length of the inserted value is less than **M**, the value is padded with zeros on the left. For example, inserting b’101’ into BIT(6) is equivalent to inserting b’000101’.

### Date and time types

The following table provides the details.

Type | Format | Lower bound | Upper bound | Description |
---|---|---|---|---|

`DATETIME` |
YYYY-MM-DD HH:MM:SS[.fraction] | 0000-01-01 00:00:00.000000 | 9999-12-31 23:59:59.999999 | The datetime (ignoring time zone) |

`TIMESTAMP` |
YYYY-MM-DD HH:MM:SS[.fraction] | 0000-01-01 00:00:00.000000 | 9999-12-31 23:59:59.999999 | The datetime (considering time zone) |

`DATE` |
YYYY-MM-DD | 0000-01-01 | 9999-12-31 | The date |

`TIME` |
HH:MM:SS[.fraction] | -838:59:59.000000 | 838:59:59.000000 | The time |

`YEAR` |
YYYY | 1901 | 2155 | The year |

The value range and precision of the `DATETIME`

, `TIMESTAMP`

and `TIME`

types are determined by scale. The scale parameter represents the maximum number of significant digits in the fractional part. The maximum value is 9, the minimum value is 0, and the default value is 6.

### Text type

The following table describes the text types:

Type | Length | Maximum length (characters) | Character set |
---|---|---|---|

`VARCHAR` |
Variable length | 262144 or 256K | `UTF8MB4` |

`VARBINARY` |
Variable length | 1048576 or 1M | `BINARY` |

`CHAR` |
Fixed length | 256 | `UTF8MB4` |

`BINARY` |
Fixed length | 256 | `BINARY` |

`enum` |
Variable length | You can define up to 65535 elements and each element has a maximum of 255 characters. | `UTF8MB4` |

`set` |
Variable length | You can define up to 64 elements and each element has a maximum of 255 characters. | `UTF8MB4` |

The following table provides information about large object types:

Type | Length | Maximum length (bytes) | Character set |
---|---|---|---|

`TINYTEXT` |
Variable length | 256 | `UTF8MB4` |

`TINYBLOB` |
Variable length | 256 | `BINARY` |

`TEXT` |
Variable length | 65536 or 64K | `UTF8MB4` |

`BLOB` |
Variable length | 65536 or 64K | `BINARY` |

`MEDIUMTEXT` |
Variable length | 16777216 or 16M | `UTF8MB4` |

`MEDIUMBLOB` |
Variable length | 16777216 or 16M | `BINARY` |

`LONGTEXT` |
Variable length | 50331648 or 48M | `UTF8MB4` |

`LONGBLOB` |
Variable length | 50331648 or 48M | `BINARY` |

# Expressions

Expressions are generalized notions. Each expression takes several input parameters and returns an output result. An input parameter may be a constant or a single row of data, or multiple rows of data. Expressions can be nested. The input of one expression can be the output of another expression.

Expressions are divided into the following categories:

- Column references
- Constants
- Operators
- Functions

Example:

`SELECT ABS(a + 1)`

`FROM t1`

`WHERE a > 0;`

- “a” is the column reference.
- 0 and 1 are constants.
`>`

and`+`

are operators that take in the 0, 1, and “a” expressions as input.`ABS`

is a function that takes in the`+`

expression as input.

# Type conversion

ApsaraDB for OceanBase supports explicit type conversion and implicit type conversion.

You can perform explicit type conversion by using the `CAST`

function.

Implicit type conversion occurs in the following scenario: An operation requires a parameter of a specified type, but the actual parameter value does not match the specified type. When this happens, ApsaraDB for OceanBase converts the actual parameter value to the specified type before performing subsequent operations.

# Character sets

The following character sets are supported:

`UTF8MB4`

: variable-length encoding. The maximum character length is 4 bytes.`BINARY`

: fixed-length encoding. The fixed length is 1 byte.

`UTF8`

and `UTF8MB3`

are subsets of `UTF8MB4`

. They are variable-length encodings with a maximum character length of 3 bytes. To support seamless migration, ApsaraDB for OceanBase treats `UTF8`

syntactically the same as `UTF8MB4`

.

# Collations

ApsaraDB for OceanBase supports follows collation rules:

`UTF8MB4_GENERAL_CI`

of`UTF8MB4`

.`UTF8MB4_BIN`

of`UTF8MB4`

.`BINARY`

of`BINARY`

.

# Data comparison rules

ApsaraDB for OceanBase allows two or more data of any type to be compared. The comparison result may be:

- A non-zero value or TRUE
- 0 or False
- NULL

If the data types involved in a comparison are different, ApsaraDB for OceanBase automatically determines a data type for comparison based on internal rules. The data are converted to this data type before the comparison.

If the comparison type is text, you also need to determine a collation for comparison.

# Literals

### Text Literals

A text literal is a string that is enclosed by single quotation marks (`'`

) or double quotation marks (`"`

). If you enable the `ANSI_QUOTES`

mode, only single quotation marks (`'`

) can be used to enclose strings.

### Numeric Literals

Decimal numeric literals are divided into exact values (integer and fixed-point values) and floating-point values. Values can include a decimal point (`.`

) as a decimal separator and prefix a hyphen (`-`

) to represent negative values.

Hexadecimal numeric literals only support integer values that prefixed with `0X`

, and allow letters from `A`

to `F`

. All letters are not case-sensitive.

### Date and time literals

Date and time literals can be in text or numeric format.

- You can use either full separators:
`'2015-07-21 12:34:56.789'`

or no separator at all:`'20150721'`

in the text format. - The numeric format only allows decimal points (
`.`

) as the separator for the seconds and microseconds:`20150721123456.789`

. - A period (
`.`

) must be used between seconds and microseconds. Other than that, if you require other separators, we recommend that you only use common separators such as hyphens (`-`

), forward slashes (`/`

), and colons (`:`

).

### Escape characters

An escape character is a string prefixed with a backslash (`\`

) that invokes an alternative interpretation on the string. Escape characters are case-sensitive. For example, `\b`

represents a backspace, but`\B`

represents `B`

.

The following table lists all escape characters.

Escape characters | Description |
---|---|

`\b` |
A backspace |

`\f` |
A form feed |

`\n` |
A line feed |

`\r` |
A carriage return |

`\t` |
A tab. |

`\\` |
A backslash () |

`\'` |
A single quotation mark (‘) |

`\"` |
A double quotation mark (“) |

`\_` |
An underscore (_) |

`\%` |
A percent sign (%) |

`\0` |
NULL. |

`\Z` |
ASCII 26 (Ctrl+Z) |

# Description

### SQL statements

In normal SQL statements, ApsaraDB for OceanBase supports the following three annotation methods:

- Begin a comment with
`#`

. - Begin a comment with
`--`

. - Begin a comment with
`/*`

and end the comment with`*/`

.

### Database object

In a DDL statement, the `COMMENT`

clause can be used to specify comments for the database object. Example:

`create table t(pk INT PRIMARY KEY COMMENT 'primary key');`