- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
MySQL Differences from Standard SQL
Let us understand the differences between MySQL and Standard SQL. MySQL performs many operations differently in certain cases −
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.
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.
- Related Articles
- MySQL Extensions to Standard SQL
- Differences between MySQL standard and MySQL Enterprise Servers
- Run SQL file in MySQL database from terminal?
- Creating a Standard user in SAP HANA system using SQL
- Executing SQL Statements from a Text File on MySQL Client
- Remove 20% from stored price in MySQL field, using SQL query?
- Assign an SQL result to variable from prepared statement in MySQL?
- Show MySQL host via SQL Command?
- Difference between MySQL and SQL Server
- How to run SQL script in MySQL?
- MySQL LIMIT clause equivalent for SQL SERVER?
- What is the difference between SQL and MySQL?
- Equivalent of SQL Server IDENTITY Column in MySQL?
- Haskell program to read numbers from standard input
- Implement Dynamic SQL query inside a MySQL stored procedure?