Compatibility with MySQL

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

OceanBase Database in MySQL mode 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.

• OceanBase Database does not support direct INSERT operations on subqueries.

UPDATE

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

• OceanBase Database supports updates by using subqueries.

• OceanBase Database supports set updates.

• OceanBase Database does not support direct UPDATE operations on subqueries.

DELETE

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

• OceanBase Database does not support direct DELETE operations on subqueries.

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 System views.

Character sets and collations

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

• Supported character sets: binary, utf8mb4, gbk, utf16, gb18030, and latin1.

• Supported collations: utf8mb4_general_ci, utf8mb4_bin, binary, gbk_chinese_ci, gbk_bin, utf16_general_ci, utf16_bin, utf8mb4_unicode_ci, utf16_unicode_ci, gb18030_chinese_ci, gb18030_bin, latin1_swedish_ci, and latin1_bin.

Functions

OceanBase Database in MySQL mode 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 Partition management.

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

• 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