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 syntax

• Procedural languages

• System views

• Character sets and collations

• Functions and expressions

• Partition support

• Backup and restore

• Storage engine

• Optimizer

• Unsupported features

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, and LONGTEXT

• Enumeration type: ENUM

• Set type: SET

• JSON data types

• Spatial data types

SQL syntax

SELECT

• OceanBase Database supports most query features, including single- and multi-table queries, subqueries, inner join, semi join, outer join, grouping, aggregation, and regular data mining functions such as probability and linear regression.

• The following set operators can be used to process the results returned by multiple SELECT operations: UNION, UNION ALL, MINUS, EXCEPT, and INTERSECT.

• You can use the following syntax to view an execution plan:

  EXPLAIN [explain_type] dml_statement;
  explain_type:
      BASIC
    | OUTLINE
    | EXTENDED
    | EXTENDED_NOADDR
    | PARTITIONS
    | FORMAT = {TRADITIONAL| JSON}
  
  dml_statement:
      SELECT statement
    | DELETE statement
    | INSERT statement
    | REPLACE statement
    | UPDATE statement
  

• SELECT ... FOR SHARE ... statements are not supported.

INSERT

• OceanBase Database supports single- and multi-row inserts. It also supports inserting data into a specified partition.

• OceanBase Database supports INSERT INTO ... SELECT ... statements.

UPDATE

• OceanBase Database supports single- and multi-column updates.

• OceanBase Database supports updates by using subqueries.

• OceanBase Database supports set updates.

DELETE

• OceanBase Database supports single- and multi-table deletion.

TRUNCATE

• OceanBase Database allows you to truncate a specified table.

• OceanBase Database does not support truncating a table that has a transaction in progress or a table that is being locked.

Procedural languages

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

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

OceanBase Database also provides particular MySQL PL packages, such as DBMS_RESOURCE_MANAGER, DBMS_STATS, DBMS_UDR, and DBMS_XPLAN.

For more information about PL features, see PL reference.

System views

OceanBase Database implements most views of two internal databases: information_schema and mysql. However, due to the differences from MySQL Database in architecture, OceanBase Database cannot implement all views of 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 and collations

OceanBase Database is compatible with some character sets and collations of MySQL Database.

• Supported character sets are the binary, utf8mb4, gbk, utf16, gb18030, latin1, and gb18030_2022 character sets.

• The following table describes the supported collations.

  Collation	Character set	Description
  utf8mb4_general_ci	utf8mb4	A general collation.
  utf8mb4_bin	utf8mb4	A binary collation.
  utf8mb4_unicode_ci	utf8mb4	A collation that is based on Unicode Collation Algorithm (UCA).
  binary	binary	A binary collation.
  gbk_chinese_ci	gbk	A collation for Chinese.
  gbk_bin	gbk	A binary collation.
  utf16_general_ci	utf16	A general collation.
  utf16_bin	utf16	A binary collation.
  utf16_unicode_ci	utf16	A collation that is based on Unicode Collation Algorithm (UCA).
  gb18030_chinese_ci	gb18030	A collation for Chinese.
  gb18030_bin	gb18030	A binary collation.
  latin1_swedish_ci	latin1	A collation for Swedish/Finnish.
  latin1_bin	latin1	A binary collation.
  gb18030_2022_bin	gb18030_2022	A binary collation.
  gb18030_2022_chinese_ci	gb18030_2022	A collation that sorts data by Pinyin. The value is case-insensitive. This is the default collation for this character set in MySQL mode.
  gb18030_2022_chinese_cs	gb18030_2022	A collation that sorts data by Pinyin. The value is case-sensitive.
  gb18030_2022_radical_ci	gb18030_2022	A collation that sorts data by radicals. The value is case-insensitive.
  gb18030_2022_radical_cs	gb18030_2022	A collation that sorts data by radicals. The value is case-sensitive.
  gb18030_2022_stroke_ci	gb18030_2022	A collation that sorts data by strokes. The value is case-insensitive.
  gb18030_2022_stroke_cs	gb18030_2022	A collation that sorts data by strokes. The value is case-sensitive.

  Applicability

  OceanBase Database Community Edition does not support utf8mb4_unicode_ci and utf16_unicode_ci.

Functions

The MySQL mode of OceanBase Database does not support the following functions:

• String functions: LOAD_FILE() and MATCH().

• XML functions: ExtractValue() and UpdateXML().

• Locking functions: GET_LOCK(), IS_FREE_LOCK(), IS_USED_LOCK(), RELEASE_ALL_LOCKS(), and RELEASE_LOCK().

• Other functions: MASTER_POS_WAIT().

The analytic (window) functions supported by OceanBase Database are a superset of those supported by MySQL Database. This means that OceanBase Database supports all analytic (window) functions supported by MySQL Database.

Partition support

The partition support feature of OceanBase Database is different from that of MySQL Database.

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

• OceanBase Database supports subpartitioning by Hash, Key, Range, Range Columns, List, and List Columns. MySQL Database supports subpartitioning only by Hash and Key.

For more information about partitions, see Partion overview.

Backup and restore

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

• OceanBase Database supports full backup and incremental backup.

• Cluster-level backup and restore are not supported.

• OceanBase Database supports hot backup but not cold backup.

• OceanBase Database does not support backup and restore for some databases within tenants or backup and restore at the table level.

• OceanBase Database does not support validation of backup data.

Storage engine

OceanBase Database uses an LSM-Tree-based storage engine, whereas MySQL Database uses data block-based InnoDB or MyISAM.

Optimizer

The optimizer of OceanBase Database is different from that of MySQL Database in the following aspects:

• Commands to query execution plans

  • Only the ID, OPERATOR, NAME, EST. ROWS, and COST columns and the operator details are exported.

  • OceanBase Database does not support using the SHOW WARNINGS statement to display additional information.

• Statistics query

  • The optimizer allows you to manually query the histogram statistics information about column values in the data dictionary table by using the ANALYZE TABLE statement.

  • The optimizer automatically displays views of the table statistics and column statistics.

• Supported query rewrite and optimization features

  • Outer join optimization

  • Outer join simplification

  • Block Nested-Loop (BNL) and Batched Key Access (BKA) joins

  • Conditional filtering

  • Constant folding optimization

  • IS NULL optimization (indexes do not store NULL values)

  • ORDER BY optimization

  • GROUP BY optimization

  • Elimination by using DISTINCT

  • LIMIT pushdown

  • Window function optimization

  • Avoiding full table scan

  • Predicate pushdown

• Optimizer hint mechanisms

  • Join-order optimizer hints

  • Table-level optimizer hints

  • Index-level optimizer hints

  • OceanBase Database supports INDEX HINT, FULL HINT, ORDERED HINT, and LEADING HINT, but does not support USE INDEX or FORCE INDEX.

• OceanBase Database is compatible with the parallel execution capabilities of MySQL Database such as parallel query, parallel replication, and parallel write. OceanBase Database also supports parallel operations such as parallel aggregate, parallel join, parallel grouping, and parallel sorting.

• OceanBase Database supports plan caching and precompilation, which are not supported by MySQL Database.

For more information about the optimizer, see SQL tuning.

Unsupported features

• SELECT ... FOR SHARE ... statements are not supported.

• Backup and restore at the database, cluster, or table level, cold backup, verification of backup data, and backup and restore for some databases within tenants

• Using the SHOW WARNINGS statement in the command that returns the execution plan of an SQL statement