Compatibility with MySQL|V4.3.0| docs|Distributed Database

Compatibility with MySQL

Last Updated：2024-06-12 03:12:31 Updated

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
PL features
System views
Character sets
Collations
Functions and expressions
Partition support
Backup and restore
Storage engine
Optimizer

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

Supported

Not supported

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.
OceanBase Database supports the EXPLAIN statement for viewing execution plans.

INSERT

OceanBase Database supports single- and multi-row inserts. It also supports inserting data into a specified partition.
OceanBase Database supports the INSERT INTO ... SELECT ... statement.

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 data deletion.

TRUNCATE

OceanBase Database allows you to truncate a specified table.

SELECT

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

TRUNCATE

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

PL features

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, DBMS_XPLAN, and DBMS_WORKLOAD_REPOSITORY.

For more information about PL features, see PL reference.

System views

OceanBase Database implements most views of two MySQL 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

OceanBase Database supports the following character sets:

binary
utf8mb4
gbk
utf16
gb18030
latin1
gb18030_2022

Collations

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 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 collation 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 collation is case-sensitive.
gb18030_2022_radical_ci	gb18030_2022	A collation that sorts data by radicals. The collation is case-insensitive.
gb18030_2022_radical_cs	gb18030_2022	A collation that sorts data by radicals. The collation is case-sensitive.
gb18030_2022_stroke_ci	gb18030_2022	A collation that sorts data by strokes. The collation is case-insensitive.
gb18030_2022_stroke_cs	gb18030_2022	A collation that sorts data by strokes. The collation is case-sensitive.

Functions

Supported

Not supported

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.

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

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 the following subpartitioning methods: HASH, KEY, RANGE, RANGE COLUMNS, LIST, and LIST COLUMNS. MySQL Database supports only HASH and KEY subpartitioning.

For more information about partitions, see Partition overview.

Backup and restore

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

Supported

Not supported

OceanBase Database supports full backup and incremental backup.
OceanBase Database supports hot backup.
OceanBase Database supports restore at the table and database levels.

OceanBase Database does not support cluster-level backup and restore.
OceanBase Database does not support cold backup.
OceanBase Database does not support backup for some databases within tenants or at the table level.
OceanBase Database does not support the 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

OceanBase Database is compatible with some optimizer features of MySQL Database. For more information about the optimizer, see SQL tuning.

Supported

Not supported

Commands to query execution plans:
- Only the ID, OPERATOR, NAME, EST. ROWS, and COST columns and the operator details are exported.
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.
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
- INDEX, FULL, ORDERED, and LEADING 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 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.

Commands to query execution plans:
- OceanBase Database does not support using the SHOW WARNINGS statement to display additional information.
Optimizer hint mechanism:
- OceanBase Database does not support USE INDEX and FORCE INDEX hints.