MySQL数据库

开发平台：

Visual C++

manual.texi：源码内容

In @strong{MySQL} Version 3.22.12 or later,
if a new user is created or if you have global grant privileges, the user's
password will be set to the password specified by the @code{IDENTIFIED BY}
clause, if one is given. If the user already had a password, it is replaced
by the new one.
@strong{WARNING:} If you create a new user but do not specify an
@code{IDENTIFIED BY} clause, the user has no password. This is insecure.
Passwords can also be set with the @code{SET PASSWORD} command.
@xref{SET OPTION, , @code{SET OPTION}}.
If you grant privileges for a database, an entry in the @code{mysql.db}
table is created if needed. When all privileges for the database have been
removed with @code{REVOKE}, this entry is deleted.
If a user doesn't have any privileges on a table, the table is not displayed
when the user requests a list of tables (for example, with a @code{SHOW TABLES}
statement).
The @code{WITH GRANT OPTION} clause gives the user the ability to give
to other users any privileges the user has at the specified privilege level.
You should be careful to whom you give the @strong{grant} privilege, as two
users with different privileges may be able to join privileges!
You cannot grant another user a privilege you don't have yourself;
the @strong{grant} privilege allows you to give away only those privileges
you possess.
Be aware that when you grant a user the @strong{grant} privilege at a
particular privilege level, any privileges the user already possesses (or
is given in the future!) at that level are also grantable by that user.
Suppose you grant a user the @strong{insert} privilege on a database. If
you then grant the @strong{select} privilege on the database and specify
@code{WITH GRANT OPTION}, the user can give away not only the @strong{select}
privilege, but also @strong{insert}. If you then grant the @strong{update}
privilege to the user on the database, the user can give away the
@strong{insert}, @strong{select} and @strong{update}.
You should not grant @strong{alter} privileges to a normal user. If you
do that, the user can try to subvert the privilege system by renaming
tables!
Note that if you are using table or column privileges for even one user, the
server examines table and column privileges for all users and this will slow
down @strong{MySQL} a bit.
When @code{mysqld} starts, all privileges are read into memory.
Database, table, and column privileges take effect at once, and
user-level privileges take effect the next time the user connects.
Modifications to the grant tables that you perform using @code{GRANT} or
@code{REVOKE} are noticed by the server immediately.
If you modify the grant tables manually (using @code{INSERT}, @code{UPDATE},
etc.), you should execute a @code{FLUSH PRIVILEGES} statement or run
@code{mysqladmin flush-privileges} to tell the server to reload the grant
tables.
@xref{Privilege changes}.
@cindex ANSI SQL, differences from
The biggest differences between the ANSI SQL and @strong{MySQL} versions of
@code{GRANT} are:
@itemize @bullet
@item
In @strong{MySQL} privileges are given for an username + hostname combination
and not only for an username.
@item
ANSI SQL doesn't have global or database-level privileges, and ANSI SQL
doesn't support all privilege types that @strong{MySQL} supports.
@strong{MySQL} doesn't support the ANSI SQL @code{TRIGGER}, @code{EXECUTE} or
@code{UNDER} privileges.
@item
ANSI SQL privileges are structured in a hierarchal manner. If you remove
an user, all privileges the user has granted are revoked. In
@strong{MySQL} the granted privileges are not automaticly revoked, but
you have to revoke these yourself if needed.
@item
If you in @strong{MySQL} have the @code{INSERT} grant on only part of the
columns in a table, you can execute @code{INSERT} statements on the
table; The columns for which you don't have the @code{INSERT} privilege
will set to their default values. ANSI SQL requires you to have the
@code{INSERT} privilege on all columns.
@item
When you drop a table in ANSI SQL, all privileges for the table are revoked.
If you revoke a privilege in ANSI SQL, all privileges that were granted based
on this privilege are also revoked. In @strong{MySQL}, privileges can be
dropped only with explicit @code{REVOKE} commands or by manipulating the
@strong{MySQL} grant tables.
@end itemize
@cindex indexes
@cindex indexes, multi-part
@cindex multi-part index
@findex CREATE INDEX
@node CREATE INDEX, DROP INDEX, GRANT, Reference
@section @code{CREATE INDEX} Syntax
@example
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],... )
@end example
The @code{CREATE INDEX} statement doesn't do anything in @strong{MySQL} prior
to Version 3.22. In Version 3.22 or later, @code{CREATE INDEX} is mapped to an
@code{ALTER TABLE} statement to create indexes.
@xref{ALTER TABLE, , @code{ALTER TABLE}}.
Normally, you create all indexes on a table at the time the table itself
is created with @code{CREATE TABLE}.
@xref{CREATE TABLE, , @code{CREATE TABLE}}.
@code{CREATE INDEX} allows you to add indexes to existing tables.
A column list of the form @code{(col1,col2,...)} creates a multiple-column
index. Index values are formed by concatenating the values of the given
columns.
For @code{CHAR} and @code{VARCHAR} columns, indexes can be created that
use only part of a column, using @code{col_name(length)} syntax. (On
@code{BLOB} and @code{TEXT} columns the length is required). The
statement shown below creates an index using the first 10 characters of
the @code{name} column:
@example
mysql> CREATE INDEX part_of_name ON customer (name(10));
@end example
Because most names usually differ in the first 10 characters, this index should
not be much slower than an index created from the entire @code{name} column.
Also, using partial columns for indexes can make the index file much smaller,
which could save a lot of disk space and might also speed up @code{INSERT}
operations!
Note that you can only add an index on a column that can have @code{NULL}
values or on a @code{BLOB}/@code{TEXT} column if you are using
@strong{MySQL} Version 3.23.2 or newer and are using the @code{MyISAM}
table type.
For more information about how @strong{MySQL} uses indexes, see
@ref{MySQL indexes, , @strong{MySQL} indexes}.
@code{FULLTEXT} indexes can index only @code{VARCHAR} and
@code{TEXT} columns, and only in @code{MyISAM} tables. @code{FULLTEXT} indexes
are available in @strong{MySQL} Version 3.23.23 and later.
@ref{MySQL full-text search}.
@findex DROP INDEX
@node DROP INDEX, Comments, CREATE INDEX, Reference
@section @code{DROP INDEX} Syntax
@example
DROP INDEX index_name ON tbl_name
@end example
@code{DROP INDEX} drops the index named @code{index_name} from the table
@code{tbl_name}. @code{DROP INDEX} doesn't do anything in @strong{MySQL}
prior to Version 3.22. In Version 3.22 or later, @code{DROP INDEX} is mapped to an
@code{ALTER TABLE} statement to drop the index.
@xref{ALTER TABLE, , @code{ALTER TABLE}}.
@findex Comment syntax
@cindex comments, adding
@node Comments, CREATE FUNCTION, DROP INDEX, Reference
@section Comment Syntax
The @strong{MySQL} server supports the @code{# to end of line}, @code{--
to end of line} and @code{/* in-line or multiple-line */} comment
styles:
@example
mysql> select 1+1; # This comment continues to the end of line
mysql> select 1+1; -- This comment continues to the end of line
mysql> select 1 /* this is an in-line comment */ + 1;
mysql> select 1+
/*
this is a
multiple-line comment
*/
1;
@end example
Note that the @code{--} comment style requires you to have at least one space
after the @code{--}!
Although the server understands the comment syntax just described,
there are some limitations on the way that the @code{mysql} client
parses @code{/* ... */} comments:
@itemize @bullet
@item
Single-quote and double-quote characters are taken to indicate the beginning
of a quoted string, even within a comment. If the quote is not matched by a
second quote within the comment, the parser doesn't realize the comment has
ended. If you are running @code{mysql} interactively, you can tell that it
has gotten confused like this because the prompt changes from @code{mysql>}
to @code{'>} or @code{">}.
@item
A semicolon is taken to indicate the end of the current SQL statement
and anything following it to indicate the beginning of the next statement.
@end itemize
These limitations apply both when you run @code{mysql} interactively
and when you put commands in a file and tell @code{mysql} to read its
input from that file with @code{mysql < some-file}.
@strong{MySQL} doesn't support the @samp{--} ANSI SQL comment style.
@xref{Missing comments}.
@findex CREATE FUNCTION
@findex DROP FUNCTION
@findex UDF functions
@findex User-defined functions
@findex Functions, user-defined
@node CREATE FUNCTION, Reserved words, Comments, Reference
@section @code{CREATE FUNCTION/DROP FUNCTION} Syntax
@example
CREATE [AGGREGATE] FUNCTION function_name RETURNS @{STRING|REAL|INTEGER@}
SONAME shared_library_name
DROP FUNCTION function_name
@end example
A user-definable function (UDF) is a way to extend @strong{MySQL} with a new
function that works like native (built in) @strong{MySQL} functions such as
@code{ABS()} and @code{CONCAT()}.
@code{AGGREGATE} is a new option for @strong{MySQL} Version 3.23. An
@code{AGGREGATE} function works exactly like a native @strong{MySQL}
@code{GROUP} function like @code{SUM} or @code{COUNT()}.
@code{CREATE FUNCTION} saves the function's name, type, and shared library
name in the @code{mysql.func} system table. You must have the
@strong{insert} and @strong{delete} privileges for the @code{mysql} database
to create and drop functions.
All active functions are reloaded each time the server starts, unless
you start @code{mysqld} with the @code{--skip-grant-tables} option. In
this case, UDF initialization is skipped and UDFs are unavailable.
(An active function is one that has been loaded with @code{CREATE FUNCTION}
and not removed with @code{DROP FUNCTION}.)
For instructions on writing user-definable functions, see @ref{Adding
functions}. For the UDF mechanism to work, functions must be written in C or
C++, your operating system must support dynamic loading and you must have
compiled @code{mysqld} dynamically (not statically).
@cindex keywords
@cindex reserved words, exceptions
@node Reserved words, , CREATE FUNCTION, Reference
@section Is MySQL Picky About Reserved Words?
A common problem stems from trying to create a table with column names that
use the names of datatypes or functions built into @strong{MySQL}, such as
@code{TIMESTAMP} or @code{GROUP}. You're allowed to do it (for example,
@code{ABS} is an allowed column name), but whitespace is not allowed between
a function name and the @samp{(} when using functions whose names are also
column names.
The following words are explicitly reserved in @strong{MySQL}. Most of
them are forbidden by ANSI SQL92 as column and/or table names
(for example, @code{group}).
A few are reserved because @strong{MySQL} needs them and is
(currently) using a @code{yacc} parser:
@c This is fixed by including the symbols table from lex.h here and then running
@c fix-mysql-reserved-words in emacs (or let David do it):
@c (defun fix-mysql-reserved-words ()
@c (interactive)
@c (let ((cnt 0))
@c (insert "n@item ")
@c (while (looking-at "[ t]*{ +"\([^"]+\)"[ t]*,.*n")
@c (replace-match "@code{\1}")
@c (incf cnt)
@c (if (> cnt 3)
@c (progn
@c (setf cnt 0)
@c (insert "n@item "))
@c (insert " @tab ")))))
@c But remove the non alphanumeric entries by hand first.
@c Updated after 3.23.4 990928 by David
@multitable @columnfractions .25 .25 .25 .25
@item @code{action} @tab @code{add} @tab @code{aggregate} @tab @code{all}
@item @code{alter} @tab @code{after} @tab @code{and} @tab @code{as}
@item @code{asc} @tab @code{avg} @tab @code{avg_row_length} @tab @code{auto_increment}
@item @code{between} @tab @code{bigint} @tab @code{bit} @tab @code{binary}
@item @code{blob} @tab @code{bool} @tab @code{both} @tab @code{by}
@item @code{cascade} @tab @code{case} @tab @code{char} @tab @code{character}
@item @code{change} @tab @code{check} @tab @code{checksum} @tab @code{column}
@item @code{columns} @tab @code{comment} @tab @code{constraint} @tab @code{create}
@item @code{cross} @tab @code{current_date} @tab @code{current_time} @tab @code{current_timestamp}
@item @code{data} @tab @code{database} @tab @code{databases} @tab @code{date}
@item @code{datetime} @tab @code{day} @tab @code{day_hour} @tab @code{day_minute}
@item @code{day_second} @tab @code{dayofmonth} @tab @code{dayofweek} @tab @code{dayofyear}
@item @code{dec} @tab @code{decimal} @tab @code{default} @tab @code{delayed}
@item @code{delay_key_write} @tab @code{delete} @tab @code{desc} @tab @code{describe}
@item @code{distinct} @tab @code{distinctrow} @tab @code{double} @tab @code{drop}
@item @code{end} @tab @code{else} @tab @code{escape} @tab @code{escaped}
@item @code{enclosed} @tab @code{enum} @tab @code{explain} @tab @code{exists}
@item @code{fields} @tab @code{file} @tab @code{first} @tab @code{float}
@item @code{float4} @tab @code{float8} @tab @code{flush} @tab @code{foreign}
@item @code{from} @tab @code{for} @tab @code{full} @tab @code{function}
@item @code{global} @tab @code{grant} @tab @code{grants} @tab @code{group}
@item @code{having} @tab @code{heap} @tab @code{high_priority} @tab @code{hour}
@item @code{hour_minute} @tab @code{hour_second} @tab @code{hosts} @tab @code{identified}
@item @code{ignore} @tab @code{in} @tab @code{index} @tab @code{infile}
@item @code{inner} @tab @code{insert} @tab @code{insert_id} @tab @code{int}
@item @code{integer} @tab @code{interval} @tab @code{int1} @tab @code{int2}
@item @code{int3} @tab @code{int4} @tab @code{int8} @tab @code{into}
@item @code{if} @tab @code{is} @tab @code{isam} @tab @code{join}
@item @code{key} @tab @code{keys} @tab @code{kill} @tab @code{last_insert_id}
@item @code{leading} @tab @code{left} @tab @code{length} @tab @code{like}
@item @code{lines} @tab @code{limit} @tab @code{load} @tab @code{local}
@item @code{lock} @tab @code{logs} @tab @code{long} @tab @code{longblob}
@item @code{longtext} @tab @code{low_priority} @tab @code{max} @tab @code{max_rows}
@item @code{match} @tab @code{mediumblob} @tab @code{mediumtext} @tab @code{mediumint}
@item @code{middleint} @tab @code{min_rows} @tab @code{minute} @tab @code{minute_second}
@item @code{modify} @tab @code{month} @tab @code{monthname} @tab @code{myisam}
@item @code{natural} @tab @code{numeric} @tab @code{no} @tab @code{not}
@item @code{null} @tab @code{on} @tab @code{optimize} @tab @code{option}
@item @code{optionally} @tab @code{or} @tab @code{order} @tab @code{outer}
@item @code{outfile} @tab @code{pack_keys} @tab @code{partial} @tab @code{password}
@item @code{precision} @tab @code{primary} @tab @code{procedure} @tab @code{process}
@item @code{processlist} @tab @code{privileges} @tab @code{read} @tab @code{real}
@item @code{references} @tab @code{reload} @tab @code{regexp} @tab @code{rename}
@item @code{replace} @tab @code{restrict} @tab @code{returns} @tab @code{revoke}
@item @code{rlike} @tab @code{row} @tab @code{rows} @tab @code{second}
@item @code{select} @tab @code{set} @tab @code{show} @tab @code{shutdown}
@item @code{smallint} @tab @code{soname} @tab @code{sql_big_tables} @tab @code{sql_big_selects}
@item @code{sql_low_priority_updates} @tab @code{sql_log_off} @tab @code{sql_log_update} @tab @code{sql_select_limit}
@item @code{sql_small_result} @tab @code{sql_big_result} @tab @code{sql_warnings} @tab @code{straight_join}
@item @code{starting} @tab @code{status} @tab @code{string} @tab @code{table}
@item @code{tables} @tab @code{temporary} @tab @code{terminated} @tab @code{text}
@item @code{then} @tab @code{time} @tab @code{timestamp} @tab @code{tinyblob}
@item @code{tinytext} @tab @code{tinyint} @tab @code{trailing} @tab @code{to}
@item @code{type} @tab @code{use} @tab @code{using} @tab @code{unique}
@item @code{unlock} @tab @code{unsigned} @tab @code{update} @tab @code{usage}
@item @code{values} @tab @code{varchar} @tab @code{variables} @tab @code{varying}
@item @code{varbinary} @tab @code{with} @tab @code{write} @tab @code{when}
@item @code{where} @tab @code{year} @tab @code{year_month} @tab @code{zerofill}
@end multitable
The following symbols (from the table above) are disallowed by ANSI SQL
but allowed by @strong{MySQL} as column/table names. This is because some
of these names are very natural names and a lot of people have already
used them.
@itemize @bullet
@item @code{ACTION}
@item @code{BIT}
@item @code{DATE}
@item @code{ENUM}
@item @code{NO}
@item @code{TEXT}
@item @code{TIME}
@item @code{TIMESTAMP}
@end itemize
@cindex table types, choosing
@cindex @code{BDB} table type
@cindex @code{Berkeley_db} table type
@cindex @code{GEMINI} table type
@cindex @code{HEAP} table type
@cindex @code{ISAM} table type
@cindex @code{INNOBASE} table type
@cindex @code{MERGE} table type
@cindex MySQL table types
@cindex @code{MyISAM} table type
@cindex types, of tables
@node Table types, Tutorial, Reference, Top
@chapter MySQL Table Types
As of @strong{MySQL} Version 3.23.6, you can choose between three basic
table formats (@code{ISAM}, @code{HEAP} and @code{MyISAM}. Newer
@strong{MySQL} may support additional table type (@code{BDB},
@code{GEMINI} or @code{INNOBASE}), depending on how you compile it.
When you create a new table, you can tell @strong{MySQL} which table
type it should use for the table. @strong{MySQL} will always create a
@code{.frm} file to hold the table and column definitions. Depending on
the table type, the index and data will be stored in other files.
The default table type in @strong{MySQL} is @code{MyISAM}. If you are
trying to use a table type that is not incompiled or activated,
@strong{MySQL} will instead create a table of type @code{MyISAM}.
You can convert tables between different types with the @code{ALTER
TABLE} statement. @xref{ALTER TABLE, , @code{ALTER TABLE}}.
Note that @strong{MySQL} supports two different kinds of
tables. Transaction-safe tables (@code{BDB}, @code{INNOBASE} or
@code{GEMINI}) and not transaction-safe tables (@code{HEAP}, @code{ISAM},
@code{MERGE}, and @code{MyISAM}).
Advantages of transaction-safe tables (TST):
@itemize @bullet
@item
Safer. Even if @strong{MySQL} crashes or you get hardware problems, you
can get your data back, either by automatic recovery or from a backup
+ the transaction log.
@item
You can combine many statements and accept these all in one go with
the @code{COMMIT} command.
@item
You can execute @code{ROLLBACK} to ignore your changes (if you are not
running in auto commit mode).
@item
If an update fails, all your changes will be restored. (With NTST tables all
changes that have taken place are permanent)
@end itemize
Advantages of not transaction-safe tables (NTST):
@itemize @bullet
@item
Much faster as there is no transcation overhead.
@item
Will use less disk space as there is no overhead of transactions.
@item
Will use less memory to do updates.
@end itemize
You can combine TST and NTST tables in the same statements to get the best
of both worlds.
@menu
* MyISAM:: MyISAM tables
* MERGE:: MERGE tables
* ISAM:: ISAM tables
* HEAP:: HEAP tables
* BDB:: BDB or Berkeley_db tables
* GEMINI:: GEMINI tables
* INNOBASE:: INNOBASE tables
@end menu
@node MyISAM, MERGE, Table types, Table types
@section MyISAM Tables
@code{MyISAM} is the default table type in @strong{MySQL} Version 3.23. It's
based on the @code{ISAM} code and has a lot of useful extensions.
The index is stored in a file with the @code{.MYI} (MYIndex) extension,
and the data is stored in a file with the @code{.MYD} (MYData) extension.
You can check/repair @code{MyISAM} tables with the @code{myisamchk}
utility. @xref{Crash recovery}.
The following is new in @code{MyISAM}:
@itemize @bullet
@item
There is a flag in the @code{MyISAM} file that indicates whether or not
the table was closed correctly. If @code{mysqld} is started with
@code{--myisam-recover}, @code{MyISAM} tables will automaticly be
checked and/or repaired on open if the table wasn't closed properly.
@item
You can @code{INSERT} new rows in a table without deleted rows,
while other threads are reading from the table.
@item
Support for big files (63-bit) on filesystems/operating systems that
support big files.
@item
All data is stored with the low byte first. This makes the data machine
and OS independent. The only requirement is that the machine uses
two's-complement signed integers (as every machine for the last 20 years
has) and IEEE floating-point format (also totally dominant among
mainstream machines). The only area of machines that may not support
binary compatibility are embedded systems (because they sometimes have
peculiar processors).
There is no big speed penalty in storing data low byte first; The bytes
in a table row is normally unaligned and it doesn't take that much more
power to read an unaligned byte in order than in reverse order. The
actual fetch-column-value code is also not time critical compared to
other code.
@item
All number keys are stored with high byte first to give better index
compression.
@item
Internal handling of one @code{AUTO_INCREMENT} column. @code{MyISAM}
will automatically update this on @code{INSERT/UPDATE}. The
@code{AUTO_INCREMENT} value can be reset with @code{myisamchk}. This
will make @code{AUTO_INCREMENT} columns faster (at least 10 %) and old
numbers will not be reused as with the old ISAM. Note that when an
@code{AUTO_INCREMENT} is defined on the end of a multi-part-key the old
behavior is still present.
@item
When inserted in sorted order (as when you are using an @code{AUTO_INCREMENT}
column) the key tree will be split so that the high node only contains one
key. This will improve the space utilization in the key tree.
@item
@code{BLOB} and @code{TEXT} columns can be indexed.
@item
@code{NULL} values are allowed in indexed columns. This takes 0-1
bytes/key.
@item
Maximum key length is 500 bytes by default (can be changed by
recompiling). In cases of keys longer than 250 bytes, a bigger key
block size than the default of 1024 bytes is used for this key.
@item
Maximum number of keys/table is 32 as default. This can be enlarged to 64
without having to recompile @code{myisamchk}.
@item
@code{myisamchk} will mark tables as checked if one runs it with
@code{--update-state}. @code{myisamchk --fast} will only check those
tables that don't have this mark.
@item
@code{myisamchk -a} stores statistics for key parts (and not only for
whole keys as in @code{ISAM}).
@item
Dynamic size rows will now be much less fragmented when mixing deletes
with updates and inserts. This is done by automatically combining adjacent
deleted blocks and by extending blocks if the next block is deleted.
@item
@code{myisampack} can pack @code{BLOB} and @code{VARCHAR} columns.
@end itemize
@code{MyISAM} also supports the following things, which @strong{MySQL}
will be able to use in the near future:
@itemize @bullet
@item
Support for a true @code{VARCHAR} type; A @code{VARCHAR} column starts
with a length stored in 2 bytes.
@item
Tables with @code{VARCHAR} may have fixed or dynamic record length.
@item
@code{VARCHAR} and @code{CHAR} may be up to 64K.
All key segments have their own language definition. This will enable
@strong{MySQL} to have different language definitions per column.
@item
A hashed computed index can be used for @code{UNIQUE}. This will allow
you to have @code{UNIQUE} on any combination of columns in a table. (You
can't search on a @code{UNIQUE} computed index, however.)
@end itemize
Note that index files are usually much smaller with @code{MyISAM} than with
@code{ISAM}. This means that @code{MyISAM} will normally use less
system resources than @code{ISAM}, but will need more CPU when inserting
data into a compressed index.
The following options to @code{mysqld} can be used to change the behavior of
@code{MyISAM} tables:
@multitable @columnfractions .40 .60
@item @strong{Option} @tab @strong{Meaning}
@item @code{--myisam-recover=#} @tab Automatic recover of crashed tables.
@item @code{-O myisam_sort_buffer_size=#} @tab Buffer used when recovering tables.
@item @code{--delay-key-write-for-all-tables} @tab Don't flush key buffers between writes for any MyISAM table
@end multitable
The automatic recovery is activated if you start mysqld with
@code{--myisam-recover=#}. @xref{Command-line options}.
On open, the table is checked if it's marked as crashed or if the open
count variable for the table is not 0 and you are running with
@code{--skip-locking}. If either of the above is true the following happens.
@itemize @bullet
@item
The table is checked for errors.
@item
If we found an error, try to do a fast repair (with sorting and without
re-creating the data file) of the table.
@item
If the repair fails because of an error in the data file (for example a
duplicate key error), we try again, but this time we re-create the data file.
@item
If the repair fails, retry once more with the old repair option method
(write row by row without sorting) which should be able to repair any
type of error with little disk requirements..
@end itemize
If the recover wouldn't be able to recover all rows from a previous
completed statement and you didn't specify @code{FORCE} as an option to
@code{myisam-recover}, then the automatic repair will abort with an error
message in the error file:
@example
Error: Couldn't repair table: test.g00pages
@end example
If you in this case had used the @code{FORCE} option you would instead have got
a warning in the error file:
@example
Warning: Found 344 of 354 rows when repairing ./test/g00pages
@end example
Note that if you run automatic recover with the @code{BACKUP} option,
you should have a cron script that automaticly moves file with names
like @file{tablename-datetime.BAK} from the database directories to a
backup media.
@xref{Command-line options}.
@menu
* Key space:: Space needed for keys
* MyISAM table formats:: MyISAM table formats
@end menu
@cindex key space, MyISAM
@node Key space, MyISAM table formats, MyISAM, MyISAM
@subsection Space Needed for Keys
@strong{MySQL} can support different index types, but the normal type is
ISAM or MyISAM. These use a B-tree index, and you can roughly calculate
the size for the index file as @code{(key_length+4)/0.67}, summed over
all keys. (This is for the worst case when all keys are inserted in
sorted order and we don't have any compressed keys.)
String indexes are space compressed. If the first index part is a
string, it will also be prefix compressed. Space compression makes the
index file smaller than the above figures if the string column has a lot
of trailing space or is a @code{VARCHAR} column that is not always used
to the full length. Prefix compression is used on keys that start
with a string. Prefix compression helps if there are many strings
with an identical prefix.
In @code{MyISAM} tables, you can also prefix compress numbers by specifying
@code{PACK_KEYS=1} when you create the table. This helps when you have
many integer keys that have an identical prefix when the numbers are stored
high-byte first.
@node MyISAM table formats, , Key space, MyISAM
@subsection MyISAM Table Formats
@strong{MyISAM} supports 3 different table types. Two of them are chosen
automatically depending on the type of columns you are using. The third,
compressed tables, can only be created with the @code{myisampack} tool.
@menu
* Static format:: Static (Fixed-length) table characteristics
* Dynamic format:: Dynamic table characteristics
* Compressed format:: Compressed table characteristics
@end menu
@node Static format, Dynamic format, MyISAM table formats, MyISAM table formats
@subsubsection Static (Fixed-length) Table Characteristics
This is the default format. It's used when the table contains no
@code{VARCHAR}, @code{BLOB}, or @code{TEXT} columns.
This format is the simplest and most secure format. It is also the
fastest of the on-disk formats. The speed comes from the easy way data
can be found on disk. When looking up something with an index and static
format it is very simple. Just multiply the row number by the row length.
Also, when scanning a table it is very easy to read a constant number of
records with each disk read.
The security is evidenced if your computer crashes when writing to a
fixed-size MyISAM file, in which case @code{myisamchk} can easily figure out where each
row starts and ends. So it can usually reclaim all records except the
partially written one. Note that in @strong{MySQL} all indexes can always be
reconstructed:
@itemize @bullet
@item
All @code{CHAR}, @code{NUMERIC}, and @code{DECIMAL} columns are space-padded
to the column width.
@item
Very quick.
@item
Easy to cache.
@item
Easy to reconstruct after a crash, because records are located in fixed
positions.
@item
Doesn't have to be reorganized (with @code{myisamchk}) unless a huge number of
records are deleted and you want to return free disk space to the operating
system.
@item
Usually requires more disk space than dynamic tables.
@end itemize
@cindex dynamic table characteristics
@cindex tables, dynamic
@node Dynamic format, Compressed format, Static format, MyISAM table formats
@subsubsection Dynamic Table Characteristics
This format is used if the table contains any @code{VARCHAR}, @code{BLOB},
or @code{TEXT} columns or if the table was created with
@code{ROW_FORMAT=dynamic}.
This format is a litte more complex because each row has to have a header
that says how long it is. One record can also end up at more than one
location when it is made longer at an update.
@cindex tables, defragment
You can use @code{OPTIMIZE table} or @code{myisamchk} to defragment a
table. If you have static data that you access/change a lot in the same
table as some @code{VARCHAR} or @code{BLOB} columns, it might be a good
idea to move the dynamic columns to other tables just to avoid
fragmentation:
@itemize @bullet
@item
All string columns are dynamic (except those with a length less than 4).
@item
Each record is preceded by a bitmap indicating which columns are empty
(@code{''}) for string columns, or zero for numeric columns. (This isn't
the same as columns containing @code{NULL} values.) If a string column
has a length of zero after removal of trailing spaces, or a numeric
column has a value of zero, it is marked in the bit map and not saved to
disk. Non-empty strings are saved as a length byte plus the string
contents.
@item
Usually takes much less disk space than fixed-length tables.
@item
Each record uses only as much space as is required. If a record becomes
larger, it is split into as many pieces as are required. This results in record
fragmentation.
@item
If you update a row with information that extends the row length, the
row will be fragmented. In this case, you may have to run @code{myisamchk
-r} from time to time to get better performance. Use @code{myisamchk -ei
tbl_name} for some statistics.
@item
Not as easy to reconstruct after a crash, because a record may be fragmented
into many pieces and a link (fragment) may be missing.
@item
The expected row length for dynamic sized records is:
@example
3
+ (number of columns + 7) / 8
+ (number of char columns)
+ packed size of numeric columns
+ length of strings
+ (number of NULL columns + 7) / 8
@end example
There is a penalty of 6 bytes for each link. A dynamic record is linked
whenever an update causes an enlargement of the record. Each new link will be
at least 20 bytes, so the next enlargement will probably go in the same link.
If not, there will be another link. You may check how many links there are
with @code{myisamchk -ed}. All links may be removed with @code{myisamchk -r}.
@end itemize
@cindex tables, compressed format
@node Compressed format, , Dynamic format, MyISAM table formats
@subsubsection Compressed Table Characteristics
This is a read-only type that is generated with the optional
@code{myisampack} tool (@code{pack_isam} for @code{ISAM} tables):
@itemize @bullet
@item
All MySQL distributions, even those that existed before @strong{MySQL}
went GPL, can read tables that were compressed with @code{myisampack}.
@item
Compressed tables take very little disk space. This minimizes disk usage, which
is very nice when using slow disks (like CD-ROMs).
@item
Each record is compressed separately (very little access overhead). The
header for a record is fixed (1-3 bytes) depending on the biggest record in the
table. Each column is compressed differently. Some of the compression types
are:
@itemize @minus
@item
There is usually a different Huffman table for each column.
@item
Suffix space compression.
@item
Prefix space compression.
@item
Numbers with value @code{0} are stored using 1 bit.
@item
If values in an integer column have a small range, the column is stored using
the smallest possible type. For example, a @code{BIGINT} column (8 bytes) may
be stored as a @code{TINYINT} column (1 byte) if all values are in the range
@code{0} to @code{255}.
@item
If a column has only a small set of possible values, the column type is
converted to @code{ENUM}.
@item
A column may use a combination of the above compressions.
@end itemize
@item
Can handle fixed- or dynamic-length records, but not @code{BLOB} or @code{TEXT}
columns.
@item
Can be uncompressed with @code{myisamchk}.
@end itemize
@cindex tables, merging
@cindex MERGE tables, defined
@node MERGE, ISAM, MyISAM, Table types
@section MERGE Tables
@code{MERGE} tables are new in @strong{MySQL} Version 3.23.25. The code
is still in beta, but should stabilize soon!
A @code{MERGE} table is a collection of identical @code{MyISAM} tables
that can be used as one. You can only @code{SELECT}, @code{DELETE}, and
@code{UPDATE} from the collection of tables. If you @code{DROP} the
@code{MERGE} table, you are only dropping the @code{MERGE}
specification.
Note that @code{DELETE FROM merge_table} used without a @code{WHERE}
will only clear the mapping for the table, not delete everything in the
mapped tables. (We plan to fix this in 4.0).
With identical tables we mean that all tables are created with identical
column information. You can't put a MERGE over tables where the columns
are packed differently or doesn't have exactly the same columns.
Some of the tables can however be compressed with @code{myisampack}.
@xref{myisampack}.
When you create a @code{MERGE} table, you will get a @code{.frm} table
definition file and a @code{.MRG} table list file. The @code{.MRG} just
contains a list of the index files (@code{.MYI} files) that should
be used as one.
For the moment you need to have @code{SELECT}, @code{UPDATE}, and
@code{DELETE} privileges on the tables you map to a @code{MERGE} table.
@code{MERGE} tables can help you solve the following problems:
@itemize @bullet
@item
Easily manage a set of log tables. For example, you can put data from
different months into separate files, compress some of them with
@code{myisampack}, and then create a @code{MERGE} to use these as one.
@item
Give you more speed. You can split a big read-only table based on some
criteria and then put the different table part on different disks.
A @code{MERGE} table on this could be much faster than using
the big table. (You can, of course, also use a RAID to get the same
kind of benefits.)
@item
Do more efficient searches. If you know exactly what you are looking
after, you can search in just one of the split tables for some queries
and use @strong{MERGE} table for others. You can even have many
different @code{MERGE} tables active, with possible overlapping files.
@item
More efficient repairs. It's easier to repair the individual files that
are mapped to a @code{MERGE} file than trying to repair a real big file.
@item
Instant mapping of many files as one. A @code{MERGE} table uses the
index of the individual tables. It doesn't need an index of its one.
This makes @code{MERGE} table collections VERY fast to make or remap.
@item
If you have a set of tables that you join to a big table on demand or
batch, you should instead create a @code{MERGE} table on them on demand.
This is much faster and will save a lot of disk space.
@item
Go around the file size limit for the operating system.
@item
You can create an alias/synonym for a table by just using MERGE over one
table. There shouldn't be any really notable performance impacts of doing this
(only a couple of indirect calls and memcpy's for each read).
@end itemize
The disadvantages with @code{MERGE} tables are:
@itemize @bullet
@item
You can't use @code{INSERT} on @code{MERGE} tables, as @strong{MySQL}
can't know in which of the tables we should insert the row.
@item
You can only use identical @code{MyISAM} tables for a @code{MERGE} table.
@item
@code{MERGE} tables uses more file descriptors. If you are using a
@strong{MERGE} that maps over 10 tables and 10 users are using this, you
are using 10*10 + 10 file descriptors. (10 data files for 10 users
and 10 shared index files.)
@item
Key reads are slower. When you do a read on a key, the @code{MERGE}
handler will need to issue a read on all underlying tables to check
which one most closely matches the given key. If you then do a 'read-next'
then the merge table handler will need to search the read buffers
to find the next key. Only when one key buffer is used up, the handler
will need to read the next key block. This makes @code{MERGE} keys much slower
on @code{eq_ref} searches, but not much slower on @code{ref} searches.
@xref{EXPLAIN}.
@item
You can't do @code{DROP TABLE}, @code{ALTER TABLE} or @code{DELETE FROM
table_name} without a @code{WHERE} clause on any of the table that is
mapped by a @code{MERGE} table that is 'open'. If you do this, the
@code{MERGE} table may still refer to the original table and you will
get unexpected results.
@end itemize
The following example shows you how to use @code{MERGE} tables:
@example
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a)) TYPE=MERGE UNION=(t1,t2);
@end example
Note that we didn't create a @code{UNIQUE} or @code{PRIMARY KEY} in the
@code{total} table as the key isn't going to be unique in the @code{total}
table.
Note that you can also manipulate the @code{.MRG} file directly from
the outside of the @strong{MySQL} server:
@example
shell> cd /mysql-data-directory/current-database
shell> ls -1 t1.MYI t2.MYI > total.MRG
shell> mysqladmin flush-tables
@end example
Now you can do things like:
@example
mysql> select * from total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+
@end example
To remap a @code{MERGE} table you can do one of the following:
@itemize @bullet
@item
@code{DROP} the table and re-create it
@item
Use @code{ALTER TABLE table_name UNION(...)}
@item
Change the @code{.MRG} file and issue a @code{FLUSH TABLE} on the
@code{MERGE} table and all underlying tables to force the handler to
read the new definition file.
@end itemize
@cindex tables, ISAM
@node ISAM, HEAP, MERGE, Table types
@section ISAM Tables
You can also use the deprecated ISAM table type. This will disappear
rather soon because @code{MyISAM} is a better implementation of the same
thing. ISAM uses a @code{B-tree} index. The index is stored in a file
with the @code{.ISM} extension, and the data is stored in a file with the
@code{.ISD} extension. You can check/repair ISAM tables with the
@code{isamchk} utility. @xref{Crash recovery}.
@code{ISAM} has the following features/properties:
@itemize @bullet
@item Compressed and fixed-length keys
@item Fixed and dynamic record length
@item 16 keys with 16 key parts/key
@item Max key length 256 (default)
@item Data is stored in machine format; this is fast, but is machine/OS dependent.
@end itemize
Most of the things true for @code{MyISAM} tables are also true for @code{ISAM}
tables. @xref{MyISAM}. The major differences compared to @code{MyISAM}
tables are:
@itemize @bullet
@item ISAM tables are not binary portable across OS/Platforms.
@item Can't handle tables > 4G.
@item Only support prefix compression on strings.
@item Smaller key limits.
@item Dynamic tables get more fragmented.
@item Tables are compressed with @code{pack_isam} rather than with @code{myisampack}.
@end itemize
@cindex tables, @code{HEAP}
@node HEAP, BDB, ISAM, Table types
@section HEAP Tables
@code{HEAP} tables use a hashed index and are stored in memory. This
makes them very fast, but if @strong{MySQL} crashes you will lose all
data stored in them. @code{HEAP} is very useful for temporary tables!
The @strong{MySQL} internal HEAP tables use 100% dynamic hashing
without overflow areas. There is no extra space needed for free lists.
@code{HEAP} tables also don't have problems with delete + inserts, which
normally is common with hashed tables:
@example
mysql> CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) as down
FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;
@end example
Here are some things you should consider when you use @code{HEAP} tables:
@itemize @bullet
@item
You should always use specify @code{MAX_ROWS} in the @code{CREATE} statement
to ensure that you accidently do not use all memory.
@item
Indexes will only be used with @code{=} and @code{<=>} (but are VERY fast).
@item
@code{HEAP} tables can only use whole keys to search for a row; compare this
to @code{MyISAM} tables where any prefix of the key can be used to find rows.
@item
@code{HEAP} tables use a fixed record length format.
@item
@code{HEAP} doesn't support @code{BLOB}/@code{TEXT} columns.
@item
@code{HEAP} doesn't support @code{AUTO_INCREMENT} columns.
@item
@code{HEAP} doesn't support an index on a @code{NULL} column.
@item
You can have non-unique keys in a @code{HEAP} table (this isn't common for
hashed tables).
@item
@code{HEAP} tables are shared between all clients (just like any other
table).
@item
You can't search for the next entry in order (that is, to use the index
to do an @code{ORDER BY}).
@item
Data for @code{HEAP} tables are allocated in small blocks. The tables
are 100% dynamic (on inserting). No overflow areas and no extra key
space are needed. Deleted rows are put in a linked list and are
reused when you insert new data into the table.
@item
You need enough extra memory for all HEAP tables that you want to use at
the same time.
@item
To free memory, you should execute @code{DELETE FROM heap_table},
@code{TRUNCATE heap_table} or @code{DROP TABLE heap_table}.
@item
@strong{MySQL} cannot find out approximately how many rows there
are between two values (this is used by the range optimizer to decide which
index to use). This may affect some queries if you change a @code{MyISAM}
table to a @code{HEAP} table.
@item
To ensure that you accidentally don't do anything foolish, you can't create
@code{HEAP} tables bigger than @code{max_heap_table_size}.
@end itemize
The memory needed for one row in a @code{HEAP} table is:
@example
SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))
@end example
@code{sizeof(char*)} is 4 on 32-bit machines and 8 on 64-bit machines.
@cindex tables, @code{BDB}
@cindex tables, @code{Berkeley DB}
@node BDB, GEMINI, HEAP, Table types
@section BDB or Berkeley_db Tables
@menu
* BDB overview::
* BDB install::
* BDB start::
* BDB characteristic::
* BDB TODO::
* BDB errors::
@end menu
@node BDB overview, BDB install, BDB, BDB
@subsection Overview over BDB tables
BDB tables are included in the @strong{MySQL} source distribution
starting from 3.23.34 and will be activated in the @strong{MySQL}-max
binary.
Berkeley DB (@uref{http://www.sleepycat.com}) has provided
@strong{MySQL} with a transaction-safe table handler. This will survive
crashes and also provides @code{COMMIT} and @code{ROLLBACK} on
transactions. The @strong{MySQL} source distribution comes with a BDB
distribution that has a couple of small patches to make it work more
smoothly with @strong{MySQL}. You can't use a not-patched @code{BDB}
version with @strong{MySQL}.
@node BDB install, BDB start, BDB overview, BDB
@subsection Installing BDB
If you have downloaded a binary version of @strong{MySQL} that includes
support for Berkeley DB, simply follow the instructions for
installing a binary version of @strong{MySQL}. @xref{Installing binary}.
To compile MySQL with Berkeley DB support, download @strong{MySQL}
3.23.34 or newer and configure @code{MySQL} with the
@code{--with-berkeley-db} option. @xref{Installing source}.
@example
cd /path/to/source/of/mysql-3.23.34
./configure --with-berkeley-db
@end example
Please refer to the manual provided by @code{BDB} distribution for
more/updated information.
Even though Berkeley DB is in itself very tested and reliable,
the @strong{MySQL} interface is still considered beta quality.
We are actively improving and optimizing it to get it stable very
soon.
@node BDB start, BDB characteristic, BDB install, BDB
@subsection BDB startup options
If you are running with @code{AUTOCOMMIT=0} then your changes in @code{BDB}
tables will not be updated until you execute @code{COMMIT}. Instead of commit
you can execute @code{ROLLBACK} to forget your changes. @xref{COMMIT}.
If you are running with @code{AUTOCOMMIT=1} (the default), your changes
will be committed immediately. You can start an extended transaction with
the @code{BEGIN WORK} SQL command, after which your changes will not be
committed until you execute @code{COMMIT} (or decide to @code{ROLLBACK}
the changes).
The following options to @code{mysqld} can be used to change the behavior of
BDB tables:
@multitable @columnfractions .30 .70
@item @strong{Option} @tab @strong{Meaning}
@item @code{--bdb-home=directory} @tab Base directory for BDB tables. This should be the same directory you use for --datadir.
@item @code{--bdb-lock-detect=#} @tab Berkeley lock detect. One of (DEFAULT, OLDEST, RANDOM, or YOUNGEST).
@item @code{--bdb-logdir=directory} @tab Berkeley DB log file directory.
@item @code{--bdb-no-sync} @tab Don't synchronously flush logs.
@item @code{--bdb-no-recover} @tab Don't start Berkeley DB in recover mode.
@item @code{--bdb-shared-data} @tab Start Berkeley DB in multi-process mode (Don't use @code{DB_PRIVATE} when initializing Berkeley DB)
@item @code{--bdb-tmpdir=directory} @tab Berkeley DB tempfile name.
@item @code{--skip-bdb} @tab Don't use berkeley db.
@item @code{-O bdb_max_lock=1000} @tab Set the maximum number of locks possible. @xref{SHOW VARIABLES}.
@end multitable
If you use @code{--skip-bdb}, @strong{MySQL} will not initialize the
Berkeley DB library and this will save a lot of memory. Of course,
you cannot use @code{BDB} tables if you are using this option.
Normally you should start mysqld without @code{--bdb-no-recover} if you
intend to use BDB tables. This may, however, give you problems when you
try to start mysqld if the BDB log files are corrupted. @xref{Starting
server}.
With @code{bdb_max_lock} you can specify the maximum number of locks
(10000 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.
You may also want to change @code{binlog_cache_size} and
@code{max_binlog_cache_size} if you are using big multi-line transactions.
@xref{COMMIT}.
@node BDB characteristic, BDB TODO, BDB start, BDB
@subsection Some characteristic of @code{BDB} tables:
@itemize @bullet
@item
To be able to rollback transactions BDB maintain log files. For maximum
performance you should place these on another disk than your databases
by using the @code{--bdb_log_dir} options.
@item
@strong{MySQL} performs a checkpoint each time a new BDB log
file is started, and removes any log files that are not needed for
current transactions. One can also run @code{FLUSH LOGS} at any time
to checkpoint the Berkeley DB tables.
For disaster recovery, one should use table backups plus
@strong{MySQL}'s binary log. @xref{Backup}.
@strong{Warning}: If you delete old log files that are in use, BDB will
not be able to do recovery at all and you may loose data if something
goes wrong.
@item
@strong{MySQL} requires a @code{PRIMARY KEY} in each BDB table to be
able to refer to previously read rows. If you don't create one,
@strong{MySQL} will create an maintain a hidden @code{PRIMARY KEY} for
you. The hidden key has a length of 5 bytes and is incremented for each
insert attempt.
@item
If all columns you access in a @code{BDB} table are part of the same index or
part of the primary key, then @strong{MySQL} can execute the query
without having to access the actual row. In a @code{MyISAM} table the
above holds only if the columns are part of the same index.
@item
The @code{PRIMARY KEY} will be faster than any other key, as the
@code{PRIMARY KEY} is stored together with the row data. As the other keys are
stored as the key data + the @code{PRIMARY KEY}, it's important to keep the
@code{PRIMARY KEY} as short as possible to save disk and get better speed.
@item
@code{LOCK TABLES} works on @code{BDB} tables as with other tables. If
you don't use @code{LOCK TABLE}, @strong{MYSQL} will issue an internal
multiple-write lock on the table to ensure that the table will be
properly locked if another thread issues a table lock.
@item
Internal locking in @code{BDB} tables is done on page level.
@item
@code{SELECT COUNT(*) FROM table_name} is slow as @code{BDB} tables doesn't
maintain a count of the number of rows in the table.
@item
Scanning is slower than with @code{MyISAM} tables as one has data in BDB
tables stored in B-trees and not in a separate data file.
@item
The application must always be prepared to handle cases where
any change of a @code{BDB} table may make an automatic rollback and any
read may fail with a deadlock error.
@item
Keys are not compressed to previous keys as with ISAM or MyISAM
tables. In other words, the key information will take a little more
space in @code{BDB} tables compared to MyISAM tables which don't use
@code{PACK_KEYS=0}.
@item
There is often holes in the BDB table to allow you to insert new rows in
the middle of the key tree. This makes BDB tables somewhat larger than
MyISAM tables.
@item
The optimizer needs to know an approximation of the number of rows in
the table. @strong{MySQL} solves this by counting inserts and
maintaining this in a separate segment in each BDB table. If you don't
do a lot of @code{DELETE} or @code{ROLLBACK}:s this number should be
accurate enough for the @strong{MySQL} optimizer, but as @strong{MySQL}
only store the number on close, it may be wrong if @strong{MySQL} dies
unexpectedly. It should not be fatal even if this number is not 100 %
correct. One can update the number of rows by executing @code{ANALYZE
TABLE} or @code{OPTIMIZE TABLE}. @xref{ANALYZE TABLE} . @xref{OPTIMIZE
TABLE}.
@item
If you get full disk with a @code{BDB} table, you will get an error
(probably error 28) and the transaction should roll back. This is in
contrast with @code{MyISAM} and @code{ISAM} tables where mysqld will
wait for enough free disk before continuing.
@end itemize
@node BDB TODO, BDB errors, BDB characteristic, BDB
@subsection Some things we need to fix for BDB in the near future:
@itemize @bullet
@item
It's very slow to open many BDB tables at the same time. If you are
going to use BDB tables, you should not have a very big table cache (>
256 ?) and you should use @code{--no-auto-rehash} with the @code{mysql}
client. We plan to partly fix this in 4.0.
@item
@code{SHOW TABLE STATUS} doesn't yet provide that much information for BDB
tables.
@item
Optimize performance.
@item
Change to not use page locks at all when we are scanning tables.
@end itemize
@node BDB errors, , BDB TODO, BDB
@subsection Errors You May Get When Using BDB Tables
@itemize @bullet
@item
If you get the following error in the @code{hostname.err log} when
starting @code{mysqld}:
@example
bdb: Ignoring log file: .../log.XXXXXXXXXX: unsupported log version #
@end example
it means that the new @code{BDB} version doesn't support the old log
file format. In this case you have to delete all @code{BDB} log BDB
from your database directory (the files that has the format
@code{log.XXXXXXXXXX} ) and restart @code{mysqld}. We would also
recommend you to do a @code{mysqldump --opt} of your old @code{BDB}
tables, delete the old table and restore the dump.
@item
If you are running in not @code{auto_commit} mode and delete a table you
are using by another thread you may get the following error messages in
the @strong{MySQL} error file:
@example
001119 23:43:56 bdb: Missing log fileid entry
001119 23:43:56 bdb: txn_abort: Log undo failed for LSN: 1 3644744: Invalid
@end example
This is not fatal but we don't recommend that you delete tables if you are
not in @code{auto_commit} mode, until this problem is fixed (the fix is
not trivial).
@end itemize
@cindex tables, @code{GEMINI}
@node GEMINI, INNOBASE, BDB, Table types
@section GEMINI Tables
@menu
* GEMINI overview::
* GEMINI start::
* GEMINI features::
* GEMINI TODO::
@end menu
@node GEMINI overview, GEMINI start, GEMINI, GEMINI
@subsection Overview of GEMINI tables
The @code{GEMINI} table type is developed and supported by NuSphere Corporation
(@uref{http://www.nusphere.com}). It features row-level locking, transaction
support (@code{COMMIT} and @code{ROLLBACK}), and automatic crash recovery.
@code{GEMINI} tables will be included in the @strong{MySQL} 3.23.35 source
distribution.
@node GEMINI start, GEMINI features, GEMINI overview, GEMINI
@subsection GEMINI startup options
If you are running with @code{AUTOCOMMIT=0} then your changes in @code{GEMINI}
tables will not be updated until you execute @code{COMMIT}. Instead of commit
you can execute @code{ROLLBACK} to forget your changes. @xref{COMMIT}.
If you are running with @code{AUTOCOMMIT=1} (the default), your changes
will be committed immediately. You can start an extended transaction with
the @code{BEGIN WORK} SQL command, after which your changes will not be
committed until you execute @code{COMMIT} (or decide to @code{ROLLBACK}
the changes).
The following options to @code{mysqld} can be used to change the behavior of
GEMINI tables:
@multitable @columnfractions .30 .70
@item @strong{Option} @tab @strong{Meaning}
@item @code{--gemini-full-recovery} @tab Default.
@item @code{--gemini-no-recovery} @tab Turn off recovery logging. Not recommended.
@item @code{--gemini-lazy-commit} @tab Relaxes the flush log at commit rule.
@item @code{--gemini-unbuffered-io} @tab All database writes bypass OS cache.
@item @code{--skip-gemini} @tab Don't use Gemini.
@item @code{--O gemini_db_buffers=#} @tab Number of database buffers in database cache.
@item @code{--O gemini_connection_limit=#} @tab Maximum number of connections to Gemini.
@item @code{--O gemini_spin_retries=#} @tab Spin lock retries (optimization).
@item @code{--O gemini_io_threads=#} @tab Number of background I/O threads.
@item @code{--O gemini_lock_table_size=#} @tab Set the maximum number of locks. Default 4096.
@end multitable
If you use @code{--skip-gemini}, @strong{MySQL} will not initialize the
Gemini table handler, saving memory; you cannot use Gemini tables if you
use @code{--skip-gemini}.
@node GEMINI features, GEMINI TODO, GEMINI start, GEMINI
@subsection Features of @code{GEMINI} tables:
@itemize @bullet
@item
If a query result can be resolved solely from the index key, Gemini will
not read the actual row stored in the database.
@item
Locking on Gemini tables is done at row level.
@item
@code{SELECT COUNT(*) FROM table_name} is fast; Gemini maintains a count
of the number of rows in the table.
@end itemize
@node GEMINI TODO, , GEMINI features, GEMINI
@subsection Current limitations of @code{GEMINI} tables:
@itemize @bullet
@item
BLOB columns are not supported in @code{GEMINI} tables.
@item
The maximum number of concurrent users accessing @code{GEMINI} tables is
limited by @code{gemini_connection_limit}. The default is 100 users.
@end itemize
NuSphere is working on removing these limitations.
@node INNOBASE, , GEMINI, Table types
@section INNOBASE Tables
@menu
* INNOBASE overview::
* INNOBASE start:: INNOBASE startup options
* Using INNOBASE tables:: Using INNOBASE tables
* INNOBASE restrictions:: Some restrictions on @code{INNOBASE} tables:
@end menu
@node INNOBASE overview, INNOBASE start, INNOBASE, INNOBASE
@subsection INNOBASE Tables overview
Innobase tables are included in the @strong{MySQL} source distribution
starting from 3.23.34 and will be activated in the @strong{MySQL}-max
binary.
If you have downloaded a binary version of @strong{MySQL} that includes
support for Innobase, simply follow the instructions for
installing a binary version of @strong{MySQL}. @xref{Installing binary}.
To compile @strong{MySQL} with Innobase support, download @strong{MySQL}
3.23.34 or newer and configure @code{MySQL} with the
@code{--with-innobase} option. @xref{Installing source}.
@example
cd /path/to/source/of/mysql-3.23.34
./configure --with-innobase
@end example
Innobase provides MySQL with a transaction safe table handler with
commit, rollback, and crash recovery capabilities. Innobase does
locking on row level, and also provides an Oracle-style consistent
non-locking read in @code{SELECTS}, which increases transaction
concurrency. There is neither need for lock escalation in Innobase,
because row level locks in Innobase fit in very small space.
Innobase is a table handler that is under the GNU GPL License Version 2
(of June 1991). In the source distribution of MySQL, Innobase appears as
a subdirectory.
@node INNOBASE start, Using INNOBASE tables, INNOBASE overview, INNOBASE
@subsection INNOBASE startup options
To use Innobase tables you must specify configuration parameters
in the MySQL configuration file in the @code{[mysqld]} section of
the configuration file. Below is an example of possible configuration
parameters in my.cnf for Innobase:
@example
innobase_data_home_dir = c:ibdata
innobase_data_file_path = ibdata1:25M;ibdata2:37M;ibdata3:100M;ibdata4:300M
set-variable = innobase_mirrored_log_groups=1
innobase_log_group_home_dir = c:iblogs
set-variable = innobase_log_files_in_group=3
set-variable = innobase_log_file_size=5M
set-variable = innobase_log_buffer_size=8M
innobase_flush_log_at_trx_commit=1
innobase_log_arch_dir = c:iblogs
innobase_log_archive=0
set-variable = innobase_buffer_pool_size=16M
set-variable = innobase_additional_mem_pool_size=2M
set-variable = innobase_file_io_threads=4
set-variable = innobase_lock_wait_timeout=50
@end example
The meanings of the configuration parameters are the following:
@multitable @columnfractions .30 .70
@item @code{innobase_data_home_dir} @tab
The common part of the directory path for all innobase data files.
@item @code{innobase_data_file_path} @tab
Paths to individual data files and their sizes. The full directory path
to each data file is acquired by concatenating innobase_data_home_dir to
the paths specified here. The file sizes are specified in megabytes,
hence the 'M' after the size specification above. Do not set a file size
bigger than 4000M, and on most operating systems not bigger than 2000M.
innobase_mirrored_log_groups Number of identical copies of log groups we
keep for the database. Currently this should be set to 1.
@item @code{innobase_log_group_home_dir} @tab
Directory path to Innobase log files.
@item @code{innobase_log_files_in_group} @tab
Number of log files in the log group. Innobase writes to the files in a
circular fashion. Value 3 is recommended here.
@item @code{innobase_log_file_size} @tab
Size of each log file in a log group in megabytes. Sensible values range
from 1M to the size of the buffer pool specified below. The bigger the
value, the less checkpoint flush activity is needed in the buffer pool,
saving disk i/o. But bigger log files also mean that recovery will be
slower in case of a crash. File size restriction as for a data file.
@item @code{innobase_log_buffer_size} @tab
The size of the buffer which Innobase uses to write log to the log files
on disk. Sensible values range from 1M to half the combined size of log
files. A big log buffer allows large transactions to run without a need
to write the log to disk until the transaction commit. Thus, if you have
big transactions, making the log buffer big will save disk i/o.
@item @code{innobase_flush_log_at_trx_commit} @tab
Normally this is set to 1, meaning that at a transaction commit the log
is flushed to disk, and the modifications made by the transaction become
permanent, and survive a database crash. If you are willing to
compromise this safety, and you are running small transactions, you may
set this to 0 to reduce disk i/o to the logs.
@item @code{innobase_log_arch_dir} @tab
The directory where fully written log files would be archived if we used
log archiving. The value of this parameter should currently be set the
same as @code{innobase_log_group_home_dir}.
@item @code{innobase_log_archive} @tab
This value should currently be set to 0. As recovery from a backup is
done by MySQL using its own log files, there is currently no need to
archive Innobase log files.
@item @code{innobase_buffer_pool_size} @tab
The size of the memory buffer Innobase uses to cache data and indexes of
its tables. The bigger you set this the less disk i/o is needed to
access data in tables. On a dedicated database server you may set this
parameter up to 90 % of the machine physical memory size. Do not set it
too large, though, because competition of the physical memory may cause
paging in the operating system.
@item @code{innobase_additional_mem_pool_size} @tab
Size of a memory pool Innobase uses to store data dictionary information
and other internal data structures. A sensible value for this might be
2M, but the more tables you have in your application the more you will
need to allocate here. If Innobase runs out of memory in this pool, it
will start to allocate memory from the operating system, and write
warning messages to the MySQL error log.
@item @code{innobase_file_io_threads} @tab
Number of file i/o threads in Innobase. Normally, this should be 4, but
on Windows NT disk i/o may benefit from a larger number.
@item @code{innobase_lock_wait_timeout} @tab
Timeout in seconds an Innobase transaction may wait for a lock before
being rolled back. Innobase automatically detects transaction deadlocks
in its own lock table and rolls back the transaction. If you use
@code{LOCK TABLES} command, or other transaction safe table handlers
than Innobase in the same transaction, then a deadlock may arise which
Innobase cannot notice. In cases like this the timeout is useful to
resolve the situation.
@end multitable
@node Using INNOBASE tables, INNOBASE restrictions, INNOBASE start, INNOBASE
@subsection Using INNOBASE tables
Technically, Innobase is a database backend placed under MySQL. Innobase
has its own buffer pool for caching data and indexes in main
memory. Innobase stores its tables and indexes in a tablespace, which
may consist of several files. This is different from, for example,
@code{MyISAM} tables where each table is stored as a separate file.
To create a table in the Innobase format you must specify
@code{TYPE = INNOBASE} in the table creation SQL command:
@example
CREATE TABLE CUSTOMERS (A INT, B CHAR (20), INDEX (A)) TYPE = INNOBASE;
@end example
A consistent non-locking read is the default locking behavior when you
do a @code{SELECT} from an Innobase table. For a searched update and an
insert row level exclusive locking is performed.
You can query the amount of free space in the Innobase tablespace (=
data files you specified in my.cnf) by issuing the table status command
of @strong{MySQL} for any table you have created with @code{TYPE =
INNOBASE}. Then the amount of free space in the tablespace appears in
the table comment section in the output of SHOW. An example:
@example
SHOW TABLE STATUS FROM TEST LIKE 'CUSTOMER'
@end example
if you have created a table of name CUSTOMER in a database you have named
TEST. Note that the statistics SHOW gives about Innobase tables
are only approximate: they are used in SQL optimization. Table and
index reserved sizes in bytes are accurate, though.
NOTE: DROP DATABASE does not currently work for Innobase tables!
You must drop the tables individually.
Note that in addition to your tables, the rollback segment uses space
from the tablespace.
Since Innobase is a multiversioned database, it must keep information
of old versions of rows in the tablespace. This information is stored
in a data structure called a rollback segment, like in Oracle. In contrast
to Oracle, you do not need to configure the rollback segment in any way in
Innobase. If you issue SELECTs, which by default do a consistent read in
Innobase, remember to commit your transaction regularly. Otherwise
the rollback segment will grow because it has to preserve the information
needed for further consistent reads in your transaction: in Innobase
all consistent reads within one transaction will see the same timepoint
snapshot of the database: the reads are also 'consistent' with
respect to each other.
Some Innobase errors: If you run out of file space in the tablespace,
you will get the MySQL 'Table is full' error. If you want to make your
tablespace bigger, you have to shut down MySQL and add a new datafile
specification to my.conf, to the innobase_data_file_path parameter.
A transaction deadlock or a timeout in a lock wait will give 'Table handler
error 1000000'.
Contact information of Innobase Oy, producer of the Innobase engine:
Website: @uref{http://www.innobase.fi}.
@email{Heikki.Tuuri@@innobase.inet.fi}
@example
phone: 358-9-6969 3250 (office) 358-40-5617367 (mobile)
Innobase Oy Inc.
World Trade Center Helsinki
Aleksanterinkatu 17
P.O.Box 800
00101 Helsinki
Finland
@end example
@node INNOBASE restrictions, , Using INNOBASE tables, INNOBASE
@subsection Some restrictions on @code{INNOBASE} tables:
@itemize @bullet
@item
You can't have a key on a @code{BLOB} or @code{TEXT} column.
@item
@code{DELETE FROM TABLE} doesn't re-generate the table but instead deletes all
rows, one by one, which isn't that fast.
@item
The maximum blob size is 8000 bytes.
@item
Before dropping a database with @code{INNOBASE} tables one has to drop
the individual tables first. If one doesn't do that, the space in the
Innobase table space will not be reclaimed.
@end itemize
@cindex tutorial
@cindex terminal monitor, defined
@cindex monitor, terminal
@cindex options, provided by MySQL
@node Tutorial, Server, Table types, Top
@chapter MySQL Tutorial
@menu
* Connecting-disconnecting:: Connecting to and disconnecting from the server
* Entering queries:: Entering queries
* Examples:: Examples
* Searching on two keys:: Searching on two keys
* Database use:: Creating and using a database
* Getting information:: Getting information about databases and tables
* Batch mode:: Using @code{mysql} in batch mode
* Twin:: Queries from twin project
@end menu
This chapter provides a tutorial introduction to @strong{MySQL} by showing
how to use the @code{mysql} client program to create and use a simple
database. @code{mysql} (sometimes referred to as the ``terminal monitor'' or
just ``monitor'') is an interactive program that allows you to connect to a
@strong{MySQL} server, run queries, and view the results. @code{mysql} may
also be used in batch mode: you place your queries in a file beforehand, then
tell @code{mysql} to execute the contents of the file. Both ways of using
@code{mysql} are covered here.
To see a list of options provided by @code{mysql}, invoke it with
the @code{--help} option:
@example
shell> mysql --help
@end example
This chapter assumes that @code{mysql} is installed on your machine and that
a @strong{MySQL} server is available to which you can connect. If this is
not true, contact your @strong{MySQL} administrator. (If @emph{you} are the
administrator, you will need to consult other sections of this manual.)
This chapter describes the entire process of setting up and using a
database. If you are interested only in accessing an already-existing
database, you may want to skip over the sections that describe how to
create the database and the tables it contains.
Because this chapter is tutorial in nature, many details are necessarily left
out. Consult the relevant sections of the manual for more
information on the topics covered here.
@cindex connecting, to the server
@cindex disconnecting, from the server
@cindex server, connecting
@cindex server, disconnecting
@node Connecting-disconnecting, Entering queries, Tutorial, Tutorial
@section Connecting to and Disconnecting from the Server
To connect to the server, you'll usually need to provide a @strong{MySQL}
user name when you invoke @code{mysql} and, most likely, a password. If the
server runs on a machine other than the one where you log in, you'll also
need to specify a hostname. Contact your administrator to find out what
connection parameters you should use to connect (that is, what host, user name,
and password to use). Once you know the proper parameters, you should be
able to connect like this:
@example
shell> mysql -h host -u user -p
Enter password: ********
@end example
The @code{********} represents your password; enter it when @code{mysql}
displays the @code{Enter password:} prompt.
If that works, you should see some introductory information followed by a
@code{mysql>} prompt:
@example
shell> mysql -h host -u user -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 459 to server version: 3.22.20a-log
Type 'help' for help.
mysql>
@end example
The prompt tells you that @code{mysql} is ready for you to enter commands.
Some @strong{MySQL} installations allow users to connect as the anonymous
(unnamed) user to the server running on the local host. If this is the case
on your machine, you should be able to connect to that server by invoking
@code{mysql} without any options:
@example
shell> mysql
@end example
After you have connected successfully, you can disconnect any time by typing
@code{QUIT} at the @code{mysql>} prompt:
@example
mysql> QUIT
Bye
@end example
You can also disconnect by pressing Control-D.
Most examples in the following sections assume you are connected to the
server. They indicate this by the @code{mysql>} prompt.
@cindex running, queries
@cindex queries, entering
@cindex entering, queries
@node Entering queries, Examples, Connecting-disconnecting, Tutorial
@section Entering Queries
Make sure you are connected to the server, as discussed in the previous
section. Doing so will not in itself select any database to work with, but
that's okay. At this point, it's more important to find out a little about
how to issue queries than to jump right in creating tables, loading data
into them, and retrieving data from them. This section describes the basic
principles of entering commands, using several queries you can try out to
familiarize yourself with how @code{mysql} works.
Here's a simple command that asks the server to tell you its version number
and the current date. Type it in as shown below following the @code{mysql>}
prompt and hit the RETURN key:
@example
mysql> SELECT VERSION(), CURRENT_DATE;
+--------------+--------------+
| version() | CURRENT_DATE |
+--------------+--------------+
| 3.22.20a-log | 1999-03-19 |
+--------------+--------------+
1 row in set (0.01 sec)
mysql>
@end example
This query illustrates several things about @code{mysql}:
@itemize @bullet
@item
A command normally consists of a SQL statement followed by a semicolon.
(There are some exceptions where a semicolon is not needed. @code{QUIT},
mentioned earlier, is one of them. We'll get to others later.)
@item
When you issue a command, @code{mysql} sends it to the server for execution
and displays the results, then prints another @code{mysql>} to indicate
that it is ready for another command.
@item
@code{mysql} displays query output as a table (rows and columns). The first
row contains labels for the columns. The rows following are the query
results. Normally, column labels are the names of the columns you fetch from
database tables. If you're retrieving the value of an expression rather than
a table column (as in the example just shown), @code{mysql} labels the column
using the expression itself.
@item
@code{mysql} shows how many rows were returned and how long the query took
to execute, which gives you a rough idea of server performance. These values
are imprecise because they represent wall clock time (not CPU or machine
time), and because they are affected by factors such as server load and
network latency. (For brevity, the ``rows in set'' line is not shown in
the remaining examples in this chapter.)
@end itemize
Keywords may be entered in any lettercase. The following queries are
equivalent:
@example
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;
@end example
Here's another query. It demonstrates that you can use @code{mysql} as a
simple calculator:
@example
mysql> SELECT SIN(PI()/4), (4+1)*5;
+-------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+-------------+---------+
| 0.707107 | 25 |
+-------------+---------+
@end example
The commands shown thus far have been relatively short, single-line
statements. You can even enter multiple statements on a single line.
Just end each one with a semicolon:
@example
mysql> SELECT VERSION(); SELECT NOW();
+--------------+
| version() |
+--------------+
| 3.22.20a-log |
+--------------+
+---------------------+
| NOW() |
+---------------------+
| 1999-03-19 00:15:33 |
+---------------------+
@end example
A command need not be given all on a single line, so lengthy commands that
require several lines are not a problem. @code{mysql} determines where your
statement ends by looking for the terminating semicolon, not by looking for
the end of the input line. (In other words, @code{mysql}
accepts free-format input: it collects input lines but does not execute them
until it sees the semicolon.)
Here's a simple multiple-line statement:
@example
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
+--------------------+--------------+
| USER() | CURRENT_DATE |
+--------------------+--------------+
| joesmith@@localhost | 1999-03-18 |
+--------------------+--------------+
@end example
In this example, notice how the prompt changes from @code{mysql>} to
@code{->} after you enter the first line of a multiple-line query. This is
how @code{mysql} indicates that it hasn't seen a complete statement and is
waiting for the rest. The prompt is your friend, because it provides
valuable feedback. If you use that feedback, you will always be aware of
what @code{mysql} is waiting for.
If you decide you don't want to execute a command that you are in the
process of entering, cancel it by typing @code{c}:
@example
mysql> SELECT
-> USER()
-> c
mysql>
@end example
Here, too, notice the prompt. It switches back to @code{mysql>} after you
type @code{c}, providing feedback to indicate that @code{mysql} is ready
for a new command.
The following table shows each of the prompts you may see and summarizes what
they mean about the state that @code{mysql} is in:
@cindex prompts, meanings
@multitable @columnfractions .10 .9
@item @strong{Prompt} @tab @strong{Meaning}
@item @code{mysql>} @tab Ready for new command.
@item @code{@ @ @ @ ->} @tab Waiting for next line of multiple-line command.
@item @code{@ @ @ @ '>} @tab Waiting for next line, collecting a string that begins
with a single quote (@samp{'}).
@item @code{@ @ @ @ ">} @tab Waiting for next line, collecting a string that begins
with a double quote (@samp{"}).
@end multitable
Multiple-line statements commonly occur by accident when you intend to
issue a command on a single line, but forget the terminating semicolon. In
this case, @code{mysql} waits for more input:
@example
mysql> SELECT USER()
->
@end example
If this happens to you (you think you've entered a statement but the only
response is a @code{->} prompt), most likely @code{mysql} is waiting for the
semicolon. If you don't notice what the prompt is telling you, you might sit
there for a while before realizing what you need to do. Enter a semicolon to
complete the statement, and @code{mysql} will execute it:
@example
mysql> SELECT USER()
-> ;
+--------------------+
| USER() |
+--------------------+
| joesmith@@localhost |
+--------------------+
@end example
The @code{'>} and @code{">} prompts occur during string collection.
In @strong{MySQL}, you can write strings surrounded by either @samp{'}
or @samp{"} characters (for example, @code{'hello'} or @code{"goodbye"}),
and @code{mysql} lets you enter strings that span multiple lines. When you
see a @code{'>} or @code{">} prompt, it means that you've entered a line
containing a string that begins with a @samp{'} or @samp{"} quote character,
but have not yet entered the matching quote that terminates the string.
That's fine if you really are entering a multiple-line string, but how likely
is that? Not very. More often, the @code{'>} and @code{">} prompts indicate
that you've inadvertantly left out a quote character. For example:
@example
mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
">
@end example
If you enter this @code{SELECT} statement, then hit RETURN and wait for the
result, nothing will happen. Instead of wondering why this
query takes so long, notice the clue provided by the @code{">} prompt. It
tells you that @code{mysql} expects to see the rest of an unterminated
string. (Do you see the error in the statement? The string @code{"Smith} is
missing the second quote.)
At this point, what do you do? The simplest thing is to cancel the command.
However, you cannot just type @code{c} in this case, because @code{mysql}
interprets it as part of the string that it is collecting! Instead, enter
the closing quote character (so @code{mysql} knows you've finished the
string), then type @code{c}:
@example
mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
"> "c
mysql>
@end example
The prompt changes back to @code{mysql>}, indicating that @code{mysql}
is ready for a new command.
It's important to know what the @code{'>} and @code{">} prompts signify,
because if you mistakenly enter an unterminated string, any further lines you
type will appear to be ignored by @code{mysql} --- including a line
containing @code{QUIT}! This can be quite confusing, especially if you
don't know that you need to supply the terminating quote before you can
cancel the current command.
@cindex queries, examples
@cindex examples, queries
@node Examples, Searching on two keys, Entering queries, Tutorial
@section Examples of Common Queries
Here are examples of how to solve some common problems with
@strong{MySQL}.
Some of the examples use the table @code{shop} to hold the price of each
article (item number) for certain traders (dealers). Supposing that each
trader has a single fixed price per article, then (@code{item},
@code{trader}) is a primary key for the records.
Start the command line tool @code{mysql} and select a database:
@example
mysql your-database-name
@end example
(In most @strong{MySQL} installations, you can use the database-name 'test').
You can create the example table as:
@example
CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
(3,'D',1.25),(4,'D',19.95);
@end example
Okay, so the example data is:
@example
mysql> SELECT * FROM shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
@end example
@menu
* example-Maximum-column:: The maximum value for a column
* example-Maximum-row:: The row holding the maximum of a certain column
* example-Maximum-column-group:: Maximum of column per group
* example-Maximum-column-group-row:: The rows holding the group-wise maximum of a certain field
* example-user-variables:: Using user variables
* example-Foreign keys:: Using foreign keys
@end menu
@node example-Maximum-column, example-Maximum-row, Examples, Examples
@subsection The Maximum Value for a Column
``What's the highest item number?''
@example
SELECT MAX(article) AS article FROM shop
+---------+
| article |
+---------+
| 4 |
+---------+
@end example
@node example-Maximum-row, example-Maximum-column-group, example-Maximum-column, Examples
@subsection The Row Holding the Maximum of a Certain Column
``Find number, dealer, and price of the most expensive article.''
In ANSI SQL this is easily done with a sub-query:
@example
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop)
@end example
In @strong{MySQL} (which does not yet have sub-selects), just do it in
two steps:
@enumerate
@item
Get the maximum price value from the table with a @code{SELECT} statement.
@item
Using this value compile the actual query:
@example
SELECT article, dealer, price
FROM shop
WHERE price=19.95
@end example
@end enumerate
Another solution is to sort all rows descending by price and only
get the first row using the @strong{MySQL} specific @code{LIMIT} clause:
@example
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1
@end example
@strong{NOTE}: If there are several most expensive articles (for example, each 19.95)
the @code{LIMIT} solution shows only one of them!
@node example-Maximum-column-group, example-Maximum-column-group-row, example-Maximum-row, Examples
@subsection Maximum of Column per Group
``What's the highest price per article?''
@example
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article
+---------+-------+
| article | price |
+---------+-------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+---------+-------+
@end example
@node example-Maximum-column-group-row, example-user-variables, example-Maximum-column-group, Examples
@subsection The Rows Holding the Group-wise Maximum of a Certain Field
``For each article, find the dealer(s) with the most expensive price.''
In ANSI SQL, I'd do it with a sub-query like this:
@example
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article)
@end example
In @strong{MySQL} it's best do it in several steps:
@enumerate
@item
Get the list of (article,maxprice).
@item
For each article get the corresponding rows that have the stored maximum
price.
@end enumerate
This can easily be done with a temporary table:
@example
CREATE TEMPORARY TABLE tmp (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);
LOCK TABLES shop read;
INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;
SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;
@end example
If you don't use a @code{TEMPORARY} table, you must also lock the 'tmp' table.
``Can it be done with a single query?''
Yes, but only by using a quite inefficient trick that I call the
``MAX-CONCAT trick'':
@example
SELECT article,
SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM shop
GROUP BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+
@end example
The last example can, of course, be made a bit more efficient by doing the
splitting of the concatenated column in the client.
@node example-user-variables, example-Foreign keys, example-Maximum-column-group-row, Examples
@subsection Using user variables
You can use @strong{MySQL} user variables to remember results without
having to store them in a temporary variables in the client.
@xref{Variables}.
For example, to find the articles with the highest and lowest price you
can do:
@example
select @@min_price:=min(price),@@max_price:=max(price) from shop;
select * from shop where price=@@min_price or price=@@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
@end example
@cindex foreign keys
@cindex keys, foreign
@node example-Foreign keys, , example-user-variables, Examples
@subsection Using Foreign Keys
You don't need foreign keys to join 2 tables.
The only thing @strong{MySQL} doesn't do is @code{CHECK} to make sure that
the keys you use really exist in the table(s) you're referencing and it
doesn't automatically delete rows from table with a foreign key
definition. If you use your keys like normal, it'll work just fine:
@example
CREATE TABLE persons (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirts (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
PRIMARY KEY (id)
);
INSERT INTO persons VALUES (NULL, 'Antonio Paz');
INSERT INTO shirts VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());
INSERT INTO persons VALUES (NULL, 'Lilliana Angelovska');
INSERT INTO shirts VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());
SELECT * FROM persons;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirts;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+
SELECT s.* FROM persons p, shirts s
WHERE p.name LIKE 'Lilliana%'
AND s.owner = p.id
AND s.color <> 'white';
+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+
@end example
@findex UNION
@cindex searching, two keys
@cindex keys, searching on two
@node Searching on two keys, Database use, Examples, Tutorial
@section Searching on Two Keys
@strong{MySQL} doesn't yet optimize when you search on two different
keys combined with @code{OR} (Searching on one key with different @code{OR}
parts is optimized quite good):
@example
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'
OR field2_index = '1'
@end example
The reason is that we haven't yet had time to come up with an efficient
way to handle this in the general case. (The @code{AND} handling is,
in comparison, now completely general and works very well).
For the moment you can solve this very efficently by using a
@code{TEMPORARY} table. This type of optimization is also very good if
you are using very complicated queries where the SQL server does the
optimizations in the wrong order.
@example
CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;
@end example
The above way to solve this query is in effect an @code{UNION} of two queries.
@cindex databases, creating
@cindex databases, using
@cindex creating, databases
@node Database use, Getting information, Searching on two keys, Tutorial
@section Creating and Using a Database
@menu
* Creating database:: Creating a database
* Creating tables:: Creating a table
* Loading tables:: Loading data into a table
* Retrieving data:: Retrieving information from a table
* Multiple tables:: Using more than one table
@end menu
Now that you know how to enter commands, it's time to access a database.
Suppose you have several pets in your home (your menagerie) and you'd
like to keep track of various types of information about them. You can do so
by creating tables to hold your data and loading them with the desired
information. Then you can answer different sorts of questions about your
animals by retrieving data from the tables. This section shows you how to:
@itemize @bullet
@item
Create a database
@item
Create a table
@item
Load data into the table
@item
Retrieve data from the table in various ways
@item
Use multiple tables
@end itemize
The menagerie database will be simple (deliberately), but it is not difficult
to think of real-world situations in which a similar type of database might
be used. For example, a database like this could be used by a farmer to keep
track of livestock, or by a veterinarian to keep track of patient records.
Use the @code{SHOW} statement to find out what databases currently exist
on the server:
@example
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
| tmp |
+----------+
@end example
The list of databases is probably different on your machine, but the
@code{mysql} and @code{test} databases are likely to be among them. The
@code{mysql} database is required because it describes user access
privileges. The @code{test} database is often provided as a workspace for
users to try things out.
If the @code{test} database exists, try to access it:
@example
mysql> USE test
Database changed
@end example
Note that @code{USE}, like @code{QUIT}, does not require a semicolon. (You
can terminate such statements with a semicolon if you like; it does no harm.)
The @code{USE} statement is special in another way, too: it must be given on
a single line.
You can use the @code{test} database (if you have access to it) for the
examples that follow, but anything you create in that database can be
removed by anyone else with access to it. For this reason, you should
probably ask your @strong{MySQL} administrator for permission to use a
database of your own. Suppose you want to call yours @code{menagerie}. The
administrator needs to execute a command like this:
@example
mysql> GRANT ALL ON menagerie.* TO your_mysql_name;
@end example
where @code{your_mysql_name} is the @strong{MySQL} user name assigned to
you.
@cindex selecting, databases
@cindex databases, selecting
@node Creating database, Creating tables, Database use, Database use
@subsection Creating and Selecting a Database
If the administrator creates your database for you when setting up your
permissions, you can begin using it. Otherwise, you need to create it
yourself:
@example
mysql> CREATE DATABASE menagerie;
@end example
Under Unix, database names are case sensitive (unlike SQL keywords), so you
must always refer to your database as @code{menagerie}, not as
@code{Menagerie}, @code{MENAGERIE}, or some other variant. This is also true
for table names. (Under Windows, this restriction does not apply, although
you must refer to databases and tables using the same lettercase throughout a
given query.)
Creating a database does not select it for use; you must do that explicitly.
To make @code{menagerie} the current database, use this command:
@example
mysql> USE menagerie
Database changed
@end example
Your database needs to be created only once, but you must select it for use
each time you begin a @code{mysql} session. You can do this by issuing a
@code{USE} statement as shown above. Alternatively, you can select the
database on the command line when you invoke @code{mysql}. Just specify its
name after any connection parameters that you might need to provide. For
example:
@example
shell> mysql -h host -u user -p menagerie
Enter password: ********
@end example
Note that @code{menagerie} is not your password on the command just shown.
If you want to supply your password on the command line after the @code{-p}
option, you must do so with no intervening space (for example, as
@code{-pmypassword}, not as @code{-p mypassword}). However, putting your
password on the command line is not recommended, because doing so exposes it
to snooping by other users logged in on your machine.
@cindex tables, creating
@cindex creating, tables
@node Creating tables, Loading tables, Creating database, Database use
@subsection Creating a Table
Creating the database is the easy part, but at this point it's empty, as
@code{SHOW TABLES} will tell you:
@example
mysql> SHOW TABLES;
Empty set (0.00 sec)
@end example
The harder part is deciding what the structure of your database should be:
what tables you will need and what columns will be in each of them.
You'll want a table that contains a record for each of your pets. This can
be called the @code{pet} table, and it should contain, as a bare minimum,
each animal's name. Because the name by itself is not very interesting, the
table should contain other information. For example, if more than one person
in your family keeps pets, you might want to list each animal's owner. You
might also want to record some basic descriptive information such as species
and sex.
How about age? That might be of interest, but it's not a good thing to store
in a database. Age changes as time passes, which means you'd have to update
your records often. Instead, it's better to store a fixed value such as
date of birth. Then, whenever you need age, you can calculate it as the
difference between the current date and the birth date. @strong{MySQL}
provides functions for doing date arithmetic, so this is not difficult.
Storing birth date rather than age has other advantages, too:
@itemize @bullet
@item
You can use the database for tasks such as generating reminders for upcoming
pet birthdays. (If you think this type of query is somewhat silly, note that
it is the same question you might ask in the context of a business database
to identify clients to whom you'll soon need to send out birthday greetings,
for that computer-assisted personal touch.)
@item
You can calculate age in relation to dates other than the current date. For
example, if you store death date in the database, you can easily calculate
how old a pet was when it died.
@end itemize
You can probably think of other types of information that would be useful in
the @code{pet} table, but the ones identified so far are sufficient for now:
name, owner, species, sex, birth, and death.
Use a @code{CREATE TABLE} statement to specify the layout of your table:
@example
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
@end example
@code{VARCHAR} is a good choice for the @code{name}, @code{owner}, and
@code{species} columns because the column values will vary in length. The
lengths of those columns need not all be the same, and need not be
@code{20}. You can pick any length from @code{1} to @code{255}, whatever
seems most reasonable to you. (If you make a poor choice and it turns
out later that you need a longer field, @strong{MySQL} provides an
@code{ALTER TABLE} statement.)
Animal sex can be represented in a variety of ways, for example, @code{"m"}
and @code{"f"}, or perhaps @code{"male"} and @code{"female"}. It's simplest
to use the single characters @code{"m"} and @code{"f"}.
The use of the @code{DATE} data type for the @code{birth} and @code{death}
columns is a fairly obvious choice.
Now that you have created a table, @code{SHOW TABLES} should produce some
output:
@example
mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| pet |
+---------------------+
@end example
To verify that your table was created the way you expected, use
a @code{DESCRIBE} statement:
@example
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
@end example
You can use @code{DESCRIBE} any time, for example, if you forget the names of
the columns in your table or what types they are.
@cindex loading, tables
@cindex tables, loading data
@cindex data, loading into tables
@node Loading tables, Retrieving data, Creating tables, Database use
@subsection Loading Data into a Table
After creating your table, you need to populate it. The @code{LOAD DATA} and
@code{INSERT} statements are useful for this.
Suppose your pet records can be described as shown below.
(Observe that @strong{MySQL} expects dates in @code{YYYY-MM-DD} format;
this may be different than what you are used to.)
@multitable @columnfractions .16 .16 .16 .16 .16 .16
@item @strong{name} @tab @strong{owner} @tab @strong{species} @tab @strong{sex} @tab @strong{birth} @tab @strong{death}
@item Fluffy @tab Harold @tab cat @tab f @tab 1993-02-04 @tab
@item Claws @tab Gwen @tab cat @tab m @tab 1994-03-17 @tab
@item Buffy @tab Harold @tab dog @tab f @tab 1989-05-13 @tab
@item Fang @tab Benny @tab dog @tab m @tab 1990-08-27 @tab
@item Bowser @tab Diane @tab dog @tab m @tab 1998-08-31 @tab 1995-07-29
@item Chirpy @tab Gwen @tab bird @tab f @tab 1998-09-11 @tab
@item Whistler @tab Gwen @tab bird @tab @tab 1997-12-09 @tab
@item Slim @tab Benny @tab snake @tab m @tab 1996-04-29 @tab
@end multitable
Because you are beginning with an empty table, an easy way to populate it is to
create a text file containing a row for each of your animals, then load the
contents of the file into the table with a single statement.
You could create a text file @file{pet.txt} containing one record per line,
with values separated by tabs, and given in the order in which the columns
were listed in the @code{CREATE TABLE} statement. For missing values (such
as unknown sexes or death dates for animals that are still living), you can
use @code{NULL} values. To represent these in your text file, use
@code{N}. For example, the record for Whistler the bird would look like
this (where the whitespace between values is a single tab character):
@multitable @columnfractions .15 .15 .15 .15 .25 .15
@item @code{Whistler} @tab @code{Gwen} @tab @code{bird} @tab @code{N} @tab @code{1997-12-09} @tab @code{N}
@end multitable
To load the text file @file{pet.txt} into the @code{pet} table, use this
command:
@example
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
@end example
You can specify the column value separator and end of line marker explicitly
in the @code{LOAD DATA} statement if you wish, but the defaults are tab and
linefeed. These are sufficient for the statement to read the file
@file{pet.txt} properly.
When you want to add new records one at a time, the @code{INSERT} statement
is useful. In its simplest form, you supply values for each column, in the
order in which the columns were listed in the @code{CREATE TABLE} statement.
Suppose Diane gets a new hamster named Puffball. You could add a new record
using an @code{INSERT} statement like this:
@example
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
@end example
Note that string and date values are specified as quoted strings here. Also,
with @code{INSERT}, you can insert @code{NULL} directly to represent a
missing value. You do not use @code{N} like you do with @code{LOAD DATA}.
From this example, you should be able to see that there would be a lot more
typing involved to load
your records initially using several @code{INSERT} statements rather
than a single @code{LOAD DATA} statement.
@cindex data, retrieving
@cindex tables, retrieving data
@cindex retrieving, data from tables
@cindex unloading, tables
@node Retrieving data, Multiple tables, Loading tables, Database use
@subsection Retrieving Information from a Table
@menu
* Selecting all:: Selecting all data
* Selecting rows:: Selecting particular rows
* Selecting columns:: Selecting particular columns
* Sorting rows:: Sorting rows
* Date calculations:: Date calculations
* Working with NULL:: Working with @code{NULL} values
* Pattern matching:: Pattern matching
* Counting rows:: Counting rows
@end menu
The @code{SELECT} statement is used to pull information from a table.
The general form of the statement is:
@example
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy
@end example
@code{what_to_select} indicates what you want to see. This can be a list of
columns, or @code{*} to indicate ``all columns.'' @code{which_table}
indicates the table from which you want to retrieve data. The @code{WHERE}
clause is optional. If it's present, @code{conditions_to_satisfy} specifies
conditions that rows must satisfy to qualify for retrieval.
@node Selecting all, Selecting rows, Retrieving data, Retrieving data
@subsubsection Selecting All Data
The simplest form of @code{SELECT} retrieves everything from a table:
@example
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
@end example
This form of @code{SELECT} is useful if you want to review your entire table,
for instance, after you've just loaded it with your initial dataset. As it
happens, the output just shown reveals an error in your data file: Bowser
appears to have been born after he died! Consulting your original pedigree
papers, you find that the correct birth year is 1989, not 1998.
There are are least a couple of ways to fix this:
@itemize @bullet
@item
Edit the file @file{pet.txt} to correct the error, then empty the table
and reload it using @code{DELETE} and @code{LOAD DATA}:
@example
mysql> SET AUTOCOMMIT=1; # Used for quick re-create of the table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
@end example
However, if you do this, you must also re-enter the record for Puffball.
@item
Fix only the erroneous record with an @code{UPDATE} statement:
@example
mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Bowser";
@end example
@end itemize
As shown above, it is easy to retrieve an entire table. But typically you
don't want to do that, particularly when the table becomes large. Instead,
you're usually more interested in answering a particular question, in which
case you specify some constraints on the information you want. Let's look at
some selection queries in terms of questions about your pets that they
answer.
@cindex rows, selecting
@cindex tables, selecting rows
@node Selecting rows, Selecting columns, Selecting all, Retrieving data
@subsubsection Selecting Particular Rows
You can select only particular rows from your table. For example, if you want
to verify the change that you made to Bowser's birth date, select Bowser's
record like this:
@example
mysql> SELECT * FROM pet WHERE name = "Bowser";
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
@end example
The output confirms that the year is correctly recorded now as 1989, not 1998.
String comparisons are normally case insensitive, so you can specify the
name as @code{"bowser"}, @code{"BOWSER"}, etc. The query result will be
the same.
You can specify conditions on any column, not just @code{name}. For example,
if you want to know which animals were born after 1998, test the @code{birth}
column:
@example
mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
@end example
You can combine conditions, for example, to locate female dogs:
@example
mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
@end example
The preceding query uses the @code{AND} logical operator. There is also an
@code{OR} operator:
@example
mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird";
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+-------+---------+------+------------+-------+
@end example
@code{AND} and @code{OR} may be intermixed. If you do that, it's a good idea
to use parentheses to indicate how conditions should be grouped:
@example
mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m")
-> OR (species = "dog" AND sex = "f");
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
@end example
@cindex columns, selecting
@cindex tables, selecting columns
@node Selecting columns, Sorting rows, Selecting rows, Retrieving data
@subsubsection Selecting Particular Columns
If you don't want to see entire rows from your table, just name the columns
in which you're interested, separated by commas. For example, if you want to
know when your animals were born, select the @code{name} and @code{birth}
columns:
@example
mysql> SELECT name, birth FROM pet;
+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+
@end example
To find out who owns pets, use this query:
@example
mysql> SELECT owner FROM pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
| Diane |
+--------+
@end example
@findex DISTINCT
However, notice that the query simply retrieves the @code{owner} field from
each record, and some of them appear more than once. To minimize the output,
retrieve each unique output record just once by adding the keyword
@code{DISTINCT}:
@example
mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner |
+--------+
| Benny |
| Diane |
| Gwen |
| Harold |
+--------+
@end example
You can use a @code{WHERE} clause to combine row selection with column
selection. For example, to get birth dates for dogs and cats only,
use this query:
@example
mysql> SELECT name, species, birth FROM pet
-> WHERE species = "dog" OR species = "cat";
+--------+---------+------------+
| name | species | birth |
+--------+---------+------------+
| Fluffy | cat | 1993-02-04 |
| Claws | cat | 1994-03-17 |
| Buffy | dog | 1989-05-13 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
+--------+---------+------------+
@end example
@cindex rows, sorting
@cindex sorting, table rows
@cindex sorting, data
@cindex tables, sorting rows
@node Sorting rows, Date calculations, Selecting columns, Retrieving data
@subsubsection Sorting Rows
You may have noticed in the preceding examples that the result rows are
displayed in no particular order. However, it's often easier to examine
query output when the rows are sorted in some meaningful way. To sort a
result, use an @code{ORDER BY} clause.
Here are animal birthdays, sorted by date:
@example
mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name | birth |
+----------+------------+
| Buffy | 1989-05-13 |
| Bowser | 1989-08-31 |
| Fang | 1990-08-27 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Slim | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+
@end example
To sort in reverse order, add the @code{DESC} (descending) keyword to the
name of the column you are sorting by:
@example
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name | birth |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Claws | 1994-03-17 |
| Fluffy | 1993-02-04 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Buffy | 1989-05-13 |
+----------+------------+
@end example
You can sort on multiple columns. For example, to sort by type of
animal, then by birth date within animal type with youngest animals first,
use the following query:
@example
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
+----------+---------+------------+
| name | species | birth |
+----------+---------+------------+
| Chirpy | bird | 1998-09-11 |
| Whistler | bird | 1997-12-09 |
| Claws | cat | 1994-03-17 |
| Fluffy | cat | 1993-02-04 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
| Buffy | dog | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim | snake | 1996-04-29 |
+----------+---------+------------+
@end example
Note that the @code{DESC} keyword applies only to the column name immediately
preceding it (@code{birth}); @code{species} values are still sorted in
ascending order.
@cindex date calculations
@cindex calculating, dates
@cindex extracting, dates
@cindex age, calculating
@node Date calculations, Working with NULL, Sorting rows, Retrieving data
@subsubsection Date Calculations
@strong{MySQL} provides several functions that you can use to perform
calculations on dates, for example, to calculate ages or extract
parts of dates.
To determine how many years old each of your pets is, compute age as the
difference between the birth date and the current date. Do this by
converting the two dates to days, take the difference, and divide by 365 (the
number of days in a year):
@example
mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 FROM pet;
+----------+-------------------------------------+
| name | (TO_DAYS(NOW())-TO_DAYS(birth))/365 |
+----------+-------------------------------------+
| Fluffy | 6.15 |
| Claws | 5.04 |
| Buffy | 9.88 |
| Fang | 8.59 |
| Bowser | 9.58 |
| Chirpy | 0.55 |
| Whistler | 1.30 |
| Slim | 2.92 |
| Puffball | 0.00 |
+----------+-------------------------------------+
@end example
Although the query works, there are some things about it that could be
improved. First, the result could be scanned more easily if the rows were
presented in some order. Second, the heading for the age column isn't very
meaningful.
The first problem can be handled by adding an @code{ORDER BY name} clause to
sort the output by name. To deal with the column heading, provide a name for
the column so that a different label appears in the output (this is called a
column alias):
@example
mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age
-> FROM pet ORDER BY name;
+----------+------+
| name | age |
+----------+------+
| Bowser | 9.58 |
| Buffy | 9.88 |
| Chirpy | 0.55 |
| Claws | 5.04 |
| Fang | 8.59 |
| Fluffy | 6.15 |
| Puffball | 0.00 |
| Slim | 2.92 |
| Whistler | 1.30 |
+----------+------+
@end example
To sort the output by @code{age} rather than @code{name}, just use a
different @code{ORDER BY} clause:
@example
mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age
-> FROM pet ORDER BY age;
+----------+------+
| name | age |
+----------+------+
| Puffball | 0.00 |
| Chirpy | 0.55 |
| Whistler | 1.30 |
| Slim | 2.92 |
| Claws | 5.04 |
| Fluffy | 6.15 |
| Fang | 8.59 |
| Bowser | 9.58 |
| Buffy | 9.88 |
+----------+------+
@end example
A similar query can be used to determine age at death for animals that have
died. You determine which animals these are by checking whether or not the
@code{death} value is @code{NULL}. Then, for those with non-@code{NULL}
values, compute the difference between the @code{death} and @code{birth}
values:
@example
mysql> SELECT name, birth, death, (TO_DAYS(death)-TO_DAYS(birth))/365 AS age
-> FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5.91 |
+--------+------------+------------+------+
@end example
The query uses @code{death IS NOT NULL} rather than @code{death != NULL}
because @code{NULL} is a special value. This is explained later.
@xref{Working with NULL, , Working with @code{NULL}}.
What if you want to know which animals have birthdays next month? For this
type of calculation, year and day are irrelevant; you simply want to extract
the month part of the @code{birth} column. @strong{MySQL} provides several
date-part extraction functions, such as @code{YEAR()}, @code{MONTH()}, and
@code{DAYOFMONTH()}. @code{MONTH()} is the appropriate function here. To
see how it works, run a simple query that displays the value of both
@code{birth} and @code{MONTH(birth)}:
@example
mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name | birth | MONTH(birth) |
+----------+------------+--------------+
| Fluffy | 1993-02-04 | 2 |
| Claws | 1994-03-17 | 3 |
| Buffy | 1989-05-13 | 5 |
| Fang | 1990-08-27 | 8 |
| Bowser | 1989-08-31 | 8 |
| Chirpy | 1998-09-11 | 9 |
| Whistler | 1997-12-09 | 12 |
| Slim | 1996-04-29 | 4 |
| Puffball | 1999-03-30 | 3 |
+----------+------------+--------------+
@end example
Finding animals with birthdays in the upcoming month is easy, too. Suppose
the current month is April. Then the month value is @code{4} and you look
for animals born in May (month 5) like this:
@example
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
@end example
There is a small complication if the current month is December, of course.
You don't just add one to the month number (@code{12}) and look for animals
born in month 13, because there is no such month. Instead, you look for
animals born in January (month 1).
You can even write the query so that it works no matter what the current
month is. That way you don't have to use a particular month number
in the query. @code{DATE_ADD()} allows you to add a time interval to a
given date. If you add a month to the value of @code{NOW()}, then extract
the month part with @code{MONTH()}, the result produces the month in which to
look for birthdays:
@example
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));
@end example
A different way to accomplish the same task is to add @code{1} to get the
next month after the current one (after using the modulo function (@code{MOD})
to wrap around the month value to @code{0} if it is currently
@code{12}):
@example
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;
@end example
Note that @code{MONTH} returns a number between 1 and 12. And
@code{MOD(something,12)} returns a number between 0 and 11. So the
addition has to be after the @code{MOD()} otherwise we would go from
November (11) to January (1).
@findex NULL
@cindex NULL value
@node Working with NULL, Pattern matching, Date calculations, Retrieving data
@subsubsection Working with @code{NULL} Values
The @code{NULL} value can be surprising until you get used to it.
Conceptually, @code{NULL} means missing value or unknown value and it
is treated somewhat differently than other values. To test for @code{NULL},
you cannot use the arithmetic comparison operators such as @code{=}, @code{<},
or @code{!=}. To demonstrate this for yourself, try the following query:
@example
mysql> SELECT 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 != NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
@end example
Clearly you get no meaningful results from these comparisons. Use
the @code{IS NULL} and @code{IS NOT NULL} operators instead:
@example
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
@end example
In @strong{MySQL}, 0 or @code{NULL} means false and anything else means true.
The default trueth value from a boolean operation is 1.
This special treatment of @code{NULL} is why, in the previous section, it
was necessary to determine which animals are no longer alive using
@code{death IS NOT NULL} instead of @code{death != NULL}.
@cindex pattern matching
@cindex matching, patterns
@cindex expressions, extended
@node Pattern matching, Counting rows, Working with NULL, Retrieving data
@subsubsection Pattern Matching
@strong{MySQL} provides standard SQL pattern matching as well as a form of
pattern matching based on extended regular expressions similar to those used
by Unix utilities such as @code{vi}, @code{grep}, and @code{sed}.
SQL pattern matching allows you to use @samp{_} to match any single
character and @samp{%} to match an arbitrary number of characters (including
zero characters). In @strong{MySQL}, SQL patterns are case insensitive by
default. Some examples are shown below. Note that you do not use @code{=}
or @code{!=} when you use SQL patterns; use the @code{LIKE} or @code{NOT
LIKE} comparison operators instead.
To find names beginning with @samp{b}:
@example
mysql> SELECT * FROM pet WHERE name LIKE "b%";
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
@end example
To find names ending with @samp{fy}:
@example
mysql> SELECT * FROM pet WHERE name LIKE "%fy";
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
@end example
To find names containing a @samp{w}:
@example
mysql> SELECT * FROM pet WHERE name LIKE "%w%";
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
@end example
To find names containing exactly five characters, use the @samp{_} pattern
character:
@example
mysql> SELECT * FROM pet WHERE name LIKE "_____";
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
@end example
The other type of pattern matching provided by @strong{MySQL} uses extended
regular expressions. When you test for a match for this type of pattern, use
the @code{REGEXP} and @code{NOT REGEXP} operators (or @code{RLIKE} and
@code{NOT RLIKE}, which are synonyms).
Some characteristics of extended regular expressions are:
@itemize @bullet
@item
@samp{.} matches any single character.