Help Center/ Distributed Database Middleware/ User Guide/ SQL Syntax/ Introduction
Updated on 2022-09-16 GMT+08:00
View PDF

Introduction

DDM is compatible with the MySQL license and syntax, but the use of SQL statements is limited due to differences between distributed databases and single-node databases.

Before selecting a DDM solution, evaluate the SQL syntax compatibility between your application and DDM.

MySQL EXPLAIN

If you add EXPLAIN before a SQL statement, you will see a specific execution plan when you execute the statement. You can analyze the time required based on the plan and modify the SQL statement for optimization.

Table 1 Description of the EXPLAIN column

Column Name

Description

table

Table that the row of data belongs to

type

Type of the connection. Connection types from the best to the worst are const, eq_reg, ref, range, index, and ALL.

possible_keys

Index that may be applied to the table

key

Index that is actually used. If the value is NULL, no index is used. In some cases, MySQL may choose to optimize indexes, for example, force MySQL to use an index by adding USE INDEX(indexname) to a SELECT statement or to ignore an index by adding IGNORE INDEX(indexname).

key_len

Length of the used index. The shorter the length is, the better the index is if accuracy is not affected.

ref

Column where the index is used. The value is generally a constant.

rows

Rows of the data returned by MySQL

Extra

Additional information about how MySQL parses queries

SQL Restrictions

  • Temporary tables are not supported.
  • Foreign keys, views, cursors, triggers, and stored procedures are not supported.
  • Customized data types and functions are not supported.
  • Process control statements such as IF and WHILE are not supported.
  • Compound statements such as BEGIN...END, LOOP...END LOOP, REPEAT...UNTIL...END REPEAT, and WHILE...DO...END WHILE are not supported.

DDL Syntax

  • Sharded and broadcast tables do not support foreign keys.
  • Modifying sharding keys is not supported.
  • ALTER DATABASE Syntax is not supported.
  • Creating sharded or broadcast tables from another table is not supported.
  • The CREATE TABLE statement does not support GENERATED COLUMN.
  • Modifying sharding keys or global sequence fields using the ALTER command is not supported.
  • Creating TEMPORARY sharded or broadcast tables is not supported.
  • The logical table name contains only letters, digits, and underscores (_).
  • CREATE TABLE tbl_name LIKE old_tbl_name is not supported.
  • The CREATE TABLE tbl_name SELECT statement is not supported.
  • Updating the sharding key by executing INSERT INTO ON DUPLICATE KEY UPDATE is not supported.
  • Cross-schema DDL is not supported, for example, CREATE TABLE db_name.tbl_name (... )
  • Reverse quotation marks are required to quote identifiers such as table names, column names, and index names that are MySQL key words or reserved words.

DML Syntax

  • PARTITION clauses are not supported.
  • Nesting a subquery in an UPDATE statement is not supported.
  • INSERT DELAYED Syntax is not supported.
  • STRAIGHT_JOIN and NATURAL JOIN are not supported.
  • Multiple-table UPDATE is supported if all tables joined across shards have primary keys.
  • Multiple-table DELETE is supported if all tables joined across shards have primary keys.
  • Using or manipulating variables in SQL statements is not supported, for example, SET @c=1, @d=@c+1; SELECT @c, @d.
  • Inserting keyword DEFAULT or updating a sharding key value to DEFAULT is not supported.
  • Repeatedly updating the same field in an UPDATE statement is not supported.
  • Updating a sharding key using UPDATE JOIN syntax is not supported.
  • UPDATE cannot be used to update self-joins.
  • Referencing other object columns in assignment statements or expressions may cause unexpected update results. Example:

    update tbl_1 a,tbl_2 b set a.name=concat(b.name,'aaaa'),b.name=concat(a.name,'bbbb') on a.id=b.id

  • If a text protocol is used, BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB data must be converted into hexadecimal data.
  • DDM processes invalid data based on sql_mode settings of associated MySQL instances.
  • UPDATE JOIN supports only joins with WHERE conditions.
  • The expression in a SQL statement has a maximum of 1000 factors.

Unsupported Functions

  • XML functions
  • GTID functions
  • Full-text search functions
  • Enterprise encryption functions
  • Function row_count()

Subqueries

Using subqueries in the HAVING clause and the JOIN ON condition is not supported.

Data Types

Spatial data types are not supported.

Parent topic: SQL Syntax