MySQL数据库

开发平台：

Visual C++

manual.txt：源码内容

convenience only. Client programs allow a different name to be
specified with the `-u' or `--user' options. This means that you
can't make a database secure in any way unless all *MySQL* user
names have passwords. Anyone may attempt to connect to the server
using any name, and they will succeed if they specify any name
that doesn't have a password.
* *MySQL* user names can be up to 16 characters long; Unix user names
typically are limited to 8 characters.
* *MySQL* passwords have nothing to do with Unix passwords. There
is no necessary connection between the password you use to log in
to a Unix machine and the password you use to access a database on
that machine.
* *MySQL* encrypts passwords using a different algorithm than the
one used during the Unix login process. See the descriptions of
the `PASSWORD()' and `ENCRYPT()' functions in *Note Miscellaneous
functions::. Note that even if the password is stored
'scrambled', and knowing your 'scrambled' password is enough to be
able to connect to the *MySQL* server!
Connecting to the MySQL Server
==============================
*MySQL* client programs generally require that you specify connection
parameters when you want to access a *MySQL* server: the host you want
to connect to, your user name, and your password. For example, the
`mysql' client can be started like this (optional arguments are enclosed
between `[' and `]'):
shell> mysql [-h host_name] [-u user_name] [-pyour_pass]
Alternate forms of the `-h', `-u', and `-p' options are
`--host=host_name', `--user=user_name', and `--password=your_pass'.
Note that there is _no space_ between `-p' or `--password=' and the
password following it.
*NOTE:* Specifying a password on the command line is not secure! Any
user on your system may then find out your password by typing a command
like: `ps auxww'. *Note Option files::.
`mysql' uses default values for connection parameters that are missing
from the command line:
* The default hostname is `localhost'.
* The default user name is your Unix login name.
* No password is supplied if `-p' is missing.
Thus, for a Unix user `joe', the following commands are equivalent:
shell> mysql -h localhost -u joe
shell> mysql -h localhost
shell> mysql -u joe
shell> mysql
Other *MySQL* clients behave similarly.
On Unix systems, you can specify different default values to be used
when you make a connection, so that you need not enter them on the
command line each time you invoke a client program. This can be done
in a couple of ways:
* You can specify connection parameters in the `[client]' section of
the `.my.cnf' configuration file in your home directory. The
relevant section of the file might look like this:
[client]
host=host_name
user=user_name
password=your_pass
*Note Option files::.
* You can specify connection parameters using environment variables.
The host can be specified for `mysql' using `MYSQL_HOST'. The
*MySQL* user name can be specified using `USER' (this is for
Windows only). The password can be specified using `MYSQL_PWD'
(but this is insecure; see the next section). *Note Environment
variables::.
Keeping Your Password Secure
============================
It is inadvisable to specify your password in a way that exposes it to
discovery by other users. The methods you can use to specify your
password when you run client programs are listed below, along with an
assessment of the risks of each method:
* Never give a normal user access to the `mysql.user' table. Knowing
the encrypted password for a user makes it possible to login as
this user. The passwords are only scrambled so that one shouldn't
be able to see the real password you used (if you happen to use a
similar password with your other applications).
* Use a `-pyour_pass' or `--password=your_pass' option on the command
line. This is convenient but insecure, because your password
becomes visible to system status programs (such as `ps') that may
be invoked by other users to display command lines. (*MySQL*
clients typically overwrite the command-line argument with zeroes
during their initialization sequence, but there is still a brief
interval during which the value is visible.)
* Use a `-p' or `--password' option (with no `your_pass' value
specified). In this case, the client program solicits the
password from the terminal:
shell> mysql -u user_name -p
Enter password: ********
The `*' characters represent your password.
It is more secure to enter your password this way than to specify
it on the command line because it is not visible to other users.
However, this method of entering a password is suitable only for
programs that you run interactively. If you want to invoke a
client from a script that runs non-interactively, there is no
opportunity to enter the password from the terminal. On some
systems, you may even find that the first line of your script is
read and interpreted (incorrectly) as your password!
* Store your password in a configuration file. For example, you can
list your password in the `[client]' section of the `.my.cnf' file
in your home directory:
[client]
password=your_pass
If you store your password in `.my.cnf', the file should not be
group or world readable or writable. Make sure the file's access
mode is `400' or `600'.
*Note Option files::.
* You can store your password in the `MYSQL_PWD' environment
variable, but this method must be considered extremely insecure
and should not be used. Some versions of `ps' include an option
to display the environment of running processes; your password
will be in plain sight for all to see if you set `MYSQL_PWD'.
Even on systems without such a version of `ps', it is unwise to
assume there is no other method to observe process environments.
*Note Environment variables::.
All in all, the safest methods are to have the client program prompt
for the password or to specify the password in a properly protected
`.my.cnf' file.
Privileges Provided by MySQL
============================
Information about user privileges is stored in the `user', `db',
`host', `tables_priv', and `columns_priv' tables in the `mysql'
database (that is, in the database named `mysql'). The *MySQL* server
reads the contents of these tables when it starts up and under the
circumstances indicated in *Note Privilege changes::.
The names used in this manual to refer to the privileges provided by
*MySQL* are shown below, along with the table column name associated
with each privilege in the grant tables and the context in which the
privilege applies:
*Privilege* *Column* *Context*
*select* `Select_priv' tables
*insert* `Insert_priv' tables
*update* `Update_priv' tables
*delete* `Delete_priv' tables
*index* `Index_priv' tables
*alter* `Alter_priv' tables
*create* `Create_priv' databases, tables, or indexes
*drop* `Drop_priv' databases or tables
*grant* `Grant_priv' databases or tables
*references*`References_priv' databases or tables
*reload* `Reload_priv' server administration
*shutdown* `Shutdown_priv' server administration
*process* `Process_priv' server administration
*file* `File_priv' file access on server
The *select*, *insert*, *update*, and *delete* privileges allow you to
perform operations on rows in existing tables in a database.
`SELECT' statements require the *select* privilege only if they
actually retrieve rows from a table. You can execute certain `SELECT'
statements even without permission to access any of the databases on the
server. For example, you could use the `mysql' client as a simple
calculator:
mysql> SELECT 1+1;
mysql> SELECT PI()*2;
The *index* privilege allows you to create or drop (remove) indexes.
The *alter* privilege allows you to use `ALTER TABLE'.
The *create* and *drop* privileges allow you to create new databases
and tables, or to drop (remove) existing databases and tables.
Note that if you grant the *drop* privilege for the `mysql' database to
a user, that user can drop the database in which the *MySQL* access
privileges are stored!
The *grant* privilege allows you to give to other users those
privileges you yourself possess.
The *file* privilege gives you permission to read and write files on
the server using the `LOAD DATA INFILE' and `SELECT ... INTO OUTFILE'
statements. Any user to whom this privilege is granted can read or
write any file that the *MySQL* server can read or write.
The remaining privileges are used for administrative operations, which
are performed using the `mysqladmin' program. The table below shows
which `mysqladmin' commands each administrative privilege allows you to
execute:
*Privilege* *Commands permitted to privilege holders*
*reload* `reload', `refresh', `flush-privileges', `flush-hosts',
`flush-logs', and `flush-tables'
*shutdown* `shutdown'
*process* `processlist', `kill'
The `reload' command tells the server to re-read the grant tables. The
`refresh' command flushes all tables and opens and closes the log
files. `flush-privileges' is a synonym for `reload'. The other
`flush-*' commands perform functions similar to `refresh' but are more
limited in scope, and may be preferable in some instances. For example,
if you want to flush just the log files, `flush-logs' is a better choice
than `refresh'.
The `shutdown' command shuts down the server.
The `processlist' command displays information about the threads
executing within the server. The `kill' command kills server threads.
You can always display or kill your own threads, but you need the
*process* privilege to display or kill threads initiated by other
users. *Note KILL::.
It is a good idea in general to grant privileges only to those users
who need them, but you should exercise particular caution in granting
certain privileges:
* The *grant* privilege allows users to give away their privileges to
other users. Two users with different privileges and with the
*grant* privilege are able to combine privileges.
* The *alter* privilege may be used to subvert the privilege system
by renaming tables.
* The *file* privilege can be abused to read any world-readable file
on the server into a database table, the contents of which can
then be accessed using `SELECT'. This includes the contents of all
databases hosted by the server!
* The *shutdown* privilege can be abused to deny service to other
users entirely, by terminating the server.
* The *process* privilege can be used to view the plain text of
currently executing queries, including queries that set or change
passwords.
* Privileges on the `mysql' database can be used to change passwords
and other access privilege information. (Passwords are stored
encrypted, so a malicious user cannot simply read them to know the
plain text password). If they can access the `mysql.user' password
column, they can use it to log into the *MySQL* server for the
given user. (With sufficient privileges, the same user can
replace a password with a different one.)
There are some things that you cannot do with the *MySQL* privilege
system:
* You cannot explicitly specify that a given user should be denied
access. That is, you cannot explicitly match a user and then
refuse the connection.
* You cannot specify that a user has privileges to create or drop
tables in a database but not to create or drop the database itself.
How the Privilege System Works
==============================
The *MySQL* privilege system ensures that all users may do exactly the
things that they are supposed to be allowed to do. When you connect to
a *MySQL* server, your identity is determined by *the host from which
you connect* and *the user name you specify*. The system grants
privileges according to your identity and *what you want to do*.
*MySQL* considers both your hostname and user name in identifying you
because there is little reason to assume that a given user name belongs
to the same person everywhere on the Internet. For example, the user
`bill' who connects from `whitehouse.gov' need not be the same person
as the user `bill' who connects from `microsoft.com'. *MySQL* handles
this by allowing you to distinguish users on different hosts that
happen to have the same name: you can grant `bill' one set of
privileges for connections from `whitehouse.gov', and a different set
of privileges for connections from `microsoft.com'.
*MySQL* access control involves two stages:
* Stage 1: The server checks whether or not you are even allowed to
connect.
* Stage 2: Assuming you can connect, the server checks each request
you issue to see whether or not you have sufficient privileges to
perform it. For example, if you try to select rows from a table
in a database or drop a table from the database, the server makes
sure you have the *select* privilege for the table or the *drop*
privilege for the database.
The server uses the `user', `db', and `host' tables in the `mysql'
database at both stages of access control. The fields in these grant
tables are shown below:
*Table name* `user' `db' `host'
*Scope `Host' `Host' `Host'
fields*
`User' `Db' `Db'
`Password' `User'
*Privilege `Select_priv' `Select_priv' `Select_priv'
fields*
`Insert_priv' `Insert_priv' `Insert_priv'
`Update_priv' `Update_priv' `Update_priv'
`Delete_priv' `Delete_priv' `Delete_priv'
`Index_priv' `Index_priv' `Index_priv'
`Alter_priv' `Alter_priv' `Alter_priv'
`Create_priv' `Create_priv' `Create_priv'
`Drop_priv' `Drop_priv' `Drop_priv'
`Grant_priv' `Grant_priv' `Grant_priv'
`References_priv'
`Reload_priv'
`Shutdown_priv'
`Process_priv'
`File_priv'
For the second stage of access control (request verification), the
server may, if the request involves tables, additionally consult the
`tables_priv' and `columns_priv' tables. The fields in these tables
are shown below:
*Table name* `tables_priv' `columns_priv'
*Scope `Host' `Host'
fields*
`Db' `Db'
`User' `User'
`Table_name' `Table_name'
`Column_name'
*Privilege `Table_priv' `Column_priv'
fields*
`Column_priv'
*Other `Timestamp' `Timestamp'
fields*
`Grantor'
Each grant table contains scope fields and privilege fields.
Scope fields determine the scope of each entry in the tables, that is,
the context in which the entry applies. For example, a `user' table
entry with `Host' and `User' values of `'thomas.loc.gov'' and `'bob''
would be used for authenticating connections made to the server by
`bob' from the host `thomas.loc.gov'. Similarly, a `db' table entry
with `Host', `User', and `Db' fields of `'thomas.loc.gov'', `'bob'' and
`'reports'' would be used when `bob' connects from the host
`thomas.loc.gov' to access the `reports' database. The `tables_priv'
and `columns_priv' tables contain scope fields indicating tables or
table/column combinations to which each entry applies.
For access-checking purposes, comparisons of `Host' values are case
insensitive. `User', `Password', `Db', and `Table_name' values are
case sensitive. `Column_name' values are case insensitive in *MySQL*
Version 3.22.12 or later.
Privilege fields indicate the privileges granted by a table entry, that
is, what operations can be performed. The server combines the
information in the various grant tables to form a complete description
of a user's privileges. The rules used to do this are described in
*Note Request access::.
Scope fields are strings, declared as shown below; the default value for
each is the empty string:
*Field *Type*
name*
`Host' `CHAR(60)'
`User' `CHAR(16)'
`Password' `CHAR(16)'
`Db' `CHAR(64)' (`CHAR(60)' for the `tables_priv' and
`columns_priv' tables)
`Table_name'`CHAR(60)'
`Column_name'`CHAR(60)'
In the `user', `db' and `host' tables, all privilege fields are
declared as `ENUM('N','Y')' -- each can have a value of `'N'' or `'Y'',
and the default value is `'N''.
In the `tables_priv' and `columns_priv' tables, the privilege fields
are declared as `SET' fields:
*Table name* *Field name* *Possible set elements*
`tables_priv' `Table_priv' `'Select', 'Insert', 'Update', 'Delete',
'Create', 'Drop', 'Grant', 'References',
'Index', 'Alter''
`tables_priv' `Column_priv' `'Select', 'Insert', 'Update',
'References''
`columns_priv' `Column_priv' `'Select', 'Insert', 'Update',
'References''
Briefly, the server uses the grant tables like this:
* The `user' table scope fields determine whether to allow or reject
incoming connections. For allowed connections, any privileges
granted in the `user' table indicate the user's global (superuser)
privileges. These privileges apply to *all* databases on the
server.
* The `db' and `host' tables are used together:
- The `db' table scope fields determine which users can access
which databases from which hosts. The privilege fields
determine which operations are allowed.
- The `host' table is used as an extension of the `db' table
when you want a given `db' table entry to apply to several
hosts. For example, if you want a user to be able to use a
database from several hosts in your network, leave the `Host'
value empty in the user's `db' table entry, then populate the
`host' table with an entry for each of those hosts. This
mechanism is described more detail in *Note Request access::.
* The `tables_priv' and `columns_priv' tables are similar to the
`db' table, but are more fine-grained: they apply at the table and
column levels rather than at the database level.
Note that administrative privileges (*reload*, *shutdown*, etc.) are
specified only in the `user' table. This is because administrative
operations are operations on the server itself and are not
database-specific, so there is no reason to list such privileges in the
other grant tables. In fact, only the `user' table need be consulted
to determine whether or not you can perform an administrative operation.
The *file* privilege is specified only in the `user' table, too. It is
not an administrative privilege as such, but your ability to read or
write files on the server host is independent of the database you are
accessing.
The `mysqld' server reads the contents of the grant tables once, when it
starts up. Changes to the grant tables take effect as indicated in
*Note Privilege changes::.
When you modify the contents of the grant tables, it is a good idea to
make sure that your changes set up privileges the way you want. For
help in diagnosing problems, see *Note Access denied::. For advice on
security issues, *note Security::.
A useful diagnostic tool is the `mysqlaccess' script, which Yves
Carlier has provided for the *MySQL* distribution. Invoke
`mysqlaccess' with the `--help' option to find out how it works. Note
that `mysqlaccess' checks access using only the `user', `db' and `host'
tables. It does not check table- or column-level privileges.
Access Control, Stage 1: Connection Verification
================================================
When you attempt to connect to a *MySQL* server, the server accepts or
rejects the connection based on your identity and whether or not you can
verify your identity by supplying the correct password. If not, the
server denies access to you completely. Otherwise, the server accepts
the connection, then enters Stage 2 and waits for requests.
Your identity is based on two pieces of information:
* The host from which you connect
* Your *MySQL* user name
Identity checking is performed using the three `user' table scope fields
(`Host', `User', and `Password'). The server accepts the connection
only if a `user' table entry matches your hostname and user name, and
you supply the correct password.
Values in the `user' table scope fields may be specified as follows:
* A `Host' value may be a hostname or an IP number, or `'localhost''
to indicate the local host.
* You can use the wild-card characters `%' and `_' in the `Host'
field.
* A `Host' value of `'%'' matches any hostname. A blank `Host'
value is equivalent to `'%''. Note that these values match _any
host that can create a connection to your server!_
* As of MySQL Version 3.23, for `Host' values specified as IP
numbers, you can specify a netmask indicating how many address
bits to use for the network number. For example:
GRANT ALL PRIVILEGES on db.* to david@'192.58.197.0/255.255.255.0';
This will allow everyone to connect from an IP where the following
is true:
user_ip & netmask = host_ip.
In the above example all IP:s in the interval 192.58.197.0 -
192.58.197.255 can connect to the *MySQL* server.
* Wild-card characters are not allowed in the `User' field, but you
can specify a blank value, which matches any name. If the `user'
table entry that matches an incoming connection has a blank user
name, the user is considered to be the anonymous user (the user
with no name), rather than the name that the client actually
specified. This means that a blank user name is used for all
further access checking for the duration of the connection (that
is, during Stage 2).
* The `Password' field can be blank. This does not mean that any
password matches, it means the user must connect without
specifying a password.
Non-blank `Password' values represent encrypted passwords. *MySQL*
does not store passwords in plaintext form for anyone to see. Rather,
the password supplied by a user who is attempting to connect is
encrypted (using the `PASSWORD()' function). The encrypted password is
then used when the client/server is checking if the password is correct
(This is done without the encrypted password ever traveling over the
connection.) Note that from *MySQL*'s point of view the encrypted
password is the REAL password, so you should not give anyone access to
it! In particular, don't give normal users read access to the tables
in the `mysql' database!
The examples below show how various combinations of `Host' and `User'
values in `user' table entries apply to incoming connections:
`Host' *value* `User' *Connections matched by entry*
*value*
`'thomas.loc.gov'' `'fred'' `fred', connecting from `thomas.loc.gov'
`'thomas.loc.gov'' `''' Any user, connecting from `thomas.loc.gov'
`'%'' `'fred'' `fred', connecting from any host
`'%'' `''' Any user, connecting from any host
`'%.loc.gov'' `'fred'' `fred', connecting from any host in the
`loc.gov' domain
`'x.y.%'' `'fred'' `fred', connecting from `x.y.net',
`x.y.com',`x.y.edu', etc. (this is
probably not useful)
`'144.155.166.177''`'fred'' `fred', connecting from the host with IP
address `144.155.166.177'
`'144.155.166.%'' `'fred'' `fred', connecting from any host in the
`144.155.166' class C subnet
`'144.155.166.0/255.255.255.0''`'fred'' Same as previous example
Because you can use IP wild-card values in the `Host' field (for
example, `'144.155.166.%'' to match every host on a subnet), there is
the possibility that someone might try to exploit this capability by
naming a host `144.155.166.somewhere.com'. To foil such attempts,
*MySQL* disallows matching on hostnames that start with digits and a
dot. Thus, if you have a host named something like `1.2.foo.com', its
name will never match the `Host' column of the grant tables. Only an
IP number can match an IP wild-card value.
An incoming connection may be matched by more than one entry in the
`user' table. For example, a connection from `thomas.loc.gov' by
`fred' would be matched by several of the entries just shown above. How
does the server choose which entry to use if more than one matches? The
server resolves this question by sorting the `user' table after reading
it at startup time, then looking through the entries in sorted order
when a user attempts to connect. The first matching entry is the one
that is used.
`user' table sorting works as follows. Suppose the `user' table looks
like this:
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ...
| % | jeffrey | ...
| localhost | root | ...
| localhost | | ...
+-----------+----------+-
When the server reads in the table, it orders the entries with the
most-specific `Host' values first (`'%'' in the `Host' column means
"any host" and is least specific). Entries with the same `Host' value
are ordered with the most-specific `User' values first (a blank `User'
value means "any user" and is least specific). The resulting sorted
`user' table looks like this:
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| localhost | root | ...
| localhost | | ...
| % | jeffrey | ...
| % | root | ...
+-----------+----------+-
When a connection is attempted, the server looks through the sorted
entries and uses the first match found. For a connection from
`localhost' by `jeffrey', the entries with `'localhost'' in the `Host'
column match first. Of those, the entry with the blank user name
matches both the connecting hostname and user name. (The
`'%'/'jeffrey'' entry would have matched, too, but it is not the first
match in the table.)
Here is another example. Suppose the `user' table looks like this:
+----------------+----------+-
| Host | User | ...
+----------------+----------+-
| % | jeffrey | ...
| thomas.loc.gov | | ...
+----------------+----------+-
The sorted table looks like this:
+----------------+----------+-
| Host | User | ...
+----------------+----------+-
| thomas.loc.gov | | ...
| % | jeffrey | ...
+----------------+----------+-
A connection from `thomas.loc.gov' by `jeffrey' is matched by the first
entry, whereas a connection from `whitehouse.gov' by `jeffrey' is
matched by the second.
A common misconception is to think that for a given user name, all
entries that explicitly name that user will be used first when the
server attempts to find a match for the connection. This is simply not
true. The previous example illustrates this, where a connection from
`thomas.loc.gov' by `jeffrey' is first matched not by the entry
containing `'jeffrey'' as the `User' field value, but by the entry with
no user name!
If you have problems connecting to the server, print out the `user'
table and sort it by hand to see where the first match is being made.
Access Control, Stage 2: Request Verification
=============================================
Once you establish a connection, the server enters Stage 2. For each
request that comes in on the connection, the server checks whether you
have sufficient privileges to perform it, based on the type of
operation you wish to perform. This is where the privilege fields in
the grant tables come into play. These privileges can come from any of
the `user', `db', `host', `tables_priv', or `columns_priv' tables. The
grant tables are manipulated with `GRANT' and `REVOKE' commands. *Note
`GRANT': GRANT. (You may find it helpful to refer to *Note
Privileges::, which lists the fields present in each of the grant
tables.)
The `user' table grants privileges that are assigned to you on a global
basis and that apply no matter what the current database is. For
example, if the `user' table grants you the *delete* privilege, you can
delete rows from any database on the server host! In other words,
`user' table privileges are superuser privileges. It is wise to grant
privileges in the `user' table only to superusers such as server or
database administrators. For other users, you should leave the
privileges in the `user' table set to `'N'' and grant privileges on a
database-specific basis only, using the `db' and `host' tables.
The `db' and `host' tables grant database-specific privileges. Values
in the scope fields may be specified as follows:
* The wild-card characters `%' and `_' can be used in the `Host' and
`Db' fields of either table.
* A `'%'' `Host' value in the `db' table means "any host." A blank
`Host' value in the `db' table means "consult the `host' table for
further information."
* A `'%'' or blank `Host' value in the `host' table means "any host."
* A `'%'' or blank `Db' value in either table means "any database."
* A blank `User' value in either table matches the anonymous user.
The `db' and `host' tables are read in and sorted when the server
starts up (at the same time that it reads the `user' table). The `db'
table is sorted on the `Host', `Db', and `User' scope fields, and the
`host' table is sorted on the `Host' and `Db' scope fields. As with
the `user' table, sorting puts the most-specific values first and
least-specific values last, and when the server looks for matching
entries, it uses the first match that it finds.
The `tables_priv' and `columns_priv' tables grant table- and
column-specific privileges. Values in the scope fields may be
specified as follows:
* The wild-card characters `%' and `_' can be used in the `Host'
field of either table.
* A `'%'' or blank `Host' value in either table means "any host."
* The `Db', `Table_name' and `Column_name' fields cannot contain
wild cards or be blank in either table.
The `tables_priv' and `columns_priv' tables are sorted on the `Host',
`Db', and `User' fields. This is similar to `db' table sorting,
although the sorting is simpler because only the `Host' field may
contain wild cards.
The request verification process is described below. (If you are
familiar with the access-checking source code, you will notice that the
description here differs slightly from the algorithm used in the code.
The description is equivalent to what the code actually does; it
differs only to make the explanation simpler.)
For administrative requests (*shutdown*, *reload*, etc.), the server
checks only the `user' table entry, because that is the only table that
specifies administrative privileges. Access is granted if the entry
allows the requested operation and denied otherwise. For example, if
you want to execute `mysqladmin shutdown' but your `user' table entry
doesn't grant the *shutdown* privilege to you, access is denied without
even checking the `db' or `host' tables. (They contain no
`Shutdown_priv' column, so there is no need to do so.)
For database-related requests (*insert*, *update*, etc.), the server
first checks the user's global (superuser) privileges by looking in the
`user' table entry. If the entry allows the requested operation,
access is granted. If the global privileges in the `user' table are
insufficient, the server determines the user's database-specific
privileges by checking the `db' and `host' tables:
1. The server looks in the `db' table for a match on the `Host',
`Db', and `User' fields. The `Host' and `User' fields are matched
to the connecting user's hostname and *MySQL* user name. The `Db'
field is matched to the database the user wants to access. If
there is no entry for the `Host' and `User', access is denied.
2. If there is a matching `db' table entry and its `Host' field is
not blank, that entry defines the user's database-specific
privileges.
3. If the matching `db' table entry's `Host' field is blank, it
signifies that the `host' table enumerates which hosts should be
allowed access to the database. In this case, a further lookup is
done in the `host' table to find a match on the `Host' and `Db'
fields. If no `host' table entry matches, access is denied. If
there is a match, the user's database-specific privileges are
computed as the intersection (_not_ the union!) of the privileges
in the `db' and `host' table entries, that is, the privileges that
are `'Y'' in both entries. (This way you can grant general
privileges in the `db' table entry and then selectively restrict
them on a host-by-host basis using the `host' table entries.)
After determining the database-specific privileges granted by the `db'
and `host' table entries, the server adds them to the global privileges
granted by the `user' table. If the result allows the requested
operation, access is granted. Otherwise, the server checks the user's
table and column privileges in the `tables_priv' and `columns_priv'
tables and adds those to the user's privileges. Access is allowed or
denied based on the result.
Expressed in boolean terms, the preceding description of how a user's
privileges are calculated may be summarized like this:
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
It may not be apparent why, if the global `user' entry privileges are
initially found to be insufficient for the requested operation, the
server adds those privileges to the database-, table-, and
column-specific privileges later. The reason is that a request might
require more than one type of privilege. For example, if you execute
an `INSERT ... SELECT' statement, you need both *insert* and *select*
privileges. Your privileges might be such that the `user' table entry
grants one privilege and the `db' table entry grants the other. In
this case, you have the necessary privileges to perform the request,
but the server cannot tell that from either table by itself; the
privileges granted by the entries in both tables must be combined.
The `host' table can be used to maintain a list of secure servers.
At TcX, the `host' table contains a list of all machines on the local
network. These are granted all privileges.
You can also use the `host' table to indicate hosts that are _not_
secure. Suppose you have a machine `public.your.domain' that is located
in a public area that you do not consider secure. You can allow access
to all hosts on your network except that machine by using `host' table
entries like this:
+--------------------+----+-
| Host | Db | ...
+--------------------+----+-
| public.your.domain | % | ... (all privileges set to 'N')
| %.your.domain | % | ... (all privileges set to 'Y')
+--------------------+----+-
Naturally, you should always test your entries in the grant tables (for
example, using `mysqlaccess') to make sure your access privileges are
actually set up the way you think they are.
When Privilege Changes Take Effect
==================================
When `mysqld' starts, all grant table contents are read into memory and
become effective at that point.
Modifications to the grant tables that you perform using `GRANT',
`REVOKE', or `SET PASSWORD' 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' or `mysqladmin reload' to tell the server
to reload the grant tables. Otherwise your changes will have _no
effect_ until you restart the server. If you change the grant tables
manually but forget to reload the privileges, you will be wondering why
your changes don't seem to make any difference!
When the server notices that the grant tables have been changed,
existing client connections are affected as follows:
* Table and column privilege changes take effect with the client's
next request.
* Database privilege changes take effect at the next `USE db_name'
command.
Global privilege changes and password changes take effect the next time
the client connects.
Setting Up the Initial MySQL Privileges
=======================================
After installing *MySQL*, you set up the initial access privileges by
running `scripts/mysql_install_db'. *Note Quick install::. The
`mysql_install_db' script starts up the `mysqld' server, then
initializes the grant tables to contain the following set of privileges:
* The *MySQL* `root' user is created as a superuser who can do
anything. Connections must be made from the local host.
*NOTE:* The initial `root' password is empty, so anyone can
connect as `root' _without a password_ and be granted all
privileges.
* An anonymous user is created that can do anything with databases
that have a name of `'test'' or starting with `'test_''.
Connections must be made from the local host. This means any
local user can connect without a password and be treated as the
anonymous user.
* Other privileges are denied. For example, normal users can't use
`mysqladmin shutdown' or `mysqladmin processlist'.
*NOTE:* The default privileges are different for Windows. *Note
Windows running::.
Because your installation is initially wide open, one of the first
things you should do is specify a password for the *MySQL* `root' user.
You can do this as follows (note that you specify the password using
the `PASSWORD()' function):
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
WHERE user='root';
mysql> FLUSH PRIVILEGES;
You can, in *MySQL* Version 3.22 and above, use the `SET PASSWORD'
statement:
shell> mysql -u root mysql
mysql> SET PASSWORD FOR root=PASSWORD('new_password');
Another way to set the password is by using the `mysqladmin' command:
shell> mysqladmin -u root password new_password
Only users with write/update access to the mysql database can change the
password for others users. All normal users (not anonymous ones) can
only change their own password with either of the above commands or with
`SET PASSWORD=PASSWORD('new password')'.
Note that if you update the password in the `user' table directly using
the first method, you must tell the server to re-read the grant tables
(with `FLUSH PRIVILEGES'), because the change will go unnoticed
otherwise.
Once the `root' password has been set, thereafter you must supply that
password when you connect to the server as `root'.
You may wish to leave the `root' password blank so that you don't need
to specify it while you perform additional setup or testing. However,
be sure to set it before using your installation for any real
production work.
See the `scripts/mysql_install_db' script to see how it sets up the
default privileges. You can use this as a basis to see how to add
other users.
If you want the initial privileges to be different than those just
described above, you can modify `mysql_install_db' before you run it.
To re-create the grant tables completely, remove all the `.frm',
`.MYI', and `.MYD' files in the directory containing the `mysql'
database. (This is the directory named `mysql' under the database
directory, which is listed when you run `mysqld --help'.) Then run the
`mysql_install_db' script, possibly after editing it first to have the
privileges you want.
*NOTE:* For *MySQL* versions older than Version 3.22.10, you should NOT
delete the `.frm' files. If you accidentally do this, you should copy
them back from your *MySQL* distribution before running
`mysql_install_db'.
Adding New User Privileges to MySQL
===================================
You can add users two different ways: by using `GRANT' statements or by
manipulating the *MySQL* grant tables directly. The preferred method
is to use `GRANT' statements, because they are more concise and less
error-prone.
The examples below show how to use the `mysql' client to set up new
users. These examples assume that privileges are set up according to
the defaults described in the previous section. This means that to
make changes, you must be on the same machine where `mysqld' is
running, you must connect as the *MySQL* `root' user, and the `root'
user must have the *insert* privilege for the `mysql' database and the
*reload* administrative privilege. Also, if you have changed the
`root' user password, you must specify it for the `mysql' commands
below.
You can add new users by issuing `GRANT' statements:
shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;
These `GRANT' statements set up three new users:
`monty'
A full superuser who can connect to the server from anywhere, but
who must use a password `'some_pass'' to do so. Note that we must
issue `GRANT' statements for both `monty@localhost' and
`monty@"%"'. If we don't add the entry with `localhost', the
anonymous user entry for `localhost' that is created by
`mysql_install_db' will take precedence when we connect from the
local host, because it has a more specific `Host' field value and
thus comes earlier in the `user' table sort order.
`admin'
A user who can connect from `localhost' without a password and who
is granted the *reload* and *process* administrative privileges.
This allows the user to execute the `mysqladmin reload',
`mysqladmin refresh', and `mysqladmin flush-*' commands, as well as
`mysqladmin processlist' . No database-related privileges are
granted. (They can be granted later by issuing additional `GRANT'
statements.)
`dummy'
A user who can connect without a password, but only from the local
host. The global privileges are all set to `'N'' -- the `USAGE'
privilege type allows you to create a user with no privileges. It
is assumed that you will grant database-specific privileges later.
You can also add the same user access information directly by issuing
`INSERT' statements and then telling the server to reload the grant
tables:
shell> mysql --user=root mysql
mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysql> INSERT INTO user SET Host='localhost',User='admin',
Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;
Depending on your *MySQL* version, you may have to use a different
number of `'Y'' values above (versions prior to Version 3.22.11 had
fewer privilege columns). For the `admin' user, the more readable
extended `INSERT' syntax that is available starting with Version
3.22.11 is used.
Note that to set up a superuser, you need only create a `user' table
entry with the privilege fields set to `'Y''. No `db' or `host' table
entries are necessary.
The privilege columns in the `user' table were not set explicitly in the
last `INSERT' statement (for the `dummy' user), so those columns are
assigned the default value of `'N''. This is the same thing that
`GRANT USAGE' does.
The following example adds a user `custom' who can connect from hosts
`localhost', `server.domain', and `whitehouse.gov'. He wants to access
the `bankaccount' database only from `localhost', the `expenses'
database only from `whitehouse.gov', and the `customer' database from
all three hosts. He wants to use the password `stupid' from all three
hosts.
To set up this user's privileges using `GRANT' statements, run these
commands:
shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON bankaccount.*
TO custom@localhost
IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO custom@whitehouse.gov
IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.*
TO custom@'%'
IDENTIFIED BY 'stupid';
To set up the user's privileges by modifying the grant tables directly,
run these commands (note the `FLUSH PRIVILEGES' at the end):
shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
VALUES('localhost','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
VALUES('server.domain','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
The first three `INSERT' statements add `user' table entries that allow
user `custom' to connect from the various hosts with the given
password, but grant no permissions to him (all privileges are set to the
default value of `'N''). The next three `INSERT' statements add `db'
table entries that grant privileges to `custom' for the `bankaccount',
`expenses', and `customer' databases, but only when accessed from the
proper hosts. As usual, when the grant tables are modified directly,
the server must be told to reload them (with `FLUSH PRIVILEGES') so
that the privilege changes take effect.
If you want to give a specific user access from any machine in a given
domain, you can issue a `GRANT' statement like the following:
mysql> GRANT ...
ON *.*
TO myusername@"%.mydomainname.com"
IDENTIFIED BY 'mypassword';
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',
PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;
You can also use `xmysqladmin', `mysql_webadmin', and even `xmysql' to
insert, change, and update values in the grant tables. You can find
these utilities in the Contrib directory of the *MySQL* Website
(http://www.mysql.com/Downloads/Contrib/).
Setting Up Passwords
====================
In most cases you should use `GRANT' to set up your users/passwords, so
the following only applies for advanced users. *Note `GRANT': GRANT.
The examples in the preceding sections illustrate an important
principle: when you store a non-empty password using `INSERT' or
`UPDATE' statements, you must use the `PASSWORD()' function to encrypt
it. This is because the `user' table stores passwords in encrypted
form, not as plaintext. If you forget that fact, you are likely to
attempt to set passwords like this:
shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;
The result is that the plaintext value `'biscuit'' is stored as the
password in the `user' table. When the user `jeffrey' attempts to
connect to the server using this password, the `mysql' client encrypts
it with `PASSWORD()' and sends the result to the server. The server
compares the value in the `user' table (the encrypted value of
`'biscuit'') to the encrypted password (which is _not_ `'biscuit'').
The comparison fails and the server rejects the connection:
shell> mysql -u jeffrey -pbiscuit test
Access denied
Passwords must be encrypted when they are inserted in the `user' table,
so the `INSERT' statement should have been specified like this instead:
mysql> INSERT INTO user (Host,User,Password)
VALUES('%','jeffrey',PASSWORD('biscuit'));
You must also use the `PASSWORD()' function when you use `SET PASSWORD'
statements:
mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit');
If you set passwords using the `GRANT ... IDENTIFIED BY' statement or
the `mysqladmin password' command, the `PASSWORD()' function is
unnecessary. They both take care of encrypting the password for you,
so you would specify a password of `'biscuit'' like this:
mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit';
or
shell> mysqladmin -u jeffrey password biscuit
*NOTE:* `PASSWORD()' does not perform password encryption in the same
way that Unix passwords are encrypted. You should not assume that if
your Unix password and your *MySQL* password are the same, that
`PASSWORD()' will result in the same encrypted value as is stored in the
Unix password file. *Note User names::.
Causes of `Access denied' Errors
================================
If you encounter `Access denied' errors when you try to connect to the
*MySQL* server, the list below indicates some courses of action you can
take to correct the problem:
* After installing *MySQL*, did you run the `mysql_install_db'
script to set up the initial grant table contents? If not, do so.
*Note Default privileges::. Test the initial privileges by
executing this command:
shell> mysql -u root test
The server should let you connect without error. You should also
make sure you have a file `user.MYD' in the *MySQL* database
directory. Ordinarily, this is `PATH/var/mysql/user.MYD', where
`PATH' is the pathname to the *MySQL* installation root.
* After a fresh installation, you should connect to the server and
set up your users and their access permissions:
shell> mysql -u root mysql
The server should let you connect because the *MySQL* `root' user
has no password initially. That is also a security risk, so
setting the `root' password is something you should do while
you're setting up your other *MySQL* users.
If you try to connect as `root' and get this error:
Access denied for user: '@unknown' to database mysql
this means that you don't have an entry in the `user' table with a
`User' column value of `'root'' and that `mysqld' cannot resolve
the hostname for your client. In this case, you must restart the
server with the `--skip-grant-tables' option and edit your
`/etc/hosts' or `windowshosts' file to add an entry for your
host.
* If you get an error like the following:
shell> mysqladmin -u root -pxxxx ver
Access denied for user: 'root@localhost' (Using password: YES)
It means that you are using a wrong password. *Note Passwords::.
If you have forgot the root password, you can restart `mysqld' with
`--skip-grant-tables' to change the password. You can find more
about this option later on in this manual section.
If you get the above error even if you haven't specified a
password, this means that you a wrong password in some `my.ini'
file. *Note Option files::. You can avoid using option files with
the `--no-defaults' option, as follows:
shell> mysqladmin --no-defaults -u root ver
* If you updated an existing *MySQL* installation from a version
earlier than Version 3.22.11 to Version 3.22.11 or later, did you
run the `mysql_fix_privilege_tables' script? If not, do so. The
structure of the grant tables changed with *MySQL* Version 3.22.11
when the `GRANT' statement became functional.
* If your privileges seem to have changed in the middle of a
session, it may be that a superuser has changed them. Reloading
the grant tables affects new client connections, but it also
affects existing connections as indicated in *Note Privilege
changes::.
* If you can't get your password to work, remember that you must use
the `PASSWORD()' function if you set the password with the
`INSERT', `UPDATE', or `SET PASSWORD' statements. The
`PASSWORD()' function is unnecessary if you specify the password
using the `GRANT ... INDENTIFIED BY' statement or the `mysqladmin
password' command. *Note Passwords::.
* `localhost' is a synonym for your local hostname, and is also the
default host to which clients try to connect if you specify no host
explicitly. However, connections to `localhost' do not work if
you are running on a system that uses MIT-pthreads (`localhost'
connections are made using Unix sockets, which are not supported
by MIT-pthreads). To avoid this problem on such systems, you
should use the `--host' option to name the server host explicitly.
This will make a TCP/IP connection to the `mysqld' server. In
this case, you must have your real hostname in `user' table
entries on the server host. (This is true even if you are running
a client program on the same host as the server.)
* If you get an `Access denied' error when trying to connect to the
database with `mysql -u user_name db_name', you may have a problem
with the `user' table. Check this by executing `mysql -u root
mysql' and issuing this SQL statement:
mysql> SELECT * FROM user;
The result should include an entry with the `Host' and `User'
columns matching your computer's hostname and your *MySQL* user
name.
* The `Access denied' error message will tell you who you are trying
to log in as, the host from which you are trying to connect, and
whether or not you were using a password. Normally, you should
have one entry in the `user' table that exactly matches the
hostname and user name that were given in the error message. For
example if you get an error message that contains `Using password:
NO', this means that you tried to login without an password.
* If you get the following error when you try to connect from a
different host than the one on which the *MySQL* server is
running, then there is no row in the `user' table that matches
that host:
Host ... is not allowed to connect to this MySQL server
You can fix this by using the command-line tool `mysql' (on the
server host!) to add a row to the `user', `db', or `host' table
for the user/hostname combination from which you are trying to
connect and then execute `mysqladmin flush-privileges'. If you are
not running *MySQL* Version 3.22 and you don't know the IP number
or hostname of the machine from which you are connecting, you
should put an entry with `'%'' as the `Host' column value in the
`user' table and restart `mysqld' with the `--log' option on the
server machine. After trying to connect from the client machine,
the information in the *MySQL* log will indicate how you really did
connect. (Then replace the `'%'' in the `user' table entry with
the actual hostname that shows up in the log. Otherwise, you'll
have a system that is insecure.)
Another reason for this error on Linux is that you are using a
binary *MySQL* version that is compiled with a different glibc
version than the one you are using. In this case you should
either upgrade your OS/glibc or download the source *MySQL*
version and compile this yourself. A source RPM is normally
trivial to compile and install, so this isn't a big problem.
* If you get an error message where the hostname is not shown or
where the hostname is an IP, even if you try to connect with a
hostname:
shell> mysqladmin -u root -pxxxx -h some-hostname ver
Access denied for user: 'root('Using password: YES)
This means that *MySQL* got some error when trying to resolve the
IP to a hostname. In this case you can execute `mysqladmin
flush-hosts' to reset the internal DNS cache. *Note DNS::.
Some permanent solutions are:
- Try to find out what is wrong with your DNS server and fix
this.
- Specify IPs instead of hostnames in the *MySQL* privilege
tables.
- Start mysqld with `--skip-name-resolve'.
- Start mysqld with `--skip-host-cache'.
- Connect to `localhost' if you are running the server and the
client on the same machine.
- Put the client machine names in `/etc/hosts'.
* If `mysql -u root test' works but `mysql -h your_hostname -u root
test' results in `Access denied', then you may not have the
correct name for your host in the `user' table. A common problem
here is that the `Host' value in the user table entry specifies an
unqualified hostname, but your system's name resolution routines
return a fully qualified domain name (or vice-versa). For
example, if you have an entry with host `'tcx'' in the `user'
table, but your DNS tells *MySQL* that your hostname is
`'tcx.subnet.se'', the entry will not work. Try adding an entry to
the `user' table that contains the IP number of your host as the
`Host' column value. (Alternatively, you could add an entry to the
`user' table with a `Host' value that contains a wild card--for
example, `'tcx.%''. However, use of hostnames ending with `%' is
_insecure_ and is _not_ recommended!)
* If `mysql -u user_name test' works but `mysql -u user_name
other_db_name' doesn't work, you don't have an entry for
`other_db_name' listed in the `db' table.
* If `mysql -u user_name db_name' works when executed on the server
machine, but `mysql -u host_name -u user_name db_name' doesn't
work when executed on another client machine, you don't have the
client machine listed in the `user' table or the `db' table.
* If you can't figure out why you get `Access denied', remove from
the `user' table all entries that have `Host' values containing
wild cards (entries that contain `%' or `_'). A very common error
is to insert a new entry with `Host'=`'%'' and `User'=`'some
user'', thinking that this will allow you to specify `localhost'
to connect from the same machine. The reason that this doesn't
work is that the default privileges include an entry with
`Host'=`'localhost'' and `User'=`'''. Because that entry has a
`Host' value `'localhost'' that is more specific than `'%'', it is
used in preference to the new entry when connecting from
`localhost'! The correct procedure is to insert a second entry
with `Host'=`'localhost'' and `User'=`'some_user'', or to remove
the entry with `Host'=`'localhost'' and `User'=`'''.
* If you get the following error, you may have a problem with the
`db' or `host' table:
Access to database denied
If the entry selected from the `db' table has an empty value in the
`Host' column, make sure there are one or more corresponding
entries in the `host' table specifying which hosts the `db' table
entry applies to.
If you get the error when using the SQL commands `SELECT ... INTO
OUTFILE' or `LOAD DATA INFILE', your entry in the `user' table
probably doesn't have the *file* privilege enabled.
* Remember that client programs will use connection parameters
specified in configuration files or environment variables. *Note
Environment variables::. If a client seems to be sending the
wrong default connection parameters when you don't specify them on
the command line, check your environment and the `.my.cnf' file in
your home directory. You might also check the system-wide *MySQL*
configuration files, though it is far less likely that client
connection parameters will be specified there. *Note Option
files::. If you get `Access denied' when you run a client without
any options, make sure you haven't specified an old password in
any of your option files! *Note Option files::.
* If you make changes to the grant tables directly (using an
`INSERT' or `UPDATE' statement) and your changes seem to be
ignored, remember that you must issue a `FLUSH PRIVILEGES'
statement or execute a `mysqladmin flush-privileges' command to
cause the server to re-read the privilege tables. Otherwise your
changes have no effect until the next time the server is
restarted. Remember that after you set the `root' password with
an `UPDATE' command, you won't need to specify it until after you
flush the privileges, because the server won't know you've changed
the password yet!
* If you have access problems with a Perl, PHP, Python, or ODBC
program, try to connect to the server with `mysql -u user_name
db_name' or `mysql -u user_name -pyour_pass db_name'. If you are
able to connect using the `mysql' client, there is a problem with
your program and not with the access privileges. (Note that there
is no space between `-p' and the password; you can also use the
`--password=your_pass' syntax to specify the password. If you use
the `-p' option alone, MySQL will prompt you for the password.)
* For testing, start the `mysqld' daemon with the
`--skip-grant-tables' option. Then you can change the *MySQL*
grant tables and use the `mysqlaccess' script to check whether or
not your modifications have the desired effect. When you are
satisfied with your changes, execute `mysqladmin flush-privileges'
to tell the `mysqld' server to start using the new grant tables.
*Note:* Reloading the grant tables overrides the
`--skip-grant-tables' option. This allows you to tell the server
to begin using the grant tables again without bringing it down and
restarting it.
* If everything else fails, start the `mysqld' daemon with a
debugging option (for example, `--debug=d,general,query'). This
will print host and user information about attempted connections,
as well as information about each command issued. *Note Debugging
server::.
* If you have any other problems with the *MySQL* grant tables and
feel you must post the problem to the mailing list, always provide
a dump of the *MySQL* grant tables. You can dump the tables with
the `mysqldump mysql' command. As always, post your problem using
the `mysqlbug' script. *Note Bug reports::. In some cases you
may need to restart `mysqld' with `--skip-grant-tables' to run
`mysqldump'.
MySQL Language Reference
************************
Literals: How to Write Strings and Numbers
==========================================
Strings
-------
A string is a sequence of characters, surrounded by either single quote
(`'') or double quote (`"') characters (only the single quote if you
run in ANSI mode). Examples:
'a string'
"another string"
Within a string, certain sequences have special meaning. Each of these
sequences begins with a backslash (`'), known as the _escape
character_. *MySQL* recognizes the following escape sequences:
`'
An ASCII 0 (`NUL') character.
`n'
A newline character.
`t'
A tab character.
`r'
A carriage return character.
`b'
A backspace character.
`''
A single quote (`'') character.
`"'
A double quote (`"') character.
`\'
A backslash (`') character.
`%'
A `%' character. This is used to search for literal instances of
`%' in contexts where `%' would otherwise be interpreted as a
wild-card character. *Note String comparison functions::.
`_'
A `_' character. This is used to search for literal instances of
`_' in contexts where `_' would otherwise be interpreted as a
wild-card character. *Note String comparison functions::.
Note that if you use `%' or `_' in some string contexts, these will
return the strings `%' and `_' and not `%' and `_'.
There are several ways to include quotes within a string:
* A `'' inside a string quoted with `'' may be written as `'''.
* A `"' inside a string quoted with `"' may be written as `""'.
* You can precede the quote character with an escape character (`').
* A `'' inside a string quoted with `"' needs no special treatment
and need not be doubled or escaped. In the same way, `"' inside a
string quoted with `'' needs no special treatment.
The `SELECT' statements shown below demonstrate how quoting and
escaping work:
mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', ''hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", ""hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
mysql> SELECT "ThisnIsnFournlines";
+--------------------+
| This
Is
Four
lines |
+--------------------+
If you want to insert binary data into a `BLOB' column, the following
characters must be represented by escape sequences:
`NUL'
ASCII 0. You should represent this by `' (a backslash and an
ASCII `0' character).
`'
ASCII 92, backslash. Represent this by `\'.
`''
ASCII 39, single quote. Represent this by `''.
`"'
ASCII 34, double quote. Represent this by `"'.
If you write C code, you can use the C API function
`mysql_escape_string()' to escape characters for the `INSERT'
statement. *Note C API function overview::. In Perl, you can use the
`quote' method of the `DBI' package to convert special characters to
the proper escape sequences. *Note Perl `DBI' Class: Perl DBI Class.
You should use an escape function on any string that might contain any
of the special characters listed above!
Numbers
-------
Integers are represented as a sequence of digits. Floats use `.' as a
decimal separator. Either type of number may be preceded by `-' to
indicate a negative value.
Examples of valid integers:
1221
0
-32
Examples of valid floating-point numbers:
294.42
-32032.6809e+10
148.00
An integer may be used in a floating-point context; it is interpreted
as the equivalent floating-point number.
Hexadecimal Values
------------------
*MySQL* supports hexadecimal values. In number context these act like
an integer (64-bit precision). In string context these act like a binary
string where each pair of hex digits is converted to a character:
mysql> SELECT 0xa+0
-> 10
mysql> select 0x5061756c;
-> Paul
Hexadecimal strings are often used by ODBC to give values for BLOB
columns.
`NULL' Values
-------------
The `NULL' value means "no data" and is different from values such as
`0' for numeric types or the empty string for string types. *Note
Problems with `NULL': Problems with NULL.
`NULL' may be represented by `N' when using the text file import or
export formats (`LOAD DATA INFILE', `SELECT ... INTO OUTFILE'). *Note
`LOAD DATA': LOAD DATA.
Database, Table, Index, Column, and Alias Names
-----------------------------------------------
Database, table, index, column, and alias names all follow the same
rules in *MySQL*.
Note that the rules changed starting with *MySQL* Version 3.23.6 when we
introduced quoting of identifiers (database, table, and column names)
with ``'. `"' will also work to quote identifiers if you run in ANSI
mode. *Note ANSI mode::.
*Identifier**Max *Allowed characters*
length*
Database 64 Any character that is allowed in a directory name
except `/'.
Table 64 Any character that is allowed in a file name,
except `/' or `.'.
Column 64 All characters.
Alias 255 All characters.
Note that in addition to the above, you can't have ASCII(0) or
ASCII(255) or the quoting character in an identifier.
Note that if the identifer is a restricted word or contains special
characters you must always quote it with ``' when you use it:
SELECT * from `select` where `select`.id > 100;
In previous versions of *MySQL*, the name rules are as follows:
* A name may consist of alphanumeric characters from the current
character set and also `_' and `$'. The default character set is
ISO-8859-1 Latin1; this may be changed with the
`--default-character-set' option to `mysqld'. *Note Character
sets::.
* A name may start with any character that is legal in a name. In
particular, a name may start with a number (this differs from many
other database systems!). However, a name cannot consist _only_
of numbers.
* You cannot use the `.' character in names because it is used to
extend the format by which you can refer to columns (see
immediately below).
It is recommended that you do not use names like `1e', because an
expression like `1e+1' is ambiguous. It may be interpreted as the
expression `1e + 1' or as the number `1e+1'.
In *MySQL* you can refer to a column using any of the following forms:
*Column reference* *Meaning*
`col_name' Column `col_name' from whichever table used in
the query contains a column of that name.
`tbl_name.col_name' Column `col_name' from table `tbl_name' of the
current database.
`db_name.tbl_name.col_name'Column `col_name' from table `tbl_name' of the
database `db_name'. This form is available in
*MySQL* Version 3.22 or later.
``column_name`' A column that is a keyword or contains special
characters.
You need not specify a `tbl_name' or `db_name.tbl_name' prefix for a
column reference in a statement unless the reference would be ambiguous.
For example, suppose tables `t1' and `t2' each contain a column `c',
and you retrieve `c' in a `SELECT' statement that uses both `t1' and
`t2'. In this case, `c' is ambiguous because it is not unique among
the tables used in the statement, so you must indicate which table you
mean by writing `t1.c' or `t2.c'. Similarly, if you are retrieving
from a table `t' in database `db1' and from a table `t' in database
`db2', you must refer to columns in those tables as `db1.t.col_name'
and `db2.t.col_name'.
The syntax `.tbl_name' means the table `tbl_name' in the current
database. This syntax is accepted for ODBC compatibility, because some
ODBC programs prefix table names with a `.' character.
Case Sensitivity in Names
.........................
In *MySQL*, databases and tables correspond to directories and files
within those directories. Consequently, the case sensitivity of the
underlying operating system determines the case sensitivity of database
and table names. This means database and table names are case
sensitive in Unix and case insensitive in Windows. *Note Extensions to
ANSI::.
*NOTE:* Although database and table names are case insensitive for
Windows, you should not refer to a given database or table using
different cases within the same query. The following query would not
work because it refers to a table both as `my_table' and as `MY_TABLE':
mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
Column names are case insensitive in all cases.
Aliases on tables are case sensitive. The following query would not
work because it refers to the alias both as `a' and as `A':
mysql> SELECT col_name FROM tbl_name AS a
WHERE a.col_name = 1 OR A.col_name = 2;
Aliases on columns are case insensitive.
If you have a problem remembering the used cases for a table names,
adopt a consistent convention, such as always creating databases and
tables using lowercase names.
One way to avoid this problem is to start `mysqld' with `-O
lower_case_table_names=1'.
In this case *MySQL* will on Windows/NT convert all table names to
lower case on storage and lookup. Note that you need to first convert
your old table names to lower case before starting `mysqld' with this
option.
User Variables
==============
*MySQL* supports thread-specific variables with the `@variablename'
syntax. A variable name may consist of alphanumeric characters from
the current character set and also `_', `$', and `.' . The default
character set is ISO-8859-1 Latin1; this may be changed with the
`--default-character-set' option to `mysqld'. *Note Character sets::.
Variables don't have to be initialized. They contain `NULL' by default
and can store an integer, real, or string value. All variables for a
thread are automatically freed when the thread exits.
You can set a variable with the `SET' syntax:
SET @variable= { integer expression | real expression | string expression }
[,@variable= ...].
You can also set a variable in an expression with the `@variable:=expr'
syntax:
select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 |
+----------------------+------+------+------+
| 5 | 5 | 1 | 4 |
+----------------------+------+------+------+
(We had to use the `:=' syntax here, because `=' was reserved for
comparisons.)
User variables may be used where expressions are allowed. Note that
this does not currently include use in contexts where a number is
explicitly required, such as in the `LIMIT' clause of a `SELECT'
statement, or the `IGNORE number LINES' clause of a `LOAD DATA'
statement.
*NOTE:* In a `SELECT' statement, each expression is only evaluated
when it's sent to the client. This means that in the `HAVING', `GROUP
BY', or `ORDER BY' clause, you can't refer to an expression that
involves variables that are set in the `SELECT' part. For example, the
following statement will NOT work as expected:
SELECT (@aa:=id) AS a, (@aa+3) AS b FROM table_name HAVING b=5;
The reason is that `@aa' will not contain the value of the current row,
but the value of `id' for the previous accepted row.
Column Types
============
*MySQL* supports a number of column types, which may be grouped into
three categories: numeric types, date and time types, and string
(character) types. This section first gives an overview of the types
available and summarizes the storage requirements for each column type,
then provides a more detailed description of the properties of the
types in each category. The overview is intentionally brief. The more
detailed descriptions should be consulted for additional information
about particular column types, such as the allowable formats in which
you can specify values.
The column types supported by *MySQL* are listed below. The following
code letters are used in the descriptions:
`M'
Indicates the maximum display size. The maximum legal display
size is 255.
`D'
Applies to floating-point types and indicates the number of digits
following the decimal point. The maximum possible value is 30, but
should be no greater than `M'-2.
Square brackets (`[' and `]') indicate parts of type specifiers that
are optional.
Note that if you specify `ZEROFILL' for a column, *MySQL* will
automatically add the `UNSIGNED' attribute to the column.
`TINYINT[(M)] [UNSIGNED] [ZEROFILL]'
A very small integer. The signed range is `-128' to `127'. The
unsigned range is `0' to `255'.
`SMALLINT[(M)] [UNSIGNED] [ZEROFILL]'
A small integer. The signed range is `-32768' to `32767'. The
unsigned range is `0' to `65535'.
`MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]'
A medium-size integer. The signed range is `-8388608' to
`8388607'. The unsigned range is `0' to `16777215'.
`INT[(M)] [UNSIGNED] [ZEROFILL]'
A normal-size integer. The signed range is `-2147483648' to
`2147483647'. The unsigned range is `0' to `4294967295'.
`INTEGER[(M)] [UNSIGNED] [ZEROFILL]'
This is a synonym for `INT'.
`BIGINT[(M)] [UNSIGNED] [ZEROFILL]'
A large integer. The signed range is `-9223372036854775808' to
`9223372036854775807'. The unsigned range is `0' to
`18446744073709551615'.
Some things you should be aware about `BIGINT' columns:
* As all arithmetic is done using signed `BIGINT' or `DOUBLE'
values, so you shouldn't use unsigned big integers larger than
`9223372036854775807' (63 bits) except with bit functions! If
you do that, some of the last digits in the result may be
wrong because of rounding errors when converting the `BIGINT'
to a `DOUBLE'.
* You can always store an exact integer value in a `BIGINT'
column by storing it as a string, as there is in this case
there will be no intermediate double representation.
* `-', `+', and `*' will use `BIGINT' arithmetic when both
arguments are `INTEGER' values! This means that if you
multiply two big integers (or results from functions that
return integers) you may get unexpected results when the
result is larger than `9223372036854775807'.
`FLOAT(precision) [ZEROFILL]'
A floating-point number. Cannot be unsigned. `precision' can be
`<=24' for a single-precision floating-point number and between 25
and 53 for a double-precision floating-point number. These types
are like the `FLOAT' and `DOUBLE' types described immediately
below. `FLOAT(X)' has the same range as the corresponding `FLOAT'
and `DOUBLE' types, but the display size and number of decimals is
undefined.
In *MySQL* Version 3.23, this is a true floating-point value. In
earlier *MySQL* versions, `FLOAT(precision)' always has 2 decimals.
Note that using `FLOAT' may give you some unexpected problems as
all calculation in *MySQL* is done with double precision. *Note
No matching rows::.
This syntax is provided for ODBC compatibility.
`FLOAT[(M,D)] [ZEROFILL]'
A small (single-precision) floating-point number. Cannot be
unsigned. Allowable values are `-3.402823466E+38' to
`-1.175494351E-38', `0', and `1.175494351E-38' to
`3.402823466E+38'. The M is the display width and D is the number
of decimals. `FLOAT' without an argument or with an argument of
<= 24 stands for a single-precision floating-point number.
`DOUBLE[(M,D)] [ZEROFILL]'
A normal-size (double-precision) floating-point number. Cannot be
unsigned. Allowable values are `-1.7976931348623157E+308' to
`-2.2250738585072014E-308', `0', and `2.2250738585072014E-308' to
`1.7976931348623157E+308'. The M is the display width and D is
the number of decimals. `DOUBLE' without an argument or
`FLOAT(X)' where 25 <= X <= 53 stands for a double-precision
floating-point number.
`DOUBLE PRECISION[(M,D)] [ZEROFILL]'
`REAL[(M,D)] [ZEROFILL]'
These are synonyms for `DOUBLE'.
`DECIMAL[(M[,D])] [ZEROFILL]'
An unpacked floating-point number. Cannot be unsigned. Behaves
like a `CHAR' column: "unpacked" means the number is stored as a
string, using one character for each digit of the value. The
decimal point and, for negative numbers, the `-' sign, are not
counted in M (but space for these are reserved). If `D' is 0,
values will have no decimal point or fractional part. The maximum
range of `DECIMAL' values is the same as for `DOUBLE', but the
actual range for a given `DECIMAL' column may be constrained by
the choice of `M' and `D'.
If `D' is left out it's set to 0. If `M' is left out it's set to
10.
Note that in *MySQL* Version 3.22 the `M' argument had to includes
the space needed for the sign and the decimal point.
`NUMERIC(M,D) [ZEROFILL]'
This is a synonym for `DECIMAL'.
`DATE'
A date. The supported range is `'1000-01-01'' to `'9999-12-31''.
*MySQL* displays `DATE' values in `'YYYY-MM-DD'' format, but
allows you to assign values to `DATE' columns using either strings
or numbers. *Note DATETIME::.
`DATETIME'
A date and time combination. The supported range is `'1000-01-01
00:00:00'' to `'9999-12-31 23:59:59''. *MySQL* displays
`DATETIME' values in `'YYYY-MM-DD HH:MM:SS'' format, but allows you
to assign values to `DATETIME' columns using either strings or
numbers. *Note DATETIME::.
`TIMESTAMP[(M)]'
A timestamp. The range is `'1970-01-01 00:00:00'' to sometime in
the year `2037'. *MySQL* displays `TIMESTAMP' values in
`YYYYMMDDHHMMSS', `YYMMDDHHMMSS', `YYYYMMDD', or `YYMMDD' format,
depending on whether `M' is `14' (or missing), `12', `8', or `6',
but allows you to assign values to `TIMESTAMP' columns using
either strings or numbers. A `TIMESTAMP' column is useful for
recording the date and time of an `INSERT' or `UPDATE' operation
because it is automatically set to the date and time of the most
recent operation if you don't give it a value yourself. You can
also set it to the current date and time by assigning it a `NULL'
value. *Note Date and time types::.
A `TIMESTAMP' is always stored in 4 bytes. The `M' argument only
affects how the `TIMESTAMP' column is displayed.
Note that `TIMESTAMP(X)' columns where X is 8 or 14 are reported to
be numbers while other `TIMESTAMP(X)' columns are reported to be
strings. This is just to ensure that one can reliably dump and
restore the table with these types! *Note DATETIME::.
`TIME'
A time. The range is `'-838:59:59'' to `'838:59:59''. *MySQL*
displays `TIME' values in `'HH:MM:SS'' format, but allows you to
assign values to `TIME' columns using either strings or numbers.
*Note TIME::.
`YEAR[(2|4)]'
A year in 2- or 4-digit format (default is 4-digit). The
allowable values are `1901' to `2155', `0000' in the 4-digit year
format, and 1970-2069 if you use the 2-digit format (70-69).
*MySQL* displays `YEAR' values in `YYYY' format, but allows you to
assign values to `YEAR' columns using either strings or numbers.
(The `YEAR' type is new in *MySQL* Version 3.22.). *Note YEAR::.
`[NATIONAL] CHAR(M) [BINARY]'
A fixed-length string that is always right-padded with spaces to
the specified length when stored. The range of `M' is 1 to 255
characters. Trailing spaces are removed when the value is
retrieved. `CHAR' values are sorted and compared in
case-insensitive fashion according to the default character set
unless the `BINARY' keyword is given.
`NATIONAL CHAR' (short form `NCHAR') is the ANSI SQL way to define
that a CHAR column should use the default CHARACTER set. This is
the default in *MySQL*.
`CHAR' is a shorthand for `CHARACTER'.
*MySQL* allows you to create a column of type `CHAR(0)'. This is
mainly useful when you have to be compliant with some old
applications that depend on the existence of a column but that do
not actually use the value. This is also quite nice when you need
a column that only can take 2 values: A `CHAR(0)', that is not
defined as `NOT NULL', will only occupy one bit and can only take
2 values: `NULL' or `""'. *Note CHAR::.
`[NATIONAL] VARCHAR(M) [BINARY]'
A variable-length string. *NOTE:* Trailing spaces are removed when
the value is stored (this differs from the ANSI SQL
specification). The range of `M' is 1 to 255 characters. `VARCHAR'
values are sorted and compared in case-insensitive fashion unless
the `BINARY' keyword is given. *Note Silent column changes::.
`VARCHAR' is a shorthand for `CHARACTER VARYING'. *Note CHAR::.
`TINYBLOB'
`TINYTEXT'
A `BLOB' or `TEXT' column with a maximum length of 255 (2^8 - 1)
characters. *Note Silent column changes::. *Note BLOB::.
`BLOB'
`TEXT'
A `BLOB' or `TEXT' column with a maximum length of 65535 (2^16 - 1)
characters. *Note Silent column changes::. *Note BLOB::.
`MEDIUMBLOB'
`MEDIUMTEXT'
A `BLOB' or `TEXT' column with a maximum length of 16777215 (2^24
- 1) characters. *Note Silent column changes::. *Note BLOB::.
`LONGBLOB'
`LONGTEXT'
A `BLOB' or `TEXT' column with a maximum length of 4294967295
(2^32 - 1) characters. *Note Silent column changes::. Note that
because the server/client protocol and MyISAM tables has currently
a limit of 16M per communication packet / table row, you can't yet
use this the whole range of this type. *Note BLOB::.
`ENUM('value1','value2',...)'
An enumeration. A string object that can have only one value,
chosen from the list of values `'value1'', `'value2'', `...',
`NULL' or the special `""' error value. An `ENUM' can have a
maximum of 65535 distinct values. *Note ENUM::.
`SET('value1','value2',...)'
A set. A string object that can have zero or more values, each of
which must be chosen from the list of values `'value1'',
`'value2'', `...' A `SET' can have a maximum of 64 members. *Note
SET::.
Column Type Storage Requirements
--------------------------------
The storage requirements for each of the column types supported by
*MySQL* are listed below by category.
Storage requirements for numeric types
......................................
*Column type* *Storage required*
`TINYINT' 1 byte
`SMALLINT' 2 bytes
`MEDIUMINT' 3 bytes
`INT' 4 bytes
`INTEGER' 4 bytes
`BIGINT' 8 bytes
`FLOAT(X)' 4 if X <= 24 or 8 if 25 <= X <= 53
`FLOAT' 4 bytes
`DOUBLE' 8 bytes
`DOUBLE PRECISION' 8 bytes
`REAL' 8 bytes
`DECIMAL(M,D)' `M+2' bytes if D > 0, `M+1' bytes
if D = 0 (`D'+2, if `M < D')
`NUMERIC(M,D)' `M+2' bytes if D > 0, `M+1' bytes
if D = 0 (`D'+2, if `M < D')
Storage requirements for date and time types
............................................
*Column type* *Storage required*
`DATE' 3 bytes
`DATETIME' 8 bytes
`TIMESTAMP' 4 bytes
`TIME' 3 bytes
`YEAR' 1 byte
Storage requirements for string types
.....................................
*Column type* *Storage required*
`CHAR(M)' `M' bytes, `1 <= M <= 255'
`VARCHAR(M)' `L'+1 bytes, where `L <= M' and `1
<= M <= 255'
`TINYBLOB', `TINYTEXT' `L'+1 bytes, where `L' < 2^8
`BLOB', `TEXT' `L'+2 bytes, where `L' < 2^16
`MEDIUMBLOB', `MEDIUMTEXT' `L'+3 bytes, where `L' < 2^24
`LONGBLOB', `LONGTEXT' `L'+4 bytes, where `L' < 2^32
`ENUM('value1','value2',...)' 1 or 2 bytes, depending on the
number of enumeration values (65535
values maximum)
`SET('value1','value2',...)' 1, 2, 3, 4 or 8 bytes, depending on
the number of set members (64
members maximum)
`VARCHAR' and the `BLOB' and `TEXT' types are variable-length types,
for which the storage requirements depend on the actual length of
column values (represented by `L' in the preceding table), rather than
on the type's maximum possible size. For example, a `VARCHAR(10)'
column can hold a string with a maximum length of 10 characters. The
actual storage required is the length of the string (`L'), plus 1 byte
to record the length of the string. For the string `'abcd'', `L' is 4
and the storage requirement is 5 bytes.
The `BLOB' and `TEXT' types require 1, 2, 3, or 4 bytes to record the
length of the column value, depending on the maximum possible length of
the type. *Note BLOB::.
If a table includes any variable-length column types, the record format
will also be variable-length. Note that when a table is created,
*MySQL* may, under certain conditions, change a column from a
variable-length type to a fixed-length type, or vice-versa. *Note
Silent column changes::.
The size of an `ENUM' object is determined by the number of different
enumeration values. One byte is used for enumerations with up to 255
possible values. Two bytes are used for enumerations with up to 65535
values. *Note ENUM::.
The size of a `SET' object is determined by the number of different set
members. If the set size is `N', the object occupies `(N+7)/8' bytes,
rounded up to 1, 2, 3, 4, or 8 bytes. A `SET' can have a maximum of 64
members. *Note SET::.
Numeric Types
-------------
*MySQL* supports all of the ANSI/ISO SQL92 numeric types. These types
include the exact numeric data types (`NUMERIC', `DECIMAL', `INTEGER',
and `SMALLINT'), as well as the approximate numeric data types
(`FLOAT', `REAL', and `DOUBLE PRECISION'). The keyword `INT' is a
synonym for `INTEGER', and the keyword `DEC' is a synonym for `DECIMAL'.
The `NUMERIC' and `DECIMAL' types are implemented as the same type by
*MySQL*, as permitted by the SQL92 standard. They are used for values
for which it is important to preserve exact precision, for example with
monetary data. When declaring a column of one of these types the
precision and scale can be (and usually is) specified; for example:
salary DECIMAL(9,2)
In this example, `9' (`precision') represents the number of significant
decimal digits that will be stored for values, and `2' (`scale')
represents the number of digits that will be stored following the
decimal point. In this case, therefore, the range of values that can
be stored in the `salary' column is from `-9999999.99' to `9999999.99'.
In ANSI/ISO SQL92, the syntax `DECIMAL(p)' is equivalent to
`DECIMAL(p,0)'. Similarly, the syntax `DECIMAL' is equivalent to
`DECIMAL(p,0)', where the implementation is allowed to decide the value
of `p'. *MySQL* does not currently support either of these variant
forms of the `DECIMAL'/`NUMERIC' data types. This is not generally a
serious problem, as the principal benefits of these types derive from
the ability to control both precision and scale explicitly.
`DECIMAL' and `NUMERIC' values are stored as strings, rather than as
binary floating-point numbers, in order to preserve the decimal
precision of those values. One character is used for each digit of the
value, the decimal point (if `scale' > 0), and the `-' sign (for
negative numbers). If `scale' is 0, `DECIMAL' and `NUMERIC' values
contain no decimal point or fractional part.
The maximum range of `DECIMAL' and `NUMERIC' values is the same as for
`DOUBLE', but the actual range for a given `DECIMAL' or `NUMERIC'
column can be constrained by the `precision' or `scale' for a given
column. When such a column is assigned a value with more digits
following the decimal point than are allowed by the specified `scale',
the value is rounded to that `scale'. When a `DECIMAL' or `NUMERIC'
column is assigned a value whose magnitude exceeds the range implied by
the specified (or defaulted) `precision' and `scale', *MySQL* stores
the value representing the corresponding end point of that range.
As an extension to the ANSI/ISO SQL92 standard, *MySQL* also supports
the integral types `TINYINT', `MEDIUMINT', and `BIGINT' as listed in
the tables above. Another extension is supported by *MySQL* for
optionally specifying the display width of an integral value in
parentheses following the base keyword for the type (for example,
`INT(4)'). This optional width specification is used to left-pad the
display of values whose width is less than the width specified for the
column, but does not constrain the range of values that can be stored
in the column, nor the number of digits that will be displayed for
values whose width exceeds that specified for the column. When used in
conjunction with the optional extension attribute `ZEROFILL', the
default padding of spaces is replaced with zeroes. For example, for a
column declared as `INT(5) ZEROFILL', a value of `4' is retrieved as
`00004'. Note that if you store larger values than the display width
in an integer column, you may experience problems when *MySQL*
generates temporary tables for some complicated joins, as in these
cases *MySQL* trusts that the data did fit into the original column
width.
All integral types can have an optional (non-standard) attribute
`UNSIGNED'. Unsigned values can be used when you want to allow only
positive numbers in a column and you need a little bigger numeric range
for the column.
The `FLOAT' type is used to represent approximate numeric data types.
The ANSI/ISO SQL92 standard allows an optional specification of the
precision (but not the range of the exponent) in bits following the
keyword `FLOAT' in parentheses. The *MySQL* implementation also
supports this optional precision specification. When the keyword
`FLOAT' is used for a column type without a precision specification,
*MySQL* uses four bytes to store the values. A variant syntax is also
supported, with two numbers given in parentheses following the `FLOAT'
keyword. With this option, the first number continues to represent the
storage requirements for the value in bytes, and the second number
specifies the number of digits to be stored and displayed following the
decimal point (as with `DECIMAL' and `NUMERIC'). When *MySQL* is asked
to store a number for such a column with more decimal digits following
the decimal point than specified for the column, the value is rounded
to eliminate the extra digits when the value is stored.
The `REAL' and `DOUBLE PRECISION' types do not accept precision
specifications. As an extension to the ANSI/ISO SQL92 standard,
*MySQL* recognizes `DOUBLE' as a synonym for the `DOUBLE PRECISION'
type. In contrast with the standard's requirement that the precision
for `REAL' be smaller than that used for `DOUBLE PRECISION', *MySQL*
implements both as 8-byte double-precision floating-point values (when
not running in "ANSI mode"). For maximum portability, code requiring
storage of approximate numeric data values should use `FLOAT' or
`DOUBLE PRECISION' with no specification of precision or number of
decimal points.
When asked to store a value in a numeric column that is outside the
column type's allowable range, *MySQL* clips the value to the
appropriate endpoint of the range and stores the resulting value
instead.
For example, the range of an `INT' column is `-2147483648' to
`2147483647'. If you try to insert `-9999999999' into an `INT' column,
the value is clipped to the lower endpoint of the range, and
`-2147483648' is stored instead. Similarly, if you try to insert
`9999999999', `2147483647' is stored instead.
If the `INT' column is `UNSIGNED', the size of the column's range is
the same but its endpoints shift up to `0' and `4294967295'. If you
try to store `-9999999999' and `9999999999', the values stored in the
column become `0' and `4294967296'.
Conversions that occur due to clipping are reported as "warnings" for
`ALTER TABLE', `LOAD DATA INFILE', `UPDATE', and multi-row `INSERT'
statements.
Date and Time Types
-------------------
The date and time types are `DATETIME', `DATE', `TIMESTAMP', `TIME',
and `YEAR'. Each of these has a range of legal values, as well as a
"zero" value that is used when you specify a really illegal value.
Note that *MySQL* allows you to store certain 'not strictly' legal date
values, for example `1999-11-31'. The reason for this is that we think
it's the responsibility of the application to handle date checking, not
the SQL servers. To make the date checking 'fast', *MySQL* only checks
that the month is in the range of 0-12 and the day is in the range of
0-31. The above ranges are defined this way because *MySQL* allows you
to store, in a `DATE' or `DATETIME' column, dates where the day or
month-day is zero. This is extremely useful for applications that need
to store a birth-date for which you don't know the exact date. In this
case you simply store the date like `1999-00-00' or `1999-01-00'. (You
cannot expect to get a correct value from functions like `DATE_SUB()'
or `DATE_ADD' for dates like these.)
Here are some general considerations to keep in mind when working with
date and time types:
* *MySQL* retrieves values for a given date or time type in a
standard format, but it attempts to interpret a variety of formats
for values that you supply (for example, when you specify a value
to be assigned to or compared to a date or time type).
Nevertheless, only the formats described in the following sections
are supported. It is expected that you will supply legal values,
and unpredictable results may occur if you use values in other
formats.
* Although *MySQL* tries to interpret values in several formats, it
always expects the year part of date values to be leftmost. Dates
must be given in year-month-day order (for example, `'98-09-04''),
rather than in the month-day-year or day-month-year orders
commonly used elsewhere (for example, `'09-04-98'', `'04-09-98'').
* *MySQL* automatically converts a date or time type value to a
number if the value is used in a numeric context, and vice versa.
* When *MySQL* encounters a value for a date or time type that is
out of range or otherwise illegal for the type (see the start of
this section), it converts the value to the "zero" value for that
type. (The exception is that out-of-range `TIME' values are
clipped to the appropriate endpoint of the `TIME' range.) The
table below shows the format of the "zero" value for each type:
*Column type* *"Zero" value*
`DATETIME' `'0000-00-00 00:00:00''
`DATE' `'0000-00-00''
`TIMESTAMP' `00000000000000' (length depends on display
size)
`TIME' `'00:00:00''
`YEAR' `0000'
* The "zero" values are special, but you can store or refer to them
explicitly using the values shown in the table. You can also do
this using the values `'0'' or `0', which are easier to write.
* "Zero" date or time values used through *MyODBC* are converted
automatically to `NULL' in *MyODBC* Version 2.50.12 and above,
because ODBC can't handle such values.
Y2K Issues and Date Types
.........................
*MySQL* itself is Y2K-safe (*note Year 2000 compliance::), but input
values presented to *MySQL* may not be. Any input containing 2-digit
year values is ambiguous, because the century is unknown. Such values
must be interpreted into 4-digit form because *MySQL* stores years
internally using four digits.
For `DATETIME', `DATE', `TIMESTAMP', and `YEAR' types, *MySQL*
interprets dates with ambiguous year values using the following rules:
* Year values in the range `00-69' are converted to `2000-2069'.
* Year values in the range `70-99' are converted to `1970-1999'.
Remember that these rules provide only reasonable guesses as to what
your data mean. If the heuristics used by *MySQL* don't produce the
correct values, you should provide unambiguous input containing 4-digit
year values.
`ORDER BY' will sort 2-digit `YEAR/DATE/DATETIME' types properly.
Note also that some functions like `MIN()' and `MAX()' will convert a
`TIMESTAMP/DATE' to a number. This means that a timestamp with a
2-digit year will not work properly with these functions. The fix in
this case is to convert the `TIMESTAMP/DATE' to 4-digit year format or
use something like `MIN(DATE_ADD(timestamp,INTERVAL 0 DAYS))'.
The `DATETIME', `DATE', and `TIMESTAMP' Types
.............................................
The `DATETIME', `DATE', and `TIMESTAMP' types are related. This
section describes their characteristics, how they are similar, and how
they differ.
The `DATETIME' type is used when you need values that contain both date
and time information. *MySQL* retrieves and displays `DATETIME' values
in `'YYYY-MM-DD HH:MM:SS'' format. The supported range is `'1000-01-01
00:00:00'' to `'9999-12-31 23:59:59''. ("Supported" means that
although earlier values might work, there is no guarantee that they
will.)
The `DATE' type is used when you need only a date value, without a time
part. *MySQL* retrieves and displays `DATE' values in `'YYYY-MM-DD''
format. The supported range is `'1000-01-01'' to `'9999-12-31''.
The `TIMESTAMP' column type provides a type that you can use to
automatically mark `INSERT' or `UPDATE' operations with the current
date and time. If you have multiple `TIMESTAMP' columns, only the first
one is updated automatically.
Automatic updating of the first `TIMESTAMP' column occurs under any of
the following conditions:
* The column is not specified explicitly in an `INSERT' or `LOAD
DATA INFILE' statement.
* The column is not specified explicitly in an `UPDATE' statement
and some other column changes value. (Note that an `UPDATE' that
sets a column to the value it already has will not cause the
`TIMESTAMP' column to be updated, because if you set a column to
its current value, *MySQL* ignores the update for efficiency.)
* You explicitly set the `TIMESTAMP' column to `NULL'.
`TIMESTAMP' columns other than the first may also be set to the current
date and time. Just set the column to `NULL' or to `NOW()'.
You can set any `TIMESTAMP' column to a value different than the current
date and time by setting it explicitly to the desired value. This is
true even for the first `TIMESTAMP' column. You can use this property
if, for example, you want a `TIMESTAMP' to be set to the current date
and time when you create a row, but not to be changed whenever the row
is updated later:
* Let *MySQL* set the column when the row is created. This will
initialize it to the current date and time.
* When you perform subsequent updates to other columns in the row,
set the `TIMESTAMP' column explicitly to its current value.
On the other hand, you may find it just as easy to use a `DATETIME'
column that you initialize to `NOW()' when the row is created and leave
alone for subsequent updates.
`TIMESTAMP' values may range from the beginning of 1970 to sometime in
the year 2037, with a resolution of one second. Values are displayed as
numbers.
The format in which *MySQL* retrieves and displays `TIMESTAMP' values
depends on the display size, as illustrated by the table below. The
`full' `TIMESTAMP' format is 14 digits, but `TIMESTAMP' columns may be
created with shorter display sizes:
*Column type* *Display format*
`TIMESTAMP(14)' `YYYYMMDDHHMMSS'
`TIMESTAMP(12)' `YYMMDDHHMMSS'
`TIMESTAMP(10)' `YYMMDDHHMM'
`TIMESTAMP(8)' `YYYYMMDD'
`TIMESTAMP(6)' `YYMMDD'
`TIMESTAMP(4)' `YYMM'
`TIMESTAMP(2)' `YY'
All `TIMESTAMP' columns have the same storage size, regardless of
display size. The most common display sizes are 6, 8, 12, and 14. You
can specify an arbitrary display size at table creation time, but
values of 0 or greater than 14 are coerced to 14. Odd-valued sizes in
the range from 1 to 13 are coerced to the next higher even number.
You can specify `DATETIME', `DATE', and `TIMESTAMP' values using any of
a common set of formats:
* As a string in either `'YYYY-MM-DD HH:MM:SS'' or `'YY-MM-DD
HH:MM:SS'' format. A "relaxed" syntax is allowed--any punctuation
character may be used as the delimiter between date parts or time
parts. For example, `'98-12-31 11:30:45'', `'98.12.31 11+30+45'',
`'98/12/31 11*30*45'', and `'98@12@31 11^30^45'' are equivalent.
* As a string in either `'YYYY-MM-DD'' or `'YY-MM-DD'' format. A
"relaxed" syntax is allowed here, too. For example, `'98-12-31'',
`'98.12.31'', `'98/12/31'', and `'98@12@31'' are equivalent.
* As a string with no delimiters in either `'YYYYMMDDHHMMSS'' or
`'YYMMDDHHMMSS'' format, provided that the string makes sense as a
date. For example, `'19970523091528'' and `'970523091528'' are
interpreted as `'1997-05-23 09:15:28'', but `'971122129015'' is
illegal (it has a nonsensical minute part) and becomes `'0000-00-00
00:00:00''.
* As a string with no delimiters in either `'YYYYMMDD'' or `'YYMMDD''
format, provided that the string makes sense as a date. For
example, `'19970523'' and `'970523'' are interpreted as
`'1997-05-23'', but `'971332'' is illegal (it has nonsensical month
and day parts) and becomes `'0000-00-00''.
* As a number in either `YYYYMMDDHHMMSS' or `YYMMDDHHMMSS' format,
provided that the number makes sense as a date. For example,
`19830905132800' and `830905132800' are interpreted as
`'1983-09-05 13:28:00''.
* As a number in either `YYYYMMDD' or `YYMMDD' format, provided that
the number makes sense as a date. For example, `19830905' and
`830905' are interpreted as `'1983-09-05''.
* As the result of a function that returns a value that is acceptable
in a `DATETIME', `DATE', or `TIMESTAMP' context, such as `NOW()'
or `CURRENT_DATE'.
Illegal `DATETIME', `DATE', or `TIMESTAMP' values are converted to the
"zero" value of the appropriate type (`'0000-00-00 00:00:00'',
`'0000-00-00'', or `00000000000000').
For values specified as strings that include date part delimiters, it
is not necessary to specify two digits for month or day values that are
less than `10'. `'1979-6-9'' is the same as `'1979-06-09''. Similarly,
for values specified as strings that include time part delimiters, it
is not necessary to specify two digits for hour, month, or second
values that are less than `10'. `'1979-10-30 1:2:3'' is the same as
`'1979-10-30 01:02:03''.
Values specified as numbers should be 6, 8, 12, or 14 digits long. If
the number is 8 or 14 digits long, it is assumed to be in `YYYYMMDD' or
`YYYYMMDDHHMMSS' format and that the year is given by the first 4
digits. If the number is 6 or 12 digits long, it is assumed to be in
`YYMMDD' or `YYMMDDHHMMSS' format and that the year is given by the
first 2 digits. Numbers that are not one of these lengths are
interpreted as though padded with leading zeros to the closest length.
Values specified as non-delimited strings are interpreted using their
length as given. If the string is 8 or 14 characters long, the year is
assumed to be given by the first 4 characters. Otherwise the year is
assumed to be given by the first 2 characters. The string is
interpreted from left to right to find year, month, day, hour, minute,
and second values, for as many parts as are present in the string.
This means you should not use strings that have fewer than 6
characters. For example, if you specify `'9903'', thinking that will
represent March, 1999, you will find that *MySQL* inserts a "zero" date
into your table. This is because the year and month values are `99'
and `03', but the day part is missing (zero), so the value is not a
legal date.
`TIMESTAMP' columns store legal values using the full precision with
which the value was specified, regardless of the display size. This has
several implications:
* Always specify year, month, and day, even if your column types are
`TIMESTAMP(4)' or `TIMESTAMP(2)'. Otherwise, the value will not
be a legal date and `0' will be stored.
* If you use `ALTER TABLE' to widen a narrow `TIMESTAMP' column,
information will be displayed that previously was "hidden".
* Similarly, narrowing a `TIMESTAMP' column does not cause
information to be lost, except in the sense that less information
is shown when the values are displayed.
* Although `TIMESTAMP' values are stored to full precision, the only
function that operates directly on the underlying stored value is
`UNIX_TIMESTAMP()'. Other functions operate on the formatted
retrieved value. This means you cannot use functions such as
`HOUR()' or `SECOND()' unless the relevant part of the `TIMESTAMP'
value is included in the formatted value. For example, the `HH'
part of a `TIMESTAMP' column is not displayed unless the display
size is at least 10, so trying to use `HOUR()' on shorter
`TIMESTAMP' values produces a meaningless result.
You can to some extent assign values of one date type to an object of a
different date type. However, there may be some alteration of the
value or loss of information:
* If you assign a `DATE' value to a `DATETIME' or `TIMESTAMP'
object, the time part of the resulting value is set to
`'00:00:00'', because the `DATE' value contains no time
information.
* If you assign a `DATETIME' or `TIMESTAMP' value to a `DATE'
object, the time part of the resulting value is deleted, because
the `DATE' type stores no time information.
* Remember that although `DATETIME', `DATE', and `TIMESTAMP' values
all can be specified using the same set of formats, the types do
not all have the same range of values. For example, `TIMESTAMP'
values cannot be earlier than `1970' or later than `2037'. This
means that a date such as `'1968-01-01'', while legal as a
`DATETIME' or `DATE' value, is not a valid `TIMESTAMP' value and
will be converted to `0' if assigned to such an object.
Be aware of certain pitfalls when specifying date values:
* The relaxed format allowed for values specified as strings can be
deceiving. For example, a value such as `'10:11:12'' might look
like a time value because of the `:' delimiter, but if used in a
date context will be interpreted as the year `'2010-11-12''. The
value `'10:45:15'' will be converted to `'0000-00-00'' because
`'45'' is not a legal month.
* Year values specified as two digits are ambiguous, because the
century is unknown. *MySQL* interprets 2-digit year values using
the following rules:
- Year values in the range `00-69' are converted to `2000-2069'.
- Year year values in the range `70-99' are converted to
`1970-1999'.
The `TIME' Type
...............
*MySQL* retrieves and displays `TIME' values in `'HH:MM:SS'' format (or
`'HHH:MM:SS'' format for large hours values). `TIME' values may range
from `'-838:59:59'' to `'838:59:59''. The reason the hours part may be
so large is that the `TIME' type may be used not only to represent a
time of day (which must be less than 24 hours), but also elapsed time
or a time interval between two events (which may be much greater than
24 hours, or even negative).
You can specify `TIME' values in a variety of formats:
* As a string in `'D HH:MM:SS.fraction'' format. (Note that *MySQL*
doesn't yet store the fraction for the time column). One can also
use one of the following "relaxed" syntax:
`HH:MM:SS.fraction', `HH:MM:SS', `HH:MM', `D HH:MM:SS', `D HH:MM',
`D HH' or `SS'. Here `D' is days between 0-33.
* As a string with no delimiters in `'HHMMSS'' format, provided that
it makes sense as a time. For example, `'101112'' is understood as
`'10:11:12'', but `'109712'' is illegal (it has a nonsensical
minute part) and becomes `'00:00:00''.
* As a number in `HHMMSS' format, provided that it makes sense as a
time. For example, `101112' is understood as `'10:11:12''. The
following alternative formats are also understood: `SS',
`MMSS',`HHMMSS', `HHMMSS.fraction'. Note that *MySQL* doesn't yet
store the fraction part.
* As the result of a function that returns a value that is acceptable
in a `TIME' context, such as `CURRENT_TIME'.
For `TIME' values specified as strings that include a time part
delimiter, it is not necessary to specify two digits for hours,
minutes, or seconds values that are less than `10'. `'8:3:2'' is the
same as `'08:03:02''.
Be careful about assigning "short" `TIME' values to a `TIME' column.
Without semicolon, *MySQL* interprets values using the assumption that
the rightmost digits represent seconds. (*MySQL* interprets `TIME'
values as elapsed time rather than as time of day.) For example, you
might think of `'1112'' and `1112' as meaning `'11:12:00'' (12 minutes
after 11 o'clock), but *MySQL* interprets them as `'00:11:12'' (11
minutes, 12 seconds). Similarly, `'12'' and `12' are interpreted as
`'00:00:12''. `TIME' values with semicolon, instead, are always
treated as time of the day. That is `'11:12'' will mean `'11:12:00'',
not `'00:11:12''.
Values that lie outside the `TIME' range but are otherwise legal are
clipped to the appropriate endpoint of the range. For example,
`'-850:00:00'' and `'850:00:00'' are converted to `'-838:59:59'' and
`'838:59:59''.
Illegal `TIME' values are converted to `'00:00:00''. Note that because
`'00:00:00'' is itself a legal `TIME' value, there is no way to tell,
from a value of `'00:00:00'' stored in a table, whether the original
value was specified as `'00:00:00'' or whether it was illegal.
The `YEAR' Type
...............
The `YEAR' type is a 1-byte type used for representing years.
*MySQL* retrieves and displays `YEAR' values in `YYYY' format. The
range is `1901' to `2155'.
You can specify `YEAR' values in a variety of formats:
* As a four-digit string in the range `'1901'' to `'2155''.
* As a four-digit number in the range `1901' to `2155'.
* As a two-digit string in the range `'00'' to `'99''. Values in the
ranges `'00'' to `'69'' and `'70'' to `'99'' are converted to
`YEAR' values in the ranges `2000' to `2069' and `1970' to `1999'.
* As a two-digit number in the range `1' to `99'. Values in the
ranges `1' to `69' and `70' to `99' are converted to `YEAR' values
in the ranges `2001' to `2069' and `1970' to `1999'. Note that
the range for two-digit numbers is slightly different than the
range for two-digit strings, because you cannot specify zero
directly as a number and have it be interpreted as `2000'. You
_must_ specify it as a string `'0'' or `'00'' or it will be
interpreted as `0000'.
* As the result of a function that returns a value that is acceptable
in a `YEAR' context, such as `NOW()'.
Illegal `YEAR' values are converted to `0000'.
String Types
------------
The string types are `CHAR', `VARCHAR', `BLOB', `TEXT', `ENUM', and
`SET'.
The `CHAR' and `VARCHAR' Types
..............................
The `CHAR' and `VARCHAR' types are similar, but differ in the way they
are stored and retrieved.
The length of a `CHAR' column is fixed to the length that you declare
when you create the table. The length can be any value between 1 and
255. (As of *MySQL* Version 3.23, the length of `CHAR' may be 0 to
255.) When `CHAR' values are stored, they are right-padded with spaces
to the specified length. When `CHAR' values are retrieved, trailing
spaces are removed.
Values in `VARCHAR' columns are variable-length strings. You can
declare a `VARCHAR' column to be any length between 1 and 255, just as
for `CHAR' columns. However, in contrast to `CHAR', `VARCHAR' values
are stored using only as many characters as are needed, plus one byte
to record the length. Values are not padded; instead, trailing spaces
are removed when values are stored. (This space removal differs from
the ANSI SQL specification.)
If you assign a value to a `CHAR' or `VARCHAR' column that exceeds the
column's maximum length, the value is truncated to fit.
The table below illustrates the differences between the two types of
columns by showing the result of storing various string values into
`CHAR(4)' and `VARCHAR(4)' columns:
*Value* `CHAR(4)' *Storage `VARCHAR(4)' *Storage required*
required*
`''' `' '' 4 bytes `''' 1 byte
`'ab'' `'ab '' 4 bytes `'ab'' 3 bytes
`'abcd'' `'abcd'' 4 bytes `'abcd'' 5 bytes
`'abcdefgh'' `'abcd'' 4 bytes `'abcd'' 5 bytes
The values retrieved from the `CHAR(4)' and `VARCHAR(4)' columns will
be the same in each case, because trailing spaces are removed from
`CHAR' columns upon retrieval.
Values in `CHAR' and `VARCHAR' columns are sorted and compared in
case-insensitive fashion, unless the `BINARY' attribute was specified
when the table was created. The `BINARY' attribute means that column
values are sorted and compared in case-sensitive fashion according to
the ASCII order of the machine where the *MySQL* server is running.
`BINARY' doesn't affect how the column is stored or retrieved.
The `BINARY' attribute is sticky. This means that if a column marked
`BINARY' is used in an expression, the whole expression is compared as a
`BINARY' value.
*MySQL* may silently change the type of a `CHAR' or `VARCHAR' column at
table creation time. *Note Silent column changes::.
The `BLOB' and `TEXT' Types
...........................
A `BLOB' is a binary large object that can hold a variable amount of
data. The four `BLOB' types `TINYBLOB', `BLOB', `MEDIUMBLOB', and
`LONGBLOB' differ only in the maximum length of the values they can
hold. *Note Storage requirements::.
The four `TEXT' types `TINYTEXT', `TEXT', `MEDIUMTEXT', and `LONGTEXT'
correspond to the four `BLOB' types and have the same maximum lengths
and storage requirements. The only difference between `BLOB' and
`TEXT' types is that sorting and comparison is performed in
case-sensitive fashion for `BLOB' values and case-insensitive fashion
for `TEXT' values. In other words, a `TEXT' is a case-insensitive
`BLOB'.
If you assign a value to a `BLOB' or `TEXT' column that exceeds the
column type's maximum length, the value is truncated to fit.
In most respects, you can regard a `TEXT' column as a `VARCHAR' column
that can be as big as you like. Similarly, you can regard a `BLOB'
column as a `VARCHAR BINARY' column. The differences are:
* You can have indexes on `BLOB' and `TEXT' columns with *MySQL*
Version 3.23.2 and newer. Older versions of *MySQL* did not
support this.
* There is no trailing-space removal for `BLOB' and `TEXT' columns
when values are stored, as there is for `VARCHAR' columns.
* `BLOB' and `TEXT' columns cannot have `DEFAULT' values.
*MyODBC* defines `BLOB' values as `LONGVARBINARY' and `TEXT' values as
`LONGVARCHAR'.
Because `BLOB' and `TEXT' values may be extremely long, you may run up
against some constraints when using them:
* If you want to use `GROUP BY' or `ORDER BY' on a `BLOB' or `TEXT'
column, you must convert the column value into a fixed-length
object. The standard way to do this is with the `SUBSTRING'
function. For example:
mysql> select comment from tbl_name,substring(comment,20) as substr
ORDER BY substr;
If you don't do this, only the first `max_sort_length' bytes of the
column are used when sorting. The default value of
`max_sort_length' is 1024; this value can be changed using the
`-O' option when starting the `mysqld' server. You can group on an
expression involving `BLOB' or `TEXT' values by specifying the
column position or by using an alias:
mysql> select id,substring(blob_col,1,100) from tbl_name
GROUP BY 2;
mysql> select id,substring(blob_col,1,100) as b from tbl_name
GROUP BY b;
* The maximum size of a `BLOB' or `TEXT' object is determined by its
type, but the largest value you can actually transmit between the
client and server is determined by the amount of available memory
and the size of the communications buffers. You can change the
message buffer size, but you must do so on both the server and
client ends. *Note Server parameters::.
Note that each `BLOB' or `TEXT' value is represented internally by a
separately allocated object. This is in contrast to all other column
types, for which storage is allocated once per column when the table is
opened.
The `ENUM' Type
...............
An `ENUM' is a string object whose value normally is chosen from a list
of allowed values that are enumerated explicitly in the column
specification at table creation time.
The value may also be the empty string (`""') or `NULL' under certain
circumstances:
* If you insert an invalid value into an `ENUM' (that is, a string
not present in the list of allowed values), the empty string is
inserted instead as a special error value.
* If an `ENUM' is declared `NULL', `NULL' is also a legal value for
the column, and the default value is `NULL'. If an `ENUM' is
declared `NOT NULL', the default value is the first element of the
list of allowed values.
Each enumeration value has an index:
* Values from the list of allowable elements in the column
specification are numbered beginning with 1.
* The index value of the empty string error value is 0. This means
that you can use the following `SELECT' statement to find rows
into which invalid `ENUM' values were assigned:
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
* The index of the `NULL' value is `NULL'.
For example, a column specified as `ENUM("one", "two", "three")' can
have any of the values shown below. The index of each value is also
shown:
*Value* *Index*
`NULL' `NULL'
`""' 0
`"one"' 1
`"two"' 2
`"three"' 3
An enumeration can have a maximum of 65535 elements.
Lettercase is irrelevant when you assign values to an `ENUM' column.
However, values retrieved from the column later have lettercase
matching the values that were used to specify the allowable values at
table creation time.
If you retrieve an `ENUM' in a numeric context, the column value's
index is returned. For example, you can retrieve numeric values from
an `ENUM' column like this:
mysql> SELECT enum_col+0 FROM tbl_name;
If you store a number into an `ENUM', the number is treated as an
index, and the value stored is the enumeration member with that index.
(However, this will not work with `LOAD DATA', which treats all input
as strings.)
`ENUM' values are sorted according to the order in which the enumeration
members were listed in the column specification. (In other words,
`ENUM' values are sorted according to their index numbers.) For
example, `"a"' sorts before `"b"' for `ENUM("a", "b")', but `"b"' sorts
before `"a"' for `ENUM("b", "a")'. The empty string sorts before
non-empty strings, and `NULL' values sort before all other enumeration
values.
If you want to get all possible values for an `ENUM' column, you should
use: `SHOW COLUMNS FROM table_name LIKE enum_column_name' and parse the
`ENUM' definition in the second column.
The `SET' Type
..............
A `SET' is a string object that can have zero or more values, each of
which must be chosen from a list of allowed values specified when the
table is created. `SET' column values that consist of multiple set
members are specified with members separated by commas (`,'). A
consequence of this is that `SET' member values cannot themselves
contain commas.
For example, a column specified as `SET("one", "two") NOT NULL' can have
any of these values:
""
"one"
"two"
"one,two"
A `SET' can have a maximum of 64 different members.
*MySQL* stores `SET' values numerically, with the low-order bit of the
stored value corresponding to the first set member. If you retrieve a
`SET' value in a numeric context, the value retrieved has bits set
corresponding to the set members that make up the column value. For
example, you can retrieve numeric values from a `SET' column like this:
mysql> SELECT set_col+0 FROM tbl_name;
If a number is stored into a `SET' column, the bits that are set in the
binary representation of the number determine the set members in the
column value. Suppose a column is specified as `SET("a","b","c","d")'.
Then the members have the following bit values:
`SET' *Decimal *Binary value*
*member* value*
`a' `1' `0001'
`b' `2' `0010'
`c' `4' `0100'
`d' `8' `1000'
If you assign a value of `9' to this column, that is `1001' in binary,
so the first and fourth `SET' value members `"a"' and `"d"' are
selected and the resulting value is `"a,d"'.
For a value containing more than one `SET' element, it does not matter
what order the elements are listed in when you insert the value. It
also does not matter how many times a given element is listed in the
value. When the value is retrieved later, each element in the value
will appear once, with elements listed according to the order in which
they were specified at table creation time. For example, if a column
is specified as `SET("a","b","c","d")', then `"a,d"', `"d,a"', and
`"d,a,a,d,d"' will all appear as `"a,d"' when retrieved.
`SET' values are sorted numerically. `NULL' values sort before
non-`NULL' `SET' values.
Normally, you perform a `SELECT' on a `SET' column using the `LIKE'
operator or the `FIND_IN_SET()' function:
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
But the following will also work:
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
mysql> SELECT * FROM tbl_name WHERE set_col & 1;