MySQL数据库

开发平台：

Visual C++

manual.txt：源码内容

`cp1251_koi8', but you can easily add new mappings by editing the
`sql/convert.cc' file in the *MySQL* source distribution. The
default mapping can be restored by using a `character_set_name'
value of `DEFAULT'.
Note that the syntax for setting the `CHARACTER SET' option differs
from the syntax for setting the other options.
`PASSWORD = PASSWORD('some password')'
Set the password for the current user. Any non-anonymous user can
change his own password!
`PASSWORD FOR user = PASSWORD('some password')'
Set the password for a specific user on the current server host.
Only a user with access to the `mysql' database can do this. The
user should be given in `user@hostname' format, where `user' and
`hostname' are exactly as they are listed in the `User' and `Host'
columns of the `mysql.user' table entry. For example, if you had
an entry with `User' and `Host' fields of `'bob'' and
`'%.loc.gov'', you would write:
mysql> SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass");
or
mysql> UPDATE mysql.user SET password=PASSWORD("newpass") where user="bob' and host="%.loc.gov";
`SQL_AUTO_IS_NULL = 0 | 1'
If set to `1' (default) then one can find the last inserted row
for a table with an auto_increment row with the following
construct: `WHERE auto_increment_column IS NULL'. This is used by
some ODBC programs like Access.
`AUTOCOMMIT= 0 | 1'
If set to `1' all changes to a table will be done at once. To start
a multi-command transaction, you have to use the `BEGIN'
statement. *Note COMMIT::. If set to `0' you have to use `COMMIT' /
`ROLLBACK' to accept/revoke that transaction. *Note COMMIT::. Note
that when you change from not `AUTOCOMMIT' mode to `AUTOCOMMIT'
mode, *MySQL* will do an automatic `COMMIT' on any open
transactions.
`SQL_BIG_TABLES = 0 | 1'
If set to `1', all temporary tables are stored on disk rather than
in memory. This will be a little slower, but you will not get the
error `The table tbl_name is full' for big `SELECT' operations that
require a large temporary table. The default value for a new
connection is `0' (that is, use in-memory temporary tables).
`SQL_BIG_SELECTS = 0 | 1'
If set to `0', *MySQL* will abort if a `SELECT' is attempted that
probably will take a very long time. This is useful when an
inadvisable `WHERE' statement has been issued. A big query is
defined as a `SELECT' that probably will have to examine more than
`max_join_size' rows. The default value for a new connection is
`1' (which will allow all `SELECT' statements).
`SQL_BUFFER_RESULT = 0 | 1'
`SQL_BUFFER_RESULT' will force the result from `SELECT''s to be
put into a temporary table. This will help *MySQL* free the table
locks early and will help in cases where it takes a long time to
send the result set to the client.
`SQL_LOW_PRIORITY_UPDATES = 0 | 1'
If set to `1', all `INSERT', `UPDATE', `DELETE', and and `LOCK
TABLE WRITE' statements wait until there is no pending `SELECT' or
`LOCK TABLE READ' on the affected table.
`SQL_MAX_JOIN_SIZE = value | DEFAULT'
Don't allow `SELECT's that will probably need to examine more than
`value' row combinations. By setting this value, you can catch
`SELECT's where keys are not used properly and that would probably
take a long time. Setting this to a value other than `DEFAULT'
will reset the `SQL_BIG_SELECTS' flag. If you set the
`SQL_BIG_SELECTS' flag again, the `SQL_MAX_JOIN_SIZE' variable
will be ignored. You can set a default value for this variable by
starting `mysqld' with `-O max_join_size=#'.
`SQL_SAFE_MODE = 0 | 1'
If set to `1', *MySQL* will abort if an `UPDATE' or `DELETE' is
attempted that doesn't use a key or `LIMIT' in the `WHERE' clause.
This makes it possible to catch wrong updates when creating SQL
commands by hand.
`SQL_SELECT_LIMIT = value | DEFAULT'
The maximum number of records to return from `SELECT' statements.
If a `SELECT' has a `LIMIT' clause, the `LIMIT' takes precedence
over the value of `SQL_SELECT_LIMIT'. The default value for a new
connection is "unlimited." If you have changed the limit, the
default value can be restored by using a `SQL_SELECT_LIMIT' value
of `DEFAULT'.
`SQL_LOG_OFF = 0 | 1'
If set to `1', no logging will be done to the standard log for this
client, if the client has the *process* privilege. This does not
affect the update log!
`SQL_LOG_UPDATE = 0 | 1'
If set to `0', no logging will be done to the update log for the
client, if the client has the *process* privilege. This does not
affect the standard log!
`SQL_QUOTE_SHOW_CREATE = 0 | 1'
If set to `1', `SHOW CREATE TABLE' will quote table and column
names. This is *on* by default, for replication of tables with
fancy column names to work. *Note `SHOW CREATE TABLE': SHOW
CREATE TABLE.
`TIMESTAMP = timestamp_value | DEFAULT'
Set the time for this client. This is used to get the original
timestamp if you use the update log to restore rows.
`LAST_INSERT_ID = #'
Set the value to be returned from `LAST_INSERT_ID()'. This is
stored in the update log when you use `LAST_INSERT_ID()' in a
command that updates a table.
`INSERT_ID = #'
Set the value to be used by the following `INSERT' or `ALTER TABLE'
command when inserting an `AUTO_INCREMENT' value. This is mainly
used with the update log.
`GRANT' and `REVOKE' Syntax
===========================
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[WITH GRANT OPTION]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]
`GRANT' is implemented in *MySQL* Version 3.22.11 or later. For earlier
*MySQL* versions, the `GRANT' statement does nothing.
The `GRANT' and `REVOKE' commands allow system administrators to grant
and revoke rights to *MySQL* users at four privilege levels:
*Global level*
Global privileges apply to all databases on a given server. These
privileges are stored in the `mysql.user' table.
*Database level*
Database privileges apply to all tables in a given database. These
privileges are stored in the `mysql.db' and `mysql.host' tables.
*Table level*
Table privileges apply to all columns in a given table. These
privileges are stored in the `mysql.tables_priv' table.
*Column level*
Column privileges apply to single columns in a given table. These
privileges are stored in the `mysql.columns_priv' table.
For examples of how `GRANT' works, see *Note Adding users::.
For the `GRANT' and `REVOKE' statements, `priv_type' may be specified
as any of the following:
ALL PRIVILEGES FILE RELOAD
ALTER INDEX SELECT
CREATE INSERT SHUTDOWN
DELETE PROCESS UPDATE
DROP REFERENCES USAGE
`ALL' is a synonym for `ALL PRIVILEGES'. `REFERENCES' is not yet
implemented. `USAGE' is currently a synonym for "no privileges." It
can be used when you want to create a user that has no privileges.
To revoke the *grant* privilege from a user, use a `priv_type' value of
`GRANT OPTION':
REVOKE GRANT OPTION ON ... FROM ...;
The only `priv_type' values you can specify for a table are `SELECT',
`INSERT', `UPDATE', `DELETE', `CREATE', `DROP', `GRANT', `INDEX', and
`ALTER'.
The only `priv_type' values you can specify for a column (that is, when
you use a `column_list' clause) are `SELECT', `INSERT', and `UPDATE'.
You can set global privileges by using `ON *.*' syntax. You can set
database privileges by using `ON db_name.*' syntax. If you specify `ON
*' and you have a current database, you will set the privileges for
that database. (*WARNING:* If you specify `ON *' and you _don't_ have
a current database, you will affect the global privileges!)
In order to accommodate granting rights to users from arbitrary hosts,
*MySQL* supports specifying the `user_name' value in the form
`user@host'. If you want to specify a `user' string containing special
characters (such as `-'), or a `host' string containing special
characters or wild-card characters (such as `%'), you can quote the
user or host name (for example, `'test-user'@'test-hostname'').
You can specify wild cards in the hostname. For example,
`user@"%.loc.gov"' applies to `user' for any host in the `loc.gov'
domain, and `user@"144.155.166.%"' applies to `user' for any host in
the `144.155.166' class C subnet.
The simple form `user' is a synonym for `user@"%"'. *NOTE:* If you
allow anonymous users to connect to the *MySQL* server (which is the
default), you should also add all local users as `user@localhost'
because otherwise the anonymous user entry for the local host in the
`mysql.user' table will be used when the user tries to log into the
*MySQL* server from the local machine! Anonymous users are defined by
inserting entries with `User=''' into the `mysql.user' table. You can
verify if this applies to you by executing this query:
mysql> SELECT Host,User FROM mysql.user WHERE User='';
For the moment, `GRANT' only supports host, table, database, and column
names up to 60 characters long. A user name can be up to 16 characters.
The privileges for a table or column are formed from the logical OR of
the privileges at each of the four privilege levels. For example, if
the `mysql.user' table specifies that a user has a global *select*
privilege, this can't be denied by an entry at the database, table, or
column level.
The privileges for a column can be calculated as follows:
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
In most cases, you grant rights to a user at only one of the privilege
levels, so life isn't normally as complicated as above. The details of
the privilege-checking procedure are presented in *Note Privilege
system::.
If you grant privileges for a user/hostname combination that does not
exist in the `mysql.user' table, an entry is added and remains there
until deleted with a `DELETE' command. In other words, `GRANT' may
create `user' table entries, but `REVOKE' will not remove them; you
must do that explicitly using `DELETE'.
In *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 `IDENTIFIED BY' clause, if one is given. If
the user already had a password, it is replaced by the new one.
*WARNING:* If you create a new user but do not specify an `IDENTIFIED
BY' clause, the user has no password. This is insecure.
Passwords can also be set with the `SET PASSWORD' command. *Note `SET
OPTION': SET OPTION.
If you grant privileges for a database, an entry in the `mysql.db'
table is created if needed. When all privileges for the database have
been removed with `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
`SHOW TABLES' statement).
The `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 *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
*grant* privilege allows you to give away only those privileges you
possess.
Be aware that when you grant a user the *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 *insert* privilege on a database.
If you then grant the *select* privilege on the database and specify
`WITH GRANT OPTION', the user can give away not only the *select*
privilege, but also *insert*. If you then grant the *update* privilege
to the user on the database, the user can give away the *insert*,
*select* and *update*.
You should not grant *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 *MySQL* a bit.
When `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 `GRANT' or `REVOKE' are
noticed by the server immediately. If you modify the grant tables
manually (using `INSERT', `UPDATE', etc.), you should execute a `FLUSH
PRIVILEGES' statement or run `mysqladmin flush-privileges' to tell the
server to reload the grant tables. *Note Privilege changes::.
The biggest differences between the ANSI SQL and *MySQL* versions of
`GRANT' are:
* In *MySQL* privileges are given for an username + hostname
combination and not only for an username.
* ANSI SQL doesn't have global or database-level privileges, and
ANSI SQL doesn't support all privilege types that *MySQL* supports.
*MySQL* doesn't support the ANSI SQL `TRIGGER', `EXECUTE' or
`UNDER' privileges.
* ANSI SQL privileges are structured in a hierarchal manner. If you
remove an user, all privileges the user has granted are revoked. In
*MySQL* the granted privileges are not automaticly revoked, but
you have to revoke these yourself if needed.
* If you in *MySQL* have the `INSERT' grant on only part of the
columns in a table, you can execute `INSERT' statements on the
table; The columns for which you don't have the `INSERT' privilege
will set to their default values. ANSI SQL requires you to have the
`INSERT' privilege on all columns.
* 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 *MySQL*, privileges can be dropped only with explicit
`REVOKE' commands or by manipulating the *MySQL* grant tables.
`CREATE INDEX' Syntax
=====================
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],... )
The `CREATE INDEX' statement doesn't do anything in *MySQL* prior to
Version 3.22. In Version 3.22 or later, `CREATE INDEX' is mapped to an
`ALTER TABLE' statement to create indexes. *Note `ALTER TABLE': ALTER
TABLE.
Normally, you create all indexes on a table at the time the table itself
is created with `CREATE TABLE'. *Note `CREATE TABLE': CREATE TABLE.
`CREATE INDEX' allows you to add indexes to existing tables.
A column list of the form `(col1,col2,...)' creates a multiple-column
index. Index values are formed by concatenating the values of the given
columns.
For `CHAR' and `VARCHAR' columns, indexes can be created that use only
part of a column, using `col_name(length)' syntax. (On `BLOB' and
`TEXT' columns the length is required). The statement shown below
creates an index using the first 10 characters of the `name' column:
mysql> CREATE INDEX part_of_name ON customer (name(10));
Because most names usually differ in the first 10 characters, this
index should not be much slower than an index created from the entire
`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 `INSERT' operations!
Note that you can only add an index on a column that can have `NULL'
values or on a `BLOB'/`TEXT' column if you are using *MySQL* Version
3.23.2 or newer and are using the `MyISAM' table type.
For more information about how *MySQL* uses indexes, see *Note *MySQL*
indexes: MySQL indexes.
`FULLTEXT' indexes can index only `VARCHAR' and `TEXT' columns, and
only in `MyISAM' tables. `FULLTEXT' indexes are available in *MySQL*
Version 3.23.23 and later. *Note MySQL full-text search::.
`DROP INDEX' Syntax
===================
DROP INDEX index_name ON tbl_name
`DROP INDEX' drops the index named `index_name' from the table
`tbl_name'. `DROP INDEX' doesn't do anything in *MySQL* prior to
Version 3.22. In Version 3.22 or later, `DROP INDEX' is mapped to an
`ALTER TABLE' statement to drop the index. *Note `ALTER TABLE': ALTER
TABLE.
Comment Syntax
==============
The *MySQL* server supports the `# to end of line', `-- to end of line'
and `/* in-line or multiple-line */' comment styles:
mysql> select 1+1; # This comment continues to the end of line
mysql> select 1+1; -- This comment continues to the end of line
mysql> select 1 /* this is an in-line comment */ + 1;
mysql> select 1+
/*
this is a
multiple-line comment
*/
1;
Note that the `--' comment style requires you to have at least one space
after the `--'!
Although the server understands the comment syntax just described,
there are some limitations on the way that the `mysql' client parses
`/* ... */' comments:
* Single-quote and double-quote characters are taken to indicate the
beginning of a quoted string, even within a comment. If the quote
is not matched by a second quote within the comment, the parser
doesn't realize the comment has ended. If you are running `mysql'
interactively, you can tell that it has gotten confused like this
because the prompt changes from `mysql>' to `'>' or `">'.
* A semicolon is taken to indicate the end of the current SQL
statement and anything following it to indicate the beginning of
the next statement.
These limitations apply both when you run `mysql' interactively and
when you put commands in a file and tell `mysql' to read its input from
that file with `mysql < some-file'.
*MySQL* doesn't support the `--' ANSI SQL comment style. *Note Missing
comments::.
`CREATE FUNCTION/DROP FUNCTION' Syntax
======================================
CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|REAL|INTEGER}
SONAME shared_library_name
DROP FUNCTION function_name
A user-definable function (UDF) is a way to extend *MySQL* with a new
function that works like native (built in) *MySQL* functions such as
`ABS()' and `CONCAT()'.
`AGGREGATE' is a new option for *MySQL* Version 3.23. An `AGGREGATE'
function works exactly like a native *MySQL* `GROUP' function like
`SUM' or `COUNT()'.
`CREATE FUNCTION' saves the function's name, type, and shared library
name in the `mysql.func' system table. You must have the *insert* and
*delete* privileges for the `mysql' database to create and drop
functions.
All active functions are reloaded each time the server starts, unless
you start `mysqld' with the `--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 `CREATE FUNCTION' and
not removed with `DROP FUNCTION'.)
For instructions on writing user-definable functions, see *Note 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 `mysqld' dynamically (not statically).
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 *MySQL*, such as
`TIMESTAMP' or `GROUP'. You're allowed to do it (for example, `ABS' is
an allowed column name), but whitespace is not allowed between a
function name and the `(' when using functions whose names are also
column names.
The following words are explicitly reserved in *MySQL*. Most of them
are forbidden by ANSI SQL92 as column and/or table names (for example,
`group'). A few are reserved because *MySQL* needs them and is
(currently) using a `yacc' parser:
`action' `add' `aggregate' `all'
`alter' `after' `and' `as'
`asc' `avg' `avg_row_length' `auto_increment'
`between' `bigint' `bit' `binary'
`blob' `bool' `both' `by'
`cascade' `case' `char' `character'
`change' `check' `checksum' `column'
`columns' `comment' `constraint' `create'
`cross' `current_date' `current_time' `current_timestamp'
`data' `database' `databases' `date'
`datetime' `day' `day_hour' `day_minute'
`day_second' `dayofmonth' `dayofweek' `dayofyear'
`dec' `decimal' `default' `delayed'
`delay_key_write' `delete' `desc' `describe'
`distinct' `distinctrow' `double' `drop'
`end' `else' `escape' `escaped'
`enclosed' `enum' `explain' `exists'
`fields' `file' `first' `float'
`float4' `float8' `flush' `foreign'
`from' `for' `full' `function'
`global' `grant' `grants' `group'
`having' `heap' `high_priority' `hour'
`hour_minute' `hour_second' `hosts' `identified'
`ignore' `in' `index' `infile'
`inner' `insert' `insert_id' `int'
`integer' `interval' `int1' `int2'
`int3' `int4' `int8' `into'
`if' `is' `isam' `join'
`key' `keys' `kill' `last_insert_id'
`leading' `left' `length' `like'
`lines' `limit' `load' `local'
`lock' `logs' `long' `longblob'
`longtext' `low_priority' `max' `max_rows'
`match' `mediumblob' `mediumtext' `mediumint'
`middleint' `min_rows' `minute' `minute_second'
`modify' `month' `monthname' `myisam'
`natural' `numeric' `no' `not'
`null' `on' `optimize' `option'
`optionally' `or' `order' `outer'
`outfile' `pack_keys' `partial' `password'
`precision' `primary' `procedure' `process'
`processlist' `privileges' `read' `real'
`references' `reload' `regexp' `rename'
`replace' `restrict' `returns' `revoke'
`rlike' `row' `rows' `second'
`select' `set' `show' `shutdown'
`smallint' `soname' `sql_big_tables' `sql_big_selects'
`sql_low_priority_updates'`sql_log_off' `sql_log_update' `sql_select_limit'
`sql_small_result' `sql_big_result' `sql_warnings' `straight_join'
`starting' `status' `string' `table'
`tables' `temporary' `terminated' `text'
`then' `time' `timestamp' `tinyblob'
`tinytext' `tinyint' `trailing' `to'
`type' `use' `using' `unique'
`unlock' `unsigned' `update' `usage'
`values' `varchar' `variables' `varying'
`varbinary' `with' `write' `when'
`where' `year' `year_month' `zerofill'
The following symbols (from the table above) are disallowed by ANSI SQL
but allowed by *MySQL* as column/table names. This is because some of
these names are very natural names and a lot of people have already
used them.
* `ACTION'
* `BIT'
* `DATE'
* `ENUM'
* `NO'
* `TEXT'
* `TIME'
* `TIMESTAMP'
MySQL Table Types
*****************
As of *MySQL* Version 3.23.6, you can choose between three basic table
formats (`ISAM', `HEAP' and `MyISAM'. Newer *MySQL* may support
additional table type (`BDB', `GEMINI' or `INNOBASE'), depending on how
you compile it.
When you create a new table, you can tell *MySQL* which table type it
should use for the table. *MySQL* will always create a `.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 *MySQL* is `MyISAM'. If you are trying to use
a table type that is not incompiled or activated, *MySQL* will instead
create a table of type `MyISAM'.
You can convert tables between different types with the `ALTER TABLE'
statement. *Note `ALTER TABLE': ALTER TABLE.
Note that *MySQL* supports two different kinds of tables.
Transaction-safe tables (`BDB', `INNOBASE' or `GEMINI') and not
transaction-safe tables (`HEAP', `ISAM', `MERGE', and `MyISAM').
Advantages of transaction-safe tables (TST):
* Safer. Even if *MySQL* crashes or you get hardware problems, you
can get your data back, either by automatic recovery or from a
backup + the transaction log.
* You can combine many statements and accept these all in one go with
the `COMMIT' command.
* You can execute `ROLLBACK' to ignore your changes (if you are not
running in auto commit mode).
* If an update fails, all your changes will be restored. (With NTST
tables all changes that have taken place are permanent)
Advantages of not transaction-safe tables (NTST):
* Much faster as there is no transcation overhead.
* Will use less disk space as there is no overhead of transactions.
* Will use less memory to do updates.
You can combine TST and NTST tables in the same statements to get the
best of both worlds.
MyISAM Tables
=============
`MyISAM' is the default table type in *MySQL* Version 3.23. It's based
on the `ISAM' code and has a lot of useful extensions.
The index is stored in a file with the `.MYI' (MYIndex) extension, and
the data is stored in a file with the `.MYD' (MYData) extension. You
can check/repair `MyISAM' tables with the `myisamchk' utility. *Note
Crash recovery::.
The following is new in `MyISAM':
* There is a flag in the `MyISAM' file that indicates whether or not
the table was closed correctly. If `mysqld' is started with
`--myisam-recover', `MyISAM' tables will automaticly be checked
and/or repaired on open if the table wasn't closed properly.
* You can `INSERT' new rows in a table without deleted rows, while
other threads are reading from the table.
* Support for big files (63-bit) on filesystems/operating systems
that support big files.
* 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.
* All number keys are stored with high byte first to give better
index compression.
* Internal handling of one `AUTO_INCREMENT' column. `MyISAM' will
automatically update this on `INSERT/UPDATE'. The `AUTO_INCREMENT'
value can be reset with `myisamchk'. This will make
`AUTO_INCREMENT' columns faster (at least 10 %) and old numbers
will not be reused as with the old ISAM. Note that when an
`AUTO_INCREMENT' is defined on the end of a multi-part-key the old
behavior is still present.
* When inserted in sorted order (as when you are using an
`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.
* `BLOB' and `TEXT' columns can be indexed.
* `NULL' values are allowed in indexed columns. This takes 0-1
bytes/key.
* 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.
* Maximum number of keys/table is 32 as default. This can be
enlarged to 64 without having to recompile `myisamchk'.
* `myisamchk' will mark tables as checked if one runs it with
`--update-state'. `myisamchk --fast' will only check those tables
that don't have this mark.
* `myisamchk -a' stores statistics for key parts (and not only for
whole keys as in `ISAM').
* 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.
* `myisampack' can pack `BLOB' and `VARCHAR' columns.
`MyISAM' also supports the following things, which *MySQL* will be able
to use in the near future:
* Support for a true `VARCHAR' type; A `VARCHAR' column starts with
a length stored in 2 bytes.
* Tables with `VARCHAR' may have fixed or dynamic record length.
* `VARCHAR' and `CHAR' may be up to 64K. All key segments have
their own language definition. This will enable *MySQL* to have
different language definitions per column.
* A hashed computed index can be used for `UNIQUE'. This will allow
you to have `UNIQUE' on any combination of columns in a table. (You
can't search on a `UNIQUE' computed index, however.)
Note that index files are usually much smaller with `MyISAM' than with
`ISAM'. This means that `MyISAM' will normally use less system
resources than `ISAM', but will need more CPU when inserting data into
a compressed index.
The following options to `mysqld' can be used to change the behavior of
`MyISAM' tables:
*Option* *Meaning*
`--myisam-recover=#' Automatic recover of crashed tables.
`-O Buffer used when recovering tables.
myisam_sort_buffer_size=#'
`--delay-key-write-for-all-tables'Don't flush key buffers between writes for
any MyISAM table
The automatic recovery is activated if you start mysqld with
`--myisam-recover=#'. *Note 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 `--skip-locking'. If
either of the above is true the following happens.
* The table is checked for errors.
* If we found an error, try to do a fast repair (with sorting and
without re-creating the data file) of the table.
* 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.
* 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..
If the recover wouldn't be able to recover all rows from a previous
completed statement and you didn't specify `FORCE' as an option to
`myisam-recover', then the automatic repair will abort with an error
message in the error file:
Error: Couldn't repair table: test.g00pages
If you in this case had used the `FORCE' option you would instead have
got a warning in the error file:
Warning: Found 344 of 354 rows when repairing ./test/g00pages
Note that if you run automatic recover with the `BACKUP' option, you
should have a cron script that automaticly moves file with names like
`tablename-datetime.BAK' from the database directories to a backup
media.
*Note Command-line options::.
Space Needed for Keys
---------------------
*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 `(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 `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 `MyISAM' tables, you can also prefix compress numbers by specifying
`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.
MyISAM Table Formats
--------------------
*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 `myisampack' tool.
Static (Fixed-length) Table Characteristics
...........................................
This is the default format. It's used when the table contains no
`VARCHAR', `BLOB', or `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 `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 *MySQL* all indexes can
always be reconstructed:
* All `CHAR', `NUMERIC', and `DECIMAL' columns are space-padded to
the column width.
* Very quick.
* Easy to cache.
* Easy to reconstruct after a crash, because records are located in
fixed positions.
* Doesn't have to be reorganized (with `myisamchk') unless a huge
number of records are deleted and you want to return free disk
space to the operating system.
* Usually requires more disk space than dynamic tables.
Dynamic Table Characteristics
.............................
This format is used if the table contains any `VARCHAR', `BLOB', or
`TEXT' columns or if the table was created with `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.
You can use `OPTIMIZE table' or `myisamchk' to defragment a table. If
you have static data that you access/change a lot in the same table as
some `VARCHAR' or `BLOB' columns, it might be a good idea to move the
dynamic columns to other tables just to avoid fragmentation:
* All string columns are dynamic (except those with a length less
than 4).
* Each record is preceded by a bitmap indicating which columns are
empty (`''') for string columns, or zero for numeric columns.
(This isn't the same as columns containing `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.
* Usually takes much less disk space than fixed-length tables.
* 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.
* 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
`myisamchk -r' from time to time to get better performance. Use
`myisamchk -ei tbl_name' for some statistics.
* Not as easy to reconstruct after a crash, because a record may be
fragmented into many pieces and a link (fragment) may be missing.
* The expected row length for dynamic sized records is:
3
+ (number of columns + 7) / 8
+ (number of char columns)
+ packed size of numeric columns
+ length of strings
+ (number of NULL columns + 7) / 8
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 `myisamchk -ed'.
All links may be removed with `myisamchk -r'.
Compressed Table Characteristics
................................
This is a read-only type that is generated with the optional
`myisampack' tool (`pack_isam' for `ISAM' tables):
* All MySQL distributions, even those that existed before *MySQL*
went GPL, can read tables that were compressed with `myisampack'.
* Compressed tables take very little disk space. This minimizes disk
usage, which is very nice when using slow disks (like CD-ROMs).
* 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:
- There is usually a different Huffman table for each column.
- Suffix space compression.
- Prefix space compression.
- Numbers with value `0' are stored using 1 bit.
- If values in an integer column have a small range, the column
is stored using the smallest possible type. For example, a
`BIGINT' column (8 bytes) may be stored as a `TINYINT' column
(1 byte) if all values are in the range `0' to `255'.
- If a column has only a small set of possible values, the
column type is converted to `ENUM'.
- A column may use a combination of the above compressions.
* Can handle fixed- or dynamic-length records, but not `BLOB' or
`TEXT' columns.
* Can be uncompressed with `myisamchk'.
MERGE Tables
============
`MERGE' tables are new in *MySQL* Version 3.23.25. The code is still in
beta, but should stabilize soon!
A `MERGE' table is a collection of identical `MyISAM' tables that can
be used as one. You can only `SELECT', `DELETE', and `UPDATE' from the
collection of tables. If you `DROP' the `MERGE' table, you are only
dropping the `MERGE' specification.
Note that `DELETE FROM merge_table' used without a `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 `myisampack'. *Note
myisampack::.
When you create a `MERGE' table, you will get a `.frm' table definition
file and a `.MRG' table list file. The `.MRG' just contains a list of
the index files (`.MYI' files) that should be used as one.
For the moment you need to have `SELECT', `UPDATE', and `DELETE'
privileges on the tables you map to a `MERGE' table.
`MERGE' tables can help you solve the following problems:
* Easily manage a set of log tables. For example, you can put data
from different months into separate files, compress some of them
with `myisampack', and then create a `MERGE' to use these as one.
* 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 `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.)
* 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 *MERGE* table for others. You can even have
many different `MERGE' tables active, with possible overlapping
files.
* More efficient repairs. It's easier to repair the individual files
that are mapped to a `MERGE' file than trying to repair a real big
file.
* Instant mapping of many files as one. A `MERGE' table uses the
index of the individual tables. It doesn't need an index of its
one. This makes `MERGE' table collections VERY fast to make or
remap.
* If you have a set of tables that you join to a big table on demand
or batch, you should instead create a `MERGE' table on them on
demand. This is much faster and will save a lot of disk space.
* Go around the file size limit for the operating system.
* 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).
The disadvantages with `MERGE' tables are:
* You can't use `INSERT' on `MERGE' tables, as *MySQL* can't know in
which of the tables we should insert the row.
* You can only use identical `MyISAM' tables for a `MERGE' table.
* `MERGE' tables uses more file descriptors. If you are using a
*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.)
* Key reads are slower. When you do a read on a key, the `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 `MERGE' keys much slower on `eq_ref' searches, but not much
slower on `ref' searches. *Note EXPLAIN::.
* You can't do `DROP TABLE', `ALTER TABLE' or `DELETE FROM
table_name' without a `WHERE' clause on any of the table that is
mapped by a `MERGE' table that is 'open'. If you do this, the
`MERGE' table may still refer to the original table and you will
get unexpected results.
The following example shows you how to use `MERGE' tables:
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);
Note that we didn't create a `UNIQUE' or `PRIMARY KEY' in the `total'
table as the key isn't going to be unique in the `total' table.
Note that you can also manipulate the `.MRG' file directly from the
outside of the *MySQL* server:
shell> cd /mysql-data-directory/current-database
shell> ls -1 t1.MYI t2.MYI > total.MRG
shell> mysqladmin flush-tables
Now you can do things like:
mysql> select * from total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+
To remap a `MERGE' table you can do one of the following:
* `DROP' the table and re-create it
* Use `ALTER TABLE table_name UNION(...)'
* Change the `.MRG' file and issue a `FLUSH TABLE' on the `MERGE'
table and all underlying tables to force the handler to read the
new definition file.
ISAM Tables
===========
You can also use the deprecated ISAM table type. This will disappear
rather soon because `MyISAM' is a better implementation of the same
thing. ISAM uses a `B-tree' index. The index is stored in a file with
the `.ISM' extension, and the data is stored in a file with the `.ISD'
extension. You can check/repair ISAM tables with the `isamchk'
utility. *Note Crash recovery::.
`ISAM' has the following features/properties:
* Compressed and fixed-length keys
* Fixed and dynamic record length
* 16 keys with 16 key parts/key
* Max key length 256 (default)
* Data is stored in machine format; this is fast, but is machine/OS
dependent.
Most of the things true for `MyISAM' tables are also true for `ISAM'
tables. *Note MyISAM::. The major differences compared to `MyISAM'
tables are:
* ISAM tables are not binary portable across OS/Platforms.
* Can't handle tables > 4G.
* Only support prefix compression on strings.
* Smaller key limits.
* Dynamic tables get more fragmented.
* Tables are compressed with `pack_isam' rather than with
`myisampack'.
HEAP Tables
===========
`HEAP' tables use a hashed index and are stored in memory. This makes
them very fast, but if *MySQL* crashes you will lose all data stored in
them. `HEAP' is very useful for temporary tables!
The *MySQL* internal HEAP tables use 100% dynamic hashing without
overflow areas. There is no extra space needed for free lists. `HEAP'
tables also don't have problems with delete + inserts, which normally
is common with hashed tables:
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;
Here are some things you should consider when you use `HEAP' tables:
* You should always use specify `MAX_ROWS' in the `CREATE' statement
to ensure that you accidently do not use all memory.
* Indexes will only be used with `=' and `<=>' (but are VERY fast).
* `HEAP' tables can only use whole keys to search for a row; compare
this to `MyISAM' tables where any prefix of the key can be used to
find rows.
* `HEAP' tables use a fixed record length format.
* `HEAP' doesn't support `BLOB'/`TEXT' columns.
* `HEAP' doesn't support `AUTO_INCREMENT' columns.
* `HEAP' doesn't support an index on a `NULL' column.
* You can have non-unique keys in a `HEAP' table (this isn't common
for hashed tables).
* `HEAP' tables are shared between all clients (just like any other
table).
* You can't search for the next entry in order (that is, to use the
index to do an `ORDER BY').
* Data for `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.
* You need enough extra memory for all HEAP tables that you want to
use at the same time.
* To free memory, you should execute `DELETE FROM heap_table',
`TRUNCATE heap_table' or `DROP TABLE heap_table'.
* *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
`MyISAM' table to a `HEAP' table.
* To ensure that you accidentally don't do anything foolish, you
can't create `HEAP' tables bigger than `max_heap_table_size'.
The memory needed for one row in a `HEAP' table is:
SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))
`sizeof(char*)' is 4 on 32-bit machines and 8 on 64-bit machines.
BDB or Berkeley_db Tables
=========================
Overview over BDB tables
------------------------
BDB tables are included in the *MySQL* source distribution starting
from 3.23.34 and will be activated in the *MySQL*-max binary.
Berkeley DB (`http://www.sleepycat.com') has provided *MySQL* with a
transaction-safe table handler. This will survive crashes and also
provides `COMMIT' and `ROLLBACK' on transactions. The *MySQL* source
distribution comes with a BDB distribution that has a couple of small
patches to make it work more smoothly with *MySQL*. You can't use a
not-patched `BDB' version with *MySQL*.
Installing BDB
--------------
If you have downloaded a binary version of *MySQL* that includes
support for Berkeley DB, simply follow the instructions for installing
a binary version of *MySQL*. *Note Installing binary::.
To compile MySQL with Berkeley DB support, download *MySQL* 3.23.34 or
newer and configure `MySQL' with the `--with-berkeley-db' option. *Note
Installing source::.
cd /path/to/source/of/mysql-3.23.34
./configure --with-berkeley-db
Please refer to the manual provided by `BDB' distribution for
more/updated information.
Even though Berkeley DB is in itself very tested and reliable, the
*MySQL* interface is still considered beta quality. We are actively
improving and optimizing it to get it stable very soon.
BDB startup options
-------------------
If you are running with `AUTOCOMMIT=0' then your changes in `BDB'
tables will not be updated until you execute `COMMIT'. Instead of
commit you can execute `ROLLBACK' to forget your changes. *Note
COMMIT::.
If you are running with `AUTOCOMMIT=1' (the default), your changes will
be committed immediately. You can start an extended transaction with
the `BEGIN WORK' SQL command, after which your changes will not be
committed until you execute `COMMIT' (or decide to `ROLLBACK' the
changes).
The following options to `mysqld' can be used to change the behavior of
BDB tables:
*Option* *Meaning*
`--bdb-home=directory' Base directory for BDB tables. This should be the
same directory you use for -datadir.
`--bdb-lock-detect=#' Berkeley lock detect. One of (DEFAULT, OLDEST,
RANDOM, or YOUNGEST).
`--bdb-logdir=directory'Berkeley DB log file directory.
`--bdb-no-sync' Don't synchronously flush logs.
`--bdb-no-recover' Don't start Berkeley DB in recover mode.
`--bdb-shared-data' Start Berkeley DB in multi-process mode (Don't
use `DB_PRIVATE' when initializing Berkeley DB)
`--bdb-tmpdir=directory'Berkeley DB tempfile name.
`--skip-bdb' Don't use berkeley db.
`-O Set the maximum number of locks possible. *Note
bdb_max_lock=1000' SHOW VARIABLES::.
If you use `--skip-bdb', *MySQL* will not initialize the Berkeley DB
library and this will save a lot of memory. Of course, you cannot use
`BDB' tables if you are using this option.
Normally you should start mysqld without `--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. *Note Starting
server::.
With `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 `bdb: Lock table is out of available
locks' or `Got error 12 from ...' when you have do long transactions
or when `mysqld' has to examine a lot of rows to calculate the query.
You may also want to change `binlog_cache_size' and
`max_binlog_cache_size' if you are using big multi-line transactions.
*Note COMMIT::.
Some characteristic of `BDB' tables:
------------------------------------
* 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 `--bdb_log_dir' options.
* *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 `FLUSH LOGS' at any time to
checkpoint the Berkeley DB tables.
For disaster recovery, one should use table backups plus *MySQL*'s
binary log. *Note Backup::.
*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.
* *MySQL* requires a `PRIMARY KEY' in each BDB table to be able to
refer to previously read rows. If you don't create one, *MySQL*
will create an maintain a hidden `PRIMARY KEY' for you. The
hidden key has a length of 5 bytes and is incremented for each
insert attempt.
* If all columns you access in a `BDB' table are part of the same
index or part of the primary key, then *MySQL* can execute the
query without having to access the actual row. In a `MyISAM'
table the above holds only if the columns are part of the same
index.
* The `PRIMARY KEY' will be faster than any other key, as the
`PRIMARY KEY' is stored together with the row data. As the other
keys are stored as the key data + the `PRIMARY KEY', it's
important to keep the `PRIMARY KEY' as short as possible to save
disk and get better speed.
* `LOCK TABLES' works on `BDB' tables as with other tables. If you
don't use `LOCK TABLE', *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.
* Internal locking in `BDB' tables is done on page level.
* `SELECT COUNT(*) FROM table_name' is slow as `BDB' tables doesn't
maintain a count of the number of rows in the table.
* Scanning is slower than with `MyISAM' tables as one has data in BDB
tables stored in B-trees and not in a separate data file.
* The application must always be prepared to handle cases where any
change of a `BDB' table may make an automatic rollback and any
read may fail with a deadlock error.
* 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 `BDB' tables compared to MyISAM tables which don't use
`PACK_KEYS=0'.
* 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.
* The optimizer needs to know an approximation of the number of rows
in the table. *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 `DELETE' or `ROLLBACK':s this number should be
accurate enough for the *MySQL* optimizer, but as *MySQL* only
store the number on close, it may be wrong if *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
`ANALYZE TABLE' or `OPTIMIZE TABLE'. *Note ANALYZE TABLE:: . *Note
OPTIMIZE TABLE::.
* If you get full disk with a `BDB' table, you will get an error
(probably error 28) and the transaction should roll back. This is
in contrast with `MyISAM' and `ISAM' tables where mysqld will wait
for enough free disk before continuing.
Some things we need to fix for BDB in the near future:
------------------------------------------------------
* 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 `--no-auto-rehash' with the
`mysql' client. We plan to partly fix this in 4.0.
* `SHOW TABLE STATUS' doesn't yet provide that much information for
BDB tables.
* Optimize performance.
* Change to not use page locks at all when we are scanning tables.
Errors You May Get When Using BDB Tables
----------------------------------------
* If you get the following error in the `hostname.err log' when
starting `mysqld':
bdb: Ignoring log file: .../log.XXXXXXXXXX: unsupported log version #
it means that the new `BDB' version doesn't support the old log
file format. In this case you have to delete all `BDB' log BDB
from your database directory (the files that has the format
`log.XXXXXXXXXX' ) and restart `mysqld'. We would also recommend
you to do a `mysqldump --opt' of your old `BDB' tables, delete the
old table and restore the dump.
* If you are running in not `auto_commit' mode and delete a table you
are using by another thread you may get the following error
messages in the *MySQL* error file:
001119 23:43:56 bdb: Missing log fileid entry
001119 23:43:56 bdb: txn_abort: Log undo failed for LSN: 1 3644744: Invalid
This is not fatal but we don't recommend that you delete tables if
you are not in `auto_commit' mode, until this problem is fixed
(the fix is not trivial).
GEMINI Tables
=============
Overview of GEMINI tables
-------------------------
The `GEMINI' table type is developed and supported by NuSphere
Corporation (`http://www.nusphere.com'). It features row-level
locking, transaction support (`COMMIT' and `ROLLBACK'), and automatic
crash recovery.
`GEMINI' tables will be included in the *MySQL* 3.23.35 source
distribution.
GEMINI startup options
----------------------
If you are running with `AUTOCOMMIT=0' then your changes in `GEMINI'
tables will not be updated until you execute `COMMIT'. Instead of
commit you can execute `ROLLBACK' to forget your changes. *Note
COMMIT::.
If you are running with `AUTOCOMMIT=1' (the default), your changes will
be committed immediately. You can start an extended transaction with
the `BEGIN WORK' SQL command, after which your changes will not be
committed until you execute `COMMIT' (or decide to `ROLLBACK' the
changes).
The following options to `mysqld' can be used to change the behavior of
GEMINI tables:
*Option* *Meaning*
`--gemini-full-recovery'Default.
`--gemini-no-recovery' Turn off recovery logging. Not recommended.
`--gemini-lazy-commit' Relaxes the flush log at commit rule.
`--gemini-unbuffered-io'All database writes bypass OS cache.
`--skip-gemini' Don't use Gemini.
`--O Number of database buffers in database cache.
gemini_db_buffers=#'
`--O Maximum number of connections to Gemini.
gemini_connection_limit=#'
`--O Spin lock retries (optimization).
gemini_spin_retries=#'
`--O Number of background I/O threads.
gemini_io_threads=#'
`--O Set the maximum number of locks. Default 4096.
gemini_lock_table_size=#'
If you use `--skip-gemini', *MySQL* will not initialize the Gemini
table handler, saving memory; you cannot use Gemini tables if you use
`--skip-gemini'.
Features of `GEMINI' tables:
----------------------------
* If a query result can be resolved solely from the index key,
Gemini will not read the actual row stored in the database.
* Locking on Gemini tables is done at row level.
* `SELECT COUNT(*) FROM table_name' is fast; Gemini maintains a count
of the number of rows in the table.
Current limitations of `GEMINI' tables:
---------------------------------------
* BLOB columns are not supported in `GEMINI' tables.
* The maximum number of concurrent users accessing `GEMINI' tables is
limited by `gemini_connection_limit'. The default is 100 users.
NuSphere is working on removing these limitations.
INNOBASE Tables
===============
INNOBASE Tables overview
------------------------
Innobase tables are included in the *MySQL* source distribution
starting from 3.23.34 and will be activated in the *MySQL*-max binary.
If you have downloaded a binary version of *MySQL* that includes
support for Innobase, simply follow the instructions for installing a
binary version of *MySQL*. *Note Installing binary::.
To compile *MySQL* with Innobase support, download *MySQL* 3.23.34 or
newer and configure `MySQL' with the `--with-innobase' option. *Note
Installing source::.
cd /path/to/source/of/mysql-3.23.34
./configure --with-innobase
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 `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.
INNOBASE startup options
------------------------
To use Innobase tables you must specify configuration parameters in the
MySQL configuration file in the `[mysqld]' section of the configuration
file. Below is an example of possible configuration parameters in
my.cnf for Innobase:
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
The meanings of the configuration parameters are the following:
`innobase_data_home_dir' The common part of the directory path for all
innobase data files.
`innobase_data_file_path' 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.
`innobase_log_group_home_dir' Directory path to Innobase log files.
`innobase_log_files_in_group' Number of log files in the log group. Innobase
writes to the files in a circular fashion. Value
3 is recommended here.
`innobase_log_file_size' 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.
`innobase_log_buffer_size' 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.
`innobase_flush_log_at_trx_commit' 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.
`innobase_log_arch_dir' 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 `innobase_log_group_home_dir'.
`innobase_log_archive' 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.
`innobase_buffer_pool_size' 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.
`innobase_additional_mem_pool_size' 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.
`innobase_file_io_threads' 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.
`innobase_lock_wait_timeout' 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 `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.
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, `MyISAM'
tables where each table is stored as a separate file.
To create a table in the Innobase format you must specify `TYPE =
INNOBASE' in the table creation SQL command:
CREATE TABLE CUSTOMERS (A INT, B CHAR (20), INDEX (A)) TYPE = INNOBASE;
A consistent non-locking read is the default locking behavior when you
do a `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 *MySQL* for any table you have created with `TYPE = INNOBASE'. Then
the amount of free space in the tablespace appears in the table comment
section in the output of SHOW. An example:
SHOW TABLE STATUS FROM TEST LIKE 'CUSTOMER'
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: `http://www.innobase.fi'.
<Heikki.Tuuri@innobase.inet.fi>
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
Some restrictions on `INNOBASE' tables:
---------------------------------------
* You can't have a key on a `BLOB' or `TEXT' column.
* `DELETE FROM TABLE' doesn't re-generate the table but instead
deletes all rows, one by one, which isn't that fast.
* The maximum blob size is 8000 bytes.
* Before dropping a database with `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.
MySQL Tutorial
**************
This chapter provides a tutorial introduction to *MySQL* by showing how
to use the `mysql' client program to create and use a simple database.
`mysql' (sometimes referred to as the "terminal monitor" or just
"monitor") is an interactive program that allows you to connect to a
*MySQL* server, run queries, and view the results. `mysql' may also be
used in batch mode: you place your queries in a file beforehand, then
tell `mysql' to execute the contents of the file. Both ways of using
`mysql' are covered here.
To see a list of options provided by `mysql', invoke it with the
`--help' option:
shell> mysql --help
This chapter assumes that `mysql' is installed on your machine and that
a *MySQL* server is available to which you can connect. If this is not
true, contact your *MySQL* administrator. (If _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.
Connecting to and Disconnecting from the Server
===============================================
To connect to the server, you'll usually need to provide a *MySQL* user
name when you invoke `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:
shell> mysql -h host -u user -p
Enter password: ********
The `********' represents your password; enter it when `mysql' displays
the `Enter password:' prompt.
If that works, you should see some introductory information followed by
a `mysql>' prompt:
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>
The prompt tells you that `mysql' is ready for you to enter commands.
Some *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 `mysql' without any options:
shell> mysql
After you have connected successfully, you can disconnect any time by
typing `QUIT' at the `mysql>' prompt:
mysql> QUIT
Bye
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 `mysql>' prompt.
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
`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
`mysql>' prompt and hit the RETURN key:
mysql> SELECT VERSION(), CURRENT_DATE;
+--------------+--------------+
| version() | CURRENT_DATE |
+--------------+--------------+
| 3.22.20a-log | 1999-03-19 |
+--------------+--------------+
1 row in set (0.01 sec)
mysql>
This query illustrates several things about `mysql':
* A command normally consists of a SQL statement followed by a
semicolon. (There are some exceptions where a semicolon is not
needed. `QUIT', mentioned earlier, is one of them. We'll get to
others later.)
* When you issue a command, `mysql' sends it to the server for
execution and displays the results, then prints another `mysql>'
to indicate that it is ready for another command.
* `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), `mysql' labels the column using the
expression itself.
* `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.)
Keywords may be entered in any lettercase. The following queries are
equivalent:
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;
Here's another query. It demonstrates that you can use `mysql' as a
simple calculator:
mysql> SELECT SIN(PI()/4), (4+1)*5;
+-------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+-------------+---------+
| 0.707107 | 25 |
+-------------+---------+
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:
mysql> SELECT VERSION(); SELECT NOW();
+--------------+
| version() |
+--------------+
| 3.22.20a-log |
+--------------+
+---------------------+
| NOW() |
+---------------------+
| 1999-03-19 00:15:33 |
+---------------------+
A command need not be given all on a single line, so lengthy commands
that require several lines are not a problem. `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, `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:
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
+--------------------+--------------+
| USER() | CURRENT_DATE |
+--------------------+--------------+
| joesmith@localhost | 1999-03-18 |
+--------------------+--------------+
In this example, notice how the prompt changes from `mysql>' to `->'
after you enter the first line of a multiple-line query. This is how
`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 `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 `c':
mysql> SELECT
-> USER()
-> c
mysql>
Here, too, notice the prompt. It switches back to `mysql>' after you
type `c', providing feedback to indicate that `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 `mysql' is in:
*Prompt**Meaning*
`mysql>'Ready for new command.
` Waiting for next line of multiple-line command.
->'
` Waiting for next line, collecting a string that begins with a
'>' single quote (`'').
` Waiting for next line, collecting a string that begins with a
">' double quote (`"').
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, `mysql' waits for more input:
mysql> SELECT USER()
->
If this happens to you (you think you've entered a statement but the
only response is a `->' prompt), most likely `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 `mysql' will execute
it:
mysql> SELECT USER()
-> ;
+--------------------+
| USER() |
+--------------------+
| joesmith@localhost |
+--------------------+
The `'>' and `">' prompts occur during string collection. In *MySQL*,
you can write strings surrounded by either `'' or `"' characters (for
example, `'hello'' or `"goodbye"'), and `mysql' lets you enter strings
that span multiple lines. When you see a `'>' or `">' prompt, it means
that you've entered a line containing a string that begins with a `''
or `"' 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 `'>' and `">' prompts indicate that you've inadvertantly left out a
quote character. For example:
mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
">
If you enter this `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 `">' prompt. It tells you
that `mysql' expects to see the rest of an unterminated string. (Do
you see the error in the statement? The string `"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 `c' in this case, because
`mysql' interprets it as part of the string that it is collecting!
Instead, enter the closing quote character (so `mysql' knows you've
finished the string), then type `c':
mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
"> "c
mysql>
The prompt changes back to `mysql>', indicating that `mysql' is ready
for a new command.
It's important to know what the `'>' and `">' prompts signify, because
if you mistakenly enter an unterminated string, any further lines you
type will appear to be ignored by `mysql' -- including a line
containing `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.
Examples of Common Queries
==========================
Here are examples of how to solve some common problems with *MySQL*.
Some of the examples use the table `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 (`item',
`trader') is a primary key for the records.
Start the command line tool `mysql' and select a database:
mysql your-database-name
(In most *MySQL* installations, you can use the database-name 'test').
You can create the example table as:
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);
Okay, so the example data is:
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 |
+---------+--------+-------+
The Maximum Value for a Column
------------------------------
"What's the highest item number?"
SELECT MAX(article) AS article FROM shop
+---------+
| article |
+---------+
| 4 |
+---------+
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:
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop)
In *MySQL* (which does not yet have sub-selects), just do it in two
steps:
1. Get the maximum price value from the table with a `SELECT'
statement.
2. Using this value compile the actual query:
SELECT article, dealer, price
FROM shop
WHERE price=19.95
Another solution is to sort all rows descending by price and only get
the first row using the *MySQL* specific `LIMIT' clause:
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1
*NOTE*: If there are several most expensive articles (for example,
each 19.95) the `LIMIT' solution shows only one of them!
Maximum of Column per Group
---------------------------
"What's the highest price per article?"
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 |
+---------+-------+
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:
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article)
In *MySQL* it's best do it in several steps:
1. Get the list of (article,maxprice).
2. For each article get the corresponding rows that have the stored
maximum price.
This can easily be done with a temporary table:
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;
If you don't use a `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":
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 |
+---------+--------+-------+
The last example can, of course, be made a bit more efficient by doing
the splitting of the concatenated column in the client.
Using user variables
--------------------
You can use *MySQL* user variables to remember results without having
to store them in a temporary variables in the client. *Note
Variables::.
For example, to find the articles with the highest and lowest price you
can do:
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 |
+---------+--------+-------+
Using Foreign Keys
------------------
You don't need foreign keys to join 2 tables.
The only thing *MySQL* doesn't do is `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:
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 |
+----+-------+--------+-------+
Searching on Two Keys
=====================
*MySQL* doesn't yet optimize when you search on two different keys
combined with `OR' (Searching on one key with different `OR' parts is
optimized quite good):
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'
OR field2_index = '1'
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 `AND' handling is, in
comparison, now completely general and works very well).
For the moment you can solve this very efficently by using a
`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.
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;
The above way to solve this query is in effect an `UNION' of two
queries.
Creating and Using a Database
=============================
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:
* Create a database
* Create a table
* Load data into the table
* Retrieve data from the table in various ways
* Use multiple tables
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 `SHOW' statement to find out what databases currently exist on
the server:
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
| tmp |
+----------+
The list of databases is probably different on your machine, but the
`mysql' and `test' databases are likely to be among them. The `mysql'
database is required because it describes user access privileges. The
`test' database is often provided as a workspace for users to try
things out.
If the `test' database exists, try to access it:
mysql> USE test
Database changed
Note that `USE', like `QUIT', does not require a semicolon. (You can
terminate such statements with a semicolon if you like; it does no
harm.) The `USE' statement is special in another way, too: it must be
given on a single line.
You can use the `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 *MySQL* administrator for permission to use a
database of your own. Suppose you want to call yours `menagerie'. The
administrator needs to execute a command like this:
mysql> GRANT ALL ON menagerie.* TO your_mysql_name;
where `your_mysql_name' is the *MySQL* user name assigned to you.
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:
mysql> CREATE DATABASE menagerie;
Under Unix, database names are case sensitive (unlike SQL keywords), so
you must always refer to your database as `menagerie', not as
`Menagerie', `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 `menagerie' the current database, use this command:
mysql> USE menagerie
Database changed
Your database needs to be created only once, but you must select it for
use each time you begin a `mysql' session. You can do this by issuing a
`USE' statement as shown above. Alternatively, you can select the
database on the command line when you invoke `mysql'. Just specify its
name after any connection parameters that you might need to provide.
For example:
shell> mysql -h host -u user -p menagerie
Enter password: ********
Note that `menagerie' is not your password on the command just shown.
If you want to supply your password on the command line after the `-p'
option, you must do so with no intervening space (for example, as
`-pmypassword', not as `-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.
Creating a Table
----------------
Creating the database is the easy part, but at this point it's empty, as
`SHOW TABLES' will tell you:
mysql> SHOW TABLES;
Empty set (0.00 sec)
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 `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. *MySQL* provides functions for doing date arithmetic, so
this is not difficult. Storing birth date rather than age has other
advantages, too:
* 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.)
* 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.
You can probably think of other types of information that would be
useful in the `pet' table, but the ones identified so far are
sufficient for now: name, owner, species, sex, birth, and death.
Use a `CREATE TABLE' statement to specify the layout of your table:
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
`VARCHAR' is a good choice for the `name', `owner', and `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 `20'. You can
pick any length from `1' to `255', whatever seems most reasonable to
you. (If you make a poor choice and it turns out later that you need a
longer field, *MySQL* provides an `ALTER TABLE' statement.)
Animal sex can be represented in a variety of ways, for example, `"m"'
and `"f"', or perhaps `"male"' and `"female"'. It's simplest to use
the single characters `"m"' and `"f"'.
The use of the `DATE' data type for the `birth' and `death' columns is
a fairly obvious choice.
Now that you have created a table, `SHOW TABLES' should produce some
output:
mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| pet |
+---------------------+
To verify that your table was created the way you expected, use a
`DESCRIBE' statement:
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 | |
+---------+-------------+------+-----+---------+-------+
You can use `DESCRIBE' any time, for example, if you forget the names of
the columns in your table or what types they are.
Loading Data into a Table
-------------------------
After creating your table, you need to populate it. The `LOAD DATA' and
`INSERT' statements are useful for this.
Suppose your pet records can be described as shown below. (Observe
that *MySQL* expects dates in `YYYY-MM-DD' format; this may be
different than what you are used to.)
*name* *owner* *species* *sex* *birth* *death*
Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Fang Benny dog m 1990-08-27
Bowser Diane dog m 1998-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11
Whistler Gwen bird 1997-12-09
Slim Benny snake m 1996-04-29
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 `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 `CREATE TABLE' statement. For missing
values (such as unknown sexes or death dates for animals that are still
living), you can use `NULL' values. To represent these in your text
file, use `N'. For example, the record for Whistler the bird would
look like this (where the whitespace between values is a single tab
character):
`Whistler' `Gwen' `bird' `N' `1997-12-09' `N'
To load the text file `pet.txt' into the `pet' table, use this command:
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
You can specify the column value separator and end of line marker
explicitly in the `LOAD DATA' statement if you wish, but the defaults
are tab and linefeed. These are sufficient for the statement to read
the file `pet.txt' properly.
When you want to add new records one at a time, the `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 `CREATE TABLE'
statement. Suppose Diane gets a new hamster named Puffball. You could
add a new record using an `INSERT' statement like this:
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Note that string and date values are specified as quoted strings here.
Also, with `INSERT', you can insert `NULL' directly to represent a
missing value. You do not use `N' like you do with `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
`INSERT' statements rather than a single `LOAD DATA' statement.
Retrieving Information from a Table
-----------------------------------
The `SELECT' statement is used to pull information from a table. The
general form of the statement is:
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy
`what_to_select' indicates what you want to see. This can be a list of
columns, or `*' to indicate "all columns." `which_table' indicates the
table from which you want to retrieve data. The `WHERE' clause is
optional. If it's present, `conditions_to_satisfy' specifies
conditions that rows must satisfy to qualify for retrieval.
Selecting All Data
..................
The simplest form of `SELECT' retrieves everything from a table:
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 |
+----------+--------+---------+------+------------+------------+
This form of `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:
* Edit the file `pet.txt' to correct the error, then empty the table
and reload it using `DELETE' and `LOAD DATA':
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;
However, if you do this, you must also re-enter the record for
Puffball.
* Fix only the erroneous record with an `UPDATE' statement:
mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Bowser";
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.
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:
mysql> SELECT * FROM pet WHERE name = "Bowser";
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
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 `"bowser"', `"BOWSER"', etc. The query result will be the same.
You can specify conditions on any column, not just `name'. For example,
if you want to know which animals were born after 1998, test the `birth'
column:
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 |
+----------+-------+---------+------+------------+-------+
You can combine conditions, for example, to locate female dogs:
mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
The preceding query uses the `AND' logical operator. There is also an
`OR' operator:
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 |
+----------+-------+---------+------+------------+-------+
`AND' and `OR' may be intermixed. If you do that, it's a good idea to
use parentheses to indicate how conditions should be grouped:
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 |
+-------+--------+---------+------+------------+-------+
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 `name' and
`birth' columns:
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 |
+----------+------------+
To find out who owns pets, use this query:
mysql> SELECT owner FROM pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
| Diane |
+--------+
However, notice that the query simply retrieves the `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 `DISTINCT':
mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner |
+--------+
| Benny |
| Diane |
| Gwen |
| Harold |
+--------+
You can use a `WHERE' clause to combine row selection with column
selection. For example, to get birth dates for dogs and cats only, use
this query:
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 |
+--------+---------+------------+
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 `ORDER BY' clause.
Here are animal birthdays, sorted by date:
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 |
+----------+------------+
To sort in reverse order, add the `DESC' (descending) keyword to the
name of the column you are sorting by:
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 |
+----------+------------+
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:
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 |
+----------+---------+------------+
Note that the `DESC' keyword applies only to the column name immediately
preceding it (`birth'); `species' values are still sorted in ascending
order.
Date Calculations
.................
*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):
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 |
+----------+-------------------------------------+
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 `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):
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 |
+----------+------+
To sort the output by `age' rather than `name', just use a different
`ORDER BY' clause:
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 |
+----------+------+
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 `death' value is `NULL'. Then, for those with non-`NULL'
values, compute the difference between the `death' and `birth' values:
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 |
+--------+------------+------------+------+
The query uses `death IS NOT NULL' rather than `death != NULL' because
`NULL' is a special value. This is explained later. *Note Working
with `NULL': Working with 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 `birth' column. *MySQL* provides