MySQL Language Reference
************************
effectively. This chapter also serves as a reference to all
functionality included in MySQL. In order to use this chapter
effectively, you may find it useful to refer to the various indexes.
Language Structure
==================
Literals: How to Write Strings and Numbers
------------------------------------------
Strings
.......
A string is a sequence of characters, surrounded by either single quote
(`'') or double quote (`"') characters (only the single quote if you
run in ANSI mode). Examples:
'a string'
"another string"
`\0'
An ASCII 0 (`NUL') character.
`\''
A single quote (`'') character.
`\"'
A double quote (`"') character.
`\b'
A backspace character.
`\n'
A newline character.
`\r'
A carriage return character.
`\t'
A tab character.
you try to use
`mysql database < filename'.)
`\\'
A backslash (`\') character.
`\_'
A `_' character. This is used to search for literal instances of
`_' in contexts where `_' would otherwise be interpreted as a
wildcard character. *Note String comparison functions::.
Note that if you use `\%' or `\_' in some string contexts, these will
return the strings `\%' and `\_' and not `%' and `_'.
There are several ways to include quotes within a string:
* A `'' inside a string quoted with `'' may be written as `'''.
* A `"' inside a string quoted with `"' may be written as `""'.
* You can precede the quote character with an escape character (`\').
* A `'' inside a string quoted with `"' needs no special treatment
and need not be doubled or escaped. In the same way, `"' inside a
string quoted with `'' needs no special treatment.
The `SELECT' statements shown here demonstrate how quoting and escaping
work:
mysql>
mysql>
mysql> SELECT "This\nIs\nFour\nlines";
+--------------------+
| This
Is
Four
lines |
+--------------------+
If you want to insert binary data into a string column (such as a
`BLOB'), the following characters must be represented by escape
sequences:
`NUL'
ASCII 0. You should represent this by `\0' (a backslash and an
ASCII `0' character).
`\'
ASCII 92, backslash. Represent this by `\\'.
`''
ASCII 39, single quote. Represent this by `\''.
`"'
ASCII 34, double quote. Represent this by `\"'.
If you write C code, you can use the C API function
`mysql_real_escape_string()' to escape characters for the `INSERT'
statement. *Note C API function overview::. In Perl, you can use the
`quote' method of the `DBI' package to convert special characters to
the proper escape sequences. *Note Perl `DBI' Class: Perl DBI Class.
You should use an escape function on any string that might contain any
of the special characters listed above!
Alternatively, many MySQL APIs provide some sort of placeholder
capability that allows you to insert special markers into a query
string, and then bind data values to them when you issue the query. In
this case, the API takes case of escaping special characters in the
values for you automatically.
Numbers
.......
Integers are represented as a sequence of digits. Floats use `.' as a
decimal separator. Either type of number may be preceded by `-' to
indicate a negative value.
Examples of valid integers:
1221
0
-32
Examples of valid floating-point numbers:
294.42
-32032.6809e+10
148.00
An integer may be used in a floating-point context; it is interpreted
as the equivalent floating-point number.
From version 4.1.0, the constant `TRUE' evaluates to `1' and the
constant `FALSE' evaluates to `0'.
Hexadecimal Values
..................
mysql> SELECT x'4D7953514C';
-> MySQL
mysql> SELECT 0xa+0;
-> 10
mysql> SELECT 0x5061756c;
-> Paul
In MySQL 4.1 (and in MySQL 4.0 when using the `--new' option) the
default type of of a hexadecimal value is a string. If you want to be
sure that the string is threated as a number, you can use `CAST( ... AS
UNSIGNED)' on the hexadecimal value.
The `x'hexstring'' syntax (new in 4.0) is based on standard SQL and the
`0x' syntax is based on ODBC. Hexadecimal strings are often used by
ODBC to supply values for `BLOB' columns. You can convert a string or
a number to string in hexadecimal format with the `HEX()' function.
`NULL' Values
.............
The `NULL' value means "no data" and is different from values such as
`0' for numeric types or the empty string for string types. *Note
Problems with `NULL': Problems with NULL.
`NULL' may be represented by `\N' when using the text file import or
export formats (`LOAD DATA INFILE', `SELECT ... INTO OUTFILE'). *Note
`LOAD DATA': LOAD DATA.
Database, Table, Index, Column, and Alias Names
-----------------------------------------------
Database, table, index, column, and alias names all follow the same
rules in MySQL.
in ANSI
mode. *Note ANSI mode::.
64 Any character that is allowed in a file name,
except `/' or `.'.
Column 64 All characters.
Alias 255 All characters.
Note that in addition to the above, you can't have ASCII(0) or
ASCII(255) or the quoting character in an identifier.
Note that if the identifier is a restricted word or contains special
characters you must always quote it with a ``' (backtick) when you use
it:
mysql> SELECT * FROM `select` WHERE `select`.id > 100;
*Note Reserved words::.
In MySQL versions prior to 3.23.6, the name rules are as follows:
option to `mysqld'. *Note Character
sets::.
_only_
of digits.
* You cannot use the `.' character in names because it is used to
extend the format by which you can refer to columns (see
immediately below).
It is recommended that you do not use names like `1e', because an
expression like `1e+1' is ambiguous. It may be interpreted as the
expression `1e + 1' or as the number `1e+1'.
In MySQL you can refer to a column using any of the following forms:
`col_name' from table `tbl_name' of the
current database.
`db_name.tbl_name.col_name'Column `col_name' from table `tbl_name' of the
database `db_name'. This form is available in
MySQL Version 3.22 or later.
``column_name`' A column that is a keyword or contains special
characters.
You need not specify a `tbl_name' or `db_name.tbl_name' prefix for a
column reference in a statement unless the reference would be ambiguous.
For example, suppose tables `t1' and `t2' each contain a column `c',
and you retrieve `c' in a `SELECT' statement that uses both `t1' and
`t2'. In this case, `c' is ambiguous because it is not unique among
the tables used in the statement, so you must indicate which table you
mean by writing `t1.c' or `t2.c'. Similarly, if you are retrieving
from a table `t' in database `db1' and from a table `t' in database
`db2', you must refer to columns in those tables as `db1.t.col_name'
and `db2.t.col_name'.
The syntax `.tbl_name' means the table `tbl_name' in the current
database. This syntax is accepted for ODBC compatibility, because some
ODBC programs prefix table names with a `.' character.
Case Sensitivity in Names
-------------------------
In MySQL, databases and tables correspond to directories and files
within those directories. Consequently, the case-sensitivity of the
underlying operating system determines the case-sensitivity of database
and table names. This means database and table names are
case-insensitive in Windows, and case-sensitive in most varieties of
Unix. One prominent exception here is Mac OS X, when the default HFS+
file system is being used. However Mac OS X also supports UFS volumes,
those are case sensitive on Mac OS X just like they are on any Unix.
*Note Extensions to ANSI::.
*Note*: although database and table names are case-insensitive for
Windows, you should not refer to a given database or table using
different cases within the same query. The following query would not
work because it refers to a table both as `my_table' and as `MY_TABLE':
mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
Column names and column aliases are case-insensitive in all cases.
Aliases on tables are case-sensitive. The following query would not
work because it refers to the alias both as `a' and as `A':
mysql> SELECT col_name FROM tbl_name AS a
-> WHERE a.col_name = 1 OR A.col_name = 2;
If you have trouble remembering the lettercase for database and table
names, adopt a consistent convention, such as always creating databases
and tables using lowercase names.
One way to avoid this problem is to start `mysqld' with `-O
lower_case_table_names=1'. By default this option is 1 on Windows and 0
on Unix.
If `lower_case_table_names' is 1 MySQL will convert all table names to
lowercase on storage and lookup. (From version 4.0.2, this option also
applies to database names. From 4.1.1 this also applies for table
alias).
Note that if you change this option, you need to first convert your old
table names to lower case before starting `mysqld'.
If you move `MyISAM' files from a Windows to a Unix disk, you may in
some cases need to use the `mysql_fix_extensions' tool to fix-up the
case of the file extensions in each specified database directory
(lowercase `.frm', uppercase `.MYI' and `.MYD').
`mysql_fix_extensions' can be found in the `scripts' subdirectory.
User Variables
--------------
MySQL supports connection-specific user variables with the
`@variablename' syntax. A variable name may consist of alphanumeric
characters from the current character set and also `_', `$', and `.' .
The default character set is ISO-8859-1 Latin1; this may be changed
with the `--default-character-set' option to `mysqld'. *Note Character
sets::. User variable names are case insensitive in versions >= 5.0,
case sensitive in versions < 5.0.
You can set a variable with the `SET' syntax:
SET @variable= { integer expression | real expression | string expression }
[,@variable= ...].
You can also assign a value to a variable in statements other than
`SET'. However, in this case the assignment operator is `:=' rather
than `=', because `=' is reserved for comparisons in non-`SET'
statements:
mysql> SET @t1=0, @t2=0, @t3=0;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 |
+----------------------+------+------+------+
| 5 | 5 | 1 | 4 |
+----------------------+------+------+------+
User variables may be used where expressions are allowed. Note that
this does not currently include contexts where a number is explicitly
required, such as in the `LIMIT' clause of a `SELECT' statement, or the
`IGNORE number LINES' clause of a `LOAD DATA' statement.
*Note*: in a `SELECT' statement, each expression is evaluated only when
it's sent to the client. This means that in the `HAVING', `GROUP BY',
or `ORDER BY' clause, you can't refer to an expression that involves
variables that are set in the `SELECT' part. For example, the following
statement will NOT work as expected:
mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM table_name HAVING b=5;
The reason is that `@aa' will not contain the value of the current row,
but the value of `id' for the previous accepted row.
The general rule is to never assign *and* use the same variable in the
same statement.
Another issue with setting a variable and using it in the same statement
is that the default result type of a variable is based on the type of
the variable at the start of the statement. (A not assigned variable is
assumed to have value `NULL' and to be of type STRING). The following
example illustrates this:
mysql> SET @a="test";
mysql> SELECT @a,(@a:=20) FROM table_name;
In this case MySQL will report to the client that column 1 is a string
and convert all accesses of `@a' to strings, even if @a will be set to
a number for the second row. After the statement is executed `@a' will
be regarded as a number.
If you have any problems with this, either avoid to set and use the same
variable in the same statement or set the variable to 0, 0.0 or ""
before you use it.
System Variables
----------------
Starting from MySQL 4.0.3 we provide better access to a lot of system
and connection variables. One can change most of them without having to
take down the server.
There are two kind of system variables: Thread-specific (or
connection-specific) variables that are unique to the current
connection and global variables that are used to configure global
events. Global variables also are used to set up the initial values of
the corresponding thread-specific variables for new connections.
When `mysqld' starts, all global variables are initialised from command
line arguments and option files. You can change the value with the
`SET GLOBAL' command. When a new thread is created, the thread-specific
variables are initialised from the global variables and they will not
change even if you issue a new `SET GLOBAL' command.
To set the value for a `GLOBAL' variable, you should use one of the
following syntaxes: (Here we use `sort_buffer_size' as an example
variable)
SET GLOBAL sort_buffer_size=value;
SET @@global.sort_buffer_size=value;
To set the value for a `SESSION' variable, you can use one of the
following syntaxes:
SET SESSION sort_buffer_size=value;
SET @@session.sort_buffer_size=value;
SET sort_buffer_size=value;
If you don't specify `GLOBAL' or `SESSION' then `SESSION' is used.
*Note SET OPTION::.
`LOCAL' is a synonym for `SESSION'.
To retrieve the value for a `GLOBAL' variable you can use one of the
following commands:
SELECT @@global.sort_buffer_size;
SHOW GLOBAL VARIABLES like 'sort_buffer_size';
To retrieve the value for a `SESSION' variable you can use one of the
following commands:
SELECT @@session.sort_buffer_size;
SHOW SESSION VARIABLES like 'sort_buffer_size';
When you *retrieve* a variable value with the `@@variable_name' syntax
and you don't specify `GLOBAL' or `SESSION' then MySQL will return the
thread-specific (`SESSION') value if it exists. If not, MySQL will
return the global value.
The reason for requiring `GLOBAL' for setting `GLOBAL' only variables
but not for retrieving them is to ensure that we don't later run into
problems if we later would introduce a thread-specific variable with
the same name or remove a thread-specific variable. In this case, you
could accidentally change the state for the server as a whole, rather
than just for your own connection.
The following is a full list of all variables that you change and
retrieve and if you can use `GLOBAL' or `SESSION' with them.
*Variable name* *Value type* *Type*
autocommit bool SESSION
big_tables bool SESSION
binlog_cache_size num GLOBAL
bulk_insert_buffer_size num GLOBAL | SESSION
concurrent_insert bool GLOBAL
connect_timeout num GLOBAL
convert_character_set string SESSION
delay_key_write OFF | ON | ALL GLOBAL
delayed_insert_limit num GLOBAL
delayed_insert_timeout num GLOBAL
delayed_queue_size num GLOBAL
error_count num SESSION
flush bool GLOBAL
flush_time num GLOBAL
foreign_key_checks bool SESSION
identity num SESSION
insert_id bool SESSION
interactive_timeout num GLOBAL | SESSION
join_buffer_size num GLOBAL | SESSION
key_buffer_size num GLOBAL
last_insert_id bool SESSION
local_infile bool GLOBAL
log_warnings bool GLOBAL
long_query_time num GLOBAL | SESSION
low_priority_updates bool GLOBAL | SESSION
max_allowed_packet num GLOBAL | SESSION
max_binlog_cache_size num GLOBAL
max_binlog_size num GLOBAL
max_connect_errors num GLOBAL
max_connections num GLOBAL
max_error_count num GLOBAL | SESSION
max_delayed_threads num GLOBAL
max_heap_table_size num GLOBAL | SESSION
max_join_size num GLOBAL | SESSION
max_relay_log_size num GLOBAL
max_sort_length num GLOBAL | SESSION
max_tmp_tables num GLOBAL
max_user_connections num GLOBAL
max_write_lock_count num GLOBAL
myisam_max_extra_sort_file_size num GLOBAL | SESSION
myisam_repair_threads num GLOBAL | SESSION
myisam_max_sort_file_size num GLOBAL | SESSION
myisam_sort_buffer_size num GLOBAL | SESSION
net_buffer_length num GLOBAL | SESSION
net_read_timeout num GLOBAL | SESSION
net_retry_count num GLOBAL | SESSION
net_write_timeout num GLOBAL | SESSION
query_cache_limit num GLOBAL
query_cache_size num GLOBAL
query_cache_type enum GLOBAL
read_buffer_size num GLOBAL | SESSION
read_rnd_buffer_size num GLOBAL | SESSION
rpl_recovery_rank num GLOBAL
safe_show_database bool GLOBAL
server_id num GLOBAL
slave_compressed_protocol bool GLOBAL
slave_net_timeout num GLOBAL
slow_launch_time num GLOBAL
sort_buffer_size num GLOBAL | SESSION
sql_auto_is_null bool SESSION
sql_big_selects bool SESSION
sql_big_tables bool SESSION
sql_buffer_result bool SESSION
sql_log_binlog bool SESSION
sql_log_off bool SESSION
sql_log_update bool SESSION
sql_low_priority_updates bool GLOBAL | SESSION
sql_max_join_size num GLOBAL | SESSION
sql_quote_show_create bool SESSION
sql_safe_updates bool SESSION
sql_select_limit bool SESSION
sql_slave_skip_counter num GLOBAL
sql_warnings bool SESSION
table_cache num GLOBAL
table_type enum GLOBAL | SESSION
thread_cache_size num GLOBAL
timestamp bool SESSION
tmp_table_size enum GLOBAL | SESSION
tx_isolation enum GLOBAL | SESSION
wait_timeout num GLOBAL | SESSION
warning_count num SESSION
unique_checks bool SESSION
Variables that are marked with `num' can be given a numerical value.
Variables that are marked with `bool' can be set to 0, 1, `ON' or
`OFF'. Variables that are of type `enum' should normally be set to one
of the available values for the variable, but can also be set to the
number that correspond to the enum value. (The first enum value is 0).
Here is a description of some of the variables:
*Variable* *Description*
identity Alias for last_insert_id (Sybase compatiblity)
sql_low_priority_updatesAlias for low_priority_updates
sql_max_join_size Alias for max_join_size
version Alias for VERSION() (Sybase (?) compatability)
A description of the other variable definitions can be found in the
startup options section, the description of `SHOW VARIABLES' and in the
`SET' section. *Note Command-line options::. *Note SHOW VARIABLES::.
*Note SET OPTION::.
Comment Syntax
--------------
The MySQL server supports the `# to end of line', `-- to end of line'
and `/* in-line or multiple-line */' comment styles:
mysql> SELECT 1+1; # This comment continues to the end of line
mysql> SELECT 1+1; -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+
/*
this is a
multiple-line comment
*/
1;
Note that the `--' (double-dash) comment style requires you to have at
least one space after the second dash!
Although the server understands the comment syntax just described,
there are some limitations on the way that the `mysql' client parses
`/* ... */' comments:
* Single-quote and double-quote characters are taken to indicate the
beginning of a quoted string, even within a comment. If the quote
is not matched by a second quote within the comment, the parser
doesn't realise the comment has ended. If you are running `mysql'
interactively, you can tell that it has gotten confused like this
because the prompt changes from `mysql>' to `'>' or `">'.
* A semicolon is taken to indicate the end of the current SQL
statement and anything following it to indicate the beginning of
the next statement.
These limitations apply both when you run `mysql' interactively and
when you put commands in a file and tell `mysql' to read its input from
that file with `mysql < some-file'.
MySQL supports the `--' SQL-99 comment style only if the second dash
is followed by a space. *Note ANSI diff comments::.
Is MySQL Picky About Reserved Words?
------------------------------------
`ABS' is
allowed as a column name). However, by default, in function invocations
no whitespace is allowed between the function name and the following
`(' character, so that a function call can be distinguished from a
reference to a column name.
causes
function names to be treated as reserved words; as a result, column
names that are the same as function names must be quoted as described
in *Note Legal names::.
using a
`yacc' parser:
*Word* *Word* *Word*
`ADD' `ALL' `ALTER'
`ANALYZE' `AND' `AS'
`ASC' `ASENSITIVE' `AUTO_INCREMENT'
`BDB' `BEFORE' `BERKELEYDB'
`BETWEEN' `BIGINT' `BINARY'
`BLOB' `BOTH' `BTREE'
`BY' `CALL' `CASCADE'
`CASE' `CHANGE' `CHAR'
`CHARACTER' `CHECK' `COLLATE'
`COLUMN' `COLUMNS' `CONNECTION'
`CONSTRAINT' `CREATE' `CROSS'
`CURRENT_DATE' `CURRENT_TIME' `CURRENT_TIMESTAMP'
`CURSOR' `DATABASE' `DATABASES'
`DAY_HOUR' `DAY_MICROSECOND' `DAY_MINUTE'
`DAY_SECOND' `DEC' `DECIMAL'
`DECLARE' `DEFAULT' `DELAYED'
`DELETE' `DESC' `DESCRIBE'
`DISTINCT' `DISTINCTROW' `DIV'
`DOUBLE' `DROP' `ELSE'
`ELSEIF' `ENCLOSED' `ERRORS'
`ESCAPED' `EXISTS' `EXPLAIN'
`FALSE' `FIELDS' `FLOAT'
`FOR' `FORCE' `FOREIGN'
`FROM' `FULLTEXT' `GRANT'
`GROUP' `HASH' `HAVING'
`HIGH_PRIORITY' `HOUR_MICROSECOND' `HOUR_MINUTE'
`HOUR_SECOND' `IF' `IGNORE'
`IN' `INDEX' `INFILE'
`INNER' `INNODB' `INOUT'
`INSENSITIVE' `INSERT' `INT'
`INTEGER' `INTERVAL' `INTO'
`IO_THREAD' `IS' `ITERATE'
`JOIN' `KEY' `KEYS'
`KILL' `LEADING' `LEAVE'
`LEFT' `LIKE' `LIMIT'
`LINES' `LOAD' `LOCALTIME'
`LOCALTIMESTAMP' `LOCK' `LONG'
`LONGBLOB' `LONGTEXT' `LOOP'
`LOW_PRIORITY' `MASTER_SERVER_ID' `MATCH'
`MEDIUMBLOB' `MEDIUMINT' `MEDIUMTEXT'
`MIDDLEINT' `MINUTE_MICROSECOND' `MINUTE_SECOND'
`MOD' `MRG_MYISAM' `NATURAL'
`NOT' `NO_WRITE_TO_BINLOG' `NULL'
`NUMERIC' `ON' `OPTIMIZE'
`OPTION' `OPTIONALLY' `OR'
`ORDER' `OUT' `OUTER'
`OUTFILE' `PRECISION' `PRIMARY'
`PRIVILEGES' `PROCEDURE' `PURGE'
`READ' `REAL' `REFERENCES'
`REGEXP' `RENAME' `REPEAT'
`REPLACE' `REQUIRE' `RESTRICT'
`RETURN' `RETURNS' `REVOKE'
`RIGHT' `RLIKE' `RTREE'
`SECOND_MICROSECOND' `SELECT' `SENSITIVE'
`SEPARATOR' `SET' `SHOW'
`SMALLINT' `SOME' `SONAME'
`SPATIAL' `SPECIFIC' `SQL_BIG_RESULT'
`SQL_CALC_FOUND_ROWS' `SQL_SMALL_RESULT' `SSL'
`STARTING' `STRAIGHT_JOIN' `STRIPED'
`TABLE' `TABLES' `TERMINATED'
`THEN' `TINYBLOB' `TINYINT'
`TINYTEXT' `TO' `TRAILING'
`TRUE' `TYPES' `UNION'
`UNIQUE' `UNLOCK' `UNSIGNED'
`UNTIL' `UPDATE' `USAGE'
`USE' `USER_RESOURCES' `USING'
`UTC_DATE' `UTC_TIME' `UTC_TIMESTAMP'
`VALUES' `VARBINARY' `VARCHAR'
`VARCHARACTER' `VARYING' `WARNINGS'
`WHEN' `WHERE' `WHILE'
`WITH' `WRITE' `XOR'
`YEAR_MONTH' `ZEROFILL'
The following symbols (from the table above) are disallowed by SQL-99
but allowed by MySQL as column/table names. This is because some of
these names are very natural names and a lot of people have already
used them.
* `ACTION'
* `BIT'
* `DATE'
* `ENUM'
* `NO'
* `TEXT'
* `TIME'
* `TIMESTAMP'
[Назад] [Содержание] [Вперед]
| Главная |