Compatibility with MySQL

This topic describes the compatibility between OceanBase Database's MySQL mode and native MySQL Database.

The MySQL mode of OceanBase Database is compatible with most features and statements of MySQL 5.7 and 8.0. Some features are not supported because of their limited popularity or the product architecture differences between the two databases. This topic will cover the following aspects of compatibility between OceanBase Database and MySQL Database:

• Data types

• SQL syntaxes

• Procedural Language (PL) features

• System views

• Character sets

• Collations

• Functions and expressions

• Partition support

• Backup and restore

• Storage engine

• Optimizer

Data types

OceanBase Database supports the following data types:

• Numeric data types

  • Integer types: BOOL/BOOLEAN/TINYINT, SMALLINT, MEDIUMINT, INT/INTEGER, and BIGINT

  • Fixed-point types: DECIMAL and NUMERIC

  • Floating-point types: FLOAT and DOUBLE

  • Bit-value type: BIT

• Date and time types: DATETIME, TIMESTAMP, DATE, TIME, and YEAR

• Character types: CHAR, VARCHAR, BINARY, and VARBINARY

• Large object types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB

• Text types: TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, and STRING

  Note

  For OceanBase Database V4.3.5, the text data type STRING is supported starting from V4.3.5 BP1.

• Enumeration type: ENUM

• Set type: SET

• JSON data types

• Spatial data types

• Roaring Bitmap data types

• Array data types

• Vector data types

SQL syntaxes

PL features

OceanBase Database is compatible with most PL features of MySQL Database, including:

• Data types
• Stored procedures
• Custom functions
• Triggers
• Exception handling

OceanBase Database also supports specific MySQL PL packages, such as DBMS_RESOURCE_MANAGER, DBMS_STATS, DBMS_UDR, DBMS_XPLAN, and DBMS_WORKLOAD_REPOSITORY.

For more information about PL features, see PL reference.

System views

OceanBase Database implements most views in the information_schema and mysql databases of MySQL Database. However, due to architectural differences, OceanBase Database does not implement all views in MySQL Database or ensure consistency with MySQL Database in all column meanings of the views.

For more information about system views, see Overview of system views.

Character sets

OceanBase Database supports the following character sets:

• binary

• utf8mb4/utf8mb3

  Note

  utf8mb3 is an alias of utf8mb4.

• gbk

• utf16

• utf16le

• gb18030

• latin1

• gb18030_2022

• ascii

• tis620

• sjis

• big5

• dec8

• gb2312

• ujis

• euckr

• eucjpms

• cp932

• cp850

• hp8

• macroman

• swe7

Collations

The following collations are supported in OceanBase Database.

Functions

Partition support

The partition support feature of OceanBase Database differs from that of MySQL Database in the following aspects:

• OceanBase Database supports partitioning, template-based subpartitioning, and non-template-based subpartitioning. MySQL Database does not support non-template-based subpartitioning.

• OceanBase Database supports HASH, KEY, RANGE, RANGE COLUMNS, LIST, and LIST COLUMNS subpartitioning. MySQL Database supports only HASH and KEY subpartitioning.

• OceanBase Database supports adding and dropping subpartitions in partitioned tables. MySQL Database does not support adding and dropping subpartitions.

  Notice

  • Adding and dropping subpartitions is not supported for scenarios where the subpartition type is HASH or KEY.
  • In OceanBase Database V4.3.5, support for adding subpartitions is available starting from V4.3.5 BP1.

For more information about partitions, see Partition overview.

Backup and restore

OceanBase Database is compatible with some backup and restore features of MySQL Database.

Storage engine

Unlike the InnoDB and MyISAM storage engines in MySQL Database, the storage engine in OceanBase Database is based on the LSM-tree architecture.

Optimizer

OceanBase Database is compatible with some optimizer features of MySQL Database. For more information about the optimizer, see SQL tuning.