Skip to content

MySQL Compatibility

This article mainly compares and introduces the MySQL schema of the MatrixOne Intelligence database and the compatibility information of the native MySQL database.

MatrixOne Intelligence has high compatibility with MySQL 8.0 protocols, as well as commonly used functions and syntaxes in MySQL 8.0. In addition, MatrixOne Intelligence also provides support for common MySQL peripheral tools, such as Navicat, MySQL Workbench, JDBC, etc. However, because MatrixOne Intelligence has different technical architectures and is still in the stage of development and improvement, some features have not yet been supported. This section will mainly describe the difference between the MySQL schema of the MatrixOne Intelligence database and the native MySQL database from the following aspects:

  • DDL statement
  • DCL statement
  • DML statements
  • Advanced SQL features
  • Data Type
  • Index and constraints
  • Partition support
  • Functions and operators
  • Storage Engine
  • Transactions
  • Security and permissions
  • Backup and restore
  • System variables
  • programming language
  • Peripheral tools

DDL statement

  • Chinese-named table names are not supported.
  • ENCRYPTION is currently supported but does not take effect.
  • ALTER DATABASE is not supported.
  • Only the utf8mb4 character set and utf8mb4_bin collation are supported by default and cannot be changed.
  • ENGINE= in table definition is not supported, but MatrixOne Intelligence will ignore ENGINE= directly.
  • These clauses in ALTER TABLE: CHANGE [COLUMN], MODIFY [COLUMN], RENAME COLUMN, ADD [CONSTRAINT [symbol]] PRIMARY KEY, DROP PRIMARY KEY and ALTER COLUMN ORDER BY can be used freely in combination in ALTER TABLE statements, but are not supported with other clauses.
  • Temporary tables do not support the use of ALTER TABLE to modify the table structure.
  • Use CREATE TABLE ... CLUSTER BY... to modify the table structure using ALTER TABLE.
  • ALTER TABLE does not support PARTITION related operations.
  • Supports the definition of the Cluster by column sentence, and pre-sort a column to speed up query.
  • The with check option clause is not supported.
  • The DEFINER and SQL SECURITY clauses are not supported.
  • MySQL does not support SEQUENCE objects, while MatrixOne Intelligence can create a sequence through CREATE SEQUENCE. The syntax of MatrixOne Intelligence is the same as PostgreSQL.
  • When using SEQUENCE in the table, you need to note that auto_increment and sequence cannot be used together, otherwise an error will be reported.

DCL statement

  • Unlike the MatrixOne kernel, there is no separate ACCOUNT concept in MatrixOne Intelligence. Each instance created in a Region of MatrixOne Intelligence belongs to a MatrixOne distributed cluster. Each instance in it is actually a separate ACCOUNT in the cluster. ACCOUNT cannot be created in the MatrixOne Intelligence instance.
  • GRANT, authorization logic is different from MySQL.
  • REVOKE, the logic of recycled permissions is different from that of MySQL.
  • MatrixOne Intelligence does not support SHOW operations on certain objects, including TRIGGER, FUNCTION, EVENT, PROCEDURE, ENGINE, etc.
  • Due to architectural differences, MatrixOne Intelligence implements some SHOW commands for syntax compatibility only, and these commands will not produce any output, such as SHOW STATUS/PRIVILEGES, etc.
  • Although some commands are syntactically the same as MySQL, due to different implementation methods, the results are significantly different from MySQL, such as: SHOW GRANTS, SHOW ERRORS, SHOW VARIABLES, SHOW PROCESSLIST.
  • MatrixOne Intelligence provides some unique SHOW commands to facilitate its own management, including SHOW BACKEND SERVERS, SHOW ACCOUNTS, SHOW ROLES, SHOW NODE LIST, etc.

About SET

  • The system variables of MatrixOne Intelligence are quite different from MySQL. Most of them are just for syntax compatibility. The actual parameters that can be set include: ROLE, SQL_MODE, TIME_ZONE.

DML statement

SELECT...FOR UPDATE currently only supports single table queries.

  • MatrixOne Intelligence does not support modifiers such as LOW_PRIORITY, DELAYED, HIGH_PRIORITY.
  • MatrixOne Intelligence does not support the use of LOW_PRIORITY and IGNORE modifiers.
  • MatrixOne Intelligence does not support modifiers such as LOW_PRIORITY, QUICK, IGNORE.
  • MatrixOne Intelligence does not support multi-layered association subqueries for IN.
  • MatrixOne Intelligence supports SET, but only supports the form of SET columns_name=nullif(expr1,expr2).
  • MatrixOne Intelligence supports the client to execute LOAD DATA LOCAL, but the --local-infile parameter must be added when connecting.
  • MatrixOne Intelligence supports importing JSONlines files, but requires some special syntax.
  • MatrixOne Intelligence supports importing files from object storage, but requires some special syntax.
  • The Explain and Explain Analyze print formats of MatrixOne Intelligence are both referenced to PostgreSQL, which is very different from MySQL.
  • JSON type output is not supported.

other

  • The REPLACE statement does not support rows composed of a set of values ​​inserted using the VALUES row_constructor_list parameter.

Advanced SQL Features

  • Triggers are not supported.
  • Stored procedures are not supported.
  • Event scheduler is not supported.
  • Custom functions are not supported.
  • Materialized views are not supported.

Data Type

  • BOOL: Unlike MySQL's boolean type that is actually int, the Boolean value of MatrixOne Intelligence is a new type, and its value can only be true or false.
  • DECIMAL: DECIMAL(P,D), the maximum accuracy of the significant number P and the number of digits after the decimal point of MatrixOne Intelligence are 38 bits, while MySQL is 65 and 30 respectively.
  • Float: Float(M,D) and Double(M,D) usage are abandoned after MySQL8.0.17, but MatrixOne Intelligence still retains this usage.
  • DATETIME: The maximum range of MySQL is '1000-01-01 00:00:00' to '9999-12-31 23:59:59', and the maximum range of MatrixOne Intelligence is '0001-01-01 00:00:00' to '9999-12-31 23:59:59'.
  • TIMESTAMP: The maximum range of MySQL values ​​is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.9999999' UTC, MatrixOne Intelligence maximum range of '0001-01-01 00:00:00' UTC to '9999-12-31 23:59:59' UTC.
  • MatrixOne supports UUID types.
  • MatrixOne Support vector type.
  • MatrixOne supports DATALINK type.
  • The Spatial type is not supported.
  • SET type is not supported.
  • The MEDIUMINT type is not supported.

Index and constraints

  • MatrixOne supports vector index.
  • Secondary index only implements syntax and has no acceleration effect.
  • Foreign keys do not support ON CASCADE DELETE cascading deletion.

Partition support

  • Supports six partition types: KEY, HASH, RANGE, RANGE COLUMNS, LIST, LIST COLUMNS.
  • Supports two partition cropping: KEY and HASH, and the other four are not yet implemented.
  • Subpartitions only implement syntax, no functions are implemented.
  • ADD/DROP/TRUNCATE PARTITION is not supported yet.

Functions and operators

Aggregation function

  • Supports Median median function unique to MatrixOne Intelligence.

CAST function

  • The type conversion rules are quite different from MySQL, please refer to CAST.

Window Function

  • Only RANK, DENSE_RANK, ROW_NUMBER is supported.

JSON Functions

  • Only support JQ(), JSON_UNQUOTE, JSON_QUOTE, JSON_EXTRACT, JSON_EXTRACT_FLOAT64(), JSON_EXTRACT_STRING(), JSON_ROW(), TRY_JQ().

System Management Functions

  • Supports CURRENT_ROLE_NAME(), CURRENT_ROLE(), CURRENT_USER_NAME(), CURRENT_USER, PURGE_LOG().

Storage Engine

  • MatrixOne Intelligence's TAE storage engine is completely independently developed and does not support MySQL's InnoDB, MyISAM and other engines.- There is only TAE storage engine in MatrixOne Intelligence, and there is no need to use ENGINE=XXX to replace the engine.

Security and Permissions

  • Only the ALTER USER method is supported to modify the password.
  • It is not supported to modify the maximum number of user connections.
  • Connection IP whitelist is not supported.
  • LOAD file authorization management is not supported.
  • SELECT INTO file authorization management can be partially supported through CREATE STAGE.

Transactions

  • MatrixOne Intelligence defaults to pessimistic transactions.
  • Unlike MySQL, the DDL statements in MatrixOne Intelligence are transactional and can roll back DDL operations in a transaction.
  • Table-level lock LOCK/UNLOCK TABLE is not supported.

Backup Recovery

  • Supports physical backups based on mobackup tools.
  • Support snapshot backup
  • The mysqldump backup tool is not supported, only the mo-dump tool is supported.
  • Binlog log backup is not supported.

System variables

  • MatrixOne's lower_case_table_names has 2 modes, and the default is 1.
  • MatrixOne's sql_mode only supports ONLY_FULL_GROUP_BY mode

programming language

  • Java, Python, C#, Golang connectors and ORM basic support, connectors and ORMs in other languages ​​may encounter compatibility issues.

Other Support Tools

  • Navicat, DBeaver, MySQL Workbench, HeidiSQL basic support, but due to the incomplete ALTER TABLE capabilities, the table design function support is incomplete.
  • The xtrabackup backup tool is not supported.