SoFunction
Updated on 2025-04-09

How MySQL extends standard SQL

MySQL extension to standard SQL

MySQL server supports some extensions that are unlikely to be found in other SQL database management systems. Note that if you use these extensions, your code may not be portable on other SQL servers.

In some cases, you can write code that contains the MySQL extension but is still portable, by using the following form of comments:

/*! MySQL-specific code */

In this case, the MySQL server parses and executes the code in the comments, just like any other SQL statement, but other SQL servers should ignore these extensions.

For example, a MySQL server can recognize the STRAIGHT_JOIN keyword in the following statement, but other servers do not:

SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...

If the version number is added after the '!' character, the syntax in the comments will only be executed if the MySQL version is greater than or equal to the specified version number.

The KEY_BLOCK_SIZE clause in the following comments is executed only on MySQL 5.1.10 or later servers:

CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;

The following description lists MySQL's extended features by category:

How data is organized on disk

The MySQL server maps each database to a directory under the MySQL data directory and maps the tables in the database to the file names in the database directory. Therefore, on operating systems with case-sensitive file names (such as most Unix systems), database and table names in MySQL servers are case-sensitive.

Common Language Syntax

  • By default, strings can be enclosed in double quotes " or single quotes ' . If ANSI_QUOTES SQL mode is enabled, strings can only be enclosed in single quotes ' , and the server interprets double quotes enclosed strings as identifiers.
  • \ is an escape character in a string.
  • In SQL statements, you can use the db_name.tbl_name syntax to access tables in different databases. Some SQL servers offer the same functionality, but call it userspace. The MySQL server does not support tablespaces similar to those used in the following statements: CREATE TABLE ralph.my_table ... IN my_tablespace.

SQL statement syntax

  • ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE and REPAIR TABLE statements.
  • CREATE DATABASE, DROP DATABASE, and ALTER DATABASE statements.
  • DO statement.
  • Use the EXPLAIN SELECT statement to get the description of the query optimizer processing table.
  • FLUSH and RESET statements.
  • SET statement.
  • SHOW statement. The information generated by many MySQL-specific SHOW statements can be obtained in a more standard way by querying INFORMATION_SCHEMA using SELECT.
  • How to use LOAD DATA. In many cases, this syntax is compatible with Oracle's LOAD DATA.
  • RENAME TABLE usage.
  • Usage of using REPLACE instead of DELETE plus INSERT.
  • Use of CHANGE col_name, DROP col_name, DROP INDEX, IGNORE, or RENAME in the ALTER TABLE statement. Usage of using multiple ADD, ALTER, DROP, or CHANGE clauses in an ALTER TABLE statement.
  • Use index names in CREATE TABLE statements, create column prefix indexes, and use INDEX or KEY.
  • Usage of using TEMPORARY or IF NOT EXISTS in CREATE TABLE statement.
  • The usage of using IF EXISTS is in the DROP TABLE and DROP DATABASE statements.
  • The ability to delete multiple tables using a single DROP TABLE statement.
  • Usage of ORDER BY and LIMIT clauses in UPDATE and DELETE statements.
  • INSERT INTO tbl_name SET col_name = ...usage of syntax.
  • Usage of the DELAYED clause of the INSERT and REPLACE statements.
  • Usage of using INTO OUTFILE or INTO DUMPFILE in SELECT statements.
  • Usage of options such as STRAIGHT_JOIN or SQL_SMALL_RESULT in SELECT statements.
  • There is no need to name all selected columns in the GROUP BY clause. This can provide better performance for some very specific but fairly common queries.
  • ASC and DESC can be used in the GROUP BY clause to specify the sort order, not just in the ORDER BY clause.
  • The ability to set variables in a statement using the := assignment operator.

Data Type

  • Medium integer type (MEDIUMINT), collection type (SET), enumeration type (ENUM), and various binary large object (BLOB) and text (TEXT) types.
  • AUTO_INCREMENT, BINARY, NULL, UNSIGNED, and ZEROFILL (Fill in Zero) are attributes of data types.

Functions and operators

  • To facilitate users migrating from other SQL environments, MySQL Server supports alias for many functions. For example, all string functions support standard SQL syntax and ODBC syntax.
  • The || and && operators in MySQL Server are understood as logical OR and AND, as is the case in the C programming language. In MySQL Server, || and OR are synonyms, as are && and AND. Due to this elegant syntax, MySQL Server does not support the standard SQL || operator for string concatenation; instead, it uses the CONCAT() function for string concatenation. Since the CONCAT() function accepts any number of parameters, it is easy to convert code using the || operator to the syntax of MySQL Server.
  • The case where COUNT(DISTINCT value_list) is used is that value_list contains multiple elements.
  • String comparisons are case-insensitive by default, and the sorting order is determined by the proofreading rules of the current character set (default is utf8mb4). To perform case-sensitive comparisons, you can use the BINARY attribute when declaring a column, or use the BINARY conversion operator, so that the comparison will be made using the underlying character code values ​​instead of the lexical order.
  • The % operator is synonymous with MOD(). That is, N % M is equivalent to MOD(N, M). The % operator is supported for use by C programmers and is consistent with PostgreSQL compatibility.
  • In a SELECT statement, you can use the =, <>, <=, <, >=, >, <<, >>, <<, >>, <=>, AND, OR, or LIKE operators in the expression of the output column list (to the left of the FROM).
  • For example:
mysql> SELECT col1=1 AND col2=2 FROM my_table;
  • The LAST_INSERT_ID() function returns the recently inserted AUTO_INCREMENT value.
  • The LIKE operator is allowed to be used on numerical values.
  • REGEXP and NOT REGEXP are extended regular expression operators. They are used to use regular expressions in comparisons to match or exclude patterns.
  • The CONCAT() or CHAR() function can accept one or more parameters. In MySQL Server, these functions can accept variable number of parameters.
  • BIT_COUNT(), CASE, ELT(), FROM_DAYS(), FORMAT(), IF(), MD5(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(), and WEEKDAY() are some of the functions available in MySQL.
  • Use the TRIM() function to trim substrings. Standard SQL supports only deletion of single characters.
  • The GROUP BY functions include STD(), BIT_OR(), BIT_AND(), BIT_XOR(), and GROUP_CONCAT().

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.