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.
Concatenation
MySQL server doesn’t support standard SQL operator ‘||’ to concatenate string. For this purpose, the ‘CONCAT’ function can be used.
Published on 08-Mar-2021 16:01:59
