MySQL数据库

开发平台：

Visual C++

manual.texi：源码内容

Note that if you update the password in the @code{user} table directly using
the first method, you must tell the server to re-read the grant tables (with
@code{FLUSH PRIVILEGES}), because the change will go unnoticed otherwise.
Once the @code{root} password has been set, thereafter you must supply that
password when you connect to the server as @code{root}.
You may wish to leave the @code{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 @code{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 @code{mysql_install_db} before you run it.
@cindex grant tables, re-creating
@cindex re-creating, grant tables
To re-create the grant tables completely, remove all the @file{.frm},
@file{.MYI}, and @file{.MYD} files in the directory containing the
@code{mysql} database. (This is the directory named @file{mysql} under
the database directory, which is listed when you run @code{mysqld
--help}.) Then run the @code{mysql_install_db} script, possibly after
editing it first to have the privileges you want.
@strong{NOTE:} For @strong{MySQL} versions older than Version 3.22.10,
you should NOT delete the @file{.frm} files. If you accidentally do this,
you should copy them back from your @strong{MySQL} distribution before
running @code{mysql_install_db}.
@cindex privileges, adding
@cindex adding, new user privileges
@cindex user privileges, adding
@findex GRANT statement
@findex statements, GRANT
@node Adding users, Passwords, Default privileges, Privilege system
@section Adding New User Privileges to MySQL
You can add users two different ways: by using @code{GRANT} statements
or by manipulating the @strong{MySQL} grant tables directly. The
preferred method is to use @code{GRANT} statements, because they are
more concise and less error-prone.
The examples below show how to use the @code{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 @code{mysqld} is running, you must
connect as the @strong{MySQL} @code{root} user, and the @code{root} user must
have the @strong{insert} privilege for the @code{mysql} database and the
@strong{reload} administrative privilege. Also, if you have changed the
@code{root} user password, you must specify it for the @code{mysql} commands
below.
You can add new users by issuing @code{GRANT} statements:
@example
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;
@end example
These @code{GRANT} statements set up three new users:
@table @code
@item monty
A full superuser who can connect to the server from anywhere, but who must
use a password @code{'some_pass'} to do so. Note that we must issue
@code{GRANT} statements for both @code{monty@@localhost} and
@code{monty@@"%"}. If we don't add the entry with @code{localhost}, the
anonymous user entry for @code{localhost} that is created by
@code{mysql_install_db} will take precedence when we connect from the local
host, because it has a more specific @code{Host} field value and thus comes
earlier in the @code{user} table sort order.
@item admin
A user who can connect from @code{localhost} without a password and who is
granted the @strong{reload} and @strong{process} administrative privileges.
This allows the user to execute the @code{mysqladmin reload},
@code{mysqladmin refresh}, and @code{mysqladmin flush-*} commands, as well as
@code{mysqladmin processlist} . No database-related privileges are granted.
(They can be granted later by issuing additional @code{GRANT} statements.)
@item dummy
A user who can connect without a password, but only from the local host. The
global privileges are all set to @code{'N'} --- the @code{USAGE} privilege
type allows you to create a user with no privileges. It is assumed that you
will grant database-specific privileges later.
@end table
@findex INSERT statement, grant privileges
@findex statements, INSERT
You can also add the same user access information directly by issuing
@code{INSERT} statements and then telling the server to reload the grant
tables:
@example
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;
@end example
Depending on your @strong{MySQL} version, you may have to use a different
number of @code{'Y'} values above (versions prior to Version 3.22.11 had fewer
privilege columns). For the @code{admin} user, the more readable extended
@code{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 @code{user} table
entry with the privilege fields set to @code{'Y'}. No @code{db} or
@code{host} table entries are necessary.
The privilege columns in the @code{user} table were not set explicitly in the
last @code{INSERT} statement (for the @code{dummy} user), so those columns
are assigned the default value of @code{'N'}. This is the same thing that
@code{GRANT USAGE} does.
The following example adds a user @code{custom} who can connect from hosts
@code{localhost}, @code{server.domain}, and @code{whitehouse.gov}. He wants
to access the @code{bankaccount} database only from @code{localhost},
the @code{expenses} database only from @code{whitehouse.gov}, and
the @code{customer} database from all three hosts. He wants
to use the password @code{stupid} from all three hosts.
To set up this user's privileges using @code{GRANT} statements, run these
commands:
@example
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';
@end example
To set up the user's privileges by modifying the grant tables directly,
run these commands (note the @code{FLUSH PRIVILEGES} at the end):
@example
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;
@end example
The first three @code{INSERT} statements add @code{user} table entries that
allow user @code{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 @code{'N'}). The next three @code{INSERT} statements add
@code{db} table entries that grant privileges to @code{custom} for the
@code{bankaccount}, @code{expenses}, and @code{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
@code{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 @code{GRANT} statement like the following:
@example
mysql> GRANT ...
ON *.*
TO myusername@@"%.mydomainname.com"
IDENTIFIED BY 'mypassword';
@end example
To do the same thing by modifying the grant tables directly, do this:
@example
mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',
PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;
@end example
You can also use @code{xmysqladmin}, @code{mysql_webadmin}, and even
@code{xmysql} to insert, change, and update values in the grant tables.
You can find these utilities in the
@uref{http://www.mysql.com/Downloads/Contrib/,Contrib directory of the @strong{MySQL}
Website}.
@cindex passwords, setting
@findex PASSWORD()
@findex SET PASSWORD statement
@cindex setting, passwords
@node Passwords, Access denied, Adding users, Privilege system
@section Setting Up Passwords
In most cases you should use @code{GRANT} to set up your users/passwords,
so the following only applies for advanced users. @xref{GRANT, , @code{GRANT}}.
The examples in the preceding sections illustrate an important principle:
when you store a non-empty password using @code{INSERT} or @code{UPDATE}
statements, you must use the @code{PASSWORD()} function to encrypt it. This
is because the @code{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:
@example
shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;
@end example
The result is that the plaintext value @code{'biscuit'} is stored as the
password in the @code{user} table. When the user @code{jeffrey} attempts to
connect to the server using this password, the @code{mysql} client encrypts
it with @code{PASSWORD()} and sends the result to the server. The server
compares the value in the @code{user} table (the encrypted value of
@code{'biscuit'}) to the encrypted password (which is @emph{not}
@code{'biscuit'}). The comparison fails and the server rejects the
connection:
@example
shell> mysql -u jeffrey -pbiscuit test
Access denied
@end example
Passwords must be encrypted when they are inserted in the @code{user}
table, so the @code{INSERT} statement should have been specified like this
instead:
@example
mysql> INSERT INTO user (Host,User,Password)
VALUES('%','jeffrey',PASSWORD('biscuit'));
@end example
You must also use the @code{PASSWORD()} function when you use @code{SET
PASSWORD} statements:
@example
mysql> SET PASSWORD FOR jeffrey@@"%" = PASSWORD('biscuit');
@end example
If you set passwords using the @code{GRANT ... IDENTIFIED BY} statement
or the @code{mysqladmin password} command, the @code{PASSWORD()} function
is unnecessary. They both take care of encrypting the password for you,
so you would specify a password of @code{'biscuit'} like this:
@example
mysql> GRANT USAGE ON *.* TO jeffrey@@"%" IDENTIFIED BY 'biscuit';
@end example
or
@example
shell> mysqladmin -u jeffrey password biscuit
@end example
@strong{NOTE:} @code{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 @strong{MySQL} password are the same, that
@code{PASSWORD()} will result in the same encrypted value as is stored in the
Unix password file. @xref{User names}.
@node Access denied, , Passwords, Privilege system
@section Causes of @code{Access denied} Errors
If you encounter @code{Access denied} errors when you try to connect to the
@strong{MySQL} server, the list below indicates some courses of
action you can take to correct the problem:
@itemize @bullet
@item
After installing @strong{MySQL}, did you run the @code{mysql_install_db}
script to set up the initial grant table contents? If not, do so.
@xref{Default privileges}. Test the initial privileges by executing
this command:
@example
shell> mysql -u root test
@end example
The server should let you connect without error. You should also make sure
you have a file @file{user.MYD} in the @strong{MySQL} database directory.
Ordinarily, this is @file{PATH/var/mysql/user.MYD}, where @code{PATH} is the
pathname to the @strong{MySQL} installation root.
@item
After a fresh installation, you should connect to the server and set up
your users and their access permissions:
@example
shell> mysql -u root mysql
@end example
The server should let you connect because the @strong{MySQL} @code{root} user
has no password initially. That is also a security risk, so setting the
@code{root} password is something you should do while you're setting up
your other @strong{MySQL} users.
If you try to connect as @code{root} and get this error:
@example
Access denied for user: '@@unknown' to database mysql
@end example
this means that you don't have an entry in the @code{user} table with a
@code{User} column value of @code{'root'} and that @code{mysqld} cannot
resolve the hostname for your client. In this case, you must restart the
server with the @code{--skip-grant-tables} option and edit your
@file{/etc/hosts} or @file{windowshosts} file to add an entry for your
host.
@item
If you get an error like the following:
@example
shell> mysqladmin -u root -pxxxx ver
Access denied for user: 'root@@localhost' (Using password: YES)
@end example
It means that you are using a wrong password. @xref{Passwords}.
If you have forgot the root password, you can restart @code{mysqld} with
@code{--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 @code{my.ini}
file. @xref{Option files}. You can avoid using option files with the @code{--no-defaults} option, as follows:
@example
shell> mysqladmin --no-defaults -u root ver
@end example
@item
@cindex @code{mysql_fix_privilege_tables}
If you updated an existing @strong{MySQL} installation from a version earlier
than Version 3.22.11 to Version 3.22.11 or later, did you run the
@code{mysql_fix_privilege_tables} script? If not, do so. The structure of
the grant tables changed with @strong{MySQL} Version 3.22.11 when the
@code{GRANT} statement became functional.
@item
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
@ref{Privilege changes}.
@item
If you can't get your password to work, remember that you must use
the @code{PASSWORD()} function if you set the password with the
@code{INSERT}, @code{UPDATE}, or @code{SET PASSWORD} statements. The
@code{PASSWORD()} function is unnecessary if you specify the password using
the @code{GRANT ... INDENTIFIED BY} statement or the @code{mysqladmin
password} command.
@xref{Passwords}.
@item
@code{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 @code{localhost} do not work if you are
running on a system that uses MIT-pthreads (@code{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 @code{--host} option to name
the server host explicitly. This will make a TCP/IP connection to the
@code{mysqld} server. In this case, you must have your real hostname in
@code{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.)
@item
If you get an @code{Access denied} error when trying to connect to the
database with @code{mysql -u user_name db_name}, you may have a problem
with the @code{user} table. Check this by executing @code{mysql -u root
mysql} and issuing this SQL statement:
@example
mysql> SELECT * FROM user;
@end example
The result should include an entry with the @code{Host} and @code{User}
columns matching your computer's hostname and your @strong{MySQL} user name.
@item
The @code{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 @code{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 @code{Using password: NO}, this means that you
tried to login without an password.
@item
If you get the following error when you try to connect from a different host
than the one on which the @strong{MySQL} server is running, then there is no
row in the @code{user} table that matches that host:
@example
Host ... is not allowed to connect to this MySQL server
@end example
You can fix this by using the command-line tool @code{mysql} (on the
server host!) to add a row to the @code{user}, @code{db}, or @code{host}
table for the user/hostname combination from which you are trying to
connect and then execute @code{mysqladmin flush-privileges}. If you are
not running @strong{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 @code{'%'} as the @code{Host} column value in the @code{user}
table and restart @code{mysqld} with the @code{--log} option on the
server machine. After trying to connect from the client machine, the
information in the @strong{MySQL} log will indicate how you really did
connect. (Then replace the @code{'%'} in the @code{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
@strong{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 @strong{MySQL} version and compile this
yourself. A source RPM is normally trivial to compile and install, so
this isn't a big problem.
@item
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:
@example
shell> mysqladmin -u root -pxxxx -h some-hostname ver
Access denied for user: 'root@' (Using password: YES)
@end example
This means that @strong{MySQL} got some error when trying to resolve the
IP to a hostname. In this case you can execute @code{mysqladmin
flush-hosts} to reset the internal DNS cache. @xref{DNS}.
Some permanent solutions are:
@itemize @minus
@item
Try to find out what is wrong with your DNS server and fix this.
@item
Specify IPs instead of hostnames in the @strong{MySQL} privilege tables.
@item
Start mysqld with @code{--skip-name-resolve}.
@item
Start mysqld with @code{--skip-host-cache}.
@item
Connect to @code{localhost} if you are running the server and the client
on the same machine.
@item
Put the client machine names in @code{/etc/hosts}.
@end itemize
@item
If @code{mysql -u root test} works but @code{mysql -h your_hostname -u root
test} results in @code{Access denied}, then you may not have the correct name
for your host in the @code{user} table. A common problem here is that the
@code{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
@code{'tcx'} in the @code{user} table, but your DNS tells @strong{MySQL} that
your hostname is @code{'tcx.subnet.se'}, the entry will not work. Try adding
an entry to the @code{user} table that contains the IP number of your host as
the @code{Host} column value. (Alternatively, you could add an entry to the
@code{user} table with a @code{Host} value that contains a wild card---for
example, @code{'tcx.%'}. However, use of hostnames ending with @samp{%} is
@emph{insecure} and is @emph{not} recommended!)
@item
If @code{mysql -u user_name test} works but @code{mysql -u user_name
other_db_name} doesn't work, you don't have an entry for @code{other_db_name}
listed in the @code{db} table.
@item
If @code{mysql -u user_name db_name} works when executed on the server
machine, but @code{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 @code{user} table or the @code{db} table.
@item
If you can't figure out why you get @code{Access denied}, remove from the
@code{user} table all entries that have @code{Host} values containing
wild cards (entries that contain @samp{%} or @samp{_}). A very common error
is to insert a new entry with @code{Host}=@code{'%'} and
@code{User}=@code{'some user'}, thinking that this will allow you to specify
@code{localhost} to connect from the same machine. The reason that this
doesn't work is that the default privileges include an entry with
@code{Host}=@code{'localhost'} and @code{User}=@code{''}. Because that entry
has a @code{Host} value @code{'localhost'} that is more specific than
@code{'%'}, it is used in preference to the new entry when connecting from
@code{localhost}! The correct procedure is to insert a second entry with
@code{Host}=@code{'localhost'} and @code{User}=@code{'some_user'}, or to
remove the entry with @code{Host}=@code{'localhost'} and
@code{User}=@code{''}.
@item
If you get the following error, you may have a problem with the @code{db} or
@code{host} table:
@example
Access to database denied
@end example
If the entry selected from the @code{db} table has an empty value in the
@code{Host} column, make sure there are one or more corresponding entries in
the @code{host} table specifying which hosts the @code{db} table entry
applies to.
If you get the error when using the SQL commands @code{SELECT ...
INTO OUTFILE} or @code{LOAD DATA INFILE}, your entry in the @code{user} table
probably doesn't have the @strong{file} privilege enabled.
@item
@cindex configuration files
@cindex environment variables
@tindex .my.cnf file
Remember that client programs will use connection parameters specified
in configuration files or environment variables. @xref{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 @file{.my.cnf} file in your home
directory. You might also check the system-wide @strong{MySQL}
configuration files, though it is far less likely that client connection
parameters will be specified there. @xref{Option files}. If you get
@code{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!
@xref{Option files}.
@item
If you make changes to the grant tables directly (using an @code{INSERT} or
@code{UPDATE} statement) and your changes seem to be ignored, remember
that you must issue a @code{FLUSH PRIVILEGES} statement or execute a
@code{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
@code{root} password with an @code{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!
@item
If you have access problems with a Perl, PHP, Python, or ODBC program, try to
connect to the server with @code{mysql -u user_name db_name} or @code{mysql
-u user_name -pyour_pass db_name}. If you are able to connect using the
@code{mysql} client, there is a problem with your program and not with the
access privileges. (Note that there is no space between @code{-p} and the
password; you can also use the @code{--password=your_pass} syntax to specify
the password. If you use the @code{-p} option alone, MySQL will prompt you
for the password.)
@item
For testing, start the @code{mysqld} daemon with the
@code{--skip-grant-tables} option. Then you can change the @strong{MySQL}
grant tables and use the @code{mysqlaccess} script to check whether or not
your modifications have the desired effect. When you are satisfied with your
changes, execute @code{mysqladmin flush-privileges} to tell the @code{mysqld}
server to start using the new grant tables. @strong{Note:} Reloading the
grant tables overrides the @code{--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.
@item
If everything else fails, start the @code{mysqld} daemon with a debugging
option (for example, @code{--debug=d,general,query}). This will print host and
user information about attempted connections, as well as information about
each command issued. @xref{Debugging server}.
@item
If you have any other problems with the @strong{MySQL} grant tables and
feel you must post the problem to the mailing list, always provide a
dump of the @strong{MySQL} grant tables. You can dump the tables with
the @code{mysqldump mysql} command. As always, post your problem using
the @code{mysqlbug} script. @xref{Bug reports}. In some cases you may need
to restart @code{mysqld} with @code{--skip-grant-tables} to run
@code{mysqldump}.
@end itemize
@node Reference, Table types, Privilege system, Top
@chapter MySQL Language Reference
@menu
* Literals:: Literals: how to write strings and numbers
* Variables:: User variables
* Column types:: Column types
* Functions:: Functions
* CREATE DATABASE:: @code{CREATE DATABASE} syntax
* DROP DATABASE:: @code{DROP DATABASE} syntax
* CREATE TABLE:: @code{CREATE TABLE} syntax
* ALTER TABLE:: @code{ALTER TABLE} syntax
* RENAME TABLE:: @code{RENAME TABLE} syntax
* DROP TABLE:: @code{DROP TABLE} syntax
* OPTIMIZE TABLE:: @code{OPTIMIZE TABLE} syntax
* CHECK TABLE:: @code{CHECK TABLE} syntax
* BACKUP TABLE:: @code{BACKUP TABLE} syntax
* RESTORE TABLE:: @code{RESTORE TABLE} syntax
* ANALYZE TABLE:: @code{ANALYZE TABLE} syntax
* REPAIR TABLE:: @code{REPAIR TABLE} syntax
* DELETE:: @code{DELETE} syntax
* TRUNCATE:: @code{TRUNCATE} syntax
* SELECT:: @code{SELECT} syntax
* JOIN:: @code{JOIN} syntax
* INSERT:: @code{INSERT} syntax
* REPLACE:: @code{REPLACE} syntax
* LOAD DATA:: @code{LOAD DATA INFILE} syntax
* UPDATE:: @code{UPDATE} syntax
* USE:: @code{USE} syntax
* FLUSH:: @code{Flush} syntax (clearing caches)
* KILL:: @code{KILL} syntax
* SHOW:: @code{SHOW} syntax (Get information about tables, columns, ...)
* EXPLAIN:: @code{EXPLAIN} syntax (Get information about a @code{SELECT})
* DESCRIBE:: @code{DESCRIBE} syntax (Get information about names of columns)
* COMMIT:: @code{BEGIN/COMMIT/ROLLBACK} syntax
* LOCK TABLES:: @code{LOCK TABLES/UNLOCK TABLES} syntax
* SET OPTION:: @code{SET OPTION} syntax
* GRANT:: @code{GRANT} and @code{REVOKE} syntax
* CREATE INDEX:: @code{CREATE INDEX} syntax
* DROP INDEX:: @code{DROP INDEX} syntax
* Comments:: Comment syntax
* CREATE FUNCTION:: @code{CREATE FUNCTION} syntax
* Reserved words:: Is @strong{MySQL} picky about reserved words?
@end menu
@cindex strings, defined
@cindex strings, escaping characters
@cindex literals
@cindex escape characters
@cindex backslash, escape character
@node Literals, Variables, Reference, Reference
@section Literals: How to Write Strings and Numbers
@menu
* String syntax:: Strings
* Number syntax:: Numbers
* Hexadecimal values:: Hexadecimal values
* NULL values:: @code{NULL} values
* Legal names:: Database, table, index, column and alias names
@end menu
@node String syntax, Number syntax, Literals, Literals
@subsection Strings
A string is a sequence of characters, surrounded by either single quote
(@samp{'}) or double quote (@samp{"}) characters (only the single quote
if you run in ANSI mode). Examples:
@example
'a string'
"another string"
@end example
Within a string, certain sequences have special meaning. Each of these
sequences begins with a backslash (@samp{}), known as the @emph{escape
character}. @strong{MySQL} recognizes the following escape sequences:
@c these aren't really functions, but that's probably the most reasonable index
@table @code
@findex (ASCII 0)
@findex NUL
@item
An ASCII 0 (@code{NUL}) character.
@findex n (newline)
@findex newline (n)
@item n
A newline character.
@findex t (tab)
@findex tab (t)
@item t
A tab character.
@findex r (carriage return)
@findex return (r)
@findex carriage return (r)
@item r
A carriage return character.
@findex b (backspace)
@findex backspace (b)
@item b
A backspace character.
@findex ' (single quote)
@findex single quote (')
@item '
A single quote (@samp{'}) character.
@findex " (double quote)
@findex double quote (")
@item "
A double quote (@samp{"}) character.
@findex \ (escape)
@findex escape (\)
@item \
A backslash (@samp{}) character.
@findex % (wild card character)
@findex Wild card character (%)
@item %
A @samp{%} character. This is used to search for literal instances of
@samp{%} in contexts where @samp{%} would otherwise be interpreted
as a wild-card character. @xref{String comparison functions}.
@findex _ (wild card character)
@findex Wild card character (_)
@item _
A @samp{_} character. This is used to search for literal instances of
@samp{_} in contexts where @samp{_} would otherwise be interpreted
as a wild-card character. @xref{String comparison functions}.
@end table
Note that if you use @samp{%} or @samp{_} in some string contexts, these
will return the strings @samp{%} and @samp{_} and not @samp{%} and
@samp{_}.
@cindex quotes, in strings
@noindent
There are several ways to include quotes within a string:
@itemize @bullet
@item
A @samp{'} inside a string quoted with @samp{'} may be written as @samp{''}.
@item
A @samp{"} inside a string quoted with @samp{"} may be written as @samp{""}.
@item
You can precede the quote character with an escape character (@samp{}).
@item
A @samp{'} inside a string quoted with @samp{"} needs no special treatment
and need not be doubled or escaped. In the same way, @samp{"} inside a
string quoted with @samp{'} needs no special treatment.
@end itemize
The @code{SELECT} statements shown below demonstrate how quoting and
escaping work:
@example
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 |
+--------------------+
@end example
@cindex quoting binary data
If you want to insert binary data into a @code{BLOB} column, the following
characters must be represented by escape sequences:
@table @code
@item NUL
ASCII 0. You should represent this by @samp{} (a backslash and an ASCII @samp{0} character).
@item
ASCII 92, backslash. Represent this by @samp{\}.
@item '
ASCII 39, single quote. Represent this by @samp{'}.
@item "
ASCII 34, double quote. Represent this by @samp{"}.
@end table
@cindex quoting
@cindex @code{BLOB}, inserting binary data
@findex mysql_escape_string()
@findex DBI->quote
If you write C code, you can use the C API function
@code{mysql_escape_string()} to escape characters for the @code{INSERT}
statement. @xref{C API function overview}. In Perl, you can use the
@code{quote} method of the @code{DBI} package to convert special
characters to the proper escape sequences. @xref{Perl DBI Class, , Perl
@code{DBI} Class}.
You should use an escape function on any string that might contain any of the
special characters listed above!
@cindex numbers
@cindex valid numbers, examples
@cindex integers
@cindex floats
@cindex negative values
@node Number syntax, Hexadecimal values, String syntax, Literals
@subsection Numbers
Integers are represented as a sequence of digits. Floats use @samp{.} as a
decimal separator. Either type of number may be preceded by @samp{-} to
indicate a negative value.
Examples of valid integers:
@example
1221
0
-32
@end example
Examples of valid floating-point numbers:
@example
294.42
-32032.6809e+10
148.00
@end example
An integer may be used in a floating-point context; it is interpreted
as the equivalent floating-point number.
@tindex hexadecimal values
@node Hexadecimal values, NULL values, Number syntax, Literals
@subsection Hexadecimal Values
@strong{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:
@example
mysql> SELECT 0xa+0
-> 10
mysql> select 0x5061756c;
-> Paul
@end example
Hexadecimal strings are often used by ODBC to give values for BLOB columns.
@tindex NULL value
@node NULL values, Legal names, Hexadecimal values, Literals
@subsection @code{NULL} Values
The @code{NULL} value means ``no data'' and is different from values such
as @code{0} for numeric types or the empty string for string types.
@xref{Problems with NULL, , Problems with @code{NULL}}.
@code{NULL} may be represented by @code{N} when using the text file import
or export formats (@code{LOAD DATA INFILE}, @code{SELECT ... INTO OUTFILE}).
@xref{LOAD DATA, , @code{LOAD DATA}}.
@cindex names
@cindex legal names
@cindex databases, names
@cindex tables, names
@cindex indexes, names
@cindex columns, names
@cindex aliases, names
@node Legal names, , NULL values, Literals
@subsection Database, Table, Index, Column, and Alias Names
@menu
* Name case sensitivity:: Case sensitivity in names
@end menu
Database, table, index, column, and alias names all follow the same rules in
@strong{MySQL}.
@tindex identifiers, quoting
@tindex quoting of identifiers
@tindex `
@tindex "
Note that the rules changed starting with @strong{MySQL} Version 3.23.6 when we
introduced quoting of identifiers (database, table, and column names)
with @samp{`}. @samp{"} will also work to quote identifiers if you run
in ANSI mode. @xref{ANSI mode}.
@multitable @columnfractions .15 .15 .70
@item @strong{Identifier} @tab @strong{Max length} @tab @strong{Allowed characters}
@item Database @tab 64 @tab Any character that is allowed in a directory name except @samp{/}.
@item Table @tab 64 @tab Any character that is allowed in a file name, except @samp{/} or @samp{.}.
@item Column @tab 64 @tab All characters.
@item Alias @tab 255 @tab All characters.
@end multitable
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 @code{`} when you use it:
@example
SELECT * from `select` where `select`.id > 100;
@end example
In previous versions of @strong{MySQL}, the name rules are as follows:
@itemize @bullet
@item
A name may consist of alphanumeric characters from the current character set
and also @samp{_} and @samp{$}. The default character set is ISO-8859-1
Latin1; this may be changed with the @code{--default-character-set} option
to @code{mysqld}.
@xref{Character sets}.
@item
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 @emph{only} of numbers.
@item
You cannot use the @samp{.} character in names because it is used to extend the
format by which you can refer to columns (see immediately below).
@end itemize
It is recommended that you do not use names like @code{1e}, because
an expression like @code{1e+1} is ambiguous. It may be interpreted as the
expression @code{1e + 1} or as the number @code{1e+1}.
In @strong{MySQL} you can refer to a column using any of the following forms:
@multitable @columnfractions .35 .65
@item @strong{Column reference} @tab @strong{Meaning}
@item @code{col_name} @tab Column @code{col_name}
from whichever table used in the query contains a column of that name.
@item @code{tbl_name.col_name} @tab Column @code{col_name} from table
@code{tbl_name} of the current database.
@item @code{db_name.tbl_name.col_name} @tab Column @code{col_name} from table
@code{tbl_name} of the database @code{db_name}. This form is available in
@strong{MySQL} Version 3.22 or later.
@item
@code{`column_name`} @tab A column that is a keyword or contains special characters.
@end multitable
You need not specify a @code{tbl_name} or @code{db_name.tbl_name} prefix for
a column reference in a statement unless the reference would be ambiguous.
For example, suppose tables @code{t1} and @code{t2} each contain a column
@code{c}, and you retrieve @code{c} in a @code{SELECT} statement that uses
both @code{t1} and @code{t2}. In this case, @code{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 @code{t1.c} or @code{t2.c}. Similarly, if
you are retrieving from a table @code{t} in database @code{db1} and from a
table @code{t} in database @code{db2}, you must refer to columns in those
tables as @code{db1.t.col_name} and @code{db2.t.col_name}.
@cindex ODBC compatibility
@cindex compatibility, with ODBC
The syntax @code{.tbl_name} means the table @code{tbl_name} in the current
database. This syntax is accepted for ODBC compatibility, because some ODBC
programs prefix table names with a @samp{.} character.
@cindex names, case-sensitivity
@cindex case-sensitivity, in names
@node Name case sensitivity, , Legal names, Legal names
@subsubsection Case Sensitivity in Names
@cindex database names, case sensitivity
@cindex table names, case sensitivity
@cindex column names, case sensitivity
@cindex alias names, case sensitivity
In @strong{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. @xref{Extensions to ANSI}.
@strong{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 @code{my_table} and as @code{MY_TABLE}:
@example
mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
@end example
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 @code{a} and as @code{A}:
@example
mysql> SELECT col_name FROM tbl_name AS a
WHERE a.col_name = 1 OR A.col_name = 2;
@end example
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 @code{mysqld} with @code{-O
lower_case_table_names=1}.
In this case @strong{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 @code{mysqld}
with this option.
@cindex variables, user
@cindex user variables
@cindex names, variables
@node Variables, Column types, Literals, Reference
@section User Variables
@strong{MySQL} supports thread-specific variables with the
@code{@@variablename} syntax. A variable name may consist of
alphanumeric characters from the current character set and also
@samp{_}, @samp{$}, and @samp{.} . The default character set is
ISO-8859-1 Latin1; this may be changed with the
@code{--default-character-set} option to @code{mysqld}. @xref{Character
sets}.
Variables don't have to be initialized. They contain @code{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 @code{SET} syntax:
@example
SET @@variable= @{ integer expression | real expression | string expression @}
[,@@variable= ...].
@end example
You can also set a variable in an expression with the @code{@@variable:=expr}
syntax:
@example
select @@t1:=(@@t2:=1)+@@t3:=4,@@t1,@@t2,@@t3;
+----------------------+------+------+------+
| @@t1:=(@@t2:=1)+@@t3:=4 | @@t1 | @@t2 | @@t3 |
+----------------------+------+------+------+
| 5 | 5 | 1 | 4 |
+----------------------+------+------+------+
@end example
(We had to use the @code{:=} syntax here, because @code{=} 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 @code{LIMIT} clause of a @code{SELECT} statement,
or the @code{IGNORE number LINES} clause of a @code{LOAD DATA} statement.
@strong{NOTE:} In a @code{SELECT} statement, each expression is only
evaluated when it's sent to the client. This means that in the @code{HAVING},
@code{GROUP BY}, or @code{ORDER BY} clause, you can't refer to an expression
that involves variables that are set in the @code{SELECT} part. For example,
the following statement will NOT work as expected:
@example
SELECT (@@aa:=id) AS a, (@@aa+3) AS b FROM table_name HAVING b=5;
@end example
The reason is that @code{@@aa} will not contain the value of the current
row, but the value of @code{id} for the previous accepted row.
@cindex columns, types
@cindex types, columns
@node Column types, Functions, Variables, Reference
@section Column Types
@strong{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 @strong{MySQL} are listed below.
The following code letters are used in the descriptions:
@cindex display size
@cindex sizes, display
@cindex digits
@cindex decimal point
@cindex brackets, square
@cindex square brackets
@table @code
@item M
Indicates the maximum display size. The maximum legal display size is 255.
@item 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 @code{M}-2.
@end table
Square brackets (@samp{[} and @samp{]}) indicate parts of type specifiers
that are optional.
@tindex Types
@c The @w{-number} stuff keeps a linebreak from occurring between
@c the - and number.
Note that if you specify @code{ZEROFILL} for a column, @strong{MySQL} will
automatically add the @code{UNSIGNED} attribute to the column.
@table @code
@tindex TINYINT
@item TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is @code{-128} to @code{127}. The
unsigned range is @code{0} to @code{255}.
@tindex SMALLINT
@item SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is @code{-32768} to @code{32767}. The
unsigned range is @code{0} to @code{65535}.
@tindex MEDIUMINT
@item MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
A medium-size integer. The signed range is @code{-8388608} to
@code{8388607}. The unsigned range is @code{0} to @code{16777215}.
@tindex INT
@item INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is @code{-2147483648} to
@code{2147483647}. The unsigned range is @code{0} to @code{4294967295}.
@tindex INTEGER
@item INTEGER[(M)] [UNSIGNED] [ZEROFILL]
This is a synonym for @code{INT}.
@tindex BIGINT
@item BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is @code{-9223372036854775808} to
@code{9223372036854775807}. The unsigned range is @code{0} to
@code{18446744073709551615}.
Some things you should be aware about @code{BIGINT} columns:
@itemize @bullet
@item
@cindex rounding errors
As all arithmetic is done using signed @code{BIGINT} or @code{DOUBLE}
values, so you shouldn't use unsigned big integers larger than
@code{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 @code{BIGINT} to a @code{DOUBLE}.
@item
You can always store an exact integer value in a @code{BIGINT} column by
storing it as a string, as there is in this case there will be no
intermediate double representation.
@item
@samp{-}, @samp{+}, and @samp{*} will use @code{BIGINT} arithmetic when
both arguments are @code{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
@code{9223372036854775807}.
@end itemize
@cindex floating-point number
@tindex FLOAT
@tindex FLOAT(precision)
@item FLOAT(precision) [ZEROFILL]
A floating-point number. Cannot be unsigned. @code{precision} can be
@code{<=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 @code{FLOAT} and @code{DOUBLE} types described immediately below.
@code{FLOAT(X)} has the same range as the corresponding @code{FLOAT} and
@code{DOUBLE} types, but the display size and number of decimals is undefined.
In @strong{MySQL} Version 3.23, this is a true floating-point value. In
earlier @strong{MySQL} versions, @code{FLOAT(precision)} always has 2 decimals.
Note that using @code{FLOAT} may give you some unexpected problems as
all calculation in @strong{MySQL} is done with double precision.
@xref{No matching rows}.
@cindex ODBC compatibility
@cindex compatibility, with ODBC
This syntax is provided for ODBC compatibility.
@tindex FLOAT
@tindex FLOAT(M,D)
@item FLOAT[(M,D)] [ZEROFILL]
A small (single-precision) floating-point number. Cannot be unsigned.
Allowable values are @code{@w{-3.402823466E+38}} to
@code{@w{-1.175494351E-38}}, @code{0}, and @code{@w{1.175494351E-38}} to
@code{3.402823466E+38}. The M is the display width and D is the
number of decimals. @code{FLOAT} without an argument or with an argument of
<= 24 stands for a single-precision floating-point number.
@tindex DOUBLE
@tindex FLOAT(precision)
@item DOUBLE[(M,D)] [ZEROFILL]
A normal-size (double-precision) floating-point number. Cannot be
unsigned. Allowable values are @code{@w{-1.7976931348623157E+308}} to
@code{@w{-2.2250738585072014E-308}}, @code{0}, and
@code{2.2250738585072014E-308} to @code{1.7976931348623157E+308}. The M
is the display width and D is the number of decimals. @code{DOUBLE}
without an argument or @code{FLOAT(X)} where 25 <= X <= 53 stands for a
double-precision floating-point number.
@tindex DOUBLE PRECISION
@tindex REAL
@item DOUBLE PRECISION[(M,D)] [ZEROFILL]
@itemx REAL[(M,D)] [ZEROFILL]
These are synonyms for @code{DOUBLE}.
@tindex DECIMAL
@item DECIMAL[(M[,D])] [ZEROFILL]
An unpacked floating-point number. Cannot be unsigned. Behaves like a
@code{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 @samp{-} sign, are not counted in M (but space
for these are reserved). If @code{D} is 0, values will have no decimal
point or fractional part. The maximum range of @code{DECIMAL} values is
the same as for @code{DOUBLE}, but the actual range for a given
@code{DECIMAL} column may be constrained by the choice of @code{M} and
@code{D}.
If @code{D} is left out it's set to 0. If @code{M} is left out it's set to 10.
Note that in @strong{MySQL} Version 3.22 the @code{M} argument had to
includes the space needed for the sign and the decimal point.
@tindex NUMERIC
@item NUMERIC(M,D) [ZEROFILL]
This is a synonym for @code{DECIMAL}.
@tindex DATE
@item DATE
A date. The supported range is @code{'1000-01-01'} to @code{'9999-12-31'}.
@strong{MySQL} displays @code{DATE} values in @code{'YYYY-MM-DD'} format, but
allows you to assign values to @code{DATE} columns using either strings or
numbers. @xref{DATETIME}.
@tindex DATETIME
@item DATETIME
A date and time combination. The supported range is @code{'1000-01-01
00:00:00'} to @code{'9999-12-31 23:59:59'}. @strong{MySQL} displays
@code{DATETIME} values in @code{'YYYY-MM-DD HH:MM:SS'} format, but allows you
to assign values to @code{DATETIME} columns using either strings or numbers.
@xref{DATETIME}.
@tindex TIMESTAMP
@item TIMESTAMP[(M)]
A timestamp. The range is @code{'1970-01-01 00:00:00'} to sometime in the
year @code{2037}. @strong{MySQL} displays @code{TIMESTAMP} values in
@code{YYYYMMDDHHMMSS}, @code{YYMMDDHHMMSS}, @code{YYYYMMDD}, or @code{YYMMDD}
format, depending on whether @code{M} is @code{14} (or missing), @code{12},
@code{8}, or @code{6}, but allows you to assign values to @code{TIMESTAMP}
columns using either strings or numbers. A @code{TIMESTAMP} column is useful
for recording the date and time of an @code{INSERT} or @code{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 @code{NULL} value. @xref{Date
and time types}.
A @code{TIMESTAMP} is always stored in 4 bytes. The @code{M} argument only
affects how the @code{TIMESTAMP} column is displayed.
Note that @code{TIMESTAMP(X)} columns where X is 8 or 14 are reported to
be numbers while other @code{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!
@xref{DATETIME}.
@tindex TIME
@item TIME
A time. The range is @code{'-838:59:59'} to @code{'838:59:59'}.
@strong{MySQL} displays @code{TIME} values in @code{'HH:MM:SS'} format, but
allows you to assign values to @code{TIME} columns using either strings or
numbers. @xref{TIME}.
@tindex YEAR
@item YEAR[(2|4)]
A year in 2- or 4-digit format (default is 4-digit). The allowable values
are @code{1901} to @code{2155}, @code{0000} in the 4-digit year format,
and 1970-2069 if you use the 2-digit format (70-69). @strong{MySQL} displays
@code{YEAR} values in @code{YYYY} format, but allows you to assign values to
@code{YEAR} columns using either strings or numbers. (The @code{YEAR} type is
new in @strong{MySQL} Version 3.22.). @xref{YEAR}.
@tindex NATIONAL CHAR
@tindex NCHAR
@tindex CHAR
@tindex CHARACTER
@item [NATIONAL] CHAR(M) [BINARY]
A fixed-length string that is always right-padded with spaces to the
specified length when stored. The range of @code{M} is 1 to 255 characters.
Trailing spaces are removed when the value is retrieved. @code{CHAR} values
are sorted and compared in case-insensitive fashion according to the
default character set unless the @code{BINARY} keyword is given.
@code{NATIONAL CHAR} (short form @code{NCHAR}) is the ANSI SQL way to
define that a CHAR column should use the default CHARACTER set. This is
the default in @strong{MySQL}.
@code{CHAR} is a shorthand for @code{CHARACTER}.
@strong{MySQL} allows you to create a column of type
@code{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 @code{CHAR(0)}, that is not defined
as @code{NOT NULL}, will only occupy one bit and can only take 2 values:
@code{NULL} or @code{""}. @xref{CHAR}.
@tindex CHARACTER VARYING
@tindex CHAR VARYING
@tindex VARCHAR
@item [NATIONAL] VARCHAR(M) [BINARY]
A variable-length string. @strong{NOTE:} Trailing spaces are removed when
the value is stored (this differs from the ANSI SQL specification). The range
of @code{M} is 1 to 255 characters. @code{VARCHAR} values are sorted and
compared in case-insensitive fashion unless the @code{BINARY} keyword is
given. @xref{Silent column changes}.
@code{VARCHAR} is a shorthand for @code{CHARACTER VARYING}.
@xref{CHAR}.
@tindex TINYBLOB
@tindex TINYTEXT
@item TINYBLOB
@itemx TINYTEXT
A @code{BLOB} or @code{TEXT} column with a maximum length of 255 (2^8 - 1)
characters. @xref{Silent column changes}. @xref{BLOB}.
@tindex BLOB
@tindex TEXT
@item BLOB
@itemx TEXT
A @code{BLOB} or @code{TEXT} column with a maximum length of 65535 (2^16 - 1)
characters. @xref{Silent column changes}. @xref{BLOB}.
@tindex MEDIUMBLOB
@tindex MEDIUMTEXT
@item MEDIUMBLOB
@itemx MEDIUMTEXT
A @code{BLOB} or @code{TEXT} column with a maximum length of 16777215
(2^24 - 1) characters. @xref{Silent column changes}. @xref{BLOB}.
@tindex LONGBLOB
@tindex LONGTEXT
@item LONGBLOB
@itemx LONGTEXT
A @code{BLOB} or @code{TEXT} column with a maximum length of 4294967295
(2^32 - 1) characters. @xref{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. @xref{BLOB}.
@tindex ENUM
@item ENUM('value1','value2',...)
An enumeration. A string object that can have only one value, chosen
from the list of values @code{'value1'}, @code{'value2'}, @code{...},
@code{NULL} or the special @code{""} error value. An @code{ENUM} can
have a maximum of 65535 distinct values. @xref{ENUM}.
@tindex SET
@item 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 @code{'value1'}, @code{'value2'},
@code{...} A @code{SET} can have a maximum of 64 members. @xref{SET}.
@end table
@menu
* Storage requirements:: Column type storage requirements
* Numeric types:: Numeric types
* Date and time types:: Date and time types
* String types:: String types
* Choosing types:: Choosing the right type for a column
* Indexes:: Column indexes
* Multiple-column indexes:: Multiple-column indexes
* Other-vendor column types:: Using column types from other database engines
@end menu
@cindex storage requirements, column type
@cindex columns, storage requirements
@node Storage requirements, Numeric types, Column types, Column types
@subsection Column Type Storage Requirements
The storage requirements for each of the column types supported by
@strong{MySQL} are listed below by category.
@cindex numeric types
@cindex types, numeric
@subsubheading Storage requirements for numeric types
@multitable @columnfractions .5 .5
@item @strong{Column type} @tab @strong{Storage required}
@item @code{TINYINT} @tab 1 byte
@item @code{SMALLINT} @tab 2 bytes
@item @code{MEDIUMINT} @tab 3 bytes
@item @code{INT} @tab 4 bytes
@item @code{INTEGER} @tab 4 bytes
@item @code{BIGINT} @tab 8 bytes
@item @code{FLOAT(X)} @tab 4 if X <= 24 or 8 if 25 <= X <= 53
@item @code{FLOAT} @tab 4 bytes
@item @code{DOUBLE} @tab 8 bytes
@item @code{DOUBLE PRECISION} @tab 8 bytes
@item @code{REAL} @tab 8 bytes
@item @code{DECIMAL(M,D)} @tab @code{M+2} bytes if D > 0, @code{M+1} bytes if D = 0 (@code{D}+2, if @code{M < D})
@item @code{NUMERIC(M,D)} @tab @code{M+2} bytes if D > 0, @code{M+1} bytes if D = 0 (@code{D}+2, if @code{M < D})
@end multitable
@cindex date types
@cindex time types
@cindex types, date
@cindex types, time
@subsubheading Storage requirements for date and time types
@multitable @columnfractions .5 .5
@item @strong{Column type} @tab @strong{Storage required}
@item @code{DATE} @tab 3 bytes
@item @code{DATETIME} @tab 8 bytes
@item @code{TIMESTAMP} @tab 4 bytes
@item @code{TIME} @tab 3 bytes
@item @code{YEAR} @tab 1 byte
@end multitable
@subsubheading Storage requirements for string types
@multitable @columnfractions .5 .5
@item @strong{Column type} @tab @strong{Storage required}
@item @code{CHAR(M)} @tab @code{M} bytes, @code{1 <= M <= 255}
@item @code{VARCHAR(M)} @tab @code{L}+1 bytes, where @code{L <= M} and
@code{1 <= M <= 255}
@item @code{TINYBLOB}, @code{TINYTEXT} @tab @code{L}+1 bytes,
where @code{L} < 2^8
@item @code{BLOB}, @code{TEXT} @tab @code{L}+2 bytes,
where @code{L} < 2^16
@item @code{MEDIUMBLOB}, @code{MEDIUMTEXT} @tab @code{L}+3 bytes,
where @code{L} < 2^24
@item @code{LONGBLOB}, @code{LONGTEXT} @tab @code{L}+4 bytes,
where @code{L} < 2^32
@item @code{ENUM('value1','value2',...)} @tab 1 or 2 bytes, depending on
the number of enumeration values (65535 values maximum)
@item @code{SET('value1','value2',...)} @tab 1, 2, 3, 4 or 8 bytes, depending
on the number of set members (64 members maximum)
@end multitable
@cindex BLOB, size
@cindex TEXT, size
@cindex VARCHAR, size
@code{VARCHAR} and the @code{BLOB} and @code{TEXT} types are variable-length
types, for which the storage requirements depend on the actual length of
column values (represented by @code{L} in the preceding table), rather than
on the type's maximum possible size. For example, a @code{VARCHAR(10)}
column can hold a string with a maximum length of 10 characters. The actual
storage required is the length of the string (@code{L}), plus 1 byte to
record the length of the string. For the string @code{'abcd'}, @code{L} is 4
and the storage requirement is 5 bytes.
The @code{BLOB} and @code{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. @xref{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, @strong{MySQL}
may, under certain conditions, change a column from a variable-length type to a
fixed-length type, or vice-versa. @xref{Silent column changes}.
@cindex ENUM, size
The size of an @code{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. @xref{ENUM}.
@cindex SET, size
The size of a @code{SET} object is determined by the number of different
set members. If the set size is @code{N}, the object occupies @code{(N+7)/8}
bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A @code{SET} can have a maximum
of 64 members. @xref{SET}.
@node Numeric types, Date and time types, Storage requirements, Column types
@subsection Numeric Types
@strong{MySQL} supports all of the ANSI/ISO SQL92 numeric types. These
types include the exact numeric data types (@code{NUMERIC},
@code{DECIMAL}, @code{INTEGER}, and @code{SMALLINT}), as well as the
approximate numeric data types (@code{FLOAT}, @code{REAL}, and
@code{DOUBLE PRECISION}). The keyword @code{INT} is a synonym for
@code{INTEGER}, and the keyword @code{DEC} is a synonym for
@code{DECIMAL}.
The @code{NUMERIC} and @code{DECIMAL} types are implemented as the same
type by @strong{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:
@example
salary DECIMAL(9,2)
@end example
In this example, @code{9} (@code{precision}) represents the number of
significant decimal digits that will be stored for values, and
@code{2} (@code{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 @code{salary} column is from
@code{-9999999.99} to @code{9999999.99}. In ANSI/ISO SQL92, the syntax
@code{DECIMAL(p)} is equivalent to @code{DECIMAL(p,0)}. Similarly, the
syntax @code{DECIMAL} is equivalent to @code{DECIMAL(p,0)}, where the
implementation is allowed to decide the value of @code{p}.
@strong{MySQL} does not currently support either of these variant forms
of the @code{DECIMAL}/@code{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.
@code{DECIMAL} and @code{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 @code{scale} > 0), and the @samp{-} sign
(for negative numbers). If @code{scale} is 0, @code{DECIMAL} and
@code{NUMERIC} values contain no decimal point or fractional part.
The maximum range of @code{DECIMAL} and @code{NUMERIC} values is the
same as for @code{DOUBLE}, but the actual range for a given
@code{DECIMAL} or @code{NUMERIC} column can be constrained by the
@code{precision} or @code{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 @code{scale}, the value is rounded to that
@code{scale}. When a @code{DECIMAL} or @code{NUMERIC} column is
assigned a value whose magnitude exceeds the range implied by the
specified (or defaulted) @code{precision} and @code{scale},
@strong{MySQL} stores the value representing the corresponding end
point of that range.
As an extension to the ANSI/ISO SQL92 standard, @strong{MySQL} also
supports the integral types @code{TINYINT}, @code{MEDIUMINT}, and
@code{BIGINT} as listed in the tables above. Another extension is
supported by @strong{MySQL} for optionally specifying the display width
of an integral value in parentheses following the base keyword for the
type (for example, @code{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
@code{ZEROFILL}, the default padding of spaces is replaced with zeroes.
For example, for a column declared as @code{INT(5) ZEROFILL}, a value
of @code{4} is retrieved as @code{00004}. Note that if you store larger
values than the display width in an integer column, you may experience
problems when @strong{MySQL} generates temporary tables for some
complicated joins, as in these cases @strong{MySQL} trusts that the
data did fit into the original column width.
All integral types can have an optional (non-standard) attribute
@code{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 @code{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 @code{FLOAT} in parentheses. The @strong{MySQL} implementation
also supports this optional precision specification. When the keyword
@code{FLOAT} is used for a column type without a precision
specification, @strong{MySQL} uses four bytes to store the values. A
variant syntax is also supported, with two numbers given in parentheses
following the @code{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 @code{DECIMAL} and
@code{NUMERIC}). When @strong{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 @code{REAL} and @code{DOUBLE PRECISION} types do not accept
precision specifications. As an extension to the ANSI/ISO SQL92
standard, @strong{MySQL} recognizes @code{DOUBLE} as a synonym for the
@code{DOUBLE PRECISION} type. In contrast with the standard's
requirement that the precision for @code{REAL} be smaller than that used
for @code{DOUBLE PRECISION}, @strong{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 @code{FLOAT} or @code{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, @strong{MySQL} clips the value to the appropriate
endpoint of the range and stores the resulting value instead.
For example, the range of an @code{INT} column is @code{-2147483648} to
@code{2147483647}. If you try to insert @code{-9999999999} into an
@code{INT} column, the value is clipped to the lower endpoint of the range,
and @code{-2147483648} is stored instead. Similarly, if you try to insert
@code{9999999999}, @code{2147483647} is stored instead.
If the @code{INT} column is @code{UNSIGNED}, the size of the column's
range is the same but its endpoints shift up to @code{0} and @code{4294967295}.
If you try to store @code{-9999999999} and @code{9999999999},
the values stored in the column become @code{0} and @code{4294967296}.
Conversions that occur due to clipping are reported as ``warnings'' for
@code{ALTER TABLE}, @code{LOAD DATA INFILE}, @code{UPDATE}, and
multi-row @code{INSERT} statements.
@cindex types, Date and Time
@cindex Date and Time types
@node Date and time types, String types, Numeric types, Column types
@subsection Date and Time Types
@menu
* Y2K issues:: Y2K issues and date types
* DATETIME:: The @code{DATETIME}, @code{DATE} and @code{TIMESTAMP} types
* TIME:: The @code{TIME} type
* YEAR:: The @code{YEAR} type
@end menu
The date and time types are @code{DATETIME}, @code{DATE},
@code{TIMESTAMP}, @code{TIME}, and @code{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 @strong{MySQL} allows you to store
certain 'not strictly' legal date values, for example @code{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', @strong{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 @strong{MySQL} allows you to store, in a
@code{DATE} or @code{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 @code{1999-00-00} or @code{1999-01-00}. (You
cannot expect to get a correct value from functions like @code{DATE_SUB()}
or @code{DATE_ADD} for dates like these.)
Here are some general considerations to keep in mind when working
with date and time types:
@itemize @bullet
@item
@strong{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.
@item
Although @strong{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, @code{'98-09-04'}), rather than
in the month-day-year or day-month-year orders commonly used elsewhere (for
example, @code{'09-04-98'}, @code{'04-09-98'}).
@item
@strong{MySQL} automatically converts a date or time type value to a number
if the value is used in a numeric context, and vice versa.
@item
When @strong{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 @code{TIME} values are clipped to
the appropriate endpoint of the @code{TIME} range.) The table below
shows the format of the ``zero'' value for each type:
@multitable @columnfractions .3 .7
@item @strong{Column type} @tab @strong{``Zero'' value}
@item @code{DATETIME} @tab @code{'0000-00-00 00:00:00'}
@item @code{DATE} @tab @code{'0000-00-00'}
@item @code{TIMESTAMP} @tab @code{00000000000000} (length depends on display size)
@item @code{TIME} @tab @code{'00:00:00'}
@item @code{YEAR} @tab @code{0000}
@end multitable
@item
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 @code{'0'} or @code{0}, which are easier to write.
@item
``Zero'' date or time values used through @strong{MyODBC} are converted
automatically to @code{NULL} in @strong{MyODBC} Version 2.50.12 and above,
because ODBC can't handle such values.
@end itemize
@cindex Year 2000 issues
@cindex date types, Y2K issues
@node Y2K issues, DATETIME, Date and time types, Date and time types
@subsubsection Y2K Issues and Date Types
@strong{MySQL} itself is Y2K-safe (@pxref{Year 2000 compliance}),
but input values presented to @strong{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 @strong{MySQL} stores
years internally using four digits.
For @code{DATETIME}, @code{DATE}, @code{TIMESTAMP}, and @code{YEAR} types,
@strong{MySQL} interprets dates with ambiguous year values using the
following rules:
@itemize @bullet
@item
Year values in the range @code{00-69} are converted to @code{2000-2069}.
@item
Year values in the range @code{70-99} are converted to @code{1970-1999}.
@end itemize
Remember that these rules provide only reasonable guesses as to what your
data mean. If the heuristics used by @strong{MySQL} don't produce the
correct values, you should provide unambiguous input containing 4-digit
year values.
@code{ORDER BY} will sort 2-digit @code{YEAR/DATE/DATETIME} types properly.
Note also that some functions like @code{MIN()} and @code{MAX()} will convert a
@code{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 @code{TIMESTAMP/DATE} to 4-digit year format or
use something like @code{MIN(DATE_ADD(timestamp,INTERVAL 0 DAYS))}.
@tindex DATETIME
@tindex DATE
@tindex TIMESTAMP
@node DATETIME, TIME, Y2K issues, Date and time types
@subsubsection The @code{DATETIME}, @code{DATE}, and @code{TIMESTAMP} Types
The @code{DATETIME}, @code{DATE}, and @code{TIMESTAMP} types are related.
This section describes their characteristics, how they are similar, and how
they differ.
The @code{DATETIME} type is used when you need values that contain both date
and time information. @strong{MySQL} retrieves and displays @code{DATETIME}
values in @code{'YYYY-MM-DD HH:MM:SS'} format. The supported range is
@code{'1000-01-01 00:00:00'} to @code{'9999-12-31 23:59:59'}. (``Supported''
means that although earlier values might work, there is no guarantee that
they will.)
The @code{DATE} type is used when you need only a date value, without a time
part. @strong{MySQL} retrieves and displays @code{DATE} values in
@code{'YYYY-MM-DD'} format. The supported range is @code{'1000-01-01'} to
@code{'9999-12-31'}.
The @code{TIMESTAMP} column type provides a type that you can use to
automatically mark @code{INSERT} or @code{UPDATE} operations with the current
date and time. If you have multiple @code{TIMESTAMP} columns, only the first
one is updated automatically.
Automatic updating of the first @code{TIMESTAMP} column occurs under any of
the following conditions:
@itemize @bullet
@item
The column is not specified explicitly in an @code{INSERT} or
@code{LOAD DATA INFILE} statement.
@item
The column is not specified explicitly in an @code{UPDATE} statement and some
other column changes value. (Note that an @code{UPDATE} that sets a column
to the value it already has will not cause the @code{TIMESTAMP} column to be
updated, because if you set a column to its current value, @strong{MySQL}
ignores the update for efficiency.)
@item
You explicitly set the @code{TIMESTAMP} column to @code{NULL}.
@end itemize
@code{TIMESTAMP} columns other than the first may also be set to the current
date and time. Just set the column to @code{NULL} or to @code{NOW()}.
You can set any @code{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 @code{TIMESTAMP} column. You can use this property if,
for example, you want a @code{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:
@itemize @bullet
@item
Let @strong{MySQL} set the column when the row is created.
This will initialize it to the current date and time.
@item
When you perform subsequent updates to other columns in the row, set
the @code{TIMESTAMP} column explicitly to its current value.
@end itemize
On the other hand, you may find it just as easy to use a @code{DATETIME}
column that you initialize to @code{NOW()} when the row is created and
leave alone for subsequent updates.
@code{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 @strong{MySQL} retrieves and displays @code{TIMESTAMP}
values depends on the display size, as illustrated by the table below. The
`full' @code{TIMESTAMP} format is 14 digits, but @code{TIMESTAMP} columns may
be created with shorter display sizes:
@multitable @columnfractions .3 .7
@item @strong{Column type} @tab @strong{Display format}
@item @code{TIMESTAMP(14)} @tab @code{YYYYMMDDHHMMSS}
@item @code{TIMESTAMP(12)} @tab @code{YYMMDDHHMMSS}
@item @code{TIMESTAMP(10)} @tab @code{YYMMDDHHMM}
@item @code{TIMESTAMP(8)} @tab @code{YYYYMMDD}
@item @code{TIMESTAMP(6)} @tab @code{YYMMDD}
@item @code{TIMESTAMP(4)} @tab @code{YYMM}
@item @code{TIMESTAMP(2)} @tab @code{YY}
@end multitable
All @code{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 @code{DATETIME}, @code{DATE}, and @code{TIMESTAMP} values using
any of a common set of formats:
@itemize @bullet
@item
As a string in either @code{'YYYY-MM-DD HH:MM:SS'} or @code{'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, @code{'98-12-31 11:30:45'}, @code{'98.12.31 11+30+45'},
@code{'98/12/31 11*30*45'}, and @code{'98@@12@@31 11^30^45'} are
equivalent.
@item
As a string in either @code{'YYYY-MM-DD'} or @code{'YY-MM-DD'} format.
A ``relaxed'' syntax is allowed here, too. For example, @code{'98-12-31'},
@code{'98.12.31'}, @code{'98/12/31'}, and @code{'98@@12@@31'} are
equivalent.
@item
As a string with no delimiters in either @code{'YYYYMMDDHHMMSS'} or
@code{'YYMMDDHHMMSS'} format, provided that the string makes sense as a
date. For example, @code{'19970523091528'} and @code{'970523091528'} are
interpreted as @code{'1997-05-23 09:15:28'}, but @code{'971122129015'} is
illegal (it has a nonsensical minute part) and becomes @code{'0000-00-00
00:00:00'}.
@item
As a string with no delimiters in either @code{'YYYYMMDD'} or @code{'YYMMDD'}
format, provided that the string makes sense as a date. For example,
@code{'19970523'} and @code{'970523'} are interpreted as
@code{'1997-05-23'}, but @code{'971332'} is illegal (it has nonsensical month
and day parts) and becomes @code{'0000-00-00'}.
@item
As a number in either @code{YYYYMMDDHHMMSS} or @code{YYMMDDHHMMSS}
format, provided that the number makes sense as a date. For example,
@code{19830905132800} and @code{830905132800} are interpreted as
@code{'1983-09-05 13:28:00'}.
@item
As a number in either @code{YYYYMMDD} or @code{YYMMDD}
format, provided that the number makes sense as a date. For example,
@code{19830905} and @code{830905} are interpreted as @code{'1983-09-05'}.
@item
As the result of a function that returns a value that is acceptable
in a @code{DATETIME}, @code{DATE}, or @code{TIMESTAMP} context, such as
@code{NOW()} or @code{CURRENT_DATE}.
@end itemize
Illegal @code{DATETIME}, @code{DATE}, or @code{TIMESTAMP} values are converted
to the ``zero'' value of the appropriate type (@code{'0000-00-00 00:00:00'},
@code{'0000-00-00'}, or @code{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
@code{10}. @code{'1979-6-9'} is the same as @code{'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 @code{10}. @code{'1979-10-30 1:2:3'} is the same as
@code{'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 @code{YYYYMMDD} or
@code{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
@code{YYMMDD} or @code{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.
@cindex non-delimited strings
@cindex strings, non-delimited
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 @code{'9903'},
thinking that will represent March, 1999, you will find that @strong{MySQL}
inserts a ``zero'' date into your table. This is because the year and month
values are @code{99} and @code{03}, but the day part is missing (zero), so
the value is not a legal date.
@code{TIMESTAMP} columns store legal values using the full precision with
which the value was specified, regardless of the display size. This has
several implications:
@itemize @bullet
@item
Always specify year, month, and day, even if your column types are
@code{TIMESTAMP(4)} or @code{TIMESTAMP(2)}. Otherwise, the value will not
be a legal date and @code{0} will be stored.
@item
If you use @code{ALTER TABLE} to widen a narrow @code{TIMESTAMP} column,
information will be displayed that previously was ``hidden''.
@item
Similarly, narrowing a @code{TIMESTAMP} column does not cause information to
be lost, except in the sense that less information is shown when the values
are displayed.
@item
Although @code{TIMESTAMP} values are stored to full precision, the only
function that operates directly on the underlying stored value is
@code{UNIX_TIMESTAMP()}. Other functions operate on the formatted retrieved
value. This means you cannot use functions such as @code{HOUR()} or
@code{SECOND()} unless the relevant part of the @code{TIMESTAMP} value is
included in the formatted value. For example, the @code{HH} part of a
@code{TIMESTAMP} column is not displayed unless the display size is at least
10, so trying to use @code{HOUR()} on shorter @code{TIMESTAMP} values
produces a meaningless result.
@end itemize
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:
@itemize @bullet
@item
If you assign a @code{DATE} value to a @code{DATETIME} or @code{TIMESTAMP}
object, the time part of the resulting value is set to @code{'00:00:00'},
because the @code{DATE} value contains no time information.
@item
If you assign a @code{DATETIME} or @code{TIMESTAMP} value to a @code{DATE}
object, the time part of the resulting value is deleted, because the
@code{DATE} type stores no time information.
@item
Remember that although @code{DATETIME}, @code{DATE}, and @code{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, @code{TIMESTAMP} values
cannot be earlier than @code{1970} or later than @code{2037}. This means
that a date such as @code{'1968-01-01'}, while legal as a @code{DATETIME} or
@code{DATE} value, is not a valid @code{TIMESTAMP} value and will be
converted to @code{0} if assigned to such an object.
@end itemize
@cindex problems, date values
@cindex date values, problems
Be aware of certain pitfalls when specifying date values:
@itemize @bullet
@item
The relaxed format allowed for values specified as strings can be deceiving.
For example, a value such as @code{'10:11:12'} might look like a time value
because of the @samp{:} delimiter, but if used in a date context will be
interpreted as the year @code{'2010-11-12'}. The value @code{'10:45:15'}
will be converted to @code{'0000-00-00'} because @code{'45'} is not a legal
month.
@item
Year values specified as two digits are ambiguous, because the century is
unknown. @strong{MySQL} interprets 2-digit year values using the following
rules:
@itemize @minus
@item
Year values in the range @code{00-69} are converted to @code{2000-2069}.
@item
Year year values in the range @code{70-99} are converted to @code{1970-1999}.
@end itemize
@end itemize
@tindex TIME
@node TIME, YEAR, DATETIME, Date and time types
@subsubsection The @code{TIME} Type
@strong{MySQL} retrieves and displays @code{TIME} values in @code{'HH:MM:SS'}
format (or @code{'HHH:MM:SS'} format for large hours values). @code{TIME}
values may range from @code{'-838:59:59'} to @code{'838:59:59'}. The reason
the hours part may be so large is that the @code{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 @code{TIME} values in a variety of formats:
@itemize @bullet
@item
As a string in @code{'D HH:MM:SS.fraction'} format. (Note that
@strong{MySQL} doesn't yet store the fraction for the time column). One
can also use one of the following ``relaxed'' syntax:
@code{HH:MM:SS.fraction}, @code{HH:MM:SS}, @code{HH:MM}, @code{D HH:MM:SS},
@code{D HH:MM}, @code{D HH} or @code{SS}. Here @code{D} is days between 0-33.
@item
As a string with no delimiters in @code{'HHMMSS'} format, provided that
it makes sense as a time. For example, @code{'101112'} is understood as
@code{'10:11:12'}, but @code{'109712'} is illegal (it has a nonsensical
minute part) and becomes @code{'00:00:00'}.
@item
As a number in @code{HHMMSS} format, provided that it makes sense as a time.
For example, @code{101112} is understood as @code{'10:11:12'}. The following
alternative formats are also understood: @code{SS}, @code{MMSS},@code{HHMMSS},
@code{HHMMSS.fraction}. Note that @strong{MySQL} doesn't yet store the
fraction part.
@item
As the result of a function that returns a value that is acceptable
in a @code{TIME} context, such as @code{CURRENT_TIME}.
@end itemize
For @code{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 @code{10}. @code{'8:3:2'} is the same as
@code{'08:03:02'}.
Be careful about assigning ``short'' @code{TIME} values to a @code{TIME}
column. Without semicolon, @strong{MySQL} interprets values using the
assumption that the rightmost digits represent seconds. (@strong{MySQL}
interprets @code{TIME} values as elapsed time rather than as time of
day.) For example, you might think of @code{'1112'} and @code{1112} as
meaning @code{'11:12:00'} (12 minutes after 11 o'clock), but
@strong{MySQL} interprets them as @code{'00:11:12'} (11 minutes, 12 seconds).
Similarly, @code{'12'} and @code{12} are interpreted as @code{'00:00:12'}.
@code{TIME} values with semicolon, instead, are always treated as
time of the day. That is @code{'11:12'} will mean @code{'11:12:00'},
not @code{'00:11:12'}.
Values that lie outside the @code{TIME} range
but are otherwise legal are clipped to the appropriate
endpoint of the range. For example, @code{'-850:00:00'} and
@code{'850:00:00'} are converted to @code{'-838:59:59'} and
@code{'838:59:59'}.
Illegal @code{TIME} values are converted to @code{'00:00:00'}. Note that
because @code{'00:00:00'} is itself a legal @code{TIME} value, there is no way
to tell, from a value of @code{'00:00:00'} stored in a table, whether the
original value was specified as @code{'00:00:00'} or whether it was illegal.
@tindex YEAR
@node YEAR, , TIME, Date and time types
@subsubsection The @code{YEAR} Type
The @code{YEAR} type is a 1-byte type used for representing years.
@strong{MySQL} retrieves and displays @code{YEAR} values in @code{YYYY}
format. The range is @code{1901} to @code{2155}.
You can specify @code{YEAR} values in a variety of formats:
@itemize @bullet
@item
As a four-digit string in the range @code{'1901'} to @code{'2155'}.
@item
As a four-digit number in the range @code{1901} to @code{2155}.
@item
As a two-digit string in the range @code{'00'} to @code{'99'}. Values in the
ranges @code{'00'} to @code{'69'} and @code{'70'} to @code{'99'} are
converted to @code{YEAR} values in the ranges @code{2000} to @code{2069} and
@code{1970} to @code{1999}.
@item
As a two-digit number in the range @code{1} to @code{99}. Values in the
ranges @code{1} to @code{69} and @code{70} to @code{99} are converted to
@code{YEAR} values in the ranges @code{2001} to @code{2069} and @code{1970}
to @code{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 @code{2000}. You
@emph{must} specify it as a string @code{'0'} or @code{'00'} or it will be
interpreted as @code{0000}.
@item
As the result of a function that returns a value that is acceptable
in a @code{YEAR} context, such as @code{NOW()}.
@end itemize
Illegal @code{YEAR} values are converted to @code{0000}.
@cindex types, strings
@cindex string types
@node String types, Choosing types, Date and time types, Column types
@subsection String Types
The string types are @code{CHAR}, @code{VARCHAR}, @code{BLOB}, @code{TEXT},
@code{ENUM}, and @code{SET}.
@tindex CHAR
@tindex VARCHAR
@menu
* CHAR:: The @code{CHAR} and @code{VARCHAR} types
* BLOB:: The @code{BLOB} and @code{TEXT} types
* ENUM:: The @code{ENUM} type
* SET:: The @code{SET} type
@end menu
@node CHAR, BLOB, String types, String types
@subsubsection The @code{CHAR} and @code{VARCHAR} Types
The @code{CHAR} and @code{VARCHAR} types are similar, but differ in the
way they are stored and retrieved.
The length of a @code{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 @strong{MySQL} Version 3.23, the length of @code{CHAR} may be 0 to 255.)
When @code{CHAR} values are stored, they are right-padded with spaces to the
specified length. When @code{CHAR} values are retrieved, trailing spaces are
removed.
Values in @code{VARCHAR} columns are variable-length strings. You can
declare a @code{VARCHAR} column to be any length between 1 and 255, just as
for @code{CHAR} columns. However, in contrast to @code{CHAR}, @code{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 @code{CHAR} or @code{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 @code{CHAR(4)}
and @code{VARCHAR(4)} columns:
@c Need to use @(space) to make sure second column values retain spacing
@c in output for table below.
@multitable @columnfractions .2 .15 .2 .2 .25
@item @strong{Value} @tab @code{CHAR(4)} @tab @strong{Storage required} @tab @code{VARCHAR(4)} @tab @strong{Storage required}
@item @code{''} @tab @code{'@ @ @ @ '} @tab 4 bytes @tab @code{''} @tab 1 byte
@item @code{'ab'} @tab @code{'ab@ @ '} @tab 4 bytes @tab @code{'ab'} @tab 3 bytes
@item @code{'abcd'} @tab @code{'abcd'} @tab 4 bytes @tab @code{'abcd'} @tab 5 bytes
@item @code{'abcdefgh'} @tab @code{'abcd'} @tab 4 bytes @tab @code{'abcd'} @tab 5 bytes
@end multitable
The values retrieved from the @code{CHAR(4)} and @code{VARCHAR(4)} columns
will be the same in each case, because trailing spaces are removed from
@code{CHAR} columns upon retrieval.
Values in @code{CHAR} and @code{VARCHAR} columns are sorted and compared
in case-insensitive fashion, unless the @code{BINARY} attribute was
specified when the table was created. The @code{BINARY} attribute means
that column values are sorted and compared in case-sensitive fashion
according to the ASCII order of the machine where the @strong{MySQL}
server is running. @code{BINARY} doesn't affect how the column is stored
or retrieved.
The @code{BINARY} attribute is sticky. This means that if a column marked
@code{BINARY} is used in an expression, the whole expression is compared as a
@code{BINARY} value.
@strong{MySQL} may silently change the type of a @code{CHAR} or @code{VARCHAR}
column at table creation time.
@xref{Silent column changes}.
@tindex BLOB
@tindex TEXT
@node BLOB, ENUM, CHAR, String types
@subsubsection The @code{BLOB} and @code{TEXT} Types
A @code{BLOB} is a binary large object that can hold a variable amount of
data. The four @code{BLOB} types @code{TINYBLOB}, @code{BLOB},
@code{MEDIUMBLOB}, and @code{LONGBLOB} differ only in the maximum length of
the values they can hold.
@xref{Storage requirements}.
The four @code{TEXT} types @code{TINYTEXT}, @code{TEXT}, @code{MEDIUMTEXT},
and @code{LONGTEXT} correspond to the four @code{BLOB} types and have the
same maximum lengths and storage requirements. The only difference between
@code{BLOB} and @code{TEXT} types is that sorting and comparison is performed
in case-sensitive fashion for @code{BLOB} values and case-insensitive fashion
for @code{TEXT} values. In other words, a @code{TEXT} is a case-insensitive
@code{BLOB}.
If you assign a value to a @code{BLOB} or @code{TEXT} column that exceeds
the column type's maximum length, the value is truncated to fit.
In most respects, you can regard a @code{TEXT} column as a @code{VARCHAR}
column that can be as big as you like. Similarly, you can regard a
@code{BLOB} column as a @code{VARCHAR BINARY} column. The differences are:
@itemize @bullet
@item
You can have indexes on @code{BLOB} and @code{TEXT} columns with
@strong{MySQL} Version 3.23.2 and newer. Older versions of
@strong{MySQL} did not support this.
@item
There is no trailing-space removal for @code{BLOB} and @code{TEXT} columns
when values are stored, as there is for @code{VARCHAR} columns.
@item
@cindex default values, @code{BLOB} and @code{TEXT} columns
@cindex @code{BLOB} columns, default values
@cindex @code{TEXT} columns, default values
@code{BLOB} and @code{TEXT} columns cannot have @code{DEFAULT} values.
@end itemize
@strong{MyODBC} defines @code{BLOB} values as @code{LONGVARBINARY} and
@code{TEXT} values as @code{LONGVARCHAR}.
Because @code{BLOB} and @code{TEXT} values may be extremely long, you
may run up against some constraints when using them:
@itemize @bullet
@item
If you want to use @code{GROUP BY} or @code{ORDER BY} on a @code{BLOB} or
@code{TEXT} column, you must convert the column value into a fixed-length
object. The standard way to do this is with the @code{SUBSTRING}
function. For example:
@example
mysql> select comment from tbl_name,substring(comment,20) as substr
ORDER BY substr;
@end example
If you don't do this, only the first @code{max_sort_length} bytes of the
column are used when sorting. The default value of @code{max_sort_length} is
1024; this value can be changed using the @code{-O} option when starting the
@code{mysqld} server. You can group on an expression involving @code{BLOB} or
@code{TEXT} values by specifying the column position or by using an alias:
@example
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;
@end example
@item
The maximum size of a @code{BLOB} or @code{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. @xref{Server parameters}.
@end itemize
Note that each @code{BLOB} or @code{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.
@tindex ENUM
@node ENUM, SET, BLOB, String types
@subsubsection The @code{ENUM} Type
An @code{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 (@code{""}) or @code{NULL} under
certain circumstances:
@itemize @bullet
@item
If you insert an invalid value into an @code{ENUM} (that is, a string not
present in the list of allowed values), the empty string is inserted
instead as a special error value.
@item
If an @code{ENUM} is declared @code{NULL}, @code{NULL} is also a legal value
for the column, and the default value is @code{NULL}. If an @code{ENUM} is
declared @code{NOT NULL}, the default value is the first element of the
list of allowed values.
@end itemize
Each enumeration value has an index:
@itemize @bullet
@item
Values from the list of allowable elements in the column specification are
numbered beginning with 1.
@item
The index value of the empty string error value is 0. This means that you
can use the following @code{SELECT} statement to find rows into which invalid
@code{ENUM} values were assigned:
@example
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
@end example
@item
The index of the @code{NULL} value is @code{NULL}.
@end itemize
For example, a column specified as @code{ENUM("one", "two", "three")} can
have any of the values shown below. The index of each value is also shown:
@multitable @columnfractions .2 .8
@item @strong{Value} @tab @strong{Index}
@item @code{NULL} @tab @code{NULL}
@item @code{""} @tab 0
@item @code{"one"} @tab 1
@item @code{"two"} @tab 2
@item @code{"three"} @tab 3
@end multitable
An enumeration can have a maximum of 65535 elements.
Lettercase is irrelevant when you assign values to an @code{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 @code{ENUM} in a numeric context, the column value's
index is returned. For example, you can retrieve numeric values from
an @code{ENUM} column like this:
@example
mysql> SELECT enum_col+0 FROM tbl_name;
@end example
If you store a number into an @code{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 @code{LOAD DATA}, which treats all
input as strings.)
@code{ENUM} values are sorted according to the order in which the enumeration
members were listed in the column specification. (In other words,
@code{ENUM} values are sorted according to their index numbers.) For
example, @code{"a"} sorts before @code{"b"} for @code{ENUM("a", "b")}, but
@code{"b"} sorts before @code{"a"} for @code{ENUM("b", "a")}. The empty
string sorts before non-empty strings, and @code{NULL} values sort before
all other enumeration values.
If you want to get all possible values for an @code{ENUM} column, you should
use: @code{SHOW COLUMNS FROM table_name LIKE enum_column_name} and parse
the @code{ENUM} definition in the second column.
@tindex SET
@node SET, , ENUM, String types
@subsubsection The @code{SET} Type
A @code{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. @code{SET} column values that consist of multiple set members
are specified with members separated by commas (@samp{,}). A consequence of
this is that @code{SET} member values cannot themselves contain commas.
For example, a column specified as @code{SET("one", "two") NOT NULL} can have
any of these values:
@example
""
"one"
"two"
"one,two"
@end example
A @code{SET} can have a maximum of 64 different members.
@strong{MySQL} stores @code{SET} values numerically, with the low-order bit
of the stored value corresponding to the first set member. If you retrieve a
@code{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 @code{SET} column like this:
@example
mysql> SELECT set_col+0 FROM tbl_name;
@end example
If a number is stored into a @code{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
@code{SET("a","b","c","d")}. Then the members have the following bit
values:
@multitable @columnfractions .2 .2 .6
@item @code{SET} @strong{member} @tab @strong{Decimal value} @tab @strong{Binary value}
@item @code{a} @tab @code{1} @tab @code{0001}
@item @code{b} @tab @code{2} @tab @code{0010}
@item @code{c} @tab @code{4} @tab @code{0100}
@item @code{d} @tab @code{8} @tab @code{1000}
@end multitable
If you assign a value of @code{9} to this column, that is @code{1001} in
binary, so the first and fourth @code{SET} value members @code{"a"} and
@code{"d"} are selected and the resulting value is @code{"a,d"}.
For a value containing more than one @code{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
@code{SET("a","b","c","d")}, then @code{"a,d"}, @code{"d,a"}, and
@code{"d,a,a,d,d"} will all appear as @code{"a,d"} when retrieved.
@code{SET} values are sorted numerically. @code{NULL} values sort before
non-@code{NULL} @code{SET} values.
Normally, you perform a @code{SELECT} on a @code{SET} column using
the @code{LIKE} operator or the @code{FIND_IN_SET()} function:
@example
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
@end example
But the following will also work:
@example
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
mysql> SELECT * FROM tbl_name WHERE set_col & 1;
@end example
The first of these statements looks for an exact match. The second looks
for values containing the first set member.
If you want to get all possible values for a @code{SET} column, you should
use: @code{SHOW COLUMNS FROM table_name LIKE set_column_name} and parse
the @code{SET} definition in the second column.
@cindex types, columns
@cindex choosing types
@node Choosing types, Indexes, String types, Column types
@subsection Choosing the Right Type for a Column
For the most efficient use of storage, try to use the most precise type in
all cases. For example, if an integer column will be used for values in the
range between @code{1} and @code{99999}, @code{MEDIUMINT UNSIGNED} is the
best type.
Accurate representation of monetary values is a common problem. In
@strong{MySQL}, you should use the @code{DECIMAL} type. This is stored as
a string, so no loss of accuracy should occur. If accuracy is not
too important, the @code{DOUBLE} type may also be good enough.
For high precision, you can always convert to a fixed-point type stored
in a @code{BIGINT}. This allows you to do all calculations with integers
and convert results back to floating-point values only when necessary.
@cindex indexes, columns
@cindex columns, indexes
@cindex keys
@node Indexes, Multiple-column indexes, Choosing types, Column types
@subsection Column Indexes
All @strong{MySQL} column types can be indexed. Use of indexes on the
relevant columns is the best way to improve the performance of @code{SELECT}
operations.
The maximum number of keys and the maximum index length is defined per
table handler. @xref{Table types}. You can with all table handlers have
at least 16 keys and a total index length of at least 256 bytes.
For @code{CHAR} and @code{VARCHAR} columns, you can index a prefix of a
column. This is much faster and requires less disk space than indexing the
whole column. The syntax to use in the @code{CREATE TABLE} statement to
index a column prefix looks like this:
@example
KEY index_name (col_name(length))
@end example
The example below creates an index for the first 10 characters of the
@code{name} column:
@example
mysql> CREATE TABLE test (
name CHAR(200) NOT NULL,
KEY index_name (name(10)));
@end example
For @code{BLOB} and @code{TEXT} columns, you must index a prefix of the
column. You cannot index the entire column.
In @strong{MySQL} Version 3.23.23 or later, you can also create special
@strong{FULLTEXT} indexes. They are used for full-text search. Only the
@code{MyISAM} table type supports @code{FULLTEXT} indexes. They can be
created only from @code{VARCHAR} and @code{TEXT} columns.
Indexing always happens over the entire column and partial indexing is not
supported. See @ref{MySQL full-text search} for details.
@cindex multi-column indexes
@cindex indexes, multi-column
@cindex keys, multi-column
@node Multiple-column indexes, Other-vendor column types, Indexes, Column types
@subsection Multiple-column Indexes
@strong{MySQL} can create indexes on multiple columns. An index may
consist of up to 15 columns. (On @code{CHAR} and @code{VARCHAR} columns you
can also use a prefix of the column as a part of an index).
A multiple-column index can be considered a sorted array containing values
that are created by concatenating the values of the indexed columns.
@strong{MySQL} uses multiple-column indexes in such a way that queries are
fast when you specify a known quantity for the first column of the index in a
@code{WHERE} clause, even if you don't specify values for the other columns.
Suppose a table is created using the following specification:
@example
mysql> CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name));
@end example
Then the index @code{name} is an index over @code{last_name} and
@code{first_name}. The index will be used for queries that specify
values in a known range for @code{last_name}, or for both @code{last_name}
and @code{first_name}.
Therefore, the @code{name} index will be used in the following queries:
@example
mysql> SELECT * FROM test WHERE last_name="Widenius";
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND first_name="Michael";
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND (first_name="Michael" OR first_name="Monty");
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND first_name >="M" AND first_name < "N";
@end example
However, the @code{name} index will NOT be used in the following queries:
@example
mysql> SELECT * FROM test WHERE first_name="Michael";
mysql> SELECT * FROM test WHERE last_name="Widenius"
OR first_name="Michael";
@end example
For more information on the manner in which @strong{MySQL} uses indexes to
improve query performance, see @ref{MySQL indexes, , @strong{MySQL}
indexes}.
@cindex types, portability
@cindex portability, types
@cindex columns, other types
@node Other-vendor column types, , Multiple-column indexes, Column types
@subsection Using Column Types from Other Database Engines
To make it easier to use code written for SQL implementations from other
vendors, @strong{MySQL} maps column types as shown in the table below. These
mappings make it easier to move table definitions from other database engines
to @strong{MySQL}:
@multitable @columnfractions .4 .6
@item @strong{Other vendor type} @tab @strong{MySQL type}
@item @code{BINARY(NUM)} @tab @code{CHAR(NUM) BINARY}
@item @code{CHAR VARYING(NUM)} @tab @code{VARCHAR(NUM)}
@item @code{FLOAT4} @tab @code{FLOAT}
@item @code{FLOAT8} @tab @code{DOUBLE}
@item @code{INT1} @tab @code{TINYINT}
@item @code{INT2} @tab @code{SMALLINT}
@item @code{INT3} @tab @code{MEDIUMINT}
@item @code{INT4} @tab @code{INT}
@item @code{INT8} @tab @code{BIGINT}
@item @code{LONG VARBINARY} @tab @code{MEDIUMBLOB}
@item @code{LONG VARCHAR} @tab @code{MEDIUMTEXT}
@item @code{MIDDLEINT} @tab @code{MEDIUMINT}
@item @code{VARBINARY(NUM)} @tab @code{VARCHAR(NUM) BINARY}
@end multitable
Column type mapping occurs at table creation time. If you create a table
with types used by other vendors and then issue a @code{DESCRIBE tbl_name}
statement, @strong{MySQL} reports the table structure using the equivalent
@strong{MySQL} types.
@cindex functions for @code{SELECT} and @code{WHERE} clauses
@node Functions, CREATE DATABASE, Column types, Reference
@section Functions for Use in @code{SELECT} and @code{WHERE} Clauses
A @code{select_expression} or @code{where_definition} in a SQL statement
can consist of any expression using the functions described below.
An expression that contains @code{NULL} always produces a @code{NULL} value
unless otherwise indicated in the documentation for the operators and
functions involved in the expression.
@strong{NOTE:} There must be no whitespace between a function name and the
parenthesis following it. This helps the @strong{MySQL} parser distinguish
between function calls and references to tables or columns that happen to
have the same name as a function. Spaces around arguments are permitted,
though.
You can force @strong{MySQL} to accept spaces after the function name by
starting @code{mysqld} with @code{--ansi} or using the
@code{CLIENT_IGNORE_SPACE} to @code{mysql_connect()}, but in this case all
function names will become reserved words. @xref{ANSI mode}.
@need 2000
For the sake of brevity, examples display the output from the @code{mysql}
program in abbreviated form. So this:
@example
mysql> select MOD(29,9);
1 rows in set (0.00 sec)
+-----------+
| mod(29,9) |
+-----------+
| 2 |
+-----------+
@end example
is displayed like this:
@example
mysql> select MOD(29,9);
-> 2
@end example
@menu
* Grouping functions:: Grouping functions
* Arithmetic functions:: Normal arithmetic operations
* Bit functions:: Bit functions
* Logical functions:: Logical operations
* Comparison functions:: Comparison operators
* String comparison functions:: String comparison functions
* Casts:: Cast operators
* Control flow functions:: Control flow functions
* Mathematical functions:: Mathematical functions
* String functions:: String functions
* Date and time functions:: Date and time functions
* Miscellaneous functions:: Miscellaneous functions
* Group by functions:: Functions for @code{GROUP BY} clause
@end menu
@cindex functions, grouping
@cindex grouping, expressions
@node Grouping functions, Arithmetic functions, Functions, Functions
@subsection Grouping Functions
@table @code
@findex () (parentheses)
@findex parentheses ( and )
@item ( ... )
Parentheses. Use these to force the order of evaluation in an expression:
@example
mysql> select 1+2*3;
-> 7
mysql> select (1+2)*3;
-> 9
@end example
@end table
@node Arithmetic functions, Bit functions, Grouping functions, Functions
@subsection Normal Arithmetic Operations
The usual arithmetic operators are available. Note that in the case of
@samp{-}, @samp{+}, and @samp{*}, the result is calculated with
@code{BIGINT} (64-bit) precision if both arguments are integers!
@cindex operations, arithmetic
@cindex arithmetic expressions
@table @code
@findex + (addition)
@findex addition (+)
@item +
Addition:
@example
mysql> select 3+5;
-> 8
@end example
@findex - (subtraction)
@findex subtraction (-)
@item -
Subtraction:
@example
mysql> select 3-5;
-> -2
@end example
@findex * (multiplication)
@findex multiplication (*)
@item *
Multiplication:
@example
mysql> select 3*5;
-> 15
mysql> select 18014398509481984*18014398509481984.0;
-> 324518553658426726783156020576256.0
mysql> select 18014398509481984*18014398509481984;
-> 0
@end example
The result of the last expression is incorrect because the result of the integer
multiplication exceeds the 64-bit range of @code{BIGINT} calculations.
@findex / (division)
@findex division (/)
@item /
Division:
@example
mysql> select 3/5;
-> 0.60
@end example
Division by zero produces a @code{NULL} result:
@example
mysql> select 102/(1-1);
-> NULL
@end example
A division will be calculated with @code{BIGINT} arithmetic only if performed
in a context where its result is converted to an integer!
@end table
@findex arithmetic functions
@findex bit functions
@findex functions, arithmetic
@findex functions, bit
@node Bit functions, Logical functions, Arithmetic functions, Functions
@subsection Bit Functions
@strong{MySQL} uses @code{BIGINT} (64-bit) arithmetic for bit operations, so
these operators have a maximum range of 64 bits.
@table @code
@findex | (bitwise OR)
@findex OR, bitwise
@item |
Bitwise OR:
@example
mysql> select 29 | 15;
-> 31
@end example
@findex & (bitwise AND)
@findex AND, bitwise
@item &
Bitwise AND:
@example
mysql> select 29 & 15;
-> 13
@end example
@findex << (left shift)
@item <<
Shifts a longlong (@code{BIGINT}) number to the left:
@example
mysql> select 1 << 2
-> 4
@end example
@findex >> (right shift)
@item >>
Shifts a longlong (@code{BIGINT}) number to the right:
@example
mysql> select 4 >> 2
-> 1
@end example
@findex ~
@item ~
Invert all bits:
@example
mysql> select 5 & ~1
-> 4
@end example
@findex BIT_COUNT()
@item BIT_COUNT(N)
Returns the number of bits that are set in the argument @code{N}:
@example
mysql> select BIT_COUNT(29);
-> 4
@end example
@end table
@findex Logical functions
@findex Functions, logical
@node Logical functions, Comparison functions, Bit functions, Functions
@subsection Logical Operations
All logical functions return @code{1} (TRUE), @code{0} (FALSE) or
@code{NULL} (unknown, which is in most cases the same as FALSE):
@table @code
@findex NOT, logical
@findex ! (logical NOT)
@item NOT
@itemx !
Logical NOT. Returns @code{1} if the argument is @code{0}, otherwise returns
@code{0}.
Exception: @code{NOT NULL} returns @code{NULL}:
@example
mysql> select NOT 1;
-> 0
mysql> select NOT NULL;
-> NULL
mysql> select ! (1+1);
-> 0
mysql> select ! 1+1;
-> 1
@end example
The last example returns @code{1} because the expression evaluates
the same way as @code{(!1)+1}.
@findex OR, logical
@findex || (logical OR)
@item OR
@itemx ||
Logical OR. Returns @code{1} if either argument is not @code{0} and not
@code{NULL}:
@example
mysql> select 1 || 0;
-> 1
mysql> select 0 || 0;
-> 0
mysql> select 1 || NULL;
-> 1
@end example
@findex AND, logical
@findex && (logical AND)
@item AND
@itemx &&
Logical AND. Returns @code{0} if either argument is @code{0} or @code{NULL},
otherwise returns @code{1}:
@example
mysql> select 1 && NULL;
-> 0
mysql> select 1 && 0;
-> 0
@end example
@end table
@cindex casts
@cindex type conversions
@findex comparison operators
@node Comparison functions, String comparison functions, Logical functions, Functions
@subsection Comparison Operators
Comparison operations result in a value of @code{1} (TRUE), @code{0} (FALSE),
or @code{NULL}. These functions work for both numbers and strings. Strings
are automatically converted to numbers and numbers to strings as needed (as
in Perl).
@strong{MySQL} performs comparisons using the following
rules:
@itemize @bullet
@item
If one or both arguments are @code{NULL}, the result of the comparison
is @code{NULL}, except for the @code{<=>} operator.
@item
If both arguments in a comparison operation are strings, they are compared as
strings.
@item
If both arguments are integers, they are compared as integers.
@item
Hexadecimal values are treated as binary strings if not compared to a number.
@item
@cindex ODBC compatibility
@cindex compatibility, with ODBC
If one of the arguments is a @code{TIMESTAMP} or @code{DATETIME} column and
the other argument is a constant, the constant is converted
to a timestamp before the comparison is performed. This is done to be more
ODBC-friendly.
@item
In all other cases, the arguments are compared as floating-point (real)
numbers.
@end itemize
By default, string comparisons are done in case-independent fashion using the
current character set (ISO-8859-1 Latin1 by default, which also works
excellently for English).
The examples below illustrate conversion of strings to numbers for comparison
operations:
@example
mysql> SELECT 1 > '6x';
-> 0
mysql> SELECT 7 > '6x';
-> 1
mysql> SELECT 0 > 'x6';
-> 0
mysql> SELECT 0 = 'x6';
-> 1
@end example
@table @code
@findex = (equal)
@findex equal (=)
@item =
Equal:
@example
mysql> select 1 = 0;
-> 0
mysql> select '0' = 0;
-> 1
mysql> select '0.0' = 0;
-> 1
mysql> select '0.01' = 0;
-> 0
mysql> select '.01' = 0.01;
-> 1
@end example
@findex <> (not equal)
@findex not equal (<>)
@findex != (not equal)
@findex not equal (!=)
@item <>
@itemx !=
Not equal:
@example
mysql> select '.01' <> '0.01';
-> 1
mysql> select .01 <> '0.01';
-> 0
mysql> select 'zapp' <> 'zappp';
-> 1
@end example
@findex <= (less than or equal)
@findex less than or equal (<=)
@item <=
Less than or equal:
@example
mysql> select 0.1 <= 2;
-> 1
@end example
@findex < (less than)
@findex less than (<)
@item <
Less than:
@example
mysql> select 2 <= 2;
-> 1
@end example
@findex >= (greater than or equal)
@findex greater than or equal (>=)
@item >=
Greater than or equal:
@example
mysql> select 2 >= 2;
-> 1
@end example
@findex > (greater than)
@findex greater than (>)
@item >
Greater than:
@example
mysql> select 2 > 2;
-> 0
@end example
@findex <=> (Equal to)
@item <=>
Null safe equal:
@example
mysql> select 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1 1 0
@end example
@findex IS NULL
@findex IS NOT NULL
@item IS NULL
@itemx IS NOT NULL
Test whether or not a value is or is not @code{NULL}:
@example
mysql> select 1 IS NULL, 0 IS NULL, NULL IS NULL:
-> 0 0 1
mysql> select 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
-> 1 1 0
@end example
@findex BETWEEN ... AND
@item expr BETWEEN min AND max
If @code{expr} is greater than or equal to @code{min} and @code{expr} is
less than or equal to @code{max}, @code{BETWEEN} returns @code{1},
otherwise it returns @code{0}. This is equivalent to the expression
@code{(min <= expr AND expr <= max)} if all the arguments are of the
same type. The first argument (@code{expr}) determines how the
comparison is performed as follows:
@itemize @bullet
@item
If @code{expr} is a @code{TIMESTAMP}, @code{DATE}, or @code{DATETIME}
column, @code{MIN()} and @code{MAX()} are formatted to the same format if
they are constants.
@item
If @code{expr} is a case-insensitive string expression, a case-insensitive
string comparison is done.
@item
If @code{expr} is a case-sensitive string expression, a case-sensitive
string comparison is done.
@item
If @code{expr} is an integer expression, an integer comparison is done.
@item
Otherwise, a floating-point (real) comparison is done.
@end itemize
@example
mysql> select 1 BETWEEN 2 AND 3;
-> 0
mysql> select 'b' BETWEEN 'a' AND 'c';
-> 1
mysql> select 2 BETWEEN 2 AND '3';
-> 1
mysql> select 2 BETWEEN 2 AND 'x-3';
-> 0
@end example
@findex IN
@item expr IN (value,...)
Returns @code{1} if @code{expr} is any of the values in the @code{IN} list,
else returns @code{0}. If all values are constants, then all values are
evaluated according to the type of @code{expr} and sorted. The search for the
item is then done using a binary search. This means @code{IN} is very quick
if the @code{IN} value list consists entirely of constants. If @code{expr}
is a case-sensitive string expression, the string comparison is performed in
case-sensitive fashion:
@example
mysql> select 2 IN (0,3,5,'wefwf');
-> 0
mysql> select 'wefwf' IN (0,3,5,'wefwf');
-> 1
@end example
@findex NOT IN
@item expr NOT IN (value,...)
Same as @code{NOT (expr IN (value,...))}.
@findex ISNULL()
@item ISNULL(expr)
If @code{expr} is @code{NULL}, @code{ISNULL()} returns @code{1}, otherwise
it returns @code{0}:
@example
mysql> select ISNULL(1+1);
-> 0
mysql> select ISNULL(1/0);
-> 1
@end example
Note that a comparison of @code{NULL} values using @code{=} will always be
false!
@findex COALESCE()
@item COALESCE(list)
Returns first non-@code{NULL} element in list:
@example
mysql> select COALESCE(NULL,1);
-> 1
mysql> select COALESCE(NULL,NULL,NULL);
-> NULL
@end example
@findex INTERVAL()
@item INTERVAL(N,N1,N2,N3,...)
Returns @code{0} if @code{N} < @code{N1}, @code{1} if @code{N} < @code{N2}
and so on. All arguments are treated as integers. It is required that
@code{N1} < @code{N2} < @code{N3} < @code{...} < @code{Nn} for this function
to work correctly. This is because a binary search is used (very fast):
@example
mysql> select INTERVAL(23, 1, 15, 17, 30, 44, 200);
-> 3
mysql> select INTERVAL(10, 1, 10, 100, 1000);
-> 2
mysql> select INTERVAL(22, 23, 30, 44, 200);
-> 0
@end example
@end table
@findex string comparison functions
@findex functions, string comparison
@node String comparison functions, Casts, Comparison functions, Functions
@subsection String Comparison Functions
@cindex case sensitivity, in string comparisons
@cindex string comparisons, case sensitivity
Normally, if any expression in a string comparison is case sensitive, the
comparison is performed in case-sensitive fashion.
@table @code
@findex LIKE
@item expr LIKE pat [ESCAPE 'escape-char']
Pattern matching using
SQL simple regular expression comparison. Returns @code{1} (TRUE) or @code{0}
(FALSE). With @code{LIKE} you can use the following two wild-card characters
in the pattern:
@multitable @columnfractions .1 .9
@item @code{%} @tab Matches any number of characters, even zero characters
@item @code{_} @tab Matches exactly one character
@end multitable
@example
mysql> select 'David!' LIKE 'David_';
-> 1
mysql> select 'David!' LIKE '%D%v%';
-> 1
@end example
To test for literal instances of a wild-card character, precede the character
with the escape character. If you don't specify the @code{ESCAPE} character,
@samp{} is assumed:
@multitable @columnfractions .1 .9
@item @code{%} @tab Matches one @code{%} character
@item @code{_} @tab Matches one @code{_} character
@end multitable
@example
mysql> select 'David!' LIKE 'David_';
-> 0
mysql> select 'David_' LIKE 'David_';
-> 1
@end example
To specify a different escape character, use the @code{ESCAPE} clause:
@example
mysql> select 'David_' LIKE 'David|_' ESCAPE '|';
-> 1
@end example
@code{LIKE} is allowed on numeric expressions! (This is a @strong{MySQL}
extension to the ANSI SQL @code{LIKE}.)
@example
mysql> select 10 LIKE '1%';
-> 1
@end example
Note: Because @strong{MySQL} uses the C escape syntax in strings (for example,
@samp{n}), you must double any @samp{} that you use in your @code{LIKE}
strings. For example, to search for @samp{n}, specify it as @samp{\n}. To
search for @samp{}, specify it as @samp{\\} (the backslashes are stripped
once by the parser and another time when the pattern match is done, leaving
a single backslash to be matched).