MySQL compatibility

Data types

Numeric types

Type	MySQL	SelectDB
Boolean	Supported. Values: 0 = false, 1 = true.	Supported with the `BOOLEAN` keyword. Values: 0 = false, 1 = true.
Bit	Supported. Width: 1 to 64.	Not supported. Express the same intent with BOOLEAN or TINYINT.
Tinyint	Both signed and unsigned variants are available. Signed range: -128 to 127. Unsigned range: 0 to 255.	Signed only; no unsigned counterpart. Range: -128 to 127.
Smallint	Both signed and unsigned variants are available. Signed range: -2^15 to 2^15-1. Unsigned range: 0 to 2^16-1.	Signed only; no unsigned counterpart. Range: -32768 to 32767.
Mediumint	Both signed and unsigned variants are available. Signed range: -2^23 to 2^23-1. Unsigned range: 0 to 2^24-1.	Not supported. Use INT instead.
Int	Both signed and unsigned variants are available. Signed range: -2^31 to 2^31-1. Unsigned range: 0 to 2^32-1.	Signed only; no unsigned counterpart. Range: -2147483648 to 2147483647.
Bigint	Both signed and unsigned variants are available. Signed range: -2^63 to 2^63-1. Unsigned range: 0 to 2^64-1.	Signed only; no unsigned counterpart. Range: -2^63 to 2^63-1.
Largeint	Not available.	SelectDB-only type, signed only. Range: -2^127 to 2^127-1.
Decimal	Both signed and unsigned were valid before MySQL 8.0.17; unsigned has been deprecated in later versions. When precision and scale are omitted, the type defaults to DECIMAL(10, 0).	Signed only; no unsigned counterpart. When precision and scale are omitted, the type defaults to DECIMAL(38, 9).
Float / Double	Both signed and unsigned were valid before MySQL 8.0.17; unsigned has been deprecated in later versions.	Signed only; no unsigned counterpart.

Date and time types

Type	MySQL	SelectDB
Date	Range: 1000-01-01 to 9999-12-31. Format: YYYY-MM-DD.	Range: 0000-01-01 to 9999-12-31. Format: YYYY-MM-DD.
DateTime	Declaration: DATETIME([P]). P is the optional fractional-second precision. Range: 1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999. Format: YYYY-MM-DD hh:mm:ss[.fraction].	Declaration: DATETIME([P]). P is the optional fractional-second precision (0-6). Range: 0000-01-01 00:00:00[.000000] to 9999-12-31 23:59:59[.999999]. Format: YYYY-MM-DD hh:mm:ss[.fraction].
Timestamp	Declaration: TIMESTAMP[(P)]. Range: 1970-01-01 00:00:01.000000 UTC to 2038-01-19 03:14:07.999999 UTC.	Not supported. Use DATETIME instead.
Time	Declaration: TIME[(P)]. Range: -838:59:59.000000 to 838:59:59.000000. Format: hh:mm:ss[.fraction].	Not supported. Store the time portion in VARCHAR or extract it from a DATETIME column at query time.
Year	Range: 1901 to 2155, or 0000. Format: YYYY.	Not supported. Use SMALLINT or DATE instead.

String types

Type	MySQL	SelectDB
Char	CHAR(M), where M is the number of characters and defaults to 1. Fixed length: shorter values are right-padded with spaces. Byte range: [0, 255].	CHAR(M), where M is the number of bytes. Variable length: stored as written, no space padding. Byte range: [1, 255].
Varchar	VARCHAR(M), where M is the number of characters. Byte range: [0, 65535].	VARCHAR(M), where M is the number of bytes. Byte range: [1, 65533].
String	No equivalent type.	SelectDB-only type for large text values. Defaults to 1 MB (1,048,576 bytes); tunable up to about 2 GB (2,147,483,643 bytes).
Binary / Varbinary	Binary counterparts of CHAR / VARCHAR.	Not supported. Use STRING instead.
Blob / Text	TinyBlob, Blob, MediumBlob, LongBlob plus the matching Text family.	Not supported. Use STRING instead.
Enum / Set	Enum supports up to 65,535 members. Set supports up to 64 members.	Not supported. Constrain values yourself with VARCHAR or STRING.

JSON data type

Both MySQL and SelectDB ship a native JSON type. You can store JSON documents directly and access fields with JSON functions.

SelectDB-specific data types

The following types are unique to SelectDB and target analytical workloads:

HLL (HyperLogLog): an approximate distinct-count type with about 1% error (occasionally up to 2%), well suited to ultra-large cardinality estimates. Cannot be a Key column. Must be used with the HLL_UNION aggregate and accessed via HLL_UNION_AGG, HLL_CARDINALITY, and similar functions.
BITMAP: an exact distinct-count type that outperforms COUNT(DISTINCT). Cannot be a Key column. Must be used with the BITMAP_UNION aggregate and accessed via BITMAP_UNION_COUNT, BITMAP_HASH, and similar functions.
QUANTILE_STATE: an approximate quantile type that switches to TDigest aggregation when value count exceeds 2,048. Cannot be a Key column. Must be used with the QUANTILE_UNION aggregate and accessed via QUANTILE_PERCENT, TO_QUANTILE_STATE, and similar functions.
ARRAY<T>: an ordered collection of elements of type T. Cannot be a Key column.
MAP<K, V>: a collection of key-value pairs of types K and V. Cannot be a Key column.
STRUCT<field_name:field_type, ...>: a struct with a fixed set of named, always-Nullable fields. Cannot be a Key column.
VARIANT: a semi-structured type that auto-detects its schema and is stored columnarly. Suited to logs and user-profile workloads where field shape evolves over time.
AGG_STATE: holds an intermediate aggregation state. Requires the aggregate function signature to be declared at table creation; storage size depends on the implementation. Cannot be a Key column. Only usable with the STATE / MERGE / UNION combinators.

DDL syntax differences

CREATE TABLE

The overall SelectDB CREATE TABLE skeleton is:

CREATE TABLE [IF NOT EXISTS] [database.]table
(
    column_definition_list
    [, index_definition_list]
)
[engine_type]
[keys_type]
[table_comment]
[partition_info]
distribution_desc
[rollup_list]
[properties]
[extra_properties]

How each clause differs from MySQL:

Clause	Difference
column_definition_list	Column definitions themselves are close to MySQL. SelectDB allows an aggregate-type qualifier after the column (used by the Aggregate Key model). MySQL declares constraints such as PRIMARY KEY and UNIQUE KEY at the end of the column list; SelectDB expresses the equivalent via the data model (keys_type) instead.
index_definition_list	SelectDB declares bitmap, inverted, or N-Gram indexes inline; the Bloom filter index is configured separately via PROPERTIES. MySQL's B+Tree and Hash indexes are not provided in SelectDB.
engine_type	Optional. SelectDB defaults to its native OLAP engine. No alternative storage engines such as InnoDB or MyISAM.
keys_type	Optional, SelectDB-specific concept that drives storage and read/write semantics. DUPLICATE KEY (default): listed columns become sort columns. AGGREGATE KEY: listed columns become dimensions. UNIQUE KEY: listed columns become the primary key. MySQL has no notion of a data model.
table_comment	Same usage as MySQL.
partition_info	Optional. SelectDB offers three RANGE partition styles: LESS THAN: only the upper bound is declared; the lower bound is inherited from the previous partition. FIXED RANGE: declare a half-open interval directly. MULTI RANGE: bulk-create RANGE partitions by time unit (year/month/day/week/hour) and step size. MySQL provides Hash, Range, and List Key plus sub-partitions; SelectDB does not have sub-partitions.
distribution_desc	Required; MySQL has no equivalent. Hash bucketing: `DISTRIBUTED BY HASH(k1[,k2 ...]) [BUCKETS num\|auto]`. Random bucketing: `DISTRIBUTED BY RANDOM [BUCKETS num\|auto]`.
rollup_list	Optional, SelectDB extension. Lets you declare one or more synchronous materialized views inside the CREATE TABLE statement. Syntax: `rollup_name (col1[, col2, ...]) [DUPLICATE KEY(col1[, ...])][PROPERTIES("key"="value")]`.
properties	Property keys and values differ entirely from MySQL. They configure SelectDB-specific knobs such as replica count and storage policy.

Full example:

CREATE TABLE demo.user_behavior (
    user_id BIGINT,
    item_id BIGINT,
    behavior VARCHAR(20),
    ts DATETIME
)
DUPLICATE KEY(user_id, item_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 8
PROPERTIES ("replication_allocation" = "tag.location.default: 1");

CREATE INDEX

CREATE INDEX [IF NOT EXISTS] index_name ON table_name (column [, ...]) [USING BITMAP];

SelectDB supports three CREATE INDEX kinds: bitmap, inverted, and N-Gram. The Bloom filter index is configured through table PROPERTIES instead. The B+Tree and Hash indexes that MySQL relies on are unnecessary in SelectDB because the underlying columnar format already provides the equivalent filtering.

CREATE VIEW

Logical views behave like MySQL views. SelectDB additionally provides synchronous and asynchronous materialized views, which MySQL does not.

-- Logical view
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name
  (column1[ COMMENT "col comment"][, column2, ...])
AS query_stmt;

-- Asynchronous materialized view
CREATE MATERIALIZED VIEW [IF NOT EXISTS] mv_name
  (col_defs)
  [BUILD (IMMEDIATE | DEFERRED)]
  [REFRESH [refresh_method] [refresh_trigger]]
  [KEY (keys)]
  [COMMENT "comment"]
  [PARTITION BY (partition_key)]
  [DISTRIBUTED BY {HASH(keys) | RANDOM} [BUCKETS num|AUTO]]
  [PROPERTIES (...)]
AS query;

ALTER / DROP TABLE and INDEX

ALTER TABLE / ALTER INDEX and DROP TABLE / DROP INDEX behave close enough to MySQL that existing scripts can usually be reused. Be aware that SelectDB column changes go through Schema Change and run asynchronously; track progress with SHOW ALTER TABLE COLUMN before issuing follow-up writes or queries that depend on the new column.

DML syntax differences

INSERT

INSERT is MySQL-compatible and adds clauses for partition pinning, the WITH LABEL identifier, and execution-plan hints:

INSERT INTO table_name
    [PARTITION (p1, ...)]
    [WITH LABEL label]
    [(column [, ...])]
    [[hint [, ...]]]
    {VALUES ({expression | DEFAULT} [, ...]) [, ...] | query};

UPDATE

UPDATE looks like MySQL's, but with two limitations:

A WHERE clause is mandatory; omitting it raises a syntax error.
Only Unique Key tables can be updated. For other models, use INSERT OVERWRITE or re-load the data.

UPDATE target_table [table_alias]
    SET assignment_list
    WHERE condition;

DELETE

DELETE is close to MySQL and offers two forms to match different data models:

-- Form 1: simple predicates only; works on any data model
DELETE FROM table_name [table_alias]
    [PARTITION partition_name | PARTITIONS (partition_name [, ...])]
    WHERE column_name op {value | value_list} [AND column_name op {value | value_list} ...];

-- Form 2: complex conditions and multi-table joins (USING); Unique Key tables only
DELETE FROM table_name [table_alias]
    [PARTITION partition_name | PARTITIONS (partition_name [, ...])]
    [USING additional_tables]
    WHERE condition;

Note

SelectDB is an analytical database and is not optimized for high-frequency single-row deletes. Maintain data through partition rotation or INSERT OVERWRITE batches whenever possible.

SELECT

SELECT is MySQL-compatible and adds Hint, EXCEPT column elimination, PARTITION/TABLET pinning, TABLESAMPLE, GROUPING SETS / ROLLUP / CUBE for multi-dimensional aggregation, and INTO OUTFILE:

SELECT
    [hint_statement, ...]
    [ALL | DISTINCT]
    select_expr [, select_expr ...]
    [EXCEPT (col_name1 [, col_name2, ...])]
    [FROM table_references
      [PARTITION partition_list]
      [TABLET tabletid_list]
      [TABLESAMPLE sample_value [ROWS | PERCENT] [REPEATABLE pos_seek]]]
    [WHERE where_condition]
    [GROUP BY [GROUPING SETS | ROLLUP | CUBE] {col_name | expr | position}]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
    [LIMIT {[offset_count,] row_count | row_count OFFSET offset_count}]
    [INTO OUTFILE 'file_name'];

SQL functions

Beyond covering most MySQL built-ins, SelectDB ships analytical extensions for BITMAP, HLL, ARRAY, MAP, and more. See the SelectDB function reference for the full inventory.

SQL Mode

Set a SQL Mode through SET sql_mode = 'MODE_NAME'. The most common modes are listed below.

Name	When set	When unset	Notes
`PIPES_AS_CONCAT`	`\|\|` evaluates as the CONCAT string-join function.	`\|\|` evaluates as the logical OR operator.	-
`NO_BACKSLASH_ESCAPES`	Backslashes inside string literals are taken literally and no longer trigger escape sequences.	Backslashes inside string literals start an escape sequence.	-
`ONLY_FULL_GROUP_BY`	Non-aggregated columns in SELECT must appear in GROUP BY, otherwise the query errors out.	SELECT may return scalar columns that are not in the GROUP BY KEY.	Available since 3.1.0.