Skip to content

MySQL Compatibility

MatrixOne SQL syntax conforms with MySQL 8.0.23 version.

SQL Type SQL Syntax Compability with MySQL8.0.23
Data Definition Language(DDL) CREATE DATABASE A database with Chinese name is not supported.
Names with Latins support limitedly.
CHARSET, COLLATE, ENCRYPTION can be used but don't work.
CREATE TABLE Partition tables are not supported.
Create table .. as clause is not supported now.
All column level constraints are not supported now.
KEY(column) is not supported yet.
AUTO_INCREMENT is supported limitedly. The custom starting value is not supported yet.
ALTER Not supported now.
CREAT VIEW The with check option clause is not supported yet.
Data Manipulation Language (DML) UPDATA Same as MySQL.
INSERT INTO VALUES with function or expression is not supported now.
Batch Insert can be supported up to 160,000 rows.
ON DUPLICATE KEY UPDATE is not supported now.
DELAYED is not supported now.
Names with Latins support limitedly.
The current SQL mode is just like only_full_group_by mode in MySQL.
SELECT Table alias is not supported in GROUP BY.
Distinct is limitedly support.
SELECT...FOR UPDATE clause is not supported now.
INTO OUTFILE is limitedly support.
LOAD DATA csv or jsonline files can be loaded currently.
The enclosed character should be "".
FIELDS TERMINATED BY should be "," or "
LINES TERMINATED BY should be "\n".
SET is supported limitedly. Only SET columns_name=nullif(expr1,expr2) is supported.
Local key word is not supported now.
ESCAPED BY is not supported now.
JOIN Same as MySQL.
SUBQUERY Non-scalar subquery is supported limitedly.
Database Administration Statements SHOW SHOW statement is supported limitedly.
Utility Statements Explain The result of explain a SQL is different with MySQL.
json output is not supported yet.
Other statements Not supported now.
Data Types Boolean Different from MySQL's boolean which is the same as int , MatrixOne's boolean is a new type, its value can only be true or false.
Int/Bigint/Smallint/Tinyint Same as MySQL.
char/varchar Same as MySQL.
Float/double The precision is a bit different with MySQL.
DECIMAL The max precision is 38 digits.
Date Only 'YYYY-MM-DD' and 'YYYYMMDD' formats are supported.
Datetime Only 'YYYY-MM-DD HH:MM:SS' and 'YYYYMMDD HH:MM:SS' formats are supported.
Timestamp Same as MySQL.
Other types Not supported now.
Operators "+","-","*","/" Same as MySQL.
DIV, %, MOD Same as MySQL.
LIKE Same as MySQL.
IN Supported for constant lists
NOT, AND, &&,OR, "||" Same as MySQL.
CAST Supported with different conversion rules.
Functions MAX, MIN, COUNT, AVG, SUM Same as MySQL.
any_value Any_value is an aggregate function in MatrixOne. Cannot be used in group by or filter condition.
REGEXP_INSTR(),REGEXP_LIKE(),REGEXP_REPLACE(),REGEXP_SUBSTR() The third parameter is not supported yet
to_date Only constants are supported for date entries
System command SHOW GRANTS FOR USERS Only the permissions of directly authorized roles can be displayed. The rights of inherited roles cannot be shown.