SQL FAQ
Which database is MatrixOne compatible?
MatrixOne maintains high compatibility with MySQL 8.0 in use, including SQL syntax, transmission protocol, operators and functions, etc. The list of differences in compatibility with MySQL 8.0 can be found in detail with MySQL Compatibility List.
What SQL statements does MatrixOne support?
The SQL statements currently supported by MatrixOne can refer to this detailed list.
What data types does MatrixOne support?
MatrixOne currently supports commonly used integer, floating point number, string, time and date, boolean, enum, binary, and JSON types. Please refer to Detailed List.
What type of character set does MatrixOne support?
MatrixOne supports UTF-8 character sets by default, and currently only supports UTF-8.
What constraints and indexes does MatrixOne support?
MatrixOne currently supports primary keys, Unique Keys, Not Nulls, Foreign Keys, Auto Increment and Secondary Index. Secondary indexes currently only implement syntax support and have no acceleration effect. In addition, MatrixOne also provides a sort key (Cluster by) for tables without primary keys, which can help us sort columns that need to be queried in advance and speed up queries.
What query types does MatrixOne support?
MatrixOne supports most commonly used SQL queries:
Basic query: Supports common basic query capabilities such as grouping, deduplication, filtering, sorting, qualifying, and regular expressions.
Advanced query: Supports advanced query capabilities such as views, subqueries, joins, combinations, common table expressions (CTEs), window functions, Prepare preprocessing, etc.
Aggregation function: Supports common AVG, COUNT, MIN, MAX, SUM and other aggregation functions.
System functions and operators: Supports common strings, dates, mathematical functions and common operators.
What are the retained keywords for MatrixOne?
For MatrixOne's reserved keyword list, see This detailed list.
When using reserved keywords as identifiers, backticks must be wrapped, otherwise an error will occur. When using non-reserved keywords as identifiers, they can be used directly without the need to wrap them with backticks.
Are functions and keywords in MatrixOne case sensitive?
Case insensitive.
In MatrixOne, there is only one case that requires case sensitivity: if the table and attribute you create have ``, the names in `` need to be case-conscious. Query this table name or attribute name, then the table name and attribute name must also be included in ``.
How to import data into MatrixOne Intelligence?
MatrixOne supports the same INSERT data insert statement as MySQL. It can write real-time data through INSERT, and it also supports offline batch import statements of LOAD DATA as MySQL.
In MatrixOne Intelligence, you can also import csv files or jsonline files stored in object storage offline through the Import Data function provided on the application interface. You can also quickly obtain data that has been stored on other instances through data publication and subscription.
How to export data from MatrixOne Intelligence to a file?
In MatrixOne Intelligence, you can export data to SQL or csv files using the binary tool mo-dump.
*Does MatrixOne support transactions? What are the supported transaction isolation levels?
MatrixOne supports ACID (atomic, consistency, isolation, persistence) transaction capabilities, supports pessimistic and optimistic transactions, and uses pessimistic transactions by default. When using pessimistic transactions, the Read Committed isolation level will be used, and when switching to optimistic transactions, the Snapshot Isolation isolation level will be used.
What is sql_mode in MatrixOne?
The default sql_mode of MatrixOne is only_full_group_by in MySQL. Therefore, all fields of select in the default query syntax, except fields in the aggregate function, must appear in group by. However, MatrixOne also supports modifying sql_mode to be compatible with the incompletely standardized group by syntax.
How to view my Query execution plan?
To see how MatrixOne performs a given query, use the EXPLAIN statement, which prints out the query plan.
EXPLAIN SELECT col1 FROM tbl1;
You can also view the graphical execution plan in the Query Analysis module provided by the MatrixOne Intelligence database management platform.