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 syntaxes
Procedural Language (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, andBIGINTFixed-point types:
DECIMALandNUMERICFloating-point types:
FLOATandDOUBLEBit-value type:
BIT
Date and time types:
DATETIME,TIMESTAMP,DATE,TIME, andYEARCharacter types:
CHAR,VARCHAR,BINARY, andVARBINARYLarge object types:
TINYBLOB,BLOB,MEDIUMBLOB, andLONGBLOBText types:
TINYTEXT,TEXT,MEDIUMTEXT, andLONGTEXTEnumeration type:
ENUMSet type:
SETJSON data types
Spatial data types
SQL syntaxes
SELECT
Supports most query features, including single- and multi-table queries, subqueries, lateral derived tables, inner joins, semi joins, outer joins, grouping, aggregation, and regular data mining functions such as probability and linear regression functions.
Allows you to perform set operations such as
UNION,UNION ALL,MINUS,EXCEPT, andINTERSECTon the results of multipleSELECTqueries.Supports the
EXPLAINstatement for viewing execution plans.
INSERT
Supports single- and multi-row inserts. OceanBase Database also supports inserting data into a specified partition.
Supports the
INSERT INTO ... SELECT ...statement.
UPDATE
Supports single- and multi-column updates.
Supports updates by using subqueries.
Supports set updates.
DELETE
- Supports single- and multi-table deletion.
TRUNCATE
- Allows you to truncate a specified table.
ROLE
CREATE ROLEDROP ROLESET DEFAULT ROLESET ROLE
SELECT
- Does not support the
SELECT ... FOR SHARE ...statement.
TRUNCATE
- 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. For more information about PL features, see PL reference.
OceanBase Database supports the following PL features of MySQL Database:
- 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.
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 columns in system views, see Outline of system views.
Character sets
OceanBase Database supports the following character sets:
binary
utf8mb4/utf8mb3
Note
utf8mb3is an alias forutf8mb4.gbk
utf16
utf16le
gb18030
latin1
gb18030_2022
ascii
tis620
sjis
big5
dec8
Collations
The following table describes the collations supported by OceanBase Database.
| 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). |
| utf8mb4_unicode_520_ci | utf8mb4 | A collation that is based on Unicode 5.2.0. The collation sorts data based on their Unicode code points and is case-insensitive. |
| utf8mb4_croatian_ci | utf8mb4 | A collation for Croatian. utf8mb4_croatian_ci is compatible with utf8_croatian_ci. |
| utf8mb4_czech_ci | utf8mb4 | A collation for Czech. |
| utf8mb4_0900_ai_ci | utf8mb4 | A collation that is based on Unicode 9.0.0. The collation is case-insensitive. |
| 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. |
| utf8mb4_german2_ci | utf16le | A collation for German. |
| utf8mb4_croatian_ci | utf16le | A collation for Croatian. |
| 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. |
| ascii_bin | ascii | A binary collation. |
| ascii_general_ci | ascii | A collation that sorts data in alphabetical order. The collation is case-insensitive. |
| tis620_bin | tis620 | A binary collation. |
| tis620_thai_ci | tis620 | A collation for Thai. The collation is case-insensitive. |
| sjis_japanese_ci | sjis | A collation for Japanese. |
| dec8_swedish_ci | dec8 | A collation for Swedish. |
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.
XML functions:
EXTRACTVALUEandUPDATEXML.Note
The MySQL mode of OceanBase Database complies with the XML 1.0 and XPath 1.0 specifications for processing XML and XPath data. OceanBase Database's MySQL mode may differ from MySQL Database in processing XML data in terms of compatibility and feature support.
The MySQL mode of OceanBase Database does not support the following functions:
String functions:
LOAD_FILE()andMATCH().Locking functions:
GET_LOCK(),IS_FREE_LOCK(),IS_USED_LOCK(),RELEASE_ALL_LOCKS(), andRELEASE_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.
Supports full backup and incremental backup.
Supports hot backup.
Supports restore at the database and table levels.
Does not support backup and restore at the cluster level
Does not support cold backup.
Does not support backup at the table level.
Does not support the validation of backup data.
Does not support database- or table-level backup within a tenant.
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.
Commands to query execution plans:
- Only the
ID,OPERATOR,NAME,EST. ROWS, andCOSTcolumns and the operator details are exported.
- Only the
Statistics queries:
The optimizer allows you to manually query the histogram statistics information about column values in the data dictionary table by using the
ANALYZE TABLEstatement.The optimizer automatically displays views of the table statistics and column statistics.
Query rewriting and optimization:
Outer join optimization
Outer join simplification
Block Nested-Loop (BNL) and Batched Key Access (BKA) joins
Conditional filtering
Constant folding optimization
IS NULLoptimization (indexes do not storeNULLvalues)ORDER BYoptimizationGROUP BYoptimizationElimination by using
DISTINCTLIMITpushdownWindow function optimization
Avoiding full table scan
Predicate pushdown
Optimizer hint mechanism:
Join-order optimizer hints
Table-level optimizer hints
Index-level optimizer hints
INDEX,FULL,ORDERED, andLEADINGhints
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 aggregation, 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:
- Does not support using the
SHOW WARNINGSstatement to display additional information.
- Does not support using the