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 syntax and all JSON functions in MySQL 5.7 and some JSON functions in MySQL 8.0. Some features are not supported because of their limited popularity or the product architecture differences between the two databases. This topic describes the differences between OceanBase Database in MySQL mode and native MySQL Database in the following aspects:

• Data types

• SQL syntax

• System views

• Character sets and collations

• Functions and expressions

• Partition support

• Backup and recovery

• 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

• Collection type: SET

• JSON data types

OceanBase Database does not support spatial data types. However, OceanBase Database supports the same or more data types of other categories than native MySQL Database does.

SQL syntax

SELECT

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

• 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 insert. 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.

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.

Character sets and collations

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

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

• 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, and gb18030_bin.

Functions

OceanBase Database in MySQL mode does not support the following functions:

• Date and time functions: ADDTIME(), DAY(), and DAYNAME().

• Character string functions: CHARACTER_LENGTH(), FROM_BASE64(), LOAD_FILE(), MATCH(), OCTET_LENGTH(), SOUNDEX(), and TO_BASE64().

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

• Encryption and compression functions: COMPRESS(), RANDOM_BYTES(), SHA1(), SHA(), SHA2(), UNCOMPRESS(), UNCOMPRESSED_LENGTH(), and VALIDATE_PASSWORD_STRENGTH().

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

• Other functions: MASTER_POS_WAIT() and NAME_CONST().

OceanBase Database in MySQL mode does not support spatial analysis functions or performance schema functions. 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.

Backup and recovery

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

• Cluster-level backup and recovery are not supported.

• OceanBase Database supports hot backup but not cold backup.

• OceanBase Database does not support database- or table-level backup or recovery.

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 queried.

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

• Statistics query

  • OceanBase Database supports the ANALYZE TABLE statement for querying the histogram statistics about column values in the data dictionary table.

  • You can query the __all_meta_table internal table for statistics information about tables and columns.

• 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

  • Analytic 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.

Unsupported features

• Spatial data types

• SELECT … FOR SHARE … statements

• Spatial analysis functions and performance schema functions

• Backup and recovery at the database, cluster, or table level, and cold backup

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