MySQL Compatibility

MatrixOne SQL syntax conforms with MySQL 8.0.23 version.

SQL TypeSQL SyntaxCompability with MySQL8.0.23
Data Definition Language(DDL)CREATE DATABASEA database with Chinese name is not supported.
Names with Latins support limitedly.
CHARSET, COLLATE, ENCRYPTION can be used but don’t work.
CREATE TABLEPartition 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.
ALTERNot supported now.
DROP DATABASESame as MySQL.
DROP TABLESame as MySQL.
CREAT VIEWThe with check option clause is not supported yet.
Data Manipulation Language (DML)UPDATASame as MySQL.
DELETESame as MySQL.
INSERTLOW_PRIORITY, DELAYED, HIGH_PRIORITY are not supported now.
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.
SELECTTable 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 DATAcsv 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.
JOINSame as MySQL.
SUBQUERYNon-scalar subquery is supported limitedly.
Database Administration StatementsSHOWSHOW statement is supported limitedly.
Utility StatementsExplainThe result of explain a SQL is different with MySQL.
json output is not supported yet.
Other statementsNot supported now.
Data TypesBooleanDifferent 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/TinyintSame as MySQL.
char/varcharSame as MySQL.
Float/doubleThe precision is a bit different with MySQL.
DECIMALThe max precision is 38 digits.
DateOnly ‘YYYY-MM-DD’ and ‘YYYYMMDD’ formats are supported.
DatetimeOnly ‘YYYY-MM-DD HH:MM:SS’ and ‘YYYYMMDD HH:MM:SS’ formats are supported.
TimestampSame as MySQL.
Other typesNot supported now.
Operators“+”,”-“,”*”,”/“Same as MySQL.
DIV, %, MODSame as MySQL.
LIKESame as MySQL.
INSupported for constant lists
NOT, AND, &&,OR, “||”Same as MySQL.
CASTSupported with different conversion rules.
FunctionsMAX, MIN, COUNT, AVG, SUMSame as MySQL.
any_valueAny_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_dateOnly constants are supported for date entries
System commandSHOW GRANTS FOR USERSOnly the permissions of directly authorized roles can be displayed. The rights of inherited roles cannot be shown.