The character data types store character (alphanumeric) data, which are words and free-form text, in the database character set or the national character set. Compared with other data types, character data types are less restrictive, and therefore have fewer attributes. This topic describes character data types and character sets supported by OceanBase Database.
Character data types
Character data is stored in strings whose byte values correspond to one of the character sets specified when the database was created. OceanBase Database supports single-byte and multi-byte character sets.
Note
A column of the character data type can store all alphanumeric values. However, a column of the NUMBER data type can store only numeric values.
The following table describes the data types supported by OceanBase Database.
Data type |
Length type |
Usage notes |
Length |
|
|---|---|---|---|---|
| CHAR(size [BYTE | CHAR]) | Fixed | This data type features high index efficiency. You can use trim to remove excess blanks in a program. |
The value range of the size parameter is [1,2000]. The default and minimum values are 1. The storage size is specified by size, in bytes or characters. |
| NCHAR[(size)] | Fixed | This data type uses the UNICODE character set. Each character requires two bytes of storage. | The value range of the size parameter is [1,2000]. The default and minimum values are 1. The storage size is twice the value specified by size, in bytes. |
|
| NVARCHAR2(size) | Variable | This data type uses the UNICODE character set. Each character requires two bytes of storage. | The value range of the size parameter is [1,32767]. The byte storage size is twice the number of characters entered. |
|
| VARCHAR2(size [BYTE | CHAR]) | Variable | This data type uses the UNICODE character set. Each character requires two bytes of storage. | The value range of the size parameter is [1,32767]. The storage size is the actual length of the bytes or characters entered, rather than the value specified by size in bytes or characters. |
| VARCHAR(size [BYTE | CHAR]) | Variable | In OceanBase Database, VARCHAR and VARCHAR2 are the same. |
The value range of the size parameter is [1,32767]. The storage size is the actual length of the bytes entered, rather than the value specified by size, in bytes. |
Note
For CHAR and VARCHAR2 data types, you must specify length, whose default value is specified by the system variable NLS_LENGTH_SEMANTICS.
Character set overview
UNICODE character set
UNICODE is a character set that can be encoded using one of the following encoding schemes: UTF-8, UTF-16, and UTF-32. UNICODE data can also be compressed and converted. Encodings determine the amount of storage space needed to store a character. Chinese and English characters require different amount of storage space in different storage modes.
The following table describes the encoding methods.
Encoding method |
Code unit size |
BOM |
Advantages |
Disadvantages |
|---|---|---|---|---|
| UTF-8 | A variable-width encoding that can be single-byte (ASCII characters) or multi-byte (nonASCII characters), with a minimum code unit of 8 bits. | Without BOM: A text that starts with EF BB BF is an UTF-8-encoded text file. | An ideal UNICODE encoding: fully compatible with ASCII, no BOM, strong self-synchronization and error correction capabilities, suitable for network data transmission, and high scalability. | Variable-length encoding is not convenient for the internal processing of text in programs. |
| UTF-16 | A 2-byte or 4-byte encoding, with a minimum code unit of 16 bits. | With BOM: UTF-16LE (little endian) is represented by FF FE and UTF-16BE (big endian) is represented by FE FF. | The earliest Unicode encoding that has been widely used in various environments, suitable for processing Unicode data in memory, and a way to encode strings in APIs in many programming languages. | Incompatible with ASCII, complex encoding (because it uses surrogate pairs to encode code points from supplementary planes), and poor scalability. |
| UTF-32 | A fixed 4-byte encoding, with a minimum code unit of 16 bits. | With BOM: UTF-16LE (little endian) is represented by FF FE and UTF-16BE (big endian) is represented by FE FF. | Easy to read as a fixed-byte encoding, suitable for use in internal processing, every Unicode code point matches a single code unit. | A waste of space and bandwidth because all characters are encoded using 4 bytes, incompatible with ASCII, poor scalability, and rarely used. |
Database character set
The database character set can be used in the following ways:
Stores data types such as
CHAR,VARCHAR2, andCLOB.Indicates table names, column names, and PL variables.
Stores SQL and PL storage units.
National character set
The national character set stores data types such as NCHAR, NVARCHAR2, and NCLOB.
The national character set is an alternative character set selected for OceanBase Database. The NCHAR data type provides another character set choice in addition to the database character set provided by the CHAR data type, thereby enhancing the character processing capability of OceanBase Database.