All Products
Search
Document Center

Basic elememt

Last Updated: Feb 21, 2020

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, andTINYINT 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:

  1. SELECT ABS(a + 1)
  2. FROM t1
  3. 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 COMMENTclause can be used to specify comments for the database object. Example:

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