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 in descending order of execution speed: 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.
A logical table name contains only letters, digits, and underscores (_).
CREATE TABLE ... LIKE statement is not supported.
CREATE TABLE ... 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.

Variables cannot be referenced or operated in SQL statements.
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.

Comments

Single-line comment
- A single-line comment can start with a pound sign (#). Any text between # and the end of the line is considered as comment content.
  Example:
```
SELECT * FROM customers; # comment content
```
- You can also start with two consecutive hyphens (--). There should be at least one space following the second hyphen. Otherwise, the comment may not be correctly parsed.
  Example:
```
SELECT * FROM Product; -- comment content
```
Multi-line comment
/* and */ are start and end delimiters for a comment that contains multi-line texts. It can span over multiple lines.

Example:
```
/* 
Comment line
*/
SELECT DISTINCT product_id, purchase_price FROM Product; 
```