Compatibility with MySQL

This topic describes the compatibility between seekdb and native MySQL.

seekdb supports most features and syntax from MySQL 5.7 and 8.0. However, due to architectural differences or limited demand, some features are not supported. The following sections provides compatibility details between seekdb and MySQL:

• Data types

• SQL syntaxes

• Procedural Language (PL) features

• System views

• Character sets

• Collations

• Functions and expressions

• Partition support

• Storage engine

• Optimizer

Data types

seekdb 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, LONGTEXT, and STRING

• Enumeration type: ENUM

• Set type: SET

• JSON data types

• Spatial data types

• Roaring Bitmap data types

• Array data types

• Vector data types

SQL syntaxes

tab Supported

SELECT

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

• Supports set operations such as UNION, UNION ALL, MINUS, EXCEPT, and INTERSECT across multiple SELECT queries.

• Supports the EXPLAIN statement for querying execution plans.

INSERT

• Supports single- and multi-row inserts, including partition-specific inserts.

• 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 completely clear a specified table.

tab Not supported

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

seekdb is compatible with most PL features of MySQL, including:

• Data types
• Stored procedures
• Custom functions
• Triggers
• Exception handling

seekdb also supports specific 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

seekdb implements most views in the information_schema and mysql internal databases. However, due to architectural differences, some views or columns may differ in meaning or implementation compared with MySQL.

For more information about system views, see Overview of system views.

Character sets

seekdb supports the following character sets:

• binary

• utf8mb4/utf8mb3

  info

  utf8mb3 is an alias of utf8mb4.

• gbk

• utf16

• utf16le

• gb18030

• latin1

• gb18030_2022

• ascii

• tis620

• sjis

• big5

• dec8

• gb2312

• ujis

• euckr

• eucjpms

• cp932

• cp850

• hp8

• macroman

• swe7

Collations

The following table lists collations supported by seekdb.

Collation	Character Set	Description
utf8mb4_general_ci	utf8mb4	Uses general collation rules.
utf8mb4_bin	utf8mb4	Uses binary collation rules.
utf8mb4_unicode_ci	utf8mb4	Uses collation rules based on the Unicode Collation Algorithm (UCA).
utf8mb4_unicode_520_ci	utf8mb4	Uses collation rules from Unicode V5.2.0, sorts by Unicode code point, and ignores case differences.
utf8mb4_croatian_ci	utf8mb4	Uses Croatian collation rules. Compatible with utf8_croatian_ci.
utf8mb4_czech_ci	utf8mb4	Uses Czech collation rules.
utf8mb4_0900_ai_ci	utf8mb4	Uses collation rules from Unicode V9.0.0, ignores case differences, and treats uppercase and lowercase letters as equivalent.
binary	binary	Uses binary collation rules.
gbk_chinese_ci	gbk	Uses Chinese collation rules.
gbk_bin	gbk	Uses binary collation rules.
utf16_general_ci	utf16	Uses general collation rules.
utf16_bin	utf16	Uses binary collation rules.
utf16_unicode_ci	utf16	Uses collation rules based on the UCA.
utf8mb4_german2_ci	utf16le	Uses German collation rules.
utf8mb4_croatian_ci	utf16le	Uses Croatian collation rules.
gb18030_chinese_ci	gb18030	Uses Chinese collation rules.
gb18030_bin	gb18030	Uses binary collation rules.
latin1_swedish_ci	latin1	Uses Swedish/Finnish collation rules.
latin1_german1_ci	latin1	Uses German collation rules for the latin1 character set.
latin1_danish_ci	latin1	Uses Danish collation rules for the latin1 character set.
latin1_german2_ci	latin1	German dictionary order collation rules for the latin1 character set.
latin1_general_ci	latin1	General collation rules, case-insensitive and accent-sensitive; suitable for some European languages.
latin1_general_cs	latin1	General collation rules, case-sensitive; supports multiple Western European languages.
latin1_spanish_ci	latin1	Spanish collation rules for the latin1 character set.
latin1_bin	latin1	Binary collation rules for the latin1 character set.
gb18030_2022_bin	gb18030_2022	Uses binary collation rules.
gb18030_2022_chinese_ci	gb18030_2022	Uses Pinyin collation rules, case-insensitive. This is the default collation for this character set.
gb18030_2022_chinese_cs	gb18030_2022	Uses Pinyin collation rules, case-sensitive.
gb18030_2022_radical_ci	gb18030_2022	Uses radical-stroke order collation rules, case-insensitive.
gb18030_2022_radical_cs	gb18030_2022	Uses radical-stroke order collation rules, case-sensitive.
gb18030_2022_stroke_ci	gb18030_2022	Uses stroke order collation rules, case-insensitive.
gb18030_2022_stroke_cs	gb18030_2022	Uses stroke order collation rules, case-sensitive.
ascii_bin	ascii	Uses binary collation rules.
ascii_general_ci	ascii	Alphabetical collation rules, case-insensitive; treats uppercase and lowercase letters as equivalent.
tis620_bin	tis620	Uses binary collation rules.
tis620_thai_ci	tis620	Thai collation rules, case-insensitive.
sjis_japanese_ci	sjis	Japanese collation rules for the SJIS character set.
dec8_swedish_ci	dec8	Swedish collation rules for the DEC8 character set.
gb2312_chinese_ci	gb2312	Chinese collation rules for the GB2312 character set, case-insensitive.
gb2312_bin	gb2312	Binary collation rules for the GB2312 character set, case-sensitive.
ujis_japanese_ci	ujis	Japanese collation rules for the UJIS character set, case-insensitive.
ujis_bin	ujis	Binary collation rules for the UJIS character set, case-sensitive.
euckr_korean_ci	euckr	Korean collation rules for the EUCKR character set, case-insensitive.
euckr_bin	euckr	Binary collation rules for the EUCKR character set, case-sensitive.
eucjpms_japanese_ci	eucjpms	Japanese collation rules for the EUCJPMS character set, case-insensitive.
eucjpms_bin	eucjpms	Binary collation rules for the EUCJPMS character set, case-sensitive.
cp932_japanese_ci	cp932	Japanese collation rules for the CP932 character set, case-insensitive.
cp932_bin	cp932	Binary collation rules for the CP932 character set, case-sensitive.
cp850_general_ci	cp850	General collation rules for the CP850 character set, case-insensitive.
cp850_bin	cp850	Binary collation rules for the CP850 character set, case-sensitive.
hp8_english_ci	hp8	English collation rules for the HP8 character set, case-insensitive.
hp8_bin	hp8	Binary collation rules for the HP8 character set, case-sensitive.
macroman_general_ci	macroman	General collation rules for the MacRoman character set, case-insensitive.
macroman_bin	macroman	Binary collation rules for the MacRoman character set, case-sensitive.
swe7_swedish_ci	swe7	Swedish collation rules for the SWE7 character set, case-insensitive.
swe7_bin	swe7	Binary collation rules for the SWE7 character set, case-sensitive.

Functions

tab Supported

seekdb supports all analytic (window) functions that are available in MySQL, and even more. In other words, every analytic (window) function supported by MySQL is also supported by seekdb.

tab Not supported

Compared with MySQL, seekdb does not support the following functions:

• String functions: LOAD_FILE() and MATCH()

• XML functions: ExtractValue() and UpdateXML()

• Other functions: MASTER_POS_WAIT()

Partition support

seekdb and MySQL differ in their support for table partitioning as follows:

• seekdb supports partitioning, template-based subpartitioning, and non-template-based subpartitioning. MySQL does not support non-template-based subpartitioning.

• For subpartitioning, seekdb supports HASH, KEY, RANGE, RANGE COLUMNS, LIST, and LIST COLUMNS subpartitioning. MySQL supports only HASH and KEY subpartitioning.

• seekdb supports adding and dropping subpartitions in partitioned tables. MySQL does not support adding and dropping subpartitions.

  tip

  Adding and dropping subpartitions is not supported for scenarios where the subpartitioning type is HASH or KEY.

For more information about partitions, see Partition overview.

Storage engine

Unlike MySQL, which uses data block-based storage engines such as InnoDB and MyISAM, seekdb uses a storage engine based on the LSM-Tree architecture.

Optimizer

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

tab Supported

• Commands to query execution plans:

  • The output columns of the execution plan view include only ID, OPERATOR, NAME, EST. ROWS, and COST, as well as the details of operators.

• Statistics queries:

  • You can manually query the histogram statistics information stored in the data dictionary table by executing the ANALYZE TABLE statement.

  • You can automatically query table statistics and column statistics through views.

• 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 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 mechanism:

  • Join-order optimizer hints

  • Table-level optimizer hints

  • Index-level optimizer hints

  • INDEX, FULL, ORDERED, and LEADING hints

• Parallel execution capabilities such as parallel query, parallel replication, and parallel write, which are compatible with those in MySQL. seekdb also supports parallel operations such as parallel aggregation, parallel join, parallel grouping, and parallel sorting.

• Plan caching and precompilation, which are not supported by MySQL.

tab Not supported

• Commands to query execution plans:

  • Does not support the SHOW WARNINGS statement for displaying additional information about execution plans.