MySQL Extensions to Standard SQL

MySQL server supports extensions which may not be found in other SQL databases. This means, if these extensions of MySQL are used, the code can’t be ported to other SQL servers. But sometimes, it can be ported.

Let us understand the MySQL extensions to standard SQL −

Enclosing Strings

The strings can be enclosed in “ (double quotes) or ‘ (single quote) by default. If the ‘ANSI_QUOTES’ SQL mode is on, the strings have to be enclosed using ‘, and if “ (double quotes) is used, the server interprets this as identifiers.

Escape Character

\ is the escape character for strings.

Accessing Table

MySQL doesn’t support tablespaces, i.e accessing the table using the name of the database and the table name like ‘database_name.table_name’.

Querying

All the selected columns don’t need to be given a name when they are selected using the ‘group by’ clause. This ensures that better performance is obtained in certain queries.

Clause

The ‘ORDER BY’ clause can’t be specified with ‘GROUP BY’ clause, but the ‘ASC’ and ‘DESC’ can be specified with the ‘GROUP BY’ clause.

Operators

The ‘||’ and ‘&&’ operators can be used to indicate logical ‘OR’ and ‘AND’ operations respectively while working with MySQL servers. That is because, the ‘||’ and ‘OR’ and ‘&&’ and ‘AND’ are synonyms.