MySQL数据库

开发平台：

Visual C++

manual.texi：源码内容

given by @code{where_definition}, and returns the number of records deleted.
@c If an @code{ORDER BY} clause is used, the rows will be deleted in that order.
@c This is really only useful in conjunction with @code{LIMIT}. For example:
@c @example
@c DELETE FROM somelog
@c WHERE user = 'jcole'
@c ORDER BY timestamp
@c LIMIT 1
@c @end example
@c This will delete the oldest entry (by @code{timestamp}) where the row matches
@c the @code{WHERE} clause.
If you issue a @code{DELETE} with no @code{WHERE} clause, all rows are
deleted. If you do this in @code{AUTOCOMMIT} mode, this works as
@code{TRUNCATE}. @xref{TRUNCATE}. One problem with this is that
@code{DELETE} will return zero as the number of affected records, but
this will be fixed in 4.0.
If you really want to know how many records are deleted when you are deleting
all rows, and are willing to suffer a speed penalty, you can use a
@code{DELETE} statement of this form:
@example
mysql> DELETE FROM tbl_name WHERE 1>0;
@end example
Note that this is MUCH slower than @code{DELETE FROM tbl_name} with no
@code{WHERE} clause, because it deletes rows one at a time.
If you specify the keyword @code{LOW_PRIORITY}, execution of the
@code{DELETE} is delayed until no other clients are reading from the table.
Deleted records are maintained in a linked list and subsequent @code{INSERT}
operations reuse old record positions. To reclaim unused space and reduce
file sizes, use the @code{OPTIMIZE TABLE} statement or the @code{myisamchk}
utility to reorganize tables. @code{OPTIMIZE TABLE} is easier, but
@code{myisamchk} is faster.
See @ref{OPTIMIZE TABLE, , @code{OPTIMIZE TABLE}} and @ref{Optimization}.
The @strong{MySQL}-specific @code{LIMIT rows} option to @code{DELETE} tells
the server the maximum number of rows to be deleted before control is
returned to the client. This can be used to ensure that a specific
@code{DELETE} command doesn't take too much time. You can simply repeat
the @code{DELETE} command until the number of affected rows is less than
the @code{LIMIT} value.
@findex TRUNCATE
@node TRUNCATE, SELECT, DELETE, Reference
@section @code{TRUNCATE} Syntax
@example
TRUNCATE TABLE table_name
@end example
Is in 3.23 and the same thing as @code{DELETE FROM table_name}. @xref{DELETE}.
The differences are:
@itemize @bullet
@item
Implemented as a drop and re-create of the table, which makes this
much faster when deleting many rows.
@item
Not transaction-safe; @code{TRUNCATE TABLE} will automaticly end the current
transaction as if @code{COMMIT} would have been called.
@item
Doesn't return the number of deleted rows.
@item
As long as the table definition file @file{table_name.frm} is
valid, the table can be re-created this way, even if the data or index
files have become corrupted.
@end itemize
@findex SELECT
@node SELECT, JOIN, TRUNCATE, Reference
@section @code{SELECT} Syntax
@c help SELECT
@example
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO @{OUTFILE | DUMPFILE@} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY @{unsigned_integer | col_name | formula@} [ASC | DESC], ...]
[HAVING where_definition]
[ORDER BY @{unsigned_integer | col_name | formula@} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ]
@end example
@c help end
@code{SELECT} is used to retrieve rows selected from one or more tables.
@code{select_expression} indicates the columns you want to retrieve.
@code{SELECT} may also be used to retrieve rows computed without reference to
any table. For example:
@example
mysql> SELECT 1 + 1;
-> 2
@end example
All keywords used must be given in exactly the order shown above. For example,
a @code{HAVING} clause must come after any @code{GROUP BY} clause and before
any @code{ORDER BY} clause.
@itemize @bullet
@item
@cindex aliases, on expressions
@cindex expression aliases
A @code{SELECT} expression may be given an alias using @code{AS}. The alias
is used as the expression's column name and can be used with
@code{ORDER BY} or @code{HAVING} clauses. For example:
@example
mysql> select concat(last_name,', ',first_name) AS full_name
from mytable ORDER BY full_name;
@end example
@item
The @code{FROM table_references} clause indicates the tables from which to
retrieve rows. If you name more than one table, you are performing a
join. For information on join syntax, see @ref{JOIN, , @code{JOIN}}.
@item
You can refer to a column as @code{col_name}, @code{tbl_name.col_name}, or
@code{db_name.tbl_name.col_name}. You need not specify a @code{tbl_name} or
@code{db_name.tbl_name} prefix for a column reference in a @code{SELECT}
statement unless the reference would be ambiguous. See @ref{Legal names},
for examples of ambiguity that require the more explicit column reference
forms.
@item
@cindex aliases, for tables
@cindex table aliases
A table reference may be aliased using @code{tbl_name [AS] alias_name}:
@example
mysql> select t1.name, t2.salary from employee AS t1, info AS t2
where t1.name = t2.name;
mysql> select t1.name, t2.salary from employee t1, info t2
where t1.name = t2.name;
@end example
@item
Columns selected for output may be referred to in @code{ORDER BY} and
@code{GROUP BY} clauses using column names, column aliases, or column
positions. Column positions begin with 1:
@example
mysql> select college, region, seed from tournament
ORDER BY region, seed;
mysql> select college, region AS r, seed AS s from tournament
ORDER BY r, s;
mysql> select college, region, seed from tournament
ORDER BY 2, 3;
@end example
To sort in reverse order, add the @code{DESC} (descending) keyword to the
name of the column in the @code{ORDER BY} clause that you are sorting by.
The default is ascending order; this may be specified explicitly using
the @code{ASC} keyword.
@item
You can in the @code{WHERE} clause use any of the functions that
@strong{MySQL} support. @xref{Functions}.
@item
The @code{HAVING} clause can refer to any column or alias named in the
@code{select_expression}. It is applied last, just before items are sent to
the client, with no optimization. Don't use @code{HAVING} for items that
should be in the @code{WHERE} clause. For example, do not write this:
@example
mysql> select col_name from tbl_name HAVING col_name > 0;
@end example
Write this instead:
@example
mysql> select col_name from tbl_name WHERE col_name > 0;
@end example
In @strong{MySQL} Version 3.22.5 or later, you can also write queries like this:
@example
mysql> select user,max(salary) from users
group by user HAVING max(salary)>10;
@end example
In older @strong{MySQL} versions, you can write this instead:
@example
mysql> select user,max(salary) AS sum from users
group by user HAVING sum>10;
@end example
@item
@code{SQL_SMALL_RESULT}, @code{SQL_BIG_RESULT}, @code{SQL_BUFFER_RESULT},
@code{STRAIGHT_JOIN}, and @code{HIGH_PRIORITY} are @strong{MySQL} extensions
to ANSI SQL92.
@item
@code{HIGH_PRIORITY} will give the @code{SELECT} higher priority than
a statement that updates a table. You should only use this for queries
that are very fast and must be done at once. A @code{SELECT HIGH_PRIORITY}
query will run if the table is locked for read even if there is an update
statement that is waiting for the table to be free.
@item
@code{SQL_BIG_RESULT} can be used with @code{GROUP BY} or @code{DISTINCT}
to tell the optimizer that the result set will have many rows. In this case,
@strong{MySQL} will directly use disk-based temporary tables if needed.
@strong{MySQL} will also, in this case, prefer sorting to doing a
temporary table with a key on the @code{GROUP BY} elements.
@item
@cindex @code{GROUP BY}, extensions to ANSI SQL
If you use @code{GROUP BY}, the output rows will be sorted according to the
@code{GROUP BY} as if you would have had an @code{ORDER BY} over all the fields
in the @code{GROUP BY}. @strong{MySQL} has extended the @code{GROUP BY} so that
you can also specify @code{ASC} and @code{DESC} to @code{GROUP BY}:
@example
SELECT a,COUNT(b) FROM test_table GROUP BY a DESC
@end example
@item
@strong{MySQL} has extended the use of @code{GROUP BY} to allow you to
select fields which are not mentioned in the @code{GROUP BY} clause.
If you are not getting the results you expect from your query, please
read the @code{GROUP BY} description.
@xref{Group by functions}.
@item
@code{SQL_BUFFER_RESULT} will force the result to be put into a temporary
table. This will help @strong{MySQL} free the table locks early and will help
in cases where it takes a long time to send the result set to the client.
@item
@code{SQL_SMALL_RESULT}, a @strong{MySQL}-specific option, can be used
with @code{GROUP BY} or @code{DISTINCT} to tell the optimizer that the
result set will be small. In this case, @strong{MySQL} will use fast
temporary tables to store the resulting table instead of using sorting. In
@strong{MySQL} Version 3.23 this shouldn't normally be needed.
@item
@code{STRAIGHT_JOIN} forces the optimizer to join the tables in the order in
which they are listed in the @code{FROM} clause. You can use this to speed up
a query if the optimizer joins the tables in non-optimal order.
@xref{EXPLAIN, , @code{EXPLAIN}}.
@item
The @code{LIMIT} clause can be used to constrain the number of rows returned
by the @code{SELECT} statement. @code{LIMIT} takes one or two numeric
arguments.
If two arguments are given, the first specifies the offset of the first row to
return, the second specifies the maximum number of rows to return.
The offset of the initial row is 0 (not 1):
@example
mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15
@end example
If one argument is given, it indicates the maximum number of rows to return:
@example
mysql> select * from table LIMIT 5; # Retrieve first 5 rows
@end example
In other words, @code{LIMIT n} is equivalent to @code{LIMIT 0,n}.
@item
@tindex /etc/passwd
The @code{SELECT ... INTO OUTFILE 'file_name'} form of @code{SELECT} writes
the selected rows to a file. The file is created on the server host and
cannot already exist (among other things, this prevents database tables and
files such as @file{/etc/passwd} from being destroyed). You must have the
@strong{file} privilege on the server host to use this form of @code{SELECT}.
@code{SELECT ... INTO OUTFILE} is mainly intended to let you very
quickly dump a table on the server machine. If you want to create the
resulting file on some other host than the server host you can't use
@code{SELECT ... INTO OUTFILE}. In this case you should instead use some
client program like @code{mysqldump --tab} or @code{mysql -e "SELECT
..." > outfile} to generate the file.
@code{SELECT ... INTO OUTFILE} is the complement of @code{LOAD DATA
INFILE}; the syntax for the @code{export_options} part of the statement
consists of the same @code{FIELDS} and @code{LINES} clauses that are used
with the @code{LOAD DATA INFILE} statement.
@xref{LOAD DATA, , @code{LOAD DATA}}.
In the resulting text file, only the following characters are escaped by
the @code{ESCAPED BY} character:
@itemize @bullet
@item The @code{ESCAPED BY} character
@item The first character in @code{FIELDS TERMINATED BY}
@item The first character in @code{LINES TERMINATED BY}
@end itemize
Additionally, @code{ASCII 0} is converted to @code{ESCAPED BY} followed by 0
(@code{ASCII 48}).
The reason for the above is that you MUST escape any @code{FIELDS
TERMINATED BY}, @code{ESCAPED BY}, or @code{LINES TERMINATED BY}
characters to reliably be able to read the file back. @code{ASCII 0} is
escaped to make it easier to view with some pagers.
As the resulting file doesn't have to conform to the SQL syntax, nothing
else need be escaped.
Here follows an example of getting a file in the format used by many
old programs.
@example
SELECT a,b,a+b INTO OUTFILE "/tmp/result.text"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "n"
FROM test_table;
@end example
@item
@findex DUMPFILE
If you use @code{INTO DUMPFILE} instead of @code{INTO OUTFILE}, @strong{MySQL}
will only write one row into the file, without any column or line
terminations and without any escaping. This is useful if you want to
store a blob in a file.
@end itemize
@findex JOIN
@findex INNER JOIN
@findex CROSS JOIN
@findex LEFT JOIN
@findex LEFT OUTER JOIN
@findex NATURAL LEFT JOIN
@findex NATURAL LEFT OUTER JOIN
@findex RIGHT JOIN
@findex RIGHT OUTER JOIN
@findex NATURAL RIGHT JOIN
@findex NATURAL RIGHT OUTER JOIN
@findex STRAIGHT_JOIN
@node JOIN, INSERT, SELECT, Reference
@section @code{JOIN} Syntax
@strong{MySQL} supports the following @code{JOIN} syntaxes for use in
@code{SELECT} statements:
@example
table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference join_condition
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference LEFT [OUTER] JOIN table_reference
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
@{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr @}
table_reference RIGHT [OUTER] JOIN table_reference join_condition
table_reference RIGHT [OUTER] JOIN table_reference
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference
@end example
Where @code{table_reference} is defined as:
@example
table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)]
@end example
and @code{join_condition} is defined as:
@example
ON conditional_expr |
USING (column_list)
@end example
Note that in versions before Version 3.23.16, the @code{INNER JOIN} didn't take
a join condition!
@cindex ODBC compatibility
@cindex compatibility, with ODBC
The last @code{LEFT OUTER JOIN} syntax shown above exists only for
compatibility with ODBC:
@itemize @bullet
@item
A table reference may be aliased using @code{tbl_name AS alias_name} or
@code{tbl_name alias_name}:
@example
mysql> select t1.name, t2.salary from employee AS t1, info AS t2
where t1.name = t2.name;
@end example
@item
@code{INNER JOIN} and @code{,} (comma) are semantically equivalent.
Both do a full join between the tables used. Normally, you specify how
the tables should be linked in the @code{WHERE} condition.
@item
The @code{ON} conditional is any conditional of the form that may be used in
a @code{WHERE} clause.
@item
If there is no matching record for the right table in the @code{ON} or
@code{USING} part in a @code{LEFT JOIN}, a row with all columns set to
@code{NULL} is used for the right table. You can use this fact to find
records in a table that have no counterpart in another table:
@example
mysql> select table1.* from table1
LEFT JOIN table2 ON table1.id=table2.id
where table2.id is NULL;
@end example
This example finds all rows in @code{table1} with an @code{id} value that is
not present in @code{table2} (that is, all rows in @code{table1} with no
corresponding row in @code{table2}). This assumes that @code{table2.id} is
declared @code{NOT NULL}, of course. @xref{LEFT JOIN optimization}.
@item
The @code{USING} @code{(column_list)} clause names a list of columns that must
exist in both tables. A @code{USING} clause such as:
@example
A LEFT JOIN B USING (C1,C2,C3,...)
@end example
is defined to be semantically identical to an @code{ON} expression like
this:
@example
A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...
@end example
@item
The @code{NATURAL [LEFT] JOIN} of two tables is defined to be
semantically equivalent to an @code{INNER JOIN} or a @code{LEFT JOIN}
with a @code{USING} clause that names all columns that exist in both
tables.
@item
@code{RIGHT JOIN} works analogously as @code{LEFT JOIN}. To keep code
portable across databases, it's recommended to use @code{LEFT JOIN}
instead of @code{RIGHT JOIN}.
@item
@code{STRAIGHT_JOIN} is identical to @code{JOIN}, except that the left table
is always read before the right table. This can be used for those (few)
cases where the join optimizer puts the tables in the wrong order.
@item
As of @strong{MySQL} Version 3.23.12, you can give hints about which
index @strong{MySQL} should use when retrieving information from a
table. This is useful if @code{EXPLAIN} shows that @strong{MySQL} is
using the wrong index. By specifying @code{USE INDEX (key_list)}, you
can tell @strong{MySQL} to use only one of the specified indexes to
find rows in the table. The alternative syntax @code{IGNORE INDEX
(key_list)} can be used to tell @strong{MySQL} to not use some
particular index.
@end itemize
Some examples:
@example
mysql> select * from table1,table2 where table1.id=table2.id;
mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> select * from table1 LEFT JOIN table2 USING (id);
mysql> select * from table1 LEFT JOIN table2 ON table1.id=table2.id
LEFT JOIN table3 ON table2.id=table3.id;
mysql> select * from table1 USE INDEX (key1,key2) WHERE key1=1 and key2=2 AND
key3=3;
mysql> select * from table1 IGNORE INDEX (key3) WHERE key1=1 and key2=2 AND
key3=3;
@end example
@xref{LEFT JOIN optimization, , @code{LEFT JOIN} optimization}.
@findex INSERT
@node INSERT, REPLACE, JOIN, Reference
@section @code{INSERT} Syntax
@example
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...),(...),...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=expression, col_name=expression, ...
or INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name
SELECT ...
@end example
@code{INSERT} inserts new rows into an existing table. The @code{INSERT
... VALUES} form of the statement inserts rows based on explicitly
specified values. The @code{INSERT ... SELECT} form inserts rows
selected from another table or tables. The @code{INSERT ... VALUES}
form with multiple value lists is supported in @strong{MySQL} Version
3.22.5 or later. The @code{col_name=expression} syntax is supported in
@strong{MySQL} Version 3.22.10 or later.
@code{tbl_name} is the table into which rows should be inserted. The column
name list or the @code{SET} clause indicates which columns the statement
specifies values for:
@itemize @bullet
@item
If you specify no column list for @code{INSERT ... VALUES} or @code{INSERT
... SELECT}, values for all columns must be provided in the
@code{VALUES()} list or by the @code{SELECT}. If you don't know the order of
the columns in the table, use @code{DESCRIBE tbl_name} to find out.
@item
Any column not explicitly given a value is set to its default value. For
example, if you specify a column list that doesn't name all the columns in
the table, unnamed columns are set to their default values. Default value
assignment is described in @ref{CREATE TABLE, , @code{CREATE TABLE}}.
@item
An @code{expression} may refer to any column that was set earlier in a value
list. For example, you can say this:
@example
mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
@end example
But not this:
@example
mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
@end example
@item
If you specify the keyword @code{LOW_PRIORITY}, execution of the
@code{INSERT} is delayed until no other clients are reading from the
table. In this case the client has to wait until the insert statement
is completed, which may take a long time if the table is in heavy
use. This is in contrast to @code{INSERT DELAYED}, which lets the client
continue at once. @xref{INSERT DELAYED}. Note that @code{LOW_PRIORITY}
should normally not be used with @code{MyISAM} tables as this disables
concurrent inserts. @xref{MyISAM}.
@item
If you specify the keyword @code{IGNORE} in an @code{INSERT} with many value
rows, any rows that duplicate an existing @code{PRIMARY} or @code{UNIQUE}
key in the table are ignored and are not inserted. If you do not specify
@code{IGNORE}, the insert is aborted if there is any row that duplicates an
existing key value. You can determine with the C API function
@code{mysql_info()} how many rows were inserted into the table.
@item
If @strong{MySQL} was configured using the @code{DONT_USE_DEFAULT_FIELDS}
option, @code{INSERT} statements generate an error unless you explicitly
specify values for all columns that require a non-@code{NULL} value.
@xref{configure options, , @code{configure} options}.
@item
You can find the value used for an @code{AUTO_INCREMENT} column
with the @code{mysql_insert_id} function.
@xref{mysql_insert_id, , @code{mysql_insert_id()}}.
@end itemize
@findex mysql_info()
If you use @code{INSERT ... SELECT} or an @code{INSERT ... VALUES}
statement with multiple value lists, you can use the C API function
@code{mysql_info()} to get information about the query. The format of the
information string is shown below:
@example
Records: 100 Duplicates: 0 Warnings: 0
@end example
@code{Duplicates} indicates the number of rows that couldn't be inserted
because they would duplicate some existing unique index value.
@code{Warnings} indicates the number of attempts to insert column values that
were problematic in some way. Warnings can occur under any of the following
conditions:
@itemize @bullet
@item
Inserting @code{NULL} into a column that has been declared @code{NOT NULL}.
The column is set to its default value.
@item
Setting a numeric column to a value that lies outside the column's range.
The value is clipped to the appropriate endpoint of the range.
@item
Setting a numeric column to a value such as @code{'10.34 a'}. The trailing
garbage is stripped and the remaining numeric part is inserted. If the value
doesn't make sense as a number at all, the column is set to @code{0}.
@item
Inserting a string into a @code{CHAR}, @code{VARCHAR}, @code{TEXT}, or
@code{BLOB} column that exceeds the column's maximum length. The value is
truncated to the column's maximum length.
@item
Inserting a value into a date or time column that is illegal for the column
type. The column is set to the appropriate zero value for the type.
@end itemize
@findex REPLACE ... SELECT
@findex INSERT ... SELECT
@menu
* INSERT SELECT::
* INSERT DELAYED::
@end menu
@node INSERT SELECT, INSERT DELAYED, INSERT, INSERT
@subsection INSERT ... SELECT Syntax
@example
INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ...
@end example
With @code{INSERT ... SELECT} statement you can quickly insert many rows
into a table from one or many tables.
@example
INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;
@end example
The following conditions hold for an @code{INSERT ... SELECT} statement:
@itemize @minus
@item
The query cannot contain an @code{ORDER BY} clause.
@item
The target table of the @code{INSERT} statement cannot appear in the
@code{FROM} clause of the @code{SELECT} part of the query because it's
forbidden in ANSI SQL to @code{SELECT} from the same table into which you are
inserting. (The problem is that the @code{SELECT} possibly would
find records that were inserted earlier during the same run. When using
sub-select clauses, the situation could easily be very confusing!)
@item
@code{AUTO_INCREMENT} columns work as usual.
@item
You can use the C API function @code{mysql_info()} to get information about
the query. @xref{INSERT}.
@end itemize
You can of course also use @code{REPLACE} instead of @code{INSERT} to
overwrite old rows.
@findex INSERT DELAYED
@findex DELAYED
@cindex INSERT DELAYED
@node INSERT DELAYED, , INSERT SELECT, INSERT
@subsection @code{INSERT DELAYED} syntax
@example
INSERT DELAYED ...
@end example
The @code{DELAYED} option for the @code{INSERT} statement is a
@strong{MySQL}-specific option that is very useful if you have clients
that can't wait for the @code{INSERT} to complete. This is a common
problem when you use @strong{MySQL} for logging and you also
periodically run @code{SELECT} and @code{UPDATE} statements that take a
long time to complete. @code{DELAYED} was introduced in @strong{MySQL}
Version 3.22.15. It is a @strong{MySQL} extension to ANSI SQL92.
@code{INSERT DELAYED} only works with @code{ISAM} and @code{MyISAM}
tables. Note that as @code{MyISAM} tables supports concurrent
@code{SELECT} and @code{INSERT}, if there is no empty blocks in the data
file, you very seldom need to use @code{INSERT DELAYED} with
@code{MyISAM}.
When you use @code{INSERT DELAYED}, the client will get an OK at once
and the row will be inserted when the table is not in use by any other thread.
Another major benefit of using @code{INSERT DELAYED} is that inserts
from many clients are bundled together and written in one block. This is much
faster than doing many separate inserts.
Note that currently the queued rows are only stored in memory until they are
inserted into the table. This means that if you kill @code{mysqld}
the hard way (@code{kill -9}) or if @code{mysqld} dies unexpectedly, any
queued rows that weren't written to disk are lost!
The following describes in detail what happens when you use the
@code{DELAYED} option to @code{INSERT} or @code{REPLACE}. In this
description, the ``thread'' is the thread that received an @code{INSERT
DELAYED} command and ``handler'' is the thread that handles all
@code{INSERT DELAYED} statements for a particular table.
@itemize @bullet
@item
When a thread executes a @code{DELAYED} statement for a table, a handler
thread is created to process all @code{DELAYED} statements for the table, if
no such handler already exists.
@item
The thread checks whether or not the handler has acquired a @code{DELAYED}
lock already; if not, it tells the handler thread to do so. The
@code{DELAYED} lock can be obtained even if other threads have a @code{READ}
or @code{WRITE} lock on the table. However, the handler will wait for all
@code{ALTER TABLE} locks or @code{FLUSH TABLES} to ensure that the table
structure is up to date.
@item
The thread executes the @code{INSERT} statement, but instead of writing
the row to the table, it puts a copy of the final row into a queue that
is managed by the handler thread. Any syntax errors are noticed by the
thread and reported to the client program.
@item
The client can't report the number of duplicates or the @code{AUTO_INCREMENT}
value for the resulting row; it can't obtain them from the server, because
the @code{INSERT} returns before the insert operation has been completed. If
you use the C API, the @code{mysql_info()} function doesn't return anything
meaningful, for the same reason.
@item
The update log is updated by the handler thread when the row is inserted into
the table. In case of multiple-row inserts, the update log is updated when
the first row is inserted.
@item
After every @code{delayed_insert_limit} rows are written, the handler checks
whether or not any @code{SELECT} statements are still pending. If so, it
allows these to execute before continuing.
@cindex delayed_insert_limit
@cindex timeout
@item
When the handler has no more rows in its queue, the table is unlocked. If no
new @code{INSERT DELAYED} commands are received within
@code{delayed_insert_timeout} seconds, the handler terminates.
@item
If more than @code{delayed_queue_size} rows are pending already in a
specific handler queue, the thread requesting @code{INSERT DELAYED}
waits until there is room in the queue. This is done to ensure that
the @code{mysqld} server doesn't use all memory for the delayed memory
queue.
@item
The handler thread will show up in the @strong{MySQL} process list
with @code{delayed_insert} in the @code{Command} column. It will
be killed if you execute a @code{FLUSH TABLES} command or kill it with
@code{KILL thread_id}. However, it will first store all queued rows into the
table before exiting. During this time it will not accept any new
@code{INSERT} commands from another thread. If you execute an @code{INSERT
DELAYED} command after this, a new handler thread will be created.
@item
Note that the above means that @code{INSERT DELAYED} commands have higher
priority than normal @code{INSERT} commands if there is an @code{INSERT
DELAYED} handler already running! Other update commands will have to wait
until the @code{INSERT DELAYED} queue is empty, someone kills the handler
thread (with @code{KILL thread_id}), or someone executes @code{FLUSH TABLES}.
@item
The following status variables provide information about @code{INSERT
DELAYED} commands:
@multitable @columnfractions .35 .65
@item @strong{Variable} @tab @strong{Meaning}
@item @code{Delayed_insert_threads} @tab Number of handler threads
@item @code{Delayed_writes} @tab Number of rows written with @code{INSERT DELAYED}
@item @code{Not_flushed_delayed_rows} @tab Number of rows waiting to be written
@end multitable
You can view these variables by issuing a @code{SHOW STATUS} statement or
by executing a @code{mysqladmin extended-status} command.
@end itemize
Note that @code{INSERT DELAYED} is slower than a normal INSERT if the
table is not in use. There is also the additional overhead for the
server to handle a separate thread for each table on which you use
@code{INSERT DELAYED}. This means that you should only use @code{INSERT
DELAYED} when you are really sure you need it!
@findex REPLACE
@node REPLACE, LOAD DATA, INSERT, Reference
@section @code{REPLACE} Syntax
@example
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...)
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name=expression, col_name=expression,...
@end example
@code{REPLACE} works exactly like @code{INSERT}, except that if an old
record in the table has the same value as a new record on a unique index,
the old record is deleted before the new record is inserted.
@xref{INSERT, , @code{INSERT}}.
In other words, you can't access the values of the old row from a
@code{REPLACE} statement. In some old @strong{MySQL} version it looked
like you could do this, but that was a bug that has been corrected.
@findex LOAD DATA INFILE
@node LOAD DATA, UPDATE, REPLACE, Reference
@section @code{LOAD DATA INFILE} Syntax
@example
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 't']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\' ]
]
[LINES TERMINATED BY 'n']
[IGNORE number LINES]
[(col_name,...)]
@end example
The @code{LOAD DATA INFILE} statement reads rows from a text file into a
table at a very high speed. If the @code{LOCAL} keyword is specified, the
file is read from the client host. If @code{LOCAL} is not specified, the
file must be located on the server. (@code{LOCAL} is available in
@strong{MySQL} Version 3.22.6 or later.)
For security reasons, when reading text files located on the server, the
files must either reside in the database directory or be readable by all.
Also, to use @code{LOAD DATA INFILE} on server files, you must have the
@strong{file} privilege on the server host.
@xref{Privileges provided}.
If you specify the keyword @code{LOW_PRIORITY}, execution of the
@code{LOAD DATA} statement is delayed until no other clients are reading
from the table.
Using @code{LOCAL} will be a bit slower than letting the server access the
files directly, because the contents of the file must travel from the client
host to the server host. On the other hand, you do not need the
@strong{file} privilege to load local files.
@c old version
If you are using @strong{MySQL} before Version 3.23.24 you can't read from a
FIFO with @code{LOAD DATA INFILE}. If you need to read from a FIFO (for
example the output from gunzip), use @code{LOAD DATA LOCAL INFILE}
instead.
@cindex @code{mysqlimport}
You can also load data files by using the @code{mysqlimport} utility; it
operates by sending a @code{LOAD DATA INFILE} command to the server. The
@code{--local} option causes @code{mysqlimport} to read data files from the
client host. You can specify the @code{--compress} option to get better
performance over slow networks if the client and server support the
compressed protocol.
When locating files on the server host, the server uses the following rules:
@itemize @bullet
@item
If an absolute pathname is given, the server uses the pathname as is.
@item
If a relative pathname with one or more leading components is given,
the server searches for the file relative to the server's data directory.
@item
If a filename with no leading components is given, the server looks for
the file in the database directory of the current database.
@end itemize
Note that these rules mean a file given as @file{./myfile.txt} is read from
the server's data directory, whereas a file given as @file{myfile.txt} is
read from the database directory of the current database. For example,
the following @code{LOAD DATA} statement reads the file @file{data.txt}
from the database directory for @code{db1} because @code{db1} is the current
database, even though the statement explicitly loads the file into a
table in the @code{db2} database:
@example
mysql> USE db1;
mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;
@end example
The @code{REPLACE} and @code{IGNORE} keywords control handling of input
records that duplicate existing records on unique key values. If you specify
@code{REPLACE}, new rows replace existing rows that have the same unique key
value. If you specify @code{IGNORE}, input rows that duplicate an existing
row on a unique key value are skipped. If you don't specify either option, an
error occurs when a duplicate key value is found, and the rest of the text
file is ignored.
If you load data from a local file using the @code{LOCAL} keyword, the server
has no way to stop transmission of the file in the middle of the operation,
so the default bahavior is the same as if @code{IGNORE} is specified.
If you use @code{LOAD DATA INFILE} on an empty @code{MyISAM} table,
all non-unique indexes are created in a separate batch (like in @code{REPAIR}).
This normally makes @code{LOAD DATA INFILE} much faster when you have many
indexes.
@code{LOAD DATA INFILE} is the complement of @code{SELECT ... INTO OUTFILE}.
@xref{SELECT, , @code{SELECT}}.
To write data from a database to a file, use @code{SELECT ... INTO OUTFILE}.
To read the file back into the database, use @code{LOAD DATA INFILE}.
The syntax of the @code{FIELDS} and @code{LINES} clauses is the same for
both commands. Both clauses are optional, but @code{FIELDS}
must precede @code{LINES} if both are specified.
If you specify a @code{FIELDS} clause,
each of its subclauses (@code{TERMINATED BY}, @code{[OPTIONALLY] ENCLOSED
BY}, and @code{ESCAPED BY}) is also optional, except that you must
specify at least one of them.
If you don't specify a @code{FIELDS} clause, the defaults are the
same as if you had written this:
@example
FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY '\'
@end example
If you don't specify a @code{LINES} clause, the default
is the same as if you had written this:
@example
LINES TERMINATED BY 'n'
@end example
In other words, the defaults cause @code{LOAD DATA INFILE} to act as follows
when reading input:
@itemize @bullet
@item
Look for line boundaries at newlines.
@item
Break lines into fields at tabs.
@item
Do not expect fields to be enclosed within any quoting characters.
@item
Interpret occurrences of tab, newline, or @samp{} preceded by
@samp{} as literal characters that are part of field values.
@end itemize
Conversely, the defaults cause @code{SELECT ... INTO OUTFILE} to act as
follows when writing output:
@itemize @bullet
@item
Write tabs between fields.
@item
Do not enclose fields within any quoting characters.
@item
Use @samp{} to escape instances of tab, newline or @samp{} that occur
within field values.
@item
Write newlines at the ends of lines.
@end itemize
Note that to write @code{FIELDS ESCAPED BY '\'}, you must specify two
backslashes for the value to be read as a single backslash.
The @code{IGNORE number LINES} option can be used to ignore a header of
column names at the start of the file:
@example
mysql> LOAD DATA INFILE "/tmp/file_name" into table test IGNORE 1 LINES;
@end example
When you use @code{SELECT ... INTO OUTFILE} in tandem with @code{LOAD
DATA INFILE} to write data from a database into a file and then read
the file back into the database later, the field and line handling
options for both commands must match. Otherwise, @code{LOAD DATA
INFILE} will not interpret the contents of the file properly. Suppose
you use @code{SELECT ... INTO OUTFILE} to write a file with
fields delimited by commas:
@example
mysql> SELECT * INTO OUTFILE 'data.txt'
FIELDS TERMINATED BY ','
FROM ...
@end example
To read the comma-delimited file back in, the correct statement would be:
@example
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
FIELDS TERMINATED BY ',';
@end example
If instead you tried to read in the file with the statement shown below, it
wouldn't work because it instructs @code{LOAD DATA INFILE} to look for
tabs between fields:
@example
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
FIELDS TERMINATED BY 't';
@end example
The likely result is that each input line would be interpreted as
a single field.
@code{LOAD DATA INFILE} can be used to read files obtained from
external sources, too. For example, a file in dBASE format will have
fields separated by commas and enclosed in double quotes. If lines in
the file are terminated by newlines, the command shown below
illustrates the field and line handling options you would use to load
the file:
@example
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY 'n';
@end example
Any of the field or line handling options may specify an empty string
(@code{''}). If not empty, the @code{FIELDS [OPTIONALLY] ENCLOSED BY}
and @code{FIELDS ESCAPED BY} values must be a single character. The
@code{FIELDS TERMINATED BY} and @code{LINES TERMINATED BY} values may
be more than one character. For example, to write lines that are
terminated by carriage return-linefeed pairs, or to read a file
containing such lines, specify a @code{LINES TERMINATED BY 'rn'}
clause.
For example, to read a file of jokes, that are separated with a line
of @code{%%}, into a SQL table you can do:
@example
create table jokes (a int not null auto_increment primary key, joke text
not null);
load data infile "/tmp/jokes.txt" into table jokes fields terminated by ""
lines terminated by "n%%n" (joke);
@end example
@code{FIELDS [OPTIONALLY] ENCLOSED BY} controls quoting of fields. For
output (@code{SELECT ... INTO OUTFILE}), if you omit the word
@code{OPTIONALLY}, all fields are enclosed by the @code{ENCLOSED BY}
character. An example of such output (using a comma as the field
delimiter) is shown below:
@example
"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a " quote","102.20"
"4","a string containing a ", quote and comma","102.20"
@end example
If you specify @code{OPTIONALLY}, the @code{ENCLOSED BY} character is
used only to enclose @code{CHAR} and @code{VARCHAR} fields:
@example
1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20
@end example
Note that occurrences of the @code{ENCLOSED BY} character within a
field value are escaped by prefixing them with the @code{ESCAPED BY}
character. Also note that if you specify an empty @code{ESCAPED BY}
value, it is possible to generate output that cannot be read properly by
@code{LOAD DATA INFILE}. For example, the output just shown above would
appear as shown below if the escape character is empty. Observe that the
second field in the fourth line contains a comma following the quote, which
(erroneously) appears to terminate the field:
@example
1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20
@end example
For input, the @code{ENCLOSED BY} character, if present, is stripped from the
ends of field values. (This is true whether or not @code{OPTIONALLY} is
specified; @code{OPTIONALLY} has no effect on input interpretation.)
Occurrences of the @code{ENCLOSED BY} character preceded by the
@code{ESCAPED BY} character are interpreted as part of the current field
value. In addition, duplicated @code{ENCLOSED BY} characters occurring
within fields are interpreted as single @code{ENCLOSED BY} characters if the
field itself starts with that character. For example, if @code{ENCLOSED BY
'"'} is specified, quotes are handled as shown below:
@example
"The ""BIG"" boss" -> The "BIG" boss
The "BIG" boss -> The "BIG" boss
The ""BIG"" boss -> The ""BIG"" boss
@end example
@code{FIELDS ESCAPED BY} controls how to write or read special characters.
If the @code{FIELDS ESCAPED BY} character is not empty, it is used to prefix
the following characters on output:
@itemize @bullet
@item
The @code{FIELDS ESCAPED BY} character
@item
The @code{FIELDS [OPTIONALLY] ENCLOSED BY} character
@item
The first character of the @code{FIELDS TERMINATED BY} and
@code{LINES TERMINATED BY} values
@item
ASCII @code{0} (what is actually written following the escape character is
ASCII @code{'0'}, not a zero-valued byte)
@end itemize
If the @code{FIELDS ESCAPED BY} character is empty, no characters are escaped.
It is probably not a good idea to specify an empty escape character,
particularly if field values in your data contain any of the characters in
the list just given.
For input, if the @code{FIELDS ESCAPED BY} character is not empty, occurrences
of that character are stripped and the following character is taken literally
as part of a field value. The exceptions are an escaped @samp{0} or
@samp{N} (for example, @code{} or @code{N} if the escape character is
@samp{}). These sequences are interpreted as ASCII @code{0} (a zero-valued
byte) and @code{NULL}. See below for the rules on @code{NULL} handling.
For more information about @samp{}-escape syntax,
see @ref{Literals}.
In certain cases, field and line handling options interact:
@itemize @bullet
@item
If @code{LINES TERMINATED BY} is an empty string and @code{FIELDS
TERMINATED BY} is non-empty, lines are also terminated with
@code{FIELDS TERMINATED BY}.
@item
If the @code{FIELDS TERMINATED BY} and @code{FIELDS ENCLOSED BY} values are
both empty (@code{''}), a fixed-row (non-delimited) format is used. With
fixed-row format, no delimiters are used between fields. Instead, column
values are written and read using the ``display'' widths of the columns. For
example, if a column is declared as @code{INT(7)}, values for the column are
written using 7-character fields. On input, values for the column are
obtained by reading 7 characters. Fixed-row format also affects handling of
@code{NULL} values; see below. Note that fixed-size format will not work
if you are using a multi-byte character set.
@end itemize
Handling of @code{NULL} values varies, depending on the @code{FIELDS} and
@code{LINES} options you use:
@itemize @bullet
@item
For the default @code{FIELDS} and @code{LINES} values,
@code{NULL} is written as @code{N} for output and @code{N} is read
as @code{NULL} for input (assuming the @code{ESCAPED BY} character
is @samp{}).
@item
If @code{FIELDS ENCLOSED BY} is not empty, a field containing the literal
word @code{NULL} as its value is read as a @code{NULL} value (this differs
from the word @code{NULL} enclosed within @code{FIELDS ENCLOSED BY}
characters, which is read as the string @code{'NULL'}).
@item
If @code{FIELDS ESCAPED BY} is empty, @code{NULL} is written as the word
@code{NULL}.
@item
With fixed-row format (which happens when @code{FIELDS TERMINATED BY} and
@code{FIELDS ENCLOSED BY} are both empty), @code{NULL} is written as an empty
string. Note that this causes both @code{NULL} values and empty strings in
the table to be indistinguishable when written to the file because they are
both written as empty strings. If you need to be able to tell the two apart
when reading the file back in, you should not use fixed-row format.
@end itemize
Some cases are not supported by @code{LOAD DATA INFILE}:
@itemize @bullet
@item
Fixed-size rows (@code{FIELDS TERMINATED BY} and @code{FIELDS ENCLOSED
BY} both empty) and @code{BLOB} or @code{TEXT} columns.
@item
If you specify one separator that is the same as or a prefix of another,
@code{LOAD DATA INFILE} won't be able to interpret the input properly.
For example, the following @code{FIELDS} clause would cause problems:
@example
FIELDS TERMINATED BY '"' ENCLOSED BY '"'
@end example
@item
If @code{FIELDS ESCAPED BY} is empty, a field value that contains an occurrence
of @code{FIELDS ENCLOSED BY} or @code{LINES TERMINATED BY}
followed by the @code{FIELDS TERMINATED BY} value will cause @code{LOAD
DATA INFILE} to stop reading a field or line too early.
This happens because @code{LOAD DATA INFILE} cannot properly determine
where the field or line value ends.
@end itemize
The following example loads all columns of the @code{persondata} table:
@example
mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
@end example
No field list is specified, so @code{LOAD DATA INFILE} expects input rows
to contain a field for each table column. The default @code{FIELDS} and
@code{LINES} values are used.
If you wish to load only some of a table's columns, specify a field list:
@example
mysql> LOAD DATA INFILE 'persondata.txt'
INTO TABLE persondata (col1,col2,...);
@end example
You must also specify a field list if the order of the fields in the input
file differs from the order of the columns in the table. Otherwise,
@strong{MySQL} cannot tell how to match up input fields with table columns.
If a row has too few fields, the columns for which no input field is present
are set to default values. Default value assignment is described in
@ref{CREATE TABLE, , @code{CREATE TABLE}}.
An empty field value is interpreted differently than if the field value
is missing:
@itemize @bullet
@item
For string types, the column is set to the empty string.
@item
For numeric types, the column is set to @code{0}.
@item
For date and time types, the column is set to the appropriate ``zero''
value for the type.
@xref{Date and time types}.
@end itemize
@code{TIMESTAMP} columns are only set to the current date and time if there
is a @code{NULL} value for the column, or (for the first @code{TIMESTAMP}
column only) if the @code{TIMESTAMP} column is left out from the field list
when a field list is specified.
If an input row has too many fields, the extra fields are ignored and
the number of warnings is incremented.
@code{LOAD DATA INFILE} regards all input as strings, so you can't use
numeric values for @code{ENUM} or @code{SET} columns the way you can with
@code{INSERT} statements. All @code{ENUM} and @code{SET} values must be
specified as strings!
@findex mysql_info()
If you are using the C API, you can get information about the query by
calling the API function @code{mysql_info()} when the @code{LOAD DATA INFILE}
query finishes. The format of the information string is shown below:
@example
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
@end example
Warnings occur under the same circumstances as when values are inserted
via the @code{INSERT} statement (@pxref{INSERT, , @code{INSERT}}), except
that @code{LOAD DATA INFILE} also generates warnings when there are too few
or too many fields in the input row. The warnings are not stored anywhere;
the number of warnings can only be used as an indication if everything went
well. If you get warnings and want to know exactly why you got them, one way
to do this is to use @code{SELECT ... INTO OUTFILE} into another file and
compare this to your original input file.
If you need @code{LOAD DATA} to read from a pipe, you can use the
following trick:
@example
mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
cat < /dev/tcp/10.1.1.12/4711 > /nt/mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x
@end example
If you are using a version of @strong{MySQL} older than 3.23.25
you can only do the above with @code{LOAD DATA LOCAL INFILE}.
For more information about the efficiency of @code{INSERT} versus
@code{LOAD DATA INFILE} and speeding up @code{LOAD DATA INFILE},
@xref{Insert speed}.
@findex UPDATE
@node UPDATE, USE, LOAD DATA, Reference
@section @code{UPDATE} Syntax
@example
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1, [col_name2=expr2, ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT #]
@end example
@code{UPDATE} updates columns in existing table rows with new values.
The @code{SET} clause indicates which columns to modify and the values
they should be given. The @code{WHERE} clause, if given, specifies
which rows should be updated. Otherwise all rows are updated. If the
@code{ORDER BY} clause is specified, the rows will be updated in the
order that is specified.
If you specify the keyword @code{LOW_PRIORITY}, execution of the
@code{UPDATE} is delayed until no other clients are reading from the table.
If you specify the keyword @code{IGNORE}, the update statement will not
abort even if we get duplicate key errors during the update. Rows that
would cause conflicts will not be updated.
If you access a column from @code{tbl_name} in an expression,
@code{UPDATE} uses the current value of the column. For example, the
following statement sets the @code{age} column to one more than its
current value:
@example
mysql> UPDATE persondata SET age=age+1;
@end example
@code{UPDATE} assignments are evaluated from left to right. For example, the
following statement doubles the @code{age} column, then increments it:
@example
mysql> UPDATE persondata SET age=age*2, age=age+1;
@end example
If you set a column to the value it currently has, @strong{MySQL} notices
this and doesn't update it.
@findex mysql_info()
@code{UPDATE} returns the number of rows that were actually changed.
In @strong{MySQL} Version 3.22 or later, the C API function @code{mysql_info()}
returns the number of rows that were matched and updated and the number of
warnings that occurred during the @code{UPDATE}.
In @strong{MySQL} Version 3.23, you can use @code{LIMIT #} to ensure that
only a given number of rows are changed.
@findex USE
@node USE, FLUSH, UPDATE, Reference
@section @code{USE} Syntax
@example
USE db_name
@end example
The @code{USE db_name} statement tells @strong{MySQL} to use the @code{db_name}
database as the default database for subsequent queries. The database remains
current until the end of the session or until another @code{USE} statement
is issued:
@example
mysql> USE db1;
mysql> SELECT count(*) FROM mytable; # selects from db1.mytable
mysql> USE db2;
mysql> SELECT count(*) FROM mytable; # selects from db2.mytable
@end example
Making a particular database current by means of the @code{USE} statement
does not preclude you from accessing tables in other databases. The example
below accesses the @code{author} table from the @code{db1} database and the
@code{editor} table from the @code{db2} database:
@example
mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
WHERE author.editor_id = db2.editor.editor_id;
@end example
@cindex Sybase compatibility
@cindex compatibility, with Sybase
The @code{USE} statement is provided for Sybase compatibility.
@cindex @code{mysqladmin}
@cindex clearing, caches
@cindex caches, clearing
@findex FLUSH
@node FLUSH, KILL, USE, Reference
@section @code{FLUSH} Syntax (Clearing Caches)
@example
FLUSH flush_option [,flush_option]
@end example
You should use the @code{FLUSH} command if you want to clear some of the
internal caches @strong{MySQL} uses. To execute @code{FLUSH}, you must have
the @strong{reload} privilege.
@code{flush_option} can be any of the following:
@multitable @columnfractions .15 .85
@item @code{HOSTS} @tab Empties the host cache tables. You should flush the
host tables if some of your hosts change IP number or if you get the
error message @code{Host ... is blocked}. When more than
@code{max_connect_errors} errors occur in a row for a given host while
connection to the @strong{MySQL} server, @strong{MySQL} assumes
something is wrong and blocks the host from further connection requests.
Flushing the host tables allows the host to attempt to connect
again. @xref{Blocked host}.) You can start @code{mysqld} with
@code{-O max_connection_errors=999999999} to avoid this error message.
@item @code{LOGS} @tab Closes and reopens all log files.
If you have specified the update log file or a binary log file without
an extension, the extension number of the log file will be incremented
by one relative to the previous file. If you have used an extension in
the file name, @strong{MySQL} will close and reopen the update log file.
@xref{Update log}.
@item @code{PRIVILEGES} @tab Reloads the privileges from the grant tables in
the @code{mysql} database.
@item @code{TABLES} @tab Closes all open tables and force all tables in use to be closed.
@item @code{[TABLE | TABLES] table_name [,table_name...]} @tab Flushes only the given tables.
@item @code{TABLES WITH READ LOCK} @tab Closes all open tables and locks all tables for all databases with a read until one executes @code{UNLOCK TABLES}. This is very convinient way to get backups if you have a file system, like Veritas,that can take snapshots in time.
@item @code{STATUS} @tab Resets most status variables to zero.
@end multitable
You can also access each of the commands shown above with the @code{mysqladmin}
utility, using the @code{flush-hosts}, @code{flush-logs}, @code{reload},
or @code{flush-tables} commands.
@cindex @code{mysqladmin}
@findex KILL
@node KILL, SHOW, FLUSH, Reference
@section @code{KILL} Syntax
@example
KILL thread_id
@end example
Each connection to @code{mysqld} runs in a separate thread. You can see
which threads are running with the @code{SHOW PROCESSLIST} command and kill
a thread with the @code{KILL thread_id} command.
If you have the @strong{process} privilege, you can see and kill all threads.
Otherwise, you can see and kill only your own threads.
You can also use the @code{mysqladmin processlist} and @code{mysqladmin kill}
commands to examine and kill threads.
When you do a @code{KILL}, a thread specific @code{kill flag} is set for
the thread.
In most cases it may take some time for the thread to die as the kill
flag is only checked at specific intervals.
@itemize @bullet
@item
In @code{SELECT}, @code{ORDER BY} and @code{GROUP BY} loops, the flag is
checked after reading a block of rows. If the kill flag is set the
statement is aborted
@item
When doing an @code{ALTER TABLE} the kill flag is checked before each block of
rows are read from the original table. If the kill flag was set the command
is aborted and the temporary table is deleted.
@item
When doing an @code{UPDATE TABLE} and @code{DELETE TABLE}, the kill flag
is checked after each block read and after each updated or delete
row. If the kill flag is set the statement is aborted. Note that if you
are not using transactions, the changes will not be rolled back!
@item
@code{GET_LOCK()} will abort with @code{NULL}.
@item
An @code{INSERT DELAYED} thread will quickly flush all rows it has in
memory and die.
@item
If the thread is in the table lock handler (state: @code{Locked}),
the table lock will be quickly aborted.
@item
If the thread is waiting for free disk space in a @code{write} call, the
write is aborted with an disk full error message.
@end itemize
@findex SHOW DATABASES
@findex SHOW TABLES
@findex SHOW COLUMNS
@findex SHOW FIELDS
@findex SHOW INDEX
@findex SHOW KEYS
@findex SHOW STATUS
@findex SHOW VARIABLES
@findex SHOW PROCESSLIST
@findex SHOW TABLE STATUS
@findex SHOW GRANTS
@findex SHOW CREATE TABLE
@node SHOW, EXPLAIN, KILL, Reference
@section @code{SHOW} Syntax (Get Information About Tables, Columns,...)
@example
SHOW DATABASES [LIKE wild]
or SHOW [OPEN] TABLES [FROM db_name] [LIKE wild]
or SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
or SHOW STATUS [LIKE wild]
or SHOW VARIABLES [LIKE wild]
or SHOW LOGS
or SHOW [FULL] PROCESSLIST
or SHOW GRANTS FOR user
or SHOW CREATE TABLE table_name
or SHOW MASTER STATUS
or SHOW MASTER LOGS
or SHOW SLAVE STATUS
@end example
@code{SHOW} provides information about databases, tables, columns, or
status information about the server. If the @code{LIKE wild} part is
used, the @code{wild} string can be a string that uses the SQL @samp{%}
and @samp{_} wild-card characters.
@findex SHOW DATABASES
@findex SHOW TABLES
@findex SHOW COLUMNS
@findex SHOW FIELDS
@findex SHOW INDEX
@findex SHOW KEYS
@menu
* SHOW DATABASE INFO::
* SHOW TABLE STATUS::
* SHOW STATUS::
* SHOW VARIABLES::
* SHOW LOGS::
* SHOW PROCESSLIST::
* SHOW GRANTS::
* SHOW CREATE TABLE::
@end menu
@cindex displaying, information, @code{SHOW}
@node SHOW DATABASE INFO, SHOW TABLE STATUS, SHOW, SHOW
@subsection SHOW Information About Databases, Tables, Columns, and Indexes
You can use @code{db_name.tbl_name} as an alternative to the @code{tbl_name
FROM db_name} syntax. These two statements are equivalent:
@example
mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;
@end example
@code{SHOW DATABASES} lists the databases on the @strong{MySQL} server
host. You can also get this list using the @code{mysqlshow} command.
@code{SHOW TABLES} lists the tables in a given database. You can also
get this list using the @code{mysqlshow db_name} command.
@strong{NOTE:} If a user doesn't have any privileges for a table, the table
will not show up in the output from @code{SHOW TABLES} or @code{mysqlshow
db_name}.
@code{SHOW OPEN TABLES} lists the tables that are currently open in
the table cache. @xref{Table cache}. The @code{Comment} field tells
how many times the table is @code{cached} and @code{in_use}.
@code{SHOW COLUMNS} lists the columns in a given table. If you specify
the @code{FULL} option, you will also get the privileges you have for
each column. If the column types are different than you expect them to
be based on a @code{CREATE TABLE} statement, note that @strong{MySQL}
sometimes changes column types. @xref{Silent column changes}.
The @code{DESCRIBE} statement provides information similar to
@code{SHOW COLUMNS}.
@xref{DESCRIBE, , @code{DESCRIBE}}.
@code{SHOW FIELDS} is a synonym for @code{SHOW COLUMNS}, and
@code{SHOW KEYS} is a synonym for @code{SHOW INDEX}. You can also
list a table's columns or indexes with @code{mysqlshow db_name tbl_name}
or @code{mysqlshow -k db_name tbl_name}.
@code{SHOW INDEX} returns the index information in a format that closely
resembles the @code{SQLStatistics} call in ODBC. The following columns
are returned:
@multitable @columnfractions .35 .65
@item @strong{Column} @tab @strong{Meaning}
@item @code{Table} @tab Name of the table.
@item @code{Non_unique} @tab 0 if the index can't contain duplicates.
@item @code{Key_name} @tab Name of the index.
@item @code{Seq_in_index} @tab Column sequence number in index, starting with 1.
@item @code{Column_name} @tab Column name.
@item @code{Collation} @tab How the column is sorted in the index. In
@strong{MySQL}, this can have values @code{A} (Ascending) or @code{NULL}
(Not sorted).
@item @code{Cardinality} @tab Number of unique values in the index.
This is updated by running @code{isamchk -a}.
@item @code{Sub_part} @tab Number of indexed characters if the column is
only partly indexed. @code{NULL} if the entire key is indexed.
@item @code{Comment} @tab Various remarks.
For now, it tells whether index is FULLTEXT or not.
@end multitable
Note that as the @code{Cardinality} is counted based on statistics
stored as integers, it's not necessarily accurate for small tables.
@cindex displaying, table status
@cindex tables, displaying status
@cindex status, tables
@node SHOW TABLE STATUS, SHOW STATUS, SHOW DATABASE INFO, SHOW
@subsection SHOW Status Information About Tables
@example
SHOW TABLE STATUS [FROM db_name] [LIKE wild]
@end example
@code{SHOW TABLE STATUS} (new in Version 3.23) works likes @code{SHOW
STATUS}, but provides a lot of information about each table. You can
also get this list using the @code{mysqlshow --status db_name} command.
The following columns are returned:
@multitable @columnfractions .30 .70
@item @strong{Column} @tab @strong{Meaning}
@item @code{Name} @tab Name of the table.
@item @code{Type} @tab Type of table. @xref{Table types}.
@item @code{Row_format} @tab The row storage format (Fixed, Dynamic, or Compressed).
@item @code{Rows} @tab Number of rows.
@item @code{Avg_row_length} @tab Average row length.
@item @code{Data_length} @tab Length of the data file.
@item @code{Max_data_length} @tab Max length of the data file.
@item @code{Index_length} @tab Length of the index file.
@item @code{Data_free} @tab Number of allocated but not used bytes.
@item @code{Auto_increment} @tab Next autoincrement value.
@item @code{Create_time} @tab When the table was created.
@item @code{Update_time} @tab When the data file was last updated.
@item @code{Check_time} @tab When the table was last checked.
@item @code{Create_options} @tab Extra options used with @code{CREATE TABLE}.
@item @code{Comment} @tab The comment used when creating the table (or some information why @strong{MySQL} couldn't access the table information).
@end multitable
@code{INNOBASE} tables will report the free space in the tablespace
in the table comment.
@node SHOW STATUS, SHOW VARIABLES, SHOW TABLE STATUS, SHOW
@subsection SHOW Status Information
@cindex @code{mysqladmin}
@code{SHOW STATUS} provides server status information
(like @code{mysqladmin extended-status}). The output resembles that shown
below, though the format and numbers probably differ:
@example
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 155372598 |
| Bytes_sent | 1176560426 |
| Connections | 30023 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 8340 |
| Created_tmp_files | 60 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 462604 |
| Handler_read_first | 105881 |
| Handler_read_key | 27820558 |
| Handler_read_next | 390681754 |
| Handler_read_prev | 6022500 |
| Handler_read_rnd | 30546748 |
| Handler_read_rnd_next | 246216530 |
| Handler_update | 16945404 |
| Handler_write | 60356676 |
| Key_blocks_used | 14955 |
| Key_read_requests | 96854827 |
| Key_reads | 162040 |
| Key_write_requests | 7589728 |
| Key_writes | 3813196 |
| Max_used_connections | 0 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1 |
| Open_files | 2 |
| Open_streams | 0 |
| Opened_tables | 44600 |
| Questions | 2026873 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 99646 |
| Select_range_check | 0 |
| Select_scan | 30802 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 30 |
| Sort_range | 500 |
| Sort_rows | 30296250 |
| Sort_scan | 4650 |
| Table_locks_immediate | 1920382 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 30022 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 80380 |
+--------------------------+------------+
@end example
@cindex variables, status
The status variables listed above have the following meaning:
@multitable @columnfractions .35 .65
@item @strong{Variable} @tab @strong{Meaning}
@item @code{Aborted_clients} @tab Number of connections aborted because the client died without closing the connection properly. @xref{Communication errors}.
@item @code{Aborted_connects} @tab Number of tries to connect to the @strong{MySQL} server that failed. @xref{Communication errors}.
@item @code{Bytes_received} @tab Number of bytes received from all clients.
@item @code{Bytes_sent} @tab Number of bytes sent to all clients.
@item @code{Connections} @tab Number of connection attempts to the @strong{MySQL} server.
@item @code{Created_tmp_disk_tables} @tab Number of implicit temporary tables on disk created while executing statements.
@item @code{Created_tmp_tables} @tab Number of implicit temporary tables in memory created while executing statements.
@item @code{Created_tmp_files} @tab How many temporary files @code{mysqld} have created.
@item @code{Delayed_insert_threads} @tab Number of delayed insert handler threads in use.
@item @code{Delayed_writes} @tab Number of rows written with @code{INSERT DELAYED}.
@item @code{Delayed_errors} @tab Number of rows written with @code{INSERT DELAYED} for which some error occurred (probably @code{duplicate key}).
@item @code{Flush_commands} @tab Number of executed @code{FLUSH} commands.
@item @code{Handler_delete} @tab Number of times a row was deleted from a table.
@item @code{Handler_read_first} @tab Number of times the first entry was read from an index.
If this is high, it suggests that the server is doing a lot of full index scans, for example,
@code{SELECT col1 FROM foo}, assuming that col1 is indexed.
@item @code{Handler_read_key} @tab Number of requests to read a row based on a key. If this
is high, it is a good indication that your queries and tables are properly indexed.
@item @code{Handler_read_next} @tab Number of requests to read next row in key order. This
will be incremented if you are querying an index column with a range contraint. This also
will be incremented if you are doing an index scan.
@item @code{Handler_read_rnd} @tab Number of requests to read a row based on a fixed position.
This will be high if you are doing a lot of queries that require sorting of the result.
@item @code{Handler_read_rnd_next} @tab Number of requests to read the next row in the datafile.
This will be high if you are doing a lot of table scans. Generally this suggests that your tables
are not properly indexed or that your queries are not written to take advantage of the indexes you
have.
@item @code{Handler_update} @tab Number of requests to update a row in a table.
@item @code{Handler_write} @tab Number of requests to insert a row in a table.
@item @code{Key_blocks_used} @tab The number of used blocks in the key cache.
@item @code{Key_read_requests} @tab The number of requests to read a key block from the cache.
@item @code{Key_reads} @tab The number of physical reads of a key block from disk.
@item @code{Key_write_requests} @tab The number of requests to write a key block to the cache.
@item @code{Key_writes} @tab The number of physical writes of a key block to disk.
@item @code{Max_used_connections} @tab The maximum number of connections in use simultaneously.
@item @code{Not_flushed_key_blocks} @tab Keys blocks in the key cache that has changed but hasn't yet been flushed to disk.
@item @code{Not_flushed_delayed_rows} @tab Number of rows waiting to be written in @code{INSERT DELAY} queues.
@item @code{Open_tables} @tab Number of tables that are open.
@item @code{Open_files} @tab Number of files that are open.
@item @code{Open_streams} @tab Number of streams that are open (used mainly for logging).
@item @code{Opened_tables} @tab Number of tables that have been opened.
@item @code{Select_full_join} @tab Number of joins without keys (Should be 0).
@item @code{Select_full_range_join} @tab Number of joins where we used a range search on reference table.
@item @code{Select_range} @tab Number of joins where we used ranges on the first table. (It's normally not critical even if this is big.)
@item @code{Select_scan} @tab Number of joins where we scanned the first table.
@item @code{Select_range_check} @tab Number of joins without keys where we check for key usage after each row (Should be 0).
@item @code{Questions} @tab Number of queries sent to the server.
@item @code{Slave_open_temp_tables} @tab Number of temporary tables currently
open by the slave thread
@item @code{Slow_launch_threads} @tab Number of threads that have taken more than @code{slow_launch_time} to connect.
@item @code{Slow_queries} @tab Number of queries that have taken more than @code{long_query_time}. @xref{Slow query log}.
@item @code{Sort_merge_passes} @tab Number of merges the sort has to do. If this value is large you should consider increasing @code{sort_buffer}.
@item @code{Sort_range} @tab Number of sorts that where done with ranges.
@item @code{Sort_rows} @tab Number of sorted rows.
@item @code{Sort_scan} @tab Number of sorts that where done by scanning the table.
@item @code{Table_locks_immediate} @tab Number of times a table lock was
acquired immediately. Available after 3.23.33.
@item @code{Table_locks_waited} @tab Number of times a table lock could not
be acquired immediately and a wait was needed. If this is high, and you
have performance problems, you should first optimize your queries, and then
either split your table(s) or use replication. Available after 3.23.33.
@item @code{Threads_cached} @tab Number of threads in the thread cache.
@item @code{Threads_connected} @tab Number of currently open connections.
@item @code{Threads_created} @tab Number of threads created to handle connections.
@item @code{Threads_running} @tab Number of threads that are not sleeping.
@item @code{Uptime} @tab How many seconds the server has been up.
@end multitable
Some comments about the above:
@itemize @bullet
@item
If @code{Opened_tables} is big, then your @code{table_cache}
variable is probably too small.
@item
If @code{key_reads} is big, then your @code{key_cache} is probably too
small. The cache hit rate can be calculated with
@code{key_reads}/@code{key_read_requests}.
@item
If @code{Handler_read_rnd} is big, then you probably have a lot of
queries that require @strong{MySQL} to scan whole tables or you have
joins that don't use keys properly.
@item
If @code{Threads_created} is big, you may want to increase the
@code{thread_cache_size} variable.
@end itemize
@node SHOW VARIABLES, SHOW LOGS, SHOW STATUS, SHOW
@subsection SHOW VARIABLES
@example
SHOW VARIABLES [LIKE wild]
@end example
@code{SHOW VARIABLES} shows the values of some @strong{MySQL} system
variables. You can also get this information using the @code{mysqladmin
variables} command. If the default values are unsuitable, you can set most
of these variables using command-line options when @code{mysqld} starts up.
@xref{Command-line options}.
The output resembles that shown below, though the format and numbers may
differ somewhat:
@example
+-------------------------+---------------------------+
| Variable_name | Value |
+-------------------------+---------------------------+
| ansi_mode | OFF |
| back_log | 50 |
| basedir | /my/monty/ |
| bdb_cache_size | 16777216 |
| bdb_log_buffer_size | 32768 |
| bdb_home | /my/monty/data/ |
| bdb_max_lock | 10000 |
| bdb_logdir | |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| binlog_cache_size | 32768 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /my/monty/data/ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| have_bdb | YES |
| have_gemini | NO |
| have_innobase | YES |
| have_raid | YES |
| have_ssl | NO |
| init_file | |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 16776192 |
| language | /my/monty/share/english/ |
| large_files_support | ON |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_connections | 100 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_recover_options | DEFAULT |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 0 |
| pid_file | /my/monty/data/donna.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 131072 |
| query_buffer_size | 0 |
| safe_show_database | OFF |
| server_id | 0 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer | 2097116 |
| table_cache | 64 |
| table_type | MYISAM |
| thread_cache_size | 4 |
| thread_stack | 65536 |
| tmp_table_size | 1048576 |
| tmpdir | /tmp/ |
| version | 3.23.29a-gamma-debug |
| wait_timeout | 28800 |
+-------------------------+---------------------------+
@end example
Each option is described below. Values for buffer sizes, lengths, and stack
sizes are given in bytes. You can specify values with a suffix of @samp{K}
or @samp{M} to indicate kilobytes or megabytes. For example, @code{16M}
indicates 16 megabytes. The case of suffix letters does not matter;
@code{16M} and @code{16m} are equivalent:
@cindex variables, values
@table @code
@item @code{ansi_mode}.
Is @code{ON} if @code{mysqld} was started with @code{--ansi}.
@xref{ANSI mode}.
@item @code{back_log}
The number of outstanding connection requests @strong{MySQL} can have. This
comes into play when the main @strong{MySQL} thread gets @strong{VERY}
many connection requests in a very short time. It then takes some time
(although very little) for the main thread to check the connection and start
a new thread. The @code{back_log} value indicates how many requests can be
stacked during this short time before @strong{MySQL} momentarily stops
answering new requests. You need to increase this only if you expect a large
number of connections in a short period of time.
In other words, this value is the size of the listen queue for incoming
TCP/IP connections. Your operating system has its own limit on the size
of this queue. The manual page for the Unix @code{listen(2)} system
call should have more details. Check your OS documentation for the
maximum value for this variable. Attempting to set @code{back_log}
higher than your operating system limit will be ineffective.
@item @code{basedir}
The value of the @code{--basedir} option.
@item @code{bdb_cache_size}
The buffer that is allocated to cache index and rows for @code{BDB}
tables. If you don't use @code{BDB} tables, you should start
@code{mysqld} with @code{--skip-bdb} to not waste memory for this
cache.
@item @code{bdb_log_buffer_size}
The buffer that is allocated to cache index and rows for @code{BDB}
tables. If you don't use @code{BDB} tables, you should set this to 0 or
start @code{mysqld} with @code{--skip-bdb} to not waste memory for this
cache.
@item @code{bdb_home}
The value of the @code{--bdb-home} option.
@item @code{bdb_max_lock}
The maximum number of locks (1000 by default) you can have active on a
BDB table. You should increase this if you get errors of type @code{bdb:
Lock table is out of available locks} or @code{Got error 12 from ...}
when you have do long transactions or when @code{mysqld} has to examine
a lot of rows to calculate the query.
@item @code{bdb_logdir}
The value of the @code{--bdb-logdir} option.
@item @code{bdb_shared_data}
Is @code{ON} if you are using @code{--bdb-shared-data}.
@item @code{bdb_tmpdir}
The value of the @code{--bdb-tmpdir} option.
@item @code{binlog_cache_size}. The size of the cache to hold the SQL
statements for the binary log during a transaction. If you often use
big, multi-statement transactions you can increase this to get more
performance. @xref{COMMIT}.
@item @code{character_set}
The default character set.
@item @code{character_sets}
The supported character sets.
@item @code{concurrent_inserts}
If @code{ON} (the default), @strong{MySQL} will allow you to use @code{INSERT}
on @code{MyISAM} tables at the same time as you run @code{SELECT} queries
on them. You can turn this option off by starting mysqld with @code{--safe}
or @code{--skip-new}.
@cindex timeout
@item @code{connect_timeout}
The number of seconds the @code{mysqld} server is waiting for a connect
packet before responding with @code{Bad handshake}.
@item @code{datadir}
The value of the @code{--datadir} option.
@item @code{delay_key_write}
If enabled (is on by default), @strong{MySQL} will honor the
@code{delay_key_write} option @code{CREATE TABLE}. This means that the
key buffer for tables with this option will not get flushed on every
index update, but only when a table is closed. This will speed up
writes on keys a lot, but you should add automatic checking of all tables
with @code{myisamchk --fast --force} if you use this. Note that if you
start @code{mysqld} with the @code{--delay-key-write-for-all-tables}
option this means that all tables will be treated as if they were
created with the @code{delay_key_write} option. You can clear this flag
by starting @code{mysqld} with @code{--skip-new} or @code{--safe-mode}.
@item @code{delayed_insert_limit}
After inserting @code{delayed_insert_limit} rows, the @code{INSERT
DELAYED} handler will check if there are any @code{SELECT} statements
pending. If so, it allows these to execute before continuing.
@item @code{delayed_insert_timeout}
How long a @code{INSERT DELAYED} thread should wait for @code{INSERT}
statements before terminating.
@item @code{delayed_queue_size}
What size queue (in rows) should be allocated for handling @code{INSERT
DELAYED}. If the queue becomes full, any client that does @code{INSERT
DELAYED} will wait until there is room in the queue again.
@item @code{flush}
This is @code{ON} if you have started @strong{MySQL} with the @code{--flush}
option.
@item @code{flush_time}
If this is set to a non-zero value, then every @code{flush_time} seconds all
tables will be closed (to free up resources and sync things to disk). We
only recommend this option on Win95, Win98, or on systems where you have
very little resources.
@item @code{have_bdb}
@code{YES} if @code{mysqld} supports Berkeley DB tables. @code{DISABLED}
if @code{--skip-bdb} is used.
@item @code{have_gemini}
@code{YES} if @code{mysqld} supports Gemini tables. @code{DISABLED}
if @code{--skip-gemini} is used.
@item @code{have_innobase}
@code{YES} if @code{mysqld} supports Innobase tables. @code{DISABLED}
if @code{--skip-innobase} is used.
@item @code{have_raid}
@code{YES} if @code{mysqld} supports the @code{RAID} option.
@item @code{have_ssl}
@code{YES} if @code{mysqld} supports SSL (encryption) on the client/server
protocol.
@item @code{init_file}
The name of the file specified with the @code{--init-file} option when
you start the server. This is a file of SQL statements you want the
server to execute when it starts.
@item @code{interactive_timeout}
The number of seconds the server waits for activity on an interactive
connection before closing it. An interactive client is defined as a
client that uses the @code{CLIENT_INTERACTIVE} option to
@code{mysql_real_connect()}. See also @code{wait_timeout}.
@item @code{join_buffer_size}
The size of the buffer that is used for full joins (joins that do not
use indexes). The buffer is allocated one time for each full join
between two tables. Increase this value to get a faster full join when
adding indexes is not possible. (Normally the best way to get fast joins
is to add indexes.)
@c Make texi2html support index @anchor{Index cache size}. Then change
@c some xrefs to point here
@cindex indexes, block size
@item @code{key_buffer_size}
Index blocks are buffered and are shared by all threads.
@code{key_buffer_size} is the size of the buffer used for index blocks.
Increase this to get better index handling (for all reads and multiple
writes) to as much as you can afford; 64M on a 256M machine that mainly
runs @strong{MySQL} is quite common. If you, however, make this too big
(more than 50% of your total memory?) your system may start to page and
become REALLY slow. Remember that because @strong{MySQL} does not cache
data read, that you will have to leave some room for the OS filesystem
cache.
You can check the performance of the key buffer by doing @code{show
status} and examine the variables @code{Key_read_requests},
@code{Key_reads}, @code{Key_write_requests}, and @code{Key_writes}. The
@code{Key_reads/Key_read_request} ratio should normally be < 0.01.
The @code{Key_write/Key_write_requests} is usually near 1 if you are
using mostly updates/deletes but may be much smaller if you tend to
do updates that affect many at the same time or if you are
using @code{delay_key_write}. @xref{SHOW}.
To get even more speed when writing many rows at the same time, use
@code{LOCK TABLES}. @xref{LOCK TABLES, , @code{LOCK TABLES}}.
@item @code{language}
The language used for error messages.
@item @code{large_file_support}
If @code{mysqld} was compiled with options for big file support.
@item @code{locked_in_memory}
If @code{mysqld} was locked in memory with @code{--memlock}
@item @code{log}
If logging of all queries is enabled.
@item @code{log_update}
If the update log is enabled.
@item @code{log_bin}
If the binary log is enabled.
@item @code{log_slave_updates}
If the updates from the slave should be logged.
@item @code{long_query_time}
If a query takes longer than this (in seconds), the @code{Slow_queries} counter
will be incremented. If you are using @code{--log-slow-queries}, the query
will be logged to the slow query logfile. @xref{Slow query log}.
@item @code{lower_case_table_names}
If set to 1 table names are stored in lowercase on disk. This will enable
you to access the table names case-insensitive also on Unix.
@xref{Name case sensitivity}.
@item @code{max_allowed_packet}
The maximum size of one packet. The message buffer is initialized to
@code{net_buffer_length} bytes, but can grow up to @code{max_allowed_packet}
bytes when needed. This value by default is small, to catch big (possibly
wrong) packets. You must increase this value if you are using big
@code{BLOB} columns. It should be as big as the biggest @code{BLOB} you want
to use. The current protocol limits @code{max_allowed_packet} to 16M.
@item @code{max_binlog_cache_size}. If a multi-statement transaction
requires more than this amount of memory, one will get the error
"Multi-statement transaction required more than 'max_binlog_cache_size'
bytes of storage".
@item @code{max_binlog_size}. Available after 3.23.33. If a write to the
binary ( replication) log exceeds the given value, rotate the logs. You
cannot set it to less than 1024 bytes, or more than 1 GB. Default is
1 GB.
@item @code{max_connections}
The number of simultaneous clients allowed. Increasing this value increases
the number of file descriptors that @code{mysqld} requires. See below for
comments on file descriptor limits. @xref{Too many connections}.
@item @code{max_connect_errors}
If there is more than this number of interrupted connections from a host
this host will be blocked from further connections. You can unblock a host
with the command @code{FLUSH HOSTS}.
@item @code{max_delayed_threads}
Don't start more than this number of threads to handle @code{INSERT DELAYED}
statements. If you try to insert data into a new table after all @code{INSERT
DELAYED} threads are in use, the row will be inserted as if the
@code{DELAYED} attribute wasn't specified.
@item @code{max_heap_table_size}
Don't allow creation of heap tables bigger than this.
@item @code{max_join_size}
Joins that are probably going to read more than @code{max_join_size}
records return an error. Set this value if your users tend to perform joins
that lack a @code{WHERE} clause, that take a long time, and that return
millions of rows.
@item @code{max_sort_length}
The number of bytes to use when sorting @code{BLOB} or @code{TEXT}
values (only the first @code{max_sort_length} bytes of each value
are used; the rest are ignored).
@item @code{max_user_connections}
The maximum number of active connections for a single user (0 = no limit).
@item @code{max_tmp_tables}
(This option doesn't yet do anything.)
Maximum number of temporary tables a client can keep open at the same time.
@item @code{max_write_lock_count}
After this many write locks, allow some read locks to run in between.
@item @code{myisam_recover_options}
The value of the @code{--myisam-recover} option.
@item @code{myisam_sort_buffer_size}
The buffer that is allocated when sorting the index when doing a
@code{REPAIR} or when creating indexes with @code{CREATE INDEX} or
@code{ALTER TABLE}.
@item @code{net_buffer_length}
The communication buffer is reset to this size between queries. This
should not normally be changed, but if you have very little memory, you
can set it to the expected size of a query. (That is, the expected length of
SQL statements sent by clients. If statements exceed this length, the buffer
is automatically enlarged, up to @code{max_allowed_packet} bytes.)
@item @code{net_read_timeout}
Number of seconds to wait for more data from a connection before aborting
the read. Note that when we don't expect data from a connection, the timeout
is defined by @code{write_timeout}.
@item @code{net_retry_count}
If a read on a communication port is interrupted, retry this many times
before giving up. This value should be quite high on @code{FreeBSD} as
internal interrupts are sent to all threads.
@item @code{net_write_timeout}
Number of seconds to wait for a block to be written to a connection before
aborting the write.
@item @code{open_files_limit}
If this is not 0, then @code{mysqld} will use this value to reserve file
descriptors to use with @code{getrlimit()}. If this value is 0 then
@code{mysqld} will reserve @code{max_connections*5} or
@code{max_connections + table_cache*2} (whichever is larger) number of
files. You should try increasing this if @code{mysqld} gives you the
error 'Too many open files'.
@item @code{pid_file}
The value of the @code{--pid-file} option.
@item @code{port}
The value of the @code{--port} option.
@item @code{protocol_version}
The protocol version used by the @strong{MySQL} server.
@item @code{record_buffer}
Each thread that does a sequential scan allocates a buffer of this
size for each table it scans. If you do many sequential scans, you may
want to increase this value.
@item @code{query_buffer_size}
The initial allocation of the query buffer. If most of your queries are
long (like when inserting blobs), you should increase this!
@item @code{safe_show_databases}
Don't show databases for which the user doesn't have any database or
table privileges. This can improve security if you're concerned about
people being able to see what databases other users have. See also
@code{skip_show_databases}.
@item @code{server_id}
The value of the @code{--server-id} option.
@item @code{skip_locking}
Is OFF if @code{mysqld} uses external locking.
@item @code{skip_networking}
Is ON if we only allow local (socket) connections.
@item @code{skip_show_databases}
This prevents people from doing @code{SHOW DATABASES} if they don't have
the @code{PROCESS_PRIV} privilege. This can improve security if you're
concerned about people being able to see what databases other users
have. See also @code{safe_show_databases}.
@item @code{slow_launch_time}
If creating the thread takes longer than this value (in seconds), the
@code{Slow_launch_threads} counter will be incremented.
@item @code{socket}
The Unix socket used by the server.
@item @code{sort_buffer}
Each thread that needs to do a sort allocates a buffer of this
size. Increase this value for faster @code{ORDER BY} or @code{GROUP BY}
operations.
@xref{Temporary files}.
@item @code{table_cache}
The number of open tables for all threads. Increasing this value
increases the number of file descriptors that @code{mysqld} requires.
@strong{MySQL} needs two file descriptors for each unique open table.
See below for comments on file descriptor limits. You can check if you
need to increase the table cache by checking the @code{Opened_tables}
variable. @xref{SHOW}. If this variable is big and you don't do
@code{FLUSH TABLES} a lot (which just forces all tables to be closed and
reopenend), then you should increase the value of this variable.
Make sure that your operating system can handle the number of open file
descriptors implied by the @code{table_cache} setting. If @code{table_cache}
is set too high, @strong{MySQL} may run out of file descriptors and refuse
connections, fail to perform queries, and be very unreliable.
For information about how the table cache works, see @ref{Table cache}.
@item @code{table_type}
The default table type
@item @code{thread_cache_size}
How many threads we should keep in a cache for reuse. When a
client disconnects, the client's threads are put in the cache if there
aren't more than @code{thread_cache_size} threads from before. All new
threads are first taken from the cache, and only when the cache is empty
is a new thread created. This variable can be increased to improve
performance if you have a lot of new connections. (Normally this doesn't
give a notable performance improvement if you have a good
thread implementation.) By examing the difference between
the @code{Connections} and @code{Threads_created} you can see how efficient
the current thread cache is for you.
@item @code{thread_concurrency}
On Solaris, @code{mysqld} will call @code{thr_setconcurrency()} with
this value. @code{thr_setconcurrency()} permits the application to give
the threads system a hint for the desired number of threads that should
be run at the same time.
@item @code{thread_stack}
The stack size for each thread. Many of the limits detected by the
@code{crash-me} test are dependent on this value. The default is
large enough for normal operation. @xref{Benchmarks}.
@item @code{timezone}
The timezone for the server.
@item @code{tmp_table_size}
If an in-memory temporary table exceeds this size, @strong{MySQL}
will automatically convert it to an on-disk @code{MyISAM} table.
Increase the value of @code{tmp_table_size} if you do many advanced
@code{GROUP BY} queries and you have lots of memory.
@item @code{tmpdir}
The directory used for temporary files and temporary tables.
@item @code{version}
The version number for the server.
@item @code{wait_timeout}
The number of seconds the server waits for activity on a connection before
closing it. See also @code{interactive_timeout}.
@end table
The manual section that describes tuning @strong{MySQL} contains some
information of how to tune the above variables. @xref{Server parameters}.
@node SHOW LOGS, SHOW PROCESSLIST, SHOW VARIABLES, SHOW
@subsection SHOW Information About Log Files
@code{SHOW LOGS} shows you status information about existing log
files. It currently only displays information about Berkeley DB log
files.
@itemize @bullet
@item @code{File} shows the full path to the log file
@item @code{Type} shows the type of the log file (@code{BDB} for Berkeley
DB log files)
@item @code{Status} shows the status of the log file (@code{FREE} if the
file can be removed, or @code{IN USE} if the file is needed by the transaction
subsystem)
@end itemize
@cindex threads, display
@cindex processes, display
@findex threads
@findex PROCESSLIST
@node SHOW PROCESSLIST, SHOW GRANTS, SHOW LOGS, SHOW
@subsection SHOW Information About Connected Threads (Processes)
@code{SHOW PROCESSLIST} shows you which threads are running. You can
also get this information using the @code{mysqladmin processlist}
command. If you have the @strong{process} privilege, you can see all
threads. Otherwise, you can see only your own threads. @xref{KILL, ,
@code{KILL}}. If you don't use the @code{FULL} option, then only
the first 100 characters of each query will be shown.
This command is very useful if you get the 'too many connections' error
message and want to find out what's going on. @strong{MySQL} reserves
one extra connection for a client with the @code{Process_priv} privilege
to ensure that you should always be able to login and check the system
(assuming you are not giving this privilege to all your users).
@cindex privileges, display
@node SHOW GRANTS, SHOW CREATE TABLE, SHOW PROCESSLIST, SHOW
@subsection SHOW GRANTS (Privileges) for a User
@code{SHOW GRANTS FOR user} lists the grant commands that must be issued to
duplicate the grants for a user.
@example
mysql> SHOW GRANTS FOR root@@localhost;
+---------------------------------------------------------------------+
| Grants for root@@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
@end example
@node SHOW CREATE TABLE, , SHOW GRANTS, SHOW
@subsection SHOW CREATE TABLE
Shows a @code{CREATE TABLE} statement that will create the given table:
@example
mysql> show create table tG
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE t (
id int(11) default NULL auto_increment,
s char(60) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM
@end example
@code{SHOW CREATE TABLE} will quote table and column names according to
@code{SQL_QUOTE_SHOW_CREATE} option.
@ref{SET OPTION, , @code{SET OPTION SQL_QUOTE_SHOW_CREATE}}.
@findex EXPLAIN
@findex SELECT, optimizing
@node EXPLAIN, DESCRIBE, SHOW, Reference
@section @code{EXPLAIN} Syntax (Get Information About a @code{SELECT})
@example
EXPLAIN tbl_name
or EXPLAIN SELECT select_options
@end example
@code{EXPLAIN tbl_name} is a synonym for @code{DESCRIBE tbl_name} or
@code{SHOW COLUMNS FROM tbl_name}.
When you precede a @code{SELECT} statement with the keyword @code{EXPLAIN},
@strong{MySQL} explains how it would process the @code{SELECT}, providing
information about how tables are joined and in which order.
With the help of @code{EXPLAIN}, you can see when you must add indexes
to tables to get a faster @code{SELECT} that uses indexes to find the
records. You can also see if the optimizer joins the tables in an optimal
order. To force the optimizer to use a specific join order for a
@code{SELECT} statement, add a @code{STRAIGHT_JOIN} clause.
For non-simple joins, @code{EXPLAIN} returns a row of information for each
table used in the @code{SELECT} statement. The tables are listed in the order
they would be read. @strong{MySQL} resolves all joins using a single-sweep
multi-join method. This means that @strong{MySQL} reads a row from the first
table, then finds a matching row in the second table, then in the third table
and so on. When all tables are processed, it outputs the selected columns and
backtracks through the table list until a table is found for which there are
more matching rows. The next row is read from this table and the process
continues with the next table.
Output from @code{EXPLAIN} includes the following columns:
@table @code
@item table
The table to which the row of output refers.
@item type
The join type. Information about the various types is given below.
@item possible_keys
The @code{possible_keys} column indicates which indexes @strong{MySQL}
could use to find the rows in this table. Note that this column is
totally independent of the order of the tables. That means that some of
the keys in possible_keys may not be usable in practice with the
generated table order.
If this column is empty, there are no relevant indexes. In this case,
you may be able to improve the performance of your query by examining
the @code{WHERE} clause to see if it refers to some column or columns
that would be suitable for indexing. If so, create an appropriate index
and check the query with @code{EXPLAIN} again. @xref{ALTER TABLE}.
To see what indexes a table has, use @code{SHOW INDEX FROM tbl_name}.
@item key
The @code{key} column indicates the key that @strong{MySQL} actually
decided to use. The key is @code{NULL} if no index was chosen. If
@strong{MySQL} chooses the wrong index, you can probably force
@strong{MySQL} to use another index by using @code{myisamchk --analyze},
@xref{myisamchk syntax}, or by using @code{USE INDEX/IGNORE INDEX}.
@xref{JOIN}.
@item key_len
The @code{key_len} column indicates the length of the key that
@strong{MySQL} decided to use. The length is @code{NULL} if the
@code{key} is @code{NULL}. Note that this tells us how many parts of a
multi-part key @strong{MySQL} will actually use.
@item ref
The @code{ref} column shows which columns or constants are used with the
@code{key} to select rows from the table.
@item rows
The @code{rows} column indicates the number of rows @strong{MySQL}
believes it must examine to execute the query.
@item Extra
This column contains additional information of how @strong{MySQL} will
resolve the query. Here is an explanation of the different text
strings that can be found in this column:
@table @code
@item Distinct
@strong{MySQL} will not continue searching for more rows for the current row
combination after it has found the first matching row.
@item Not exists
@strong{MySQL} was able to do a @code{LEFT JOIN} optimization on the
query and will not examine more rows in this table for a row combination
after it finds one row that matches the @code{LEFT JOIN} criteria.
@item @code{range checked for each record (index map: #)}
@strong{MySQL} didn't find a real good index to use. It will, instead, for
each row combination in the preceding tables, do a check on which index to
use (if any), and use this index to retrieve the rows from the table. This
isn't very fast but is faster than having to do a join without
an index.
@item Using filesort
@strong{MySQL} will need to do an extra pass to find out how to retrieve
the rows in sorted order. The sort is done by going through all rows
according to the @code{join type} and storing the sort key + pointer to
the row for all rows that match the @code{WHERE}. Then the keys are
sorted. Finally the rows are retrieved in sorted order.
@item Using index
The column information is retrieved from the table using only
information in the index tree without having to do an additional seek to
read the actual row. This can be done when all the used columns for
the table are part of the same index.
@item Using temporary
To resolve the query @strong{MySQL} will need to create a
temporary table to hold the result. This typically happens if you do an
@code{ORDER BY} on a different column set than you did a @code{GROUP
BY} on.
@item Where used
A @code{WHERE} clause will be used to restrict which rows will be
matched against the next table or sent to the client. If you don't have
this information and the table is of type @code{ALL} or @code{index},
you may have something wrong in your query (if you don't intend to
fetch/examine all rows from the table).
@end table
If you want to get your queries as fast as possible, you should look out for
@code{Using filesort} and @code{Using temporary}.
@end table
The different join types are listed below, ordered from best to worst type:
@cindex system table
@cindex tables, system
@table @code
@item system
The table has only one row (= system table). This is a special case of
the @code{const} join type.
@cindex constant table
@cindex tables, constant
@item const
The table has at most one matching row, which will be read at the start
of the query. Because there is only one row, values from the column in
this row can be regarded as constants by the rest of the
optimizer. @code{const} tables are very fast as they are read only once!
@item eq_ref
One row will be read from this table for each combination of rows from
the previous tables. This is the best possible join type, other than the
@code{const} types. It is used when all parts of an index are used by
the join and the index is @code{UNIQUE} or a @code{PRIMARY KEY}.
@item ref
All rows with matching index values will be read from this table for each
combination of rows from the previous tables. @code{ref} is used if the join
uses only a leftmost prefix of the key, or if the key is not @code{UNIQUE}
or a @code{PRIMARY KEY} (in other words, if the join cannot select a single
row based on the key value). If the key that is used matches only a few rows,
this join type is good.
@item range
Only rows that are in a given range will be retrieved, using an index to
select the rows. The @code{key} column indicates which index is used.
The @code{key_len} contains the longest key part that was used.
The @code{ref} column will be NULL for this type.
@item index
This is the same as @code{ALL}, except that only the index tree is
scanned. This is usually faster than @code{ALL}, as the index file is usually
smaller than the data file.
@item ALL
A full table scan will be done for each combination of rows from the
previous tables. This is normally not good if the table is the first
table not marked @code{const}, and usually @strong{very} bad in all other
cases. You normally can avoid @code{ALL} by adding more indexes, so that
the row can be retrieved based on constant values or column values from
earlier tables.
@end table
You can get a good indication of how good a join is by multiplying all values
in the @code{rows} column of the @code{EXPLAIN} output. This should tell you
roughly how many rows @strong{MySQL} must examine to execute the query. This
number is also used when you restrict queries with the @code{max_join_size}
variable.
@xref{Server parameters}.
The following example shows how a @code{JOIN} can be optimized progressively
using the information provided by @code{EXPLAIN}.
Suppose you have the @code{SELECT} statement shown below, that you examine
using @code{EXPLAIN}:
@example
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
tt.ProjectReference, tt.EstimatedShipDate,
tt.ActualShipDate, tt.ClientID,
tt.ServiceCodes, tt.RepetitiveID,
tt.CurrentProcess, tt.CurrentDPPerson,
tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
et_1.COUNTRY, do.CUSTNAME
FROM tt, et, et AS et_1, do
WHERE tt.SubmitTime IS NULL
AND tt.ActualPC = et.EMPLOYID
AND tt.AssignedPC = et_1.EMPLOYID
AND tt.ClientID = do.CUSTNMBR;
@end example
For this example, assume that:
@itemize @bullet
@item
The columns being compared have been declared as follows:
@multitable @columnfractions .1 .2 .7
@item @strong{Table} @tab @strong{Column} @tab @strong{Column type}
@item @code{tt} @tab @code{ActualPC} @tab @code{CHAR(10)}
@item @code{tt} @tab @code{AssignedPC} @tab @code{CHAR(10)}
@item @code{tt} @tab @code{ClientID} @tab @code{CHAR(10)}
@item @code{et} @tab @code{EMPLOYID} @tab @code{CHAR(15)}
@item @code{do} @tab @code{CUSTNMBR} @tab @code{CHAR(15)}
@end multitable
@item
The tables have the indexes shown below:
@multitable @columnfractions .1 .9
@item @strong{Table} @tab @strong{Index}
@item @code{tt} @tab @code{ActualPC}
@item @code{tt} @tab @code{AssignedPC}
@item @code{tt} @tab @code{ClientID}
@item @code{et} @tab @code{EMPLOYID} (primary key)
@item @code{do} @tab @code{CUSTNMBR} (primary key)
@end multitable
@item
The @code{tt.ActualPC} values aren't evenly distributed.
@end itemize
Initially, before any optimizations have been performed, the @code{EXPLAIN}
statement produces the following information:
@example
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
do ALL PRIMARY NULL NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872
range checked for each record (key map: 35)
@end example
Because @code{type} is @code{ALL} for each table, this output indicates that
@strong{MySQL} is doing a full join for all tables! This will take quite a
long time, as the product of the number of rows in each table must be
examined! For the case at hand, this is @code{74 * 2135 * 74 * 3872 =
45,268,558,720} rows. If the tables were bigger, you can only imagine how
long it would take.
One problem here is that @strong{MySQL} can't (yet) use indexes on columns
efficiently if they are declared differently. In this context,
@code{VARCHAR} and @code{CHAR} are the same unless they are declared as
different lengths. Because @code{tt.ActualPC} is declared as @code{CHAR(10)}
and @code{et.EMPLOYID} is declared as @code{CHAR(15)}, there is a length
mismatch.
To fix this disparity between column lengths, use @code{ALTER TABLE} to
lengthen @code{ActualPC} from 10 characters to 15 characters:
@example
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
@end example
Now @code{tt.ActualPC} and @code{et.EMPLOYID} are both @code{VARCHAR(15)}.
Executing the @code{EXPLAIN} statement again produces this result:
@example
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used
do ALL PRIMARY NULL NULL NULL 2135
range checked for each record (key map: 1)
et_1 ALL PRIMARY NULL NULL NULL 74
range checked for each record (key map: 1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
@end example
This is not perfect, but is much better (the product of the @code{rows}
values is now less by a factor of 74). This version is executed in a couple
of seconds.
A second alteration can be made to eliminate the column length mismatches
for the @code{tt.AssignedPC = et_1.EMPLOYID} and @code{tt.ClientID =
do.CUSTNMBR} comparisons:
@example
mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
MODIFY ClientID VARCHAR(15);
@end example
Now @code{EXPLAIN} produces the output shown below:
@example
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
@end example
This is almost as good as it can get.
The remaining problem is that, by default, @strong{MySQL} assumes that values
in the @code{tt.ActualPC} column are evenly distributed, and that isn't the
case for the @code{tt} table. Fortunately, it is easy to tell @strong{MySQL}
about this:
@example
shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt
shell> mysqladmin refresh
@end example
Now the join is perfect, and @code{EXPLAIN} produces this result:
@example
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
@end example
Note that the @code{rows} column in the output from @code{EXPLAIN} is an
educated guess from the @strong{MySQL} join optimizer. To optimize a
query, you should check if the numbers are even close to the truth. If not,
you may get better performance by using @code{STRAIGHT_JOIN} in your
@code{SELECT} statement and trying to list the tables in a different order in
the @code{FROM} clause.
@findex DESC
@findex DESCRIBE
@node DESCRIBE, COMMIT, EXPLAIN, Reference
@section @code{DESCRIBE} Syntax (Get Information About Columns)
@example
@{DESCRIBE | DESC@} tbl_name @{col_name | wild@}
@end example
@code{DESCRIBE} provides information about a table's columns. @code{col_name}
may be a column name or a string containing the SQL @samp{%} and @samp{_}
wild-card characters.
If the column types are different than you expect them to be based on a
@code{CREATE TABLE} statement, note that @strong{MySQL} sometimes
changes column types. @xref{Silent column changes}.
@cindex Oracle compatibility
@cindex compatibility, with Oracle
This statement is provided for Oracle compatibility.
The @code{SHOW} statement provides similar information.
@xref{SHOW, , @code{SHOW}}.
@findex BEGIN
@findex COMMIT
@findex ROLLBACK
@node COMMIT, LOCK TABLES, DESCRIBE, Reference
@section @code{BEGIN/COMMIT/ROLLBACK} Syntax
By default, @strong{MySQL} runs in @code{autocommit} mode. This means that
as soon as you execute an update, @strong{MySQL} will store the update on
disk.
If you are using transactions safe tables (like @code{BDB},
@code{INNOBASE} or @code{GEMINI}), you can put @strong{MySQL} into
non-@code{autocommit} mode with the following command:
@example
SET AUTOCOMMIT=0
@end example
After this you must use @code{COMMIT} to store your changes to disk or
@code{ROLLBACK} if you want to ignore the changes you have made since
the beginning of your transaction.
If you want to switch from @code{AUTOCOMMIT} mode for one series of
statements, you can use the @code{BEGIN} or @code{BEGIN WORK} statement:
@example
BEGIN;
SELECT @@A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@@A WHERE type=1;
COMMIT;
@end example
Note that if you are using non-transaction-safe tables, the changes will be
stored at once, independent of the status of the @code{autocommit} mode.
If you do a @code{ROLLBACK} when you have updated a non-transactional
table you will get an error (@code{ER_WARNING_NOT_COMPLETE_ROLLBACK}) as
a warning. All transactional safe tables will be restored but any
non-transactional table will not change.
If you are using @code{BEGIN} or @code{SET AUTO_COMMIT=0}, you
should use the @strong{MySQL} binary log for backups instead of the
old update log; The transaction is stored in the binary log
in one chunk, during @code{COMMIT}, the to ensure and @code{ROLLBACK}:ed
transactions are not stored. @xref{Binary log}.
The following commands automaticly ends an transaction (as if you had done
a @code{COMMIT} before executing the command):
@multitable @columnfractions .33 .33 .33
@item @code{ALTER TABLE} @tab @code{BEGIN} @tab @code{CREATE INDEX}
@item @code{DROP DATABASE} @tab @code{DROP TABLE} @tab @code{RENAME TABLE}
@item @code{TRUNCATE}
@end multitable
@findex LOCK TABLES
@findex UNLOCK TABLES
@node LOCK TABLES, SET OPTION, COMMIT, Reference
@section @code{LOCK TABLES/UNLOCK TABLES} Syntax
@example
LOCK TABLES tbl_name [AS alias] @{READ | [READ LOCAL] | [LOW_PRIORITY] WRITE@}
[, tbl_name @{READ | [LOW_PRIORITY] WRITE@} ...]
...
UNLOCK TABLES
@end example
@code{LOCK TABLES} locks tables for the current thread. @code{UNLOCK
TABLES} releases any locks held by the current thread. All tables that
are locked by the current thread are automatically unlocked when the
thread issues another @code{LOCK TABLES}, or when the connection to the
server is closed.
If a thread obtains a @code{READ} lock on a table, that thread (and all other
threads) can only read from the table. If a thread obtains a @code{WRITE}
lock on a table, then only the thread holding the lock can @code{READ} from
or @code{WRITE} to the table. Other threads are blocked.
The difference between @code{READ LOCAL} and @code{READ} is that
@code{READ LOCAL} allows non-conflicting @code{INSERT} statements to
execute while the lock is held. This can't however be used if you are
going to manipulate the database files outside @strong{MySQL} while you
hold the lock.
Each thread waits (without timing out) until it obtains all the locks it has
requested.
@code{WRITE} locks normally have higher priority than @code{READ} locks, to
ensure that updates are processed as soon as possible. This means that if one
thread obtains a @code{READ} lock and then another thread requests a
@code{WRITE} lock, subsequent @code{READ} lock requests will wait until the
@code{WRITE} thread has gotten the lock and released it. You can use
@code{LOW_PRIORITY WRITE} locks to allow other threads to obtain @code{READ}
locks while the thread is waiting for the @code{WRITE} lock. You should only
use @code{LOW_PRIORITY WRITE} locks if you are sure that there will
eventually be a time when no threads will have a @code{READ} lock.
When you use @code{LOCK TABLES}, you must lock all tables that you are
going to use and you must use the same alias that you are going to use
in your queries! If you are using a table multiple times in a query
(with aliases), you must get a lock for each alias! This policy ensures
that table locking is deadlock free and makes the locking code smaller,
simpler and much faster.
Note that you should @strong{NOT} lock any tables that you are using with
@code{INSERT DELAYED}. This is because that in this case the @code{INSERT}
is done by a separate thread.
Normally, you don't have to lock tables, as all single @code{UPDATE} statements
are atomic; no other thread can interfere with any other currently executing
SQL statement. There are a few cases when you would like to lock tables
anyway:
@itemize @bullet
@item
If you are going to run many operations on a bunch of tables, it's much
faster to lock the tables you are going to use. The downside is, of course,
that no other thread can update a @code{READ}-locked table and no other
thread can read a @code{WRITE}-locked table.
@item
@strong{MySQL} doesn't support a transaction environment, so you must use
@code{LOCK TABES} if you want to ensure that no other thread comes between a
@code{SELECT} and an @code{UPDATE}. The example shown below
requires @code{LOCK TABLES} in order to execute safely:
@example
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select sum(value) from trans where customer_id= some_id;
mysql> update customer set total_value=sum_from_previous_statement
where customer_id=some_id;
mysql> UNLOCK TABLES;
@end example
Without @code{LOCK TABLES}, there is a chance that another thread might
insert a new row in the @code{trans} table between execution of the
@code{SELECT} and @code{UPDATE} statements.
@end itemize
By using incremental updates (@code{UPDATE customer SET
value=value+new_value}) or the @code{LAST_INSERT_ID()} function, you can
avoid using @code{LOCK TABLES} in many cases.
You can also solve some cases by using the user-level lock functions
@code{GET_LOCK()} and @code{RELEASE_LOCK()}. These locks are saved in a hash
table in the server and implemented with @code{pthread_mutex_lock()} and
@code{pthread_mutex_unlock()} for high speed.
@xref{Miscellaneous functions}.
See @ref{Internal locking}, for more information on locking policy.
You can also lock all tables in all databases with read locks with the
@code{FLUSH TABLES WITH READ LOCK} command. @xref{FLUSH}. This is very
convinient way to get backups if you have a file system, like Veritas,
that can take snapshots in time.
@strong{NOTE}: @code{LOCK TABLES} is not transaction safe and will
automaticly commit any active transactions before attempting to lock the
tables.
@findex SET OPTION
@node SET OPTION, GRANT, LOCK TABLES, Reference
@section @code{SET} Syntax
@example
SET [OPTION] SQL_VALUE_OPTION= value, ...
@end example
@code{SET OPTION} sets various options that affect the operation of the
server or your client. Any option you set remains in effect until the
current session ends, or until you set the option to a different value.
@table @code
@item CHARACTER SET character_set_name | DEFAULT
This maps all strings from and to the client with the given mapping.
Currently the only option for @code{character_set_name} is
@code{cp1251_koi8}, but you can easily add new mappings by editing the
@file{sql/convert.cc} file in the @strong{MySQL} source distribution. The
default mapping can be restored by using a @code{character_set_name} value of
@code{DEFAULT}.
Note that the syntax for setting the @code{CHARACTER SET} option differs
from the syntax for setting the other options.
@item PASSWORD = PASSWORD('some password')
@cindex passwords, setting
Set the password for the current user. Any non-anonymous user can change his
own password!
@item PASSWORD FOR user = PASSWORD('some password')
Set the password for a specific user on the current server host. Only a user
with access to the @code{mysql} database can do this. The user should be
given in @code{user@@hostname} format, where @code{user} and @code{hostname}
are exactly as they are listed in the @code{User} and @code{Host} columns of
the @code{mysql.user} table entry. For example, if you had an entry with
@code{User} and @code{Host} fields of @code{'bob'} and @code{'%.loc.gov'},
you would write:
@example
mysql> SET PASSWORD FOR bob@@"%.loc.gov" = PASSWORD("newpass");
or
mysql> UPDATE mysql.user SET password=PASSWORD("newpass") where user="bob' and host="%.loc.gov";
@end example
@item SQL_AUTO_IS_NULL = 0 | 1
If set to @code{1} (default) then one can find the last inserted row
for a table with an auto_increment row with the following construct:
@code{WHERE auto_increment_column IS NULL}. This is used by some
ODBC programs like Access.
@item AUTOCOMMIT= 0 | 1
If set to @code{1} all changes to a table will be done at once. To start
a multi-command transaction, you have to use the @code{BEGIN}
statement. @xref{COMMIT}. If set to @code{0} you have to use @code{COMMIT} /
@code{ROLLBACK} to accept/revoke that transaction. @xref{COMMIT}. Note
that when you change from not @code{AUTOCOMMIT} mode to
@code{AUTOCOMMIT} mode, @strong{MySQL} will do an automatic
@code{COMMIT} on any open transactions.
@item SQL_BIG_TABLES = 0 | 1
@cindex table is full
If set to @code{1}, all temporary tables are stored on disk rather than in
memory. This will be a little slower, but you will not get the error
@code{The table tbl_name is full} for big @code{SELECT} operations that
require a large temporary table. The default value for a new connection is
@code{0} (that is, use in-memory temporary tables).
@item SQL_BIG_SELECTS = 0 | 1
If set to @code{0}, @strong{MySQL} will abort if a @code{SELECT} is attempted
that probably will take a very long time. This is useful when an inadvisable
@code{WHERE} statement has been issued. A big query is defined as a
@code{SELECT} that probably will have to examine more than
@code{max_join_size} rows. The default value for a new connection is
@code{1} (which will allow all @code{SELECT} statements).
@item SQL_BUFFER_RESULT = 0 | 1
@code{SQL_BUFFER_RESULT} will force the result from @code{SELECT}'s
to be put into a temporary table. This will help @strong{MySQL} free the
table locks early and will help in cases where it takes a long time to
send the result set to the client.
@item SQL_LOW_PRIORITY_UPDATES = 0 | 1
If set to @code{1}, all @code{INSERT}, @code{UPDATE}, @code{DELETE}, and
and @code{LOCK TABLE WRITE} statements wait until there is no pending
@code{SELECT} or @code{LOCK TABLE READ} on the affected table.
@item SQL_MAX_JOIN_SIZE = value | DEFAULT
Don't allow @code{SELECT}s that will probably need to examine more than
@code{value} row combinations. By setting this value, you can catch
@code{SELECT}s where keys are not used properly and that would probably
take a long time. Setting this to a value other than @code{DEFAULT} will reset
the @code{SQL_BIG_SELECTS} flag. If you set the @code{SQL_BIG_SELECTS}
flag again, the @code{SQL_MAX_JOIN_SIZE} variable will be ignored.
You can set a default value for this variable by starting @code{mysqld} with
@code{-O max_join_size=#}.
@item SQL_SAFE_MODE = 0 | 1
If set to @code{1}, @strong{MySQL} will abort if an @code{UPDATE} or
@code{DELETE} is attempted that doesn't use a key or @code{LIMIT} in the
@code{WHERE} clause. This makes it possible to catch wrong updates
when creating SQL commands by hand.
@item SQL_SELECT_LIMIT = value | DEFAULT
The maximum number of records to return from @code{SELECT} statements. If
a @code{SELECT} has a @code{LIMIT} clause, the @code{LIMIT} takes precedence
over the value of @code{SQL_SELECT_LIMIT}. The default value for a new
connection is ``unlimited.'' If you have changed the limit, the default value
can be restored by using a @code{SQL_SELECT_LIMIT} value of @code{DEFAULT}.
@item SQL_LOG_OFF = 0 | 1
If set to @code{1}, no logging will be done to the standard log for this
client, if the client has the @strong{process} privilege. This does not
affect the update log!
@item SQL_LOG_UPDATE = 0 | 1
If set to @code{0}, no logging will be done to the update log for the client,
if the client has the @strong{process} privilege. This does not affect the
standard log!
@item SQL_QUOTE_SHOW_CREATE = 0 | 1
If set to @code{1}, @code{SHOW CREATE TABLE} will quote
table and column names. This is @strong{on} by default,
for replication of tables with fancy column names to work.
@ref{SHOW CREATE TABLE, , @code{SHOW CREATE TABLE}}.
@item TIMESTAMP = timestamp_value | DEFAULT
Set the time for this client. This is used to get the original timestamp if
you use the update log to restore rows.
@item LAST_INSERT_ID = #
Set the value to be returned from @code{LAST_INSERT_ID()}. This is stored in
the update log when you use @code{LAST_INSERT_ID()} in a command that updates
a table.
@item INSERT_ID = #
Set the value to be used by the following @code{INSERT} or @code{ALTER TABLE}
command when inserting an @code{AUTO_INCREMENT} value. This is mainly used
with the update log.
@end table
@cindex privileges, granting
@cindex privileges, revoking
@cindex global privileges
@cindex revoking, privleges
@cindex granting, privleges
@findex GRANT
@findex REVOKE
@node GRANT, CREATE INDEX, SET OPTION, Reference
@section @code{GRANT} and @code{REVOKE} Syntax
@example
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON @{tbl_name | * | *.* | db_name.*@}
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[WITH GRANT OPTION]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON @{tbl_name | * | *.* | db_name.*@}
FROM user_name [, user_name ...]
@end example
@code{GRANT} is implemented in @strong{MySQL} Version 3.22.11 or later. For
earlier @strong{MySQL} versions, the @code{GRANT} statement does nothing.
The @code{GRANT} and @code{REVOKE} commands allow system administrators to
grant and revoke rights to @strong{MySQL} users at four privilege levels:
@table @strong
@item Global level
Global privileges apply to all databases on a given server. These privileges
are stored in the @code{mysql.user} table.
@item Database level
Database privileges apply to all tables in a given database. These privileges
are stored in the @code{mysql.db} and @code{mysql.host} tables.
@item Table level
Table privileges apply to all columns in a given table. These privileges are
stored in the @code{mysql.tables_priv} table.
@item Column level
Column privileges apply to single columns in a given table. These privileges are
stored in the @code{mysql.columns_priv} table.
@end table
For examples of how @code{GRANT} works, see @ref{Adding users}.
For the @code{GRANT} and @code{REVOKE} statements, @code{priv_type} may be
specified as any of the following:
@example
ALL PRIVILEGES FILE RELOAD
ALTER INDEX SELECT
CREATE INSERT SHUTDOWN
DELETE PROCESS UPDATE
DROP REFERENCES USAGE
@end example
@code{ALL} is a synonym for @code{ALL PRIVILEGES}. @code{REFERENCES} is not
yet implemented. @code{USAGE} is currently a synonym for ``no privileges.''
It can be used when you want to create a user that has no privileges.
To revoke the @strong{grant} privilege from a user, use a @code{priv_type}
value of @code{GRANT OPTION}:
@example
REVOKE GRANT OPTION ON ... FROM ...;
@end example
The only @code{priv_type} values you can specify for a table are @code{SELECT},
@code{INSERT}, @code{UPDATE}, @code{DELETE}, @code{CREATE}, @code{DROP},
@code{GRANT}, @code{INDEX}, and @code{ALTER}.
The only @code{priv_type} values you can specify for a column (that is, when
you use a @code{column_list} clause) are @code{SELECT}, @code{INSERT}, and
@code{UPDATE}.
You can set global privileges by using @code{ON *.*} syntax. You can set
database privileges by using @code{ON db_name.*} syntax. If you specify
@code{ON *} and you have a current database, you will set the privileges for
that database. (@strong{WARNING:} If you specify @code{ON *} and you
@emph{don't} have a current database, you will affect the global privileges!)
In order to accommodate granting rights to users from arbitrary hosts,
@strong{MySQL} supports specifying the @code{user_name} value in the form
@code{user@@host}. If you want to specify a @code{user} string
containing special characters (such as @samp{-}), or a @code{host} string
containing special characters or wild-card characters (such as @samp{%}), you
can quote the user or host name (for example, @code{'test-user'@@'test-hostname'}).
You can specify wild cards in the hostname. For example,
@code{user@@"%.loc.gov"} applies to @code{user} for any host in the
@code{loc.gov} domain, and @code{user@@"144.155.166.%"} applies to @code{user}
for any host in the @code{144.155.166} class C subnet.
The simple form @code{user} is a synonym for @code{user@@"%"}.
@strong{NOTE:} If you allow anonymous users to connect to the @strong{MySQL}
server (which is the default), you should also add all local users as
@code{user@@localhost} because otherwise the anonymous user entry for the
local host in the @code{mysql.user} table will be used when the user tries to
log into the @strong{MySQL} server from the local machine! Anonymous users
are defined by inserting entries with @code{User=''} into the
@code{mysql.user} table. You can verify if this applies to you by executing
this query:
@example
mysql> SELECT Host,User FROM mysql.user WHERE User='';
@end example
For the moment, @code{GRANT} only supports host, table, database, and
column names up to 60 characters long. A user name can be up to 16
characters.
The privileges for a table or column are formed from the
logical OR of the privileges at each of the four privilege
levels. For example, if the @code{mysql.user} table specifies that a
user has a global @strong{select} privilege, this can't be denied by an
entry at the database, table, or column level.
The privileges for a column can be calculated as follows:
@example
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
@end example
In most cases, you grant rights to a user at only one of the privilege
levels, so life isn't normally as complicated as above. The details of the
privilege-checking procedure are presented in
@ref{Privilege system}.
If you grant privileges for a user/hostname combination that does not exist
in the @code{mysql.user} table, an entry is added and remains there until
deleted with a @code{DELETE} command. In other words, @code{GRANT} may
create @code{user} table entries, but @code{REVOKE} will not remove them;
you must do that explicitly using @code{DELETE}.
@cindex passwords, setting