Compatibility with MySQL

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

OceanBase Database in MySQL mode is compatible with most features and statements of MySQL 5.6, and does not support some features considering the popularity of these features or the differences between the two databases in product architecture. This topic describes the differences of OceanBase Database in MySQL mode from the native MySQL database in the following aspects:

• Data types

• SQL syntax

• System views

• Character sets and collations

• Functions and expressions

• Partition support

• Backup and restoration

• Storage engines

• Optimizers

• Unsupported features

Data types

OceanBase Database supports the following data types:

• Numeric type

  • 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

  • VARCHAR, VARBINARY, CHAR, BINARY, ENUM, and SET

• Large object (LOB) types

  • TINYTEXT, TINYBLOB, TEXT, BLOB, MEDIUMTEXT, MEDIUMBLOB, LONGTEXT, and LONGBLOB

Compared with the native MySQL database, OceanBase Database does not support spatial data type and JSON data types. OceanBase Database supports other data types same as or more than the native MySQL database.

SQL syntax

SELECT

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

• OceanBase Database supports the following set operations:

  UNION, UNION ALL, INTERSECT, and MINUS

• You can run the following statement to view an execution plan:

  EXPLAIN <SQL Statement>;
  EXPLAIN extended <SQL Statement>
  

• OceanBase Database does not support the SELECT ... FOR SHARE ... statement.

INSERT

• OceanBase Database supports single- and multi-row insertion, and supports data insertion into a specified partition.

• OceanBase Database supports the INSERT INTO ... SELECT ... statement.

UPDATE

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

• OceanBase Database supports update by using sub queries.

• OceanBase Database supports set updates.

DELETE

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

TRUNCATE

• OceanBase Database allows you to truncate a specified table.

System views

OceanBase Database implements most views of the information_schema and mysql databases. However, due to the differences from MySQL Database in architecture, OceanBase Database cannot implement all views of the databases or ensure consistency with MySQL Database in all view column meanings.

For more information about system views, see System views in Reference Guide (MySQL mode).

Character sets and collations

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

• OceanBase Database supports character sets such as binary, utf8mb4, gbk, utf16, and gb18030.

• OceanBase Database supports the following 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:

• Mathematical functions: COT(), CRC32(), DEGREES(), LN(), LOG(), PI(), and RADIANS()

• Date and time functions: ADDDATE(), ADDTIME(), CONVERT_TZ(), DAY(), DAYNAME(), GET_FORMAT(), LOCALTIME(), MAKEDATE(), MAKETIME(), MONTHNAME(), PERIOD_ADD(), QUARTER(), SUBDATE(), TIME_FORMAT(), TIMESTAMP(), and UTC_DATE()

• String functions: BIT_LENGTH(), CHARACTER_LENGTH(), EXPORT_SET(), FROM_BASE64(), LCASE(), LOAD_FILE(), MATCH, OCTET_LENGTH(), RLIKE, SOUNDEX(), SOUNDS LIKE, TO_BASE64(), UCASE(), and WEIGHT_STRING()

• Cast function: BINARY

• XML functions: ExtractValue() and UpdateXML()

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

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

• Information functions: BENCHMARK(), CURRENT_ROLE(), ICU_VERSION(), ROLES_GRAPHML(), SCHEMA(), SESSION_USER(), and SYSTEM_USER()

• Aggregate functions: BIT_AND(), BIT_OR(), BIT_XOR(), JSON_ARRAYAGG(), JSON_OBJECTAGG(), STD(), VAR_POP(), and VAR_SAMP()

• Window functions: The window functions supported by OceanBase Database is a superset of those supported by MySQL Database.

• Other functions: ANY_VALUE(), BIN_TO_UUID(), INET_ATON(), INET_NTOA(), INET6_ATON(), INET6_NTOA(), IS_IPV4(), IS_IPV4_COMPAT(), IS_IPV4_MAPPED(), IS_IPV6(), IS_UUID(), MASTER_POS_WAIT(), NAME_CONST(), UUID_SHORT(), and UUID_TO_BIN()

OceanBase Database in MySQL mode does not support spatial analysis functions, JSON functions, and performance schema functions.

Partition support

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

• OceanBase Database supports partitioning and templated-based 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 only subpartitioning by HASH and KEY .

For more information about partitioning, see Manage partitioned tables and partitioned indexes in Administrator Guide.

Backup and restoration

OceanBase Database is compatible with some backup and restoration features of MySQL Database:

Storage engines

OceanBase Database uses a storage engine that is based on the LSM-Tree, whereas MySQL Database uses InnoDB and MyISAM engines that are based on data blocks.

Optimizers

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

• Commands to query execution plans

  • The output columns include only ID, OPERATOR, NAME, EST.EST. ROWS, COST, and operator details.

  • OceanBase Database does not support using the SHOW WARNINGS statement to query other information.

• Statistics query

  • OceanBase Database does not support the ANALYZE TABLE statement for querying the histogram statistics information about column values in the data dictionary table.

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

• Query rewrite and optimization features supported

  • 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 the INDEX HINT, FULL HINT, ORDERED HINT, and LEADING HINT hints, but does not support the USE INDEX and FORCE INDEX hints.

• 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 operators 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 Execution process of SQL queries.

Unsupported features

• Spatial data types and JSON data types

• SELECT ... FOR SHARE ... statement

• Spatial analysis functions, JSON functions, or performance schema functions

• Database- and table-level backup and restoration, and validity verification of backup data

• SHOW WARNINGS statement for querying more information except that specified and the ANALYZE TABLE statement for querying the histogram statistics information about column values in the data dictionary table