DDL differences

Updated at:
Copy as MD

AnalyticDB for MySQL supports a subset of MySQL data definition language (DDL) statements. Use this page to check support status and restrictions before migrating DDL scripts or writing new ones.

AnalyticDB for MySQL fully supports 9 DDL statements, partially supports 4 with restrictions, and does not support 25 that rely on MySQL-specific server-side features such as stored procedures, triggers, events, and tablespaces.

Fully supported statements

These statements work the same way as in MySQL and have no restrictions.

Statement

Description

ALTER TABLE Partition

Modifies partitions of a table.

CREATE TABLE

Creates a table.

CREATE TABLE ... LIKE

Creates a table with the same structure as an existing table.

CREATE TABLE ... SELECT

Creates a table from a SELECT query (CTAS).

CREATE VIEW

Creates a view.

DROP TABLE

Deletes one or more tables.

CREATE VIEW

Deletes one or more views.

RENAME TABLE

Renames one or more tables.

TRUNCATE TABLE

Removes all rows from a table.

Partially supported statements

These statements are supported with restrictions. Review the restrictions column before use.

Statement

Description

Restrictions

ALTER TABLE

Modifies the definition of a table.

  • Table and column options cannot be modified.

  • Only one column can be modified per statement.

  • Adding constraints is not supported.

CREATE DATABASE

Creates a database.

CHARACTER SET and COLLATE values are ignored.

CREATE INDEX

Creates an index.

XUANWU_V2 tables do not create full-column indexes by default. XUANWU tables create full-column indexes by default (index_all='Y'). Multicolumn, unique, and spatial indexes are not supported, and [index_option] [algorithm_option | lock_option] are ignored.

DROP DATABASE

Deletes a database.

All tables in the database must be deleted before the database can be dropped.

Unsupported statements

The following MySQL DDL statements are not supported. They rely on MySQL-specific server-side features that do not apply to AnalyticDB for MySQL.

Statement

MySQL definition

Stored procedures and functions

ALTER FUNCTION

Modifies the characteristics of a stored function.

ALTER PROCEDURE

Modifies the characteristics of a stored procedure.

CREATE FUNCTION

Creates a stored function.

CREATE PROCEDURE

Creates a stored procedure.

DROP FUNCTION

Deletes a stored function.

DROP PROCEDURE

Deletes a stored procedure.

Triggers and events

ALTER EVENT

Modifies a scheduled event.

CREATE EVENT

Creates a scheduled event.

CREATE TRIGGER

Creates a trigger.

DROP EVENT

Deletes a scheduled event.

DROP TRIGGER

Deletes a trigger.

Tablespaces and log file groups

ALTER LOGFILE GROUP

Modifies a log file group.

ALTER TABLESPACE

Modifies a tablespace.

CREATE LOGFILE GROUP

Creates a log file group.

CREATE TABLESPACE

Creates a tablespace.

DROP LOGFILE GROUP

Deletes a log file group.

DROP TABLESPACE

Deletes a tablespace.

Server

ALTER SERVER

Modifies a remote server definition.

CREATE SERVER

Creates a remote server definition.

DROP SERVER

Deletes a remote server definition.

Other

ALTER DATABASE

Modifies the characteristics of a database.

ALTER VIEW

Modifies a view definition. Use CREATE OR REPLACE VIEW as an alternative.

CREATE TEMPORARY TABLE

Creates a temporary table.

DROP INDEX

Deletes an index. Use ALTER TABLE ... DROP INDEX as an alternative.

FOREIGN KEY constraints

Enforces referential integrity between tables.