Schema

Database objects are identified by name, check is case-insensitive, so for example table myTable vs MyTable is considered as the same table.

All objects except Events and Sequences are built by reading object properties from different system metadata tables and views. Application supports the most commonly used object attributes. However in many cases it is only the subset of MySQL/MariaDB specifications, and the application does not support some MySQL/MariaDB language features. Such features are not recognized and if an object has changes in any non-supported attribute then such change is ignored. In case of object merge these attributes can be lost in the target database. Below you will find the information about supported/unsupported features.

The Pro version also allows you to compare results of SHOW CREATE TABLE results, this can be useful if you want to check how a database server generates table scripts. There is an appropriate button in the Table structure diff tab. For any other object (FUNCTION, etc.) you can compare SHOW CREATE .. results using the Query result diff tab.

All objects are compared as their text presentation, that's the text you observe if you open an object in a Text diff tab. In the Pro you can set up text diff options to ignore some general text-related changes like case-insensitive or ignore-whitespace. Application also allows additional custom text normalization to skip some changes that may look false-positive. For example, you can specify to ignore optional function parenthesis, to make the function call CURRENT_DATE to be considered as unchanged compared to the CURRENT_DATE() function call (with vs without parenthesis).

Table definitions

Here is the high-level presentation of supported/unsupported table definition attributes, based on MySQL and MariaDB CREATE TABLE specifications. Unsupported ones are shown as ~~crossed out~~:

CREATE TABLE tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

create_definition: {
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | check_constraint_definition
  | period_definition
}

column_definition: {
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
      [VISIBLE | INVISIBLE]
      [ON UPDATE [NOW | CURRENT_TIMESTAMP] [(precision)]]
      [AUTO_INCREMENT] [ZEROFILL]
      [{WITH|WITHOUT} SYSTEM VERSIONING]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
      [ENGINE_ATTRIBUTE [=] 'string']
      [SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
      [STORAGE {DISK | MEMORY}]
      [REF_SYSTEM_ID = value]
      [reference_definition]
      [check_constraint_definition]
  | data_type [COLLATE collation_name] [GENERATED ALWAYS] AS (expr) ..
}

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

check_constraint_definition:
    [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
    
reference_definition:
    REFERENCES tbl_name (key_part,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

Note that this definition excludes [UNIQUE [KEY]] [[PRIMARY] KEY] of column_definition because scripts for constraints are generated as parts of create_definition.

Views

For Views application recognizes only select_statement and ignores ALGORITHM, DEFINER, SQL SECURITY and CHECK OPTION options. Database engine does not store view SELECT statement formatting, application uses its own SQL indentation logic to display view text.

Functions, Stored procedures and Triggers

Application ignores DEFINER statements for these object types. For Functions and Stored procedures it also ignores SQL SECURITY characteristic and { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } hint.

Events

Event definitions are retrieved using SHOW CREATE EVENT statements, followed by some adjustments and formatting. DEFINER clause is excluded, to be unified with all other object types which do not support it.

Sequences

Sequence definitions are retrieved using SHOW CREATE SEQUENCE statements with some additional formatting.

Last updated: 2023-10-03