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

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

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, 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() 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().

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

• OceanBase Database supports template-based and non-template-based subpartitioned table groups. MySQL databases do not support non-template-based subpartitioned table groups.

For more information about partitioning, see the 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.

• OceanBase Database supports secondary backup.

• OceanBase Database supports the verification of backup data.

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

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

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

  • OceanBase Database allows you to view statistics about tables and columns by querying the __all_meta_table internal table.

• 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 the SQL Tuning Guide.

Unsupported features

• Conversion of primary key columns from the INT type to the BIGINT type

• SELECT ... FOR SHARE ... statements

• Performance schema functions

• Backup and restore for some databases within tenants and backup and restore at the table level

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