MySQL Differences from Standard SQL

Let us understand the differences between MySQL and Standard SQL. MySQL performs many operations differently in certain cases −

Privileges

There are many differences between MySQL and standard SQL with respect to privileges given to the user. In MySQL, privileges for a table are automatically not revoked when a table is deleted. A REVOKE statement needs to be explicitly issued to revoke privileges for a table.

Foreign Key Constraints

The MySQL implementation of foreign key constraints is different from the SQL standard. If there are many rows in the parent table with the same referenced key value, InnoDB engine does a foreign key check like the other parent rows with the same key value does not exist.

For example, if a RESTRICT type constraint is defined, and there is a child row with many parent rows, InnoDB wont’ permit the deletion of the parent rows.

In an SQL statement that uses insert, delete, or update on multiple rows, foreign key constraints (such as unique constraints) are checked row-after-row. When foreign key checks are performed, InnoDB sets the shared row-level locks on child or parent records which need to be checked for.

MySQL mandates that the referenced columns have to be indexed for performance reasons. But MySQL doesn’t enforce a need that the referenced columns need to be UNIQUE or to be declared NOT NULL.

Comments

Standard SQL uses the C syntax /* this is a comment */ to show comments. MySQL Server supports this syntax too. Standard SQL uses ’’--’’ as a start-comment sequence. MySQL Server uses ‘#‘ as the start comment character.