MySQL Language Structure

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

MySQL Language Structure

Language Structure

This page discusses the syntactical and structural rules for writing the following elements of MySQL statements.

Content:

  • Literal values (string, numbers etc.)

  • Schema Object Names

  • User-defined and system variables

  • Expression Syntax

  • Comments

  • Reserved words

Literal values (string, numbers etc.)

The terms literal refer to a fixed data value. MySQL evaluates seven types of literal values numeric, character string, date and time, hexadecimal, boolean, bit-field, and NULL Values

Numeric Literals

Numeric literal notation is used to specify fixed and floating-point numbers. Floating-point numbers use '.' as a decimal separator. Both types of numbers may be preceded by '+' or '-' to indicate a positive or negative numbers. You can use the integer notation in expressions, conditions, SQL functions, and SQL statements. The examples of integer is as follows :

Valid integers :

  • 0

  • 1254

  • -256

Valid floating-point numbers :

  • 132.45

  • 12.00

  • -21032.6309e+10

String Literals

A string is a sequence of bytes or characters, enclosed within a single quote ('w3resource') or double quote ("w3resource"). You can use string literal notation in expressions, conditions, SQL functions, and SQL statements. Here are some examples :

  • 'MySQL Tutorial'

  • "SQL Tutorial"

Note : If ANSI_QUOTES SQL mode is enabled, you cannot use double quotation marks to quote literal strings, because it is interpreted as an identifier.

A binary string is a string of bytes that has no character set or collation whereas a nonbinary string is a string of characters that has a character set and collation. For both types of strings, comparisons are based on the numeric values of the string unit.

Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode (Disable the use of the backslash character (“”) as an escape character within strings) is enabled. Each of these sequences begins with a backslash (""), known as the escape character. MySQL recognizes the escape sequences shown in the following table.

Special Character Escape Sequences

Escape Sequence

Character represented by Sequence

An ASCII NUL (0x00) character.

'

A single quote ("'") character.

"

A double quote (“"”) character.



A backspace character.

 

A newline (linefeed) character.

 

A carriage return character.

 

A tab character.



ASCII 26 (Control+Z). See note following the table.

A backslash (“”) character.

\%

A "%" character. See note following the table.

\_

A "_" character. See note following the table.

Here are some examples :

MySQL> SELECT 'w3r', '"w3r"', "'w3r'", '""w3r""', 'w3''resource', ''w3r', '"w3r';
+-----+-------+-------+---------+-------------+------+------+
| w3r | "w3r" | 'w3r' | ""w3r"" | w3'resource | 'w3r | "w3r |
+-----+-------+-------+---------+-------------+------+------+
| w3r | "w3r" | 'w3r' | ""w3r"" | w3'resource | 'w3r | "w3r |
+-----+-------+-------+---------+-------------+------+------+
1 row in set (0.00 sec)

MySQL> SELECT 'The Quick Brown Fox';
+---------------------+
| The
Quick
Brown
Fox |
+---------------------+
| The
Quick
Brown
Fox |
+---------------------+
1 row in set (0.00 sec)

Date Time Literals

You can specify a date and time values in several formates, such as numbers or as quoted strings, depending on the exact type of the value and other factors. MySQL interprets '2017-08-22', '20170822', and 20170822 as a date.

MySQL recognizes DATE values in these formats :

  • As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. You can use any punctuation character as the delimiter between date parts. For example, '2014-10-30', '2014/10/30', '2014^10^30, and '2014@10@30' are equivalent.

  • As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string maintains valid date format. For example, '20080623' and '080623' are interpreted as '2008-06-23', but '071432' is illegal (it has invalid month and day parts) and becomes '0000-00-00'.

  • As a number in either YYYYMMDD or YYMMDD format provided that the number makes sense as a date. For example, 19841105 and 841105 are interpreted as '1984-11-05'.

MySQL recognizes DATETIME and TIMESTAMP values in these formats :

  • As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. You can use any punctuation character as the delimiter between date parts. For example, '2014-12-31 11:30:45', '2014^12^31 11+30+45', '2014/12/31 11*30*45', and '2014@12@31 11^30^45' are equivalent. The date and time parts can be separated by T rather than space. For example, '2014-11-30 11:30:45' '2014-11-30T11:30:45' are equivalent.

  • As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string maintains valid date format. For example, '20080623091528'' is interpreted as '2008-06-23 09:15:28', but '071122129015' is illegal (it has an invalid minute part) and becomes '0000-00-00 00:00:00'.

  • As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format provided that the number maintains as a valid date. For example, 19860805132800 and 860805132800 are interpreted as '1986-08-05 13:28:00'.

MySQL recognizes TIME values in these formats :

  • As a string in 'D HH:MM:SS' format. You can also use one of the following "relaxed" syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH', or 'SS'. Here D represents days and can have a value from 0 to 34.

  • As a string with no delimiters in 'HHMMSS' format, provided that it makes sense at a time. For example, '101112' is understood as '10:11:12', but '109712' is illegal (it has an invalid minute part) and becomes '00:00:00'.

  • As a number in HHMMSS format provided that it makes sense at a time. For example, 101112 is understood as '10:11:12'. The following alternative formats are also understood: SS, MMSS, or HHMMSS.

Hexadecimal Literals

MySQL supports hexadecimal values, written using X'val', x'val', or 0xval format, where val contains hexadecimal digits (0..9, A..F). Lettercase of the digits does not matter. Here are some examples :

MySQL> SELECT X'773372736F75726365';
+-----------------------+
| X'773372736F75726365' |
+-----------------------+
| w3rsource             |
+-----------------------+
1 row in set (0.01 sec)

MySQL> SELECT 0xC8+0;
+--------+
| 0xC8+0 |
+--------+
|    200 |
+--------+
1 row in set (0.05 sec)

MySQL>SELECT 0x555341;
+----------+
| 0x555341 |
+----------+
| USA      |
+----------+
1 row in set (0.00 sec)

Boolean Literals

The constants TRUE and FALSE evaluate to 1 and 0, respectively. The constant names can be written both upper and lower case letters. See the following examples :

MySQL> SELECT TRUE, true;
+------+------+
| TRUE | TRUE |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

MySQL> SELECT FALSE, false;
+-------+-------+
| FALSE | FALSE |
+-------+-------+
|     0 |     0 |
+-------+-------+
1 row in set (0.00 sec)

Bit-Field Literals

Bit-field values can be written using b'value' or 0bvalue notation. value is a binary value written using zeros and ones.

NULL Values

The NULL value means “no data.” NULL can be written in any lettercase.

Schema Object Names

A schema is a collection of logical structures of data or schema objects. A schema is owned by a database user. In MySQL, some objects including database, table, column, alias, view, stored procedure, partition, tablespace, and other object names are known as identifiers. An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.
Identifiers are converted to Unicode internally. They may contain these characters :

  • Following characters are permitted in unquoted identifiers :

    • ASCII : [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

    • Extended : U+0080 .. U+FFFF

  • Following characters are permitted in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000 :

    • ASCII: U+0001 .. U+007F

    • Extended: U+0080 .. U+FFFF

  • ASCII NUL (U+0000) and supplementary characters (U+10000 and higher) are not permitted in quoted or unquoted identifiers.

  • Identifiers may begin with a digit but unless quoted may not consist solely of digits.

  • The database, table, and column names cannot end with space characters.

The following table shows the maximum length for each type of identifier

Identifier

Maximum Length
(characters)

Database

64

Table

64

Column

64

Index

64

Constraint

64

Stored Procedure or Function

64

Trigger

64

View

64

Event

64

Tablespace

64

Server

64

Log File Group

64

Alias

256 (see exception)

Compound Statement Label

16

Identifier Qualifiers

MySQL allows names that consist of a single identifier or multiple identifiers. The components of a multiple-part name must be separated by period (".") characters. The initial parts of a multiple-part name act as qualifiers that affect the context within which the final identifier is interpreted.

In MySQL, you can refer to a table column using any of the following forms.

Column Reference

Meaning

col_name

The column col_name from whichever table used in the statement contains a column of that name.

tbl_name.col_name

The column col_name from table tbl_name of the default database.

db_name.tbl_name.col_name

The column col_name from table tbl_name of the database db_name.

The qualifier character is a separate token and needs not be contiguous with the associated identifiers. For example, tbl_name.col_name and tbl_name . col_name are equivalent.

Identifier Case Sensitivity

In MySQL database, table, and trigger names are not case sensitive in Windows, but are case sensitive in most varieties of Unix. In Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive. Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases, but logfile groups are case sensitive. This differs from standard SQL. By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X.

Mapping of Identifiers to File Names

There is a correspondence between database and table identifiers and names in the file system. MySQL represents each database as a directory in the data directory, and each table by one or more files in the appropriate database directory. For the table format files (.FRM), the data is always stored in this structure and location.

For the data and index files, the exact representation on disk is storage engine specific. These files may be stored in the same location as the FRM files, or the information may be stored in a separate file. InnoDB data is stored in the InnoDB data files. If you are using tablespaces with InnoDB, then the specific tablespace files you create are used instead.

Any character is legal in database or table identifiers except ASCII NUL (0x00). MySQL encodes any characters that are problematic in the corresponding file system objects when it creates database directories or table files:

  • Basic Latin letters (a..zA..Z), digits (0..9) and underscore (_) are encoded as is. Consequently, their case sensitivity directly depends on file system features.

  • All other national letters from alphabets that have uppercase/lowercase mapping are encoded as shown in the following table. Values in the Code Range column are UCS-2 values.

Mapping of Identifiers to File Names

MySQL 5.6 supports built-in (native) functions, user-defined functions (UDFs), and stored functions.

Built-In Function Name Parsing:

The parser uses default rules for parsing names of built-in functions. These rules can be changed by enabling the IGNORE_SPACE SQL mode. When the parser encounters a word that is the name of a built-in function, it must determine whether the name signifies a function call or is instead a nonexpression reference to an identifier such as a table or column name

Function Name Resolution : The following rules describe how the server resolves references to function names for function creation and invocation :

  • Built-in functions and user-defined functions An error occurs if you try to create a UDF with the same name as a built-in function.

  • Built-in functions and stored functions. It is possible to create a stored function with the same name as a built-in function, but to invoke the stored function it is necessary to qualify it with a schema name.

  • User-defined functions and stored functions share the same namespace, so you cannot create a UDF and a stored function with the same name.

 

     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
   

 

 

MySQL: User-Defined Variables

In MySQL, you can store a value in a user-defined variable in one statement and later you can refer in another statement. This enables you to pass the values from one statement to another. User-defined variables are session-specific, therefore these variables are private to a particular user and another user can not see or use these. All variables for a given client session are automatically freed when that client exits.

Syntax : @var_name
var_name consists of alphanumeric characters, ".", "_", and "$". A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var', @"my-var", or @`my-var`).
Note : User variable names are not case sensitive in MySQL 5.0 and up.

One way to set a user-defined variable is by issuing a SET statement :

SET @var_name = expr [, @var_name = expr] ...

Example :

MySQL>  SET @x = 10; @y = 20; @z: = 30;
Query OK, 0 rows affected (0.07 sec)

MySQL> SELECT @x, @y, @z;
+------+------+------+
| @x   | @y   | @z   |
+------+------+------+
|   10 |   20 |   30 |
+------+------+------+
1 row in set (0.00 sec) 

For more details read variables in Stored Programs.

MySQL: Expression Syntax

The following rules define expression syntax in MySQL. The grammar shown here is based on that given in the sql/sql_yacc.yy file of MySQL source distributions.

expr :

  • expr OR expr

  • expr || expr

  • expr XOR expr

  • expr AND expr

  • expr && expr

  • NOT expr

  • ! expr

  • boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN}

  • boolean_primary

boolean_primary :

  • boolean_primary IS [NOT] NULL

  • boolean_primary <=> predicate

  • boolean_primary comparison_operator predicate

  • boolean_primary comparison_operator {ALL | ANY} (subquery)

  • predicate

comparison_operator :

= | >= | > | <= | < | <> | !=

predicate :

  • bit_expr [NOT] IN (subquery)

  • bit_expr [NOT] IN (expr [, expr] ...)

  • bit_expr [NOT] BETWEEN bit_expr AND predicate

  • bit_expr SOUNDS LIKE bit_expr

  • bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr]

  • bit_expr [NOT] REGEXP bit_expr

  • bit_expr

gbit_expr :

  • bit_expr | bit_expr

  • bit_expr & bit_expr

  • bit_expr << bit_expr

  • bit_expr >> bit_expr

  • bit_expr + bit_expr

  • bit_expr - bit_expr

  • bit_expr * bit_expr

  • bit_expr / bit_expr

  • bit_expr DIV bit_expr

  • bit_expr MOD bit_expr

  • bit_expr % bit_expr

  • bit_expr ^ bit_expr

  • bit_expr + interval_expr

  • bit_expr - interval_expr

  • simple_expr

simple_expr :

  • literal

  • identifier

  • function_call

  • simple_expr COLLATE collation_name

  • param_marker

  • variable

  • simple_expr || simple_expr

  • + simple_expr

  • - simple_expr

  • ~ simple_expr

  • ! simple_expr

  • BINARY simple_expr

  • (expr [, expr] ...)

  • ROW (expr, expr [, expr] ...)

  • (subquery)

  • EXISTS (subquery)

  • {identifier expr}

  • match_expr

  • case_expr

  • interval_expr

MySQL Comment Syntax

MySQL supports three comment styles :

  • "#" character to the end of the line.

  • "--" sequence to the end of the line.

  • /* Text here */ sequence, as in the C programming language

See the following examples :

MySQL> SELECT 1+1; # Single line comment

MySQL> SELECT 1+1; -- Single line comment

MySQL>  SELECT 1 /* In-line comment*/ + 1;

MySQL>  SELECT 1+
     /*
     this is a
     multiple-line comment
     */
     1;

MySQL Reserved Words

Certain words such as SELECT, DELETE, or BIGINT are reserved and require special treatment for use as identifiers such as table and column names. The following table lists MySQL reserved wordS.

Reserved Words in MySQL 5.6

ACCESSIBLE

ADD

ALL

ALTER

ANALYZE

AND

AS

ASC

ASENSITIVE

BEFORE

BETWEEN

BIGINT

BINARY

BLOB

BOTH

BY

CALL

CASCADE

CASE

CHANGE

CHAR

CHARACTER

CHECK

COLLATE

COLUMN

CONDITION

CONSTRAINT

CONTINUE

CONVERT

CREATE

CROSS

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURRENT_USER

CURSOR

DATABASE

DATABASES

DAY_HOUR

DAY_MICROSECOND

DAY_MINUTE

DAY_SECOND

DEC

DECIMAL

DECLARE

DEFAULT

DELAYED

DELETE

DESC

DESCRIBE

DETERMINISTIC

DISTINCT

DISTINCTROW

DIV

DOUBLE

DROP

DUAL

EACH

ELSE

ELSEIF

ENCLOSED

ESCAPED

EXISTS

EXIT

EXPLAIN

FALSE

FETCH

FLOAT

FLOAT4

FLOAT8

FOR

FORCE

FOREIGN

FROM

FULLTEXT

GENERAL

GET

GRANT

GROUP

HAVING

HIGH_PRIORITY

HOUR_MICROSECOND

HOUR_MINUTE

HOUR_SECOND

IF

IGNORE

IGNORE_SERVER_IDS

IN

INDEX

INFILE

INNER

INOUT

INSENSITIVE

INSERT

INT

INT1

INT2

INT3

INT4

INT8

INTEGER

INTERVAL

INTO

IO_AFTER_GTIDS

IO_BEFORE_GTIDS

IS

ITERATE

JOIN

KEY

KEYS

KILL

LEADING

LEAVE

LEFT

LIKE

LIMIT

LINEAR

LINES

LOAD

LOCALTIME

LOCALTIMESTAMP

LOCK

LONG

LONGBLOB

LONGTEXT

LOOP

LOW_PRIORITY

MASTER_BIND

MASTER_HEARTBEAT_PERIOD

MASTER_SSL_VERIFY_SERVER_CERT

MATCH

MAXVALUE

MEDIUMBLOB

MEDIUMINT

MEDIUMTEXT

MIDDLEINT

MINUTE_MICROSECOND

MINUTE_SECOND

MOD

MODIFIES

NATURAL

NOT

NO_WRITE_TO_BINLOG

NULL

NUMERIC

ON

ONE_SHOT

OPTIMIZE

OPTION

OPTIONALLY

OR

ORDER

OUT

OUTER

OUTFILE

PARTITION

PRECISION

PRIMARY

PROCEDURE

PURGE

RANGE

READ

READS

READ_WRITE

REAL

REFERENCES

REGEXP

RELEASE

RENAME

REPEAT

REPLACE

REQUIRE

RESIGNAL

RESTRICT

RETURN

REVOKE

RIGHT

RLIKE

SCHEMA

SCHEMAS

SECOND_MICROSECOND

SELECT

SENSITIVE

SEPARATOR

SET

SHOW

SIGNAL

SLOW

SMALLINT

SPATIAL

SPECIFIC

SQL

SQLEXCEPTION

SQLSTATE

SQLWARNING

SQL_AFTER_GTIDS

SQL_BEFORE_GTIDS

SQL_BIG_RESULT

SQL_CALC_FOUND_ROWS

SQL_SMALL_RESULT

SSL

STARTING

STRAIGHT_JOIN

TABLE

TERMINATED

THEN

TINYBLOB

TINYINT

TINYTEXT

TO

TRAILING

TRIGGER

TRUE

UNDO

UNION

UNIQUE

UNLOCK

UNSIGNED

UPDATE

USAGE

USE

USING

UTC_DATE

UTC_TIME

UTC_TIMESTAMP

VALUES

VARBINARY

VARCHAR

VARCHARACTER

VARYING

WHEN

WHERE

WHILE

WITH

WRITE

XOR

YEAR_MONTH

ZEROFILL