This is the MySQL reference manual. This version documents the 3.21.31 version of MySQL.
MySQL is a basically free, very fast SQL database server. See section 3 Licensing or When do I have/want to pay for MySQL?.
(Debian note: This manual was compiled for the Debian GNU/Linux mysql-doc package, and includes some additional notes for Debian users. These additions are all clearly marked as Debian note.)
The latest information about MySQL is found at the MySQL Home page
To see what it can do. See section 1.4 The main features of MySQL.
For installation instructions See section 4 Compiling and installing MySQL. For tips on how to port MySQL to new machines/operating systems See section G Comments on porting to other systems..
If you have any suggestions concerning additions or corrections to this
manual, please send them to the MySQL mailing list
documentation suggestion: [Insert Topic Here].
See section 2.1 Subscribing to/un-subscribing from the MySQL mailing list..
See section 8.5.2 Upgrading to 3.21 from a 3.20 version, for information about upgrading from a 3.20 release.
For examples of SQL and benchmarking information see the `bench' directory.
For future plans See section F List of things we want to add to MySQL in the future (The TODO)..
A history of new features/bug fixes See section D MySQL change history.
For the currently known bugs/misfeatures (known errors) See section E Known errors and design deficiencies in MySQL.
For A list of all the contributors to this product See section C Who has helped to make MySQL..
IMPORTANT:
Send bug (error) reports, questions and comments to the mailing list at
Please use the mysqlbug script when posting bug reports or
questions about MySQL. mysqlbug will gather some
information about your system and start your editor with a form in which
you can describe your problem. Bug reports might be silently ignored by
the MySQL maintainers if there is not a good reason included in
the report as to why mysqlbug has not been used. A report that says
'MySQL does not work for me. Why?' is not consider a valid bug report.
The mysqlbug script can be found in the `scripts' directory in the distribution, that is `there-you-installed-mysql/scripts'.
MySQL is a SQL (Structured Query Language) database server.
SQL is the most popular database language in the world. MySQL
is a client server implementation that consists of a server daemon
mysqld and many different client programs/libraries.
The main goals of MySQL are speed and robustness.
The base upon which MySQL is built is a set of routines that have been used in a highly demanding production environment for many years. While MySQL is still in development it already offers a rich and highly useful function set.
The official way to pronounce MySQL is 'My Ess Que Ell' (Not MY-SEQUEL).
This manual is currently available in TeXInfo, Raw text, Info and HTML versions. A PostScript version as available do download separately because of its size.
The primary document is the TeXInfo file. The HTML version is
automatically produced with a modified texi2html. The ASCII and
info version are produced with makeinfo. The Postscript version
is produced using texi2dvi and dvips.
This manual is written and maintained by David Axmark, Michael (Monty) Widenius and Kim Aldale. For other contributors See section B Contributed programs.
We once started off with the intention to use mSQL to connect to
our own fast low level (ISAM) tables. However, after some testing we
came to the conclusion that mSQL was not fast or flexible enough
for our needs. This resulted in a new SQL interface to our database but
with almost the same API interface as mSQL. This API was chosen
to ease porting of third-party code.
It is not perfectly clear where the name MySQL derives from. Our base directory and a large amount of our libraries and tools have had the prefix 'my' for well over 10 years. However, Monty's daughter (some years younger) is also named My. So which of the two gave its name to MySQL is still a mystery, even for us.
FLOAT, DOUBLE, CHAR, VARCHAR,
TEXT, BLOB, DATE, DATETIME, YEAR,
SET and ENUM types. See section 7.2 Column types.
select column1 + column2 from table where column1/column2 > 0
GROUP BY and ORDER BY. Support for
group functions (COUNT, AVG, STD, SUM, MAX
and MIN).
LEFT OUTER JOIN with ANSI SQL and ODBC syntax.
autoconf for portability.
purify).
DELETE, INSERT, REPLACE, and UPDATE returns
how many rows were affected.
--help or -? for help.
show tables, SHOW INDEX FROM table and
show columns from table
There is one SQL tutor on the net at http://w3.one.net/~jhoffman/sqltut.htm
This one has been recommended by a lot of people on the MySQL mailing list.
Judith S. Bowman, Sandra L. Emerson and Marcy Darnovsky "The Practical SQL Handbook: Using Structured Query Language" Second Edition Addison Wesley ISBN 0-201-62623-3 http://www.awl.com
And another book also recommended by people on the MySQL mailing list.
Understanding SQL ISBN 0-89588-644-8 Publisher Sybex 510 523 8233 Alameda CA USA
There are also many web pages that use MySQL. See section A Some users of MySQL.. Send any additions to this list to
A stored procedure is a set of SQL commands that can be stored and compiled in the server. After this, the client doesn't have to issue the whole query but can refer to the stored procedure. This gives some more speed because the query only has to be parsed once and there is less data that has to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server.
A trigger is a stored procedure that is invoked when something happens. For example one can install a stored procedure that checks every delete to a transaction table and does an automatic delete on the corresponding customer when all his transactions are deleted.
To see when MySQL might get these functions See section F List of things we want to add to MySQL in the future (The TODO)..
Requests to be added or dropped from the MySQL list should be
sent to the electronic mail address mdomo@tcx.se. Sending a
one line message saying either subscribe mysql or
un-subscribe mysql will suffice. If your reply address is not
valid you may use subscribe mysql your@address.your-domain or
un-subscribe mysql your@address.your-domain.
Please do not send mail about [un]subscribing to automatically forwarded to hundreds of other users.
Your local site may have many subscribers to mysql@tcx.se. In that
case, it may have a local mailing list, so that a single message from
tcx.se is sent to the site and propagated to the local list. In
such cases, please contact your system administrator to be added to or
dropped from the local mysql@tcx.se list.
Mail to mdomo is handled automatically by majordomo.
The following mailinglists exists:
mysql-announce
mysql
mysql-digest
mysql-Java
mysql-win32
myodbc
msql-mysql-modules
msql-mysql-modules-digest
You subscribe/unsubscribe to all lists in the same way as described
above. Just exchange mysql with the list name.
Before you ask a question on the mailing list, it is a good idea to check this manual. If you can't find an answer here, check with your local MySQL expert. If you still can't find an answer to your question go ahead and read the next section about how to send mail to
If you can, please use the `mysqlbug' script that can be found in the scripts directory in the distribution. If that is not possible, remember to specify (if relevant) the following. Note that it is possible to answer a letter with too much information but not one with too little. You should always use `mysqlbug' if your question is anyway related to a MySQL version you are using!
mysqlbug should automaticly finds most of the following information, but if something important missing please include this in your question!
mysqladmin version.
uname -a.
If you are a support customer, please post the bug report to the specified mailing list for higher priority treatment.
When answers are sent to you individually and not to the mailing list, it is considered good etiquette to summarise the answers and mail them to the mailing list.
Since it is very hard to now why something crashing please do one of the following things.
First try to check if thigs that work for other crash for you:
--with-debug switch to
configure. This will include a safe memory allocator that can find some
errors. This also gives a lot of output about what is happening.
back (or the backtrace command in your debugger) when
mysqld core dumps.
Try to make your answer broad enough that people other than the original poster may benefit from it. If you consider your answer to have broad interest, you may want to post it to the mailing list instead of replying directly to the individual who asked. In such cases, please make sure that your answer is not a duplication of a previous answer.
Try to summarise the essential part of the question in your reply, but don't feel obliged to quote the whole question.
Basic licensing issues:
See section J The MySQL server license.
For normal use MySQL costs nothing. When you sell MySQL directly or as a part of another product you have to pay for it. See section J The MySQL server license.
The client access part of MySQL is in the public domain. The command line client includes parts that is under the GNU Public License (readline).
These are our current license prices. All prices are in US Dollars. If you pay by credit card, the currency is FIM (Finish Marks) so the prices will differ slightly.
| Number of licenses | Price/Copy | Total |
| 1 | US $200 | US $200 |
| 10 pack | US $150 | US $1500 |
| 50 pack | US $120 | US $6000 |
| licenses | Price/Copy | Minimum at one time | Minimum Payment |
| 100-1000 | $40 | 100 | $4000 |
| 1000-2500 | $25 | 200 | $5000 |
| 2500-5000 | $20 | 400 | $8000 |
A full-price license includes really basic support. This means that we are trying to answer any relevant question. If the answer is in the documentation, we are going to direct you to the relevant documentation. If you do not have a license/support we will probably not answer at all.
If you discover what we consider a real bug, we are likely to fix it in any case. But if you pay for support we will notify you about the fix status instead of just fixing it in a later release.
More comprehensive support is sold separately:
One year of basic email support costs $200 (USD).
It includes:
One year of extended email support costs $1000 (USD).
Extended basic supports contains everything in basic email support with these additions:
BLOB or TEXT types
yet). The current server includes support to read such databases but not
the packing tool.
One year of email/phone/telnet support costs $2000 (USD).
Login supports contains everything in extended basic email support with these additions:
One year of extended email/phone/telnet support costs $5000 (USD).
Extended login supports contains everything in login support with these additions:
select my_calculation(column1,column2) from database;
Currently we can take SWIFT payments, cheques or credit cards.
Payment should be made to:
Postgirot Bank AB 105 06 STOCKHOLM, SWEDEN T.C.X DataKonsult AB BOX 6434 11382 STOCKHOLM, SWEDEN SWIFT address: PGSI SESS Account number: 96 77 06 - 3 Specify: license and/or support and your name and email address.
In Europe and Japan you can use EuroGiro (that should be cheaper) to the same account.
If you want to pay by cheque make it payable to "Monty Program KB". And mail it to the address below.
T.C.X DataKonsult AB BOX 6434 11382 STOCKHOLM, SWEDEN
If you want to pay with credit card over the Internet you can use TcX's secure license form
For commercial licensing, or if you have any questions about any of the information in this section, please contact:
David Axmark Detron HB Kungsgatan 65 B 753 21 UPPSALA SWEDEN Voice Phone +46-18-10 22 80 (Swedish and English spoken) Fax +46-8-729 69 05 (Email *much* preferred) E-Mail: mysql-licensing@tcx.se
There are (at least) four different copyright's on the MySQL distribution.
mysqlclient
library and programs in the `client' directory is in the public
domain. Each file which is in the public domain has a header which clearly
states so. This is everything in the `client' directory and some parts of
mysys, mystring and dbug libraries.
getopt) are covered by the
"GNU LIBRARY LIBRARY GENERAL PUBLIC LICENSE".
See the `mysys/COPYING.LIB' file.
readline) are covered by the
"GNU GENERAL PUBLIC LICENSE". See the `readline/COPYING' file.
regexp library) are covered by a Berkeley
style copyright.
Our philosophy behind this is:
This is a clarification of the information in the 'MySQL FREE PUBLIC LICENSE'. See section J The MySQL server license.
MySQL may be used freely, including by commercial entities for evaluation or unsupported internal use. However, distribution for commercial purposes of MySQL, or anything containing or derived from MySQL in whole or in part, requires a written commercial license from TcX AB, the sole entity authorised to grant such licenses.
You may not include MySQL "free" in a package containing anything for which a charge is being made except as noted below.
The intent of the exception provided in the second clause is to allow commercial organisations operating an FTP server or a bulletin board to distribute MySQL freely from it, provided that:
If you want to distribute software in a commercial context that incorporates MySQL and you do not want to meet these conditions, you should contact TcX AB to find out about commercial licensing. Commercial licenses involve a payment, and include support and other benefits. These are the only ways you legally can distribute MySQL or anything containing MySQL: either by distributing MySQL under the requirements of the FPL, or by getting a commercial license from TcX AB.
I want to sell a product that can be configured to use MySQL although my customer is responsible for obtaining/installing MySQL (or some other supported alternative). Does one of us owe you money if my customer chooses to use MySQL?
If your product REQUIRES MySQL to work, you would have to buy a license. If MySQL just added some new features it should fall inside normal use. For example, if using MySQL added logging to a database instead of a text file it would not require a license. This would, of course, mean that the user has to fetch and install MySQL by himself. If the program is (almost) useless without MySQL you would have to get a MySQL license to sell your product.
Do I have to get a license for my copy?
No, you are not selling MySQL itself. But in this case we would like you to purchase MySQL support. That is either your support of MySQL or our support of you (the later is more expensive since our time is limited).
Is your script designed for MySQL alone? Does it require MySQL to function at all? Or is it designed for `a database' and can run under MySQL, PostgreSQL, or something else?
If you've designed it strictly around MySQL then you've really made a commercial product that requires the engine, so you need to buy a license.
If, however, you can support any database with a base level of functionality (and you don't rely on anything that only MySQL supports) you probably DO NOT have to pay.
It also depends on what you're doing for the client. Are you tying into a database you expect to already exist by the time your software is purchased? Then you again probably don't have to pay. Or do you plan to distribute MySQL or give them detailed instructions on installing it with your software? Then you probably do.
One thing I'd like to suggest, folks. Look, development won't last forever if nobody pays. I agree that buying a copy for every software user is prohibitive compared to other products available but would it not be courtesy for commercial developers to register their OWN copy that they develop with?
We may choose to distribute older versions of MySQL with the GPL in the future. However these versions will be identified as GNU MySQL. Also all copyright notices in the relevant files will be changed to the GPL.
You can always check MySQL's home page to read the latest news.
But since the Internet connection at TcX is not very fast we would prefer if you do the actual downloading from one of the mirrors below.
Europe:
Austria WWW [Univ. of Technology/Vienna]
Bulgaria FTP [Naturella]
Czech Republic WWW [CESNET]
Denmark WWW [Ake]
Denmark WWW [SunSITE]
Denmark FTP [SunSITE]
Estonia WWW [Tradenet]
France WWW [minet]
Germany WWW [Wolfenbuettel]
Germany WWW [Staufen]
Hungary WWW [Xenia]
Israel WWW [Netvision]
Israel FTP [Netvision]
Italy WWW [Matrice]
Poland WWW [Sunsite]
Poland FTP [Sunsite]
Portugal WWW [Telenet]
Russia FTP [Cityline]
Romania WWW [Timisoara]
Romania FTP [Timisoara]
Romania WWW [Bucharest]
Romania FTP [Bucharest]
Sweden WWW [Sunet]
Sweden FTP [Sunet]
UK WWW [Omnipotent/UK]
UK FTP [Omnipotent/UK]
UK WWW [PLiG/UK]
UK FTP [PLiG/UK]
North America:
Canada WWW [Polaris Computing]
Canada WWW [Tryc]
Canada WWW [Cyberus]
Canada FTP [Cyberus]
USA WWW [Hurricane Electric/San Jose]
USA WWW [Buoy/New York]
USA WWW [Hypernet Communications/Dallas]
USA WWW [Hurricane Electric/California]
USA FTP [Netcasting/West Coast]
USA WWW [Savages/Oregon]
USA WWW [Circle Net/North Carolina]
USA WWW [Gina net/Florida]
USA FTP [DIGEX]
Asia:
Korea WWW [KREONet]
Japan WWW [HappySize]
Japan FTP [HappySize]
Singapore WWW [Com5 Productions]
Singapore FTP [Com5 Productions]
Taiwan WWW [NCTU]
Australia:
The first decision is if you want to use the latest development release or the last stable release.
Normally if you are starting with development we recommend going with the development release. This is because there are usually no really bad bugs in the development release and you can easily test it on your machine with the crash-me and benchmark tests. See section 11 MySQL benchmark suite.
The second decision is if you want a source or a binary release.
If you want to run MySQL on a platform that has a current binary release, use that. A binary version of MySQL is easier to install.
If you want to read (and/or modify) the C and C++ code that makes up MySQL you should always get a source distribution. The code is always the ultimate manual. The source distribution also contains more tests and examples than the binary distribution.
To clarify our naming schema:
All MySQL versions are run through our standard test and the benchmarks to ensure that they are relatively safe to use. The standard tests are also extended the whole time to test for all previously found bugs, so it gets better the whole time.
The MySQL release numbers consist of 3 numbers and a suffix.
So a release name like mysql-3.21.17-beta means.
Note that all releases have at least been tested with:
Another test is our internal production. We usually use the latest version for this (at least on one machine) and we have more than 100 gigabytes of data to work with.
We are using the following policy when updating MySQL:
Each minor update will increment the last number in the version string. When there are major new features or minor incompatibilities with previous versions, the second number in the version string will be incremented. When the file format changes the first number will be increased.
We use GNU autoconf so it is possible to port MySQL to all modern systems with working Posix threads and a C++ compiler. The client code requires C++ but not threads. We use/develop the software ourselves primarily on Sun Solaris (versions 2.5 & 2.6) and some on RedHat Linux 5.0.
The following OS/thread packages have been reported to compile MySQL successfully. Note that for many OSes the native thread support only works in the latest versions.
What you need:
gzip to uncompress the distribution.
tar to unpack the distribution. GNU tar is
known to work.
C++ compiler. gcc >= 2.7, SGI C++, SunPro C++ are
some of the compilers that are known to work. libg++ is not
needed when using gcc.
make program. If you have problems we recommend trying GNU
make.
Unpack tar archive in a directory. The tar file should be named like mysql-VERSION.tar.gz (VERSION is a number like 3.21.31). A directory named mysql-VERSION should be created.
zcat mysql-VERSION.tar.gz | tar xvf -
cd mysql-VERSION
./configure
make
make install
./scripts/mysql_install_db
'installation_directory'/bin/mysqladmin version
mysqladmin Ver 6.3 Distrib 3.21.17, for pc-linux-gnu on i686 TCX Datakonsult AB, by Monty Server version 3.21.17-alpha Protocol version 10 Connection Localhost via UNIX socket TCP port 3306 UNIX socket /tmp/mysql.sock Uptime: 16 sec Running threads: 1 Questions: 20 Reloads: 2 Open tables: 3
Remember that if you reconfigure MySQL you have to do
rm config.cache or make distclean before doing a new configure!
GNU make is always recommended and is sometimes required.
--without-server. If you only
want to have the client library and don't have a C++ compiler, you
can remove the code in configure (in the source distribution) that tests
for the C++ compiler and then compile with --without-server. Ignore in
this case any warnings about mysql.cc (The only MySQL client
that needs C++).
--prefix=/usr/local or --prefix=/usr/local
--localstatedir=/usr/local/data/mysql
--with-unix-socket-path=absolute_file_name
Internal compiler error: program cc1plus got fatal signal 11 or
Out of virtual memory you can try the configure switch:
--with-low-memory.
This adds -fno-inline to the compile line if you are using gcc
and -O0 if you are using something else. The problem is that gcc requires
about 180M to compile sql_yacc.cc with inline functions.
LDFLAGS=-static ./configure.
CC=gcc CXX=gcc ./configure
DEFAULT values at all and you want to get
an error for an INSERTs that doesn't use all NOT NULL columns
configure with CXXFLAGS=-DDONT_USE_DEFAULT_FIELDS ./configure.
--with-charset=charset.
Where [charset] may be one of: big5, czech, dec8,
dos, german1, hp8, koi8_ru, latin1,
latin2, swe7, usa7, ujis, or sjis.
See section 9.1.1 Character set used for data & sorting.
If you want to convert characters between the server and the client,
you should take a look at the SET OPTION CHARACTER SET.
See section 7.20 SET OPTION syntax.
Warning: If you change character sets after having created a
table you will have to run isamchk -r -q on every
table. Otherwise things will be sorted incorecctly in some cases (but
not all!).
Some times patches appear on the mailing list. To apply them, do something like this:
cd 'old-mysql-source-distribution-path' gunzip < patch-file-name.gz | patch -p1 rm config.cache make clean
And then follow the instructions for a normal source install from the
./configure step.
And then restart your MySQL server.
If your compile fails with something like:
configure: error: installation or configuration problem: C++ compiler cannot create executables.
Try setting the environment variable CXX to "gcc -O3" (If
you are using gcc). For example CXX="gcc -O3"
./configure. If you use this you don't nead to have libg++ installed!
If you have any problems with using g++, or libg++ or libstdc++, you can probably always solve these by configuring as above!
You can also install libg++. By default `configure' picks
c++ as a compiler name and GNU c++ links with
-lg++.
making all in mit-pthreads make: Fatal error in reader: Makefile, line 18: Badly formed macro assignmentThis means you have to upgrade your make to GNU
make.
CC="gcc -O4" CXX="gcc -O4" export CC CXX
make stops with Can't find Makefile.PL when
you should try using GNU make. Solaris and FreeBSD
are known to have troublesome make programs.
pthread.h: No such file or directory
This means you have to upgrade your make to GNU make
(GNU make version 3.75 is known to work).
client/libmysql.c:273: parse
error before `__attribute__'
This means you need to upgrade your gcc compiler (2.7.2 is known to work).
mysqlbug
when posting questions to mysql@tcx.se. See section 2.3 I think I have found a bug. What information do you need to help me?.
Even if the problem isn't a bug, mysqlbug gathers some system
information that will help others solve your problem!
configure --with-debug=yes and link your clients with
the new client library.
Before running a client you should do:
MYSQL_DEBUG=d:t:O,/tmp/client.trace export MYSQL_DEBUGYou will now get a trace file in `/tmp/client.trace'.
cxx: Error: mysqld.cc, line 645: In this statement, the referenced type of
the pointer value "&length" is "unsigned long", which is not compatible
with "int".
new_sock = accept(sock, (struct sockaddr *)&cAddr, &length);
Then configure didn't detect the type of the last argument to
accept(), getsockname() and getpeername(). Search
after the line:
/* Define as the base type of the last arg to accept */ #define SOCKET_SIZE_TYPE ###and change ### to size_t or int depending on your operating system.
mysql --debug=d:t:o,/tmp/client.trace before mailing a bug
report. See section 2.3 I think I have found a bug. What information do you need to help me?.
All MySQL programs compile clean (no warnings) for us (on Solaris using gcc). But warning may appear because of different system include files. Se below for warnings that may occur when using mit-pthreads.
You probably have to use bison to compile sql_yacc.yy. If you get an error like:
"sql_yacc.yy", line xxx fatal: default action causes potential...
you have to install bison (the GNU yacc). Normally you don't have to do this as MySQL comes with a already compiled sql_yacc.cc file.
If you want to have static linked code, use (with gcc):
LDFLAGS="-static" ./configure ...
You can on most systems force the usage of mit-pthreads with the configure
switch --with-mit-threads.
Building in a non source directory is not supported when using MIT-threads. This is because we want to keep our changes to this code minimal.
MIT-pthreads doesn't support the AF_UNIX protocol so we must use
the TCP/IP protocol for all connections (which is a little slower). If
you can't connect to a table, try using the host (-h or
--host) switch to mysql. This must be done if you have
compiled the client code --without-server because the default
connection is to use Unix sockets.
MySQL compiled with MIT threads has system locking disabled by
default for performance reasons. One can start the server with system
locking with the --use-locking switch.
Sometimes (at least on Solaris) the pthread bind() command fails
to bind to a socket without any error message. The result of this is
that all connections to server fails.
> mysqladmin ver mysqladmin: connect to server at " failed;
error: 'Can't connect to mysql server on localhost (146)'
The solution to this is to kill the mysqld daemon and restart it.
This has only happened to us when we have forced the daemon down and done
a restart immediately.
sleep() isn't interruptible with SIGINT (break) with
MIT-threads. This is only notable in mysqladmin --sleep. One must
wait for the end of the sleep() before the interrupt is served
and the process stops.
We haven't got readline to work with MIT threads. (This isn't needed, but may be interesting for someone)
When linking (at least on Solaris) you will receive warning messages like:
ld: warning: symbol `_iob' has differing sizes: (file /my/local/pthreads/lib/libpthread.a(findfp.o) value=0x4; file /usr/lib/libc.so value=0x140); /my/local/pthreads/lib/libpthread.a(findfp.o) definition taken ld: warning: symbol `__iob' has differing sizes: (file /my/local/pthreads/lib/libpthread.a(findfp.o) value=0x4; file /usr/lib/libc.so value=0x140); /my/local/pthreads/lib/libpthread.a(findfp.o) definition taken
Some other warnings which also can be ignored:
implicit declaration of function `int strtoll(...)' implicit declaration of function `int strtoul(...)'
The included perl client code requires perl5.004 or later.
If you got a the following error (from mysqlperl or DBD-mysql):
/usr/bin/perl: can't resolve symbol '__moddi3' /usr/bin/perl: can't resolve symbol '__divdi3'
You are probably using gcc (or using an old binary compiled with
gcc). Add -L/usr/lib/gcc-lib/... -lgcc to the link command when
the mysql.so library gets build (check the output for mysql.so when you
compile the perl client).
-L/... is the path to the directory where libgcc.a
exists.
Another problem may be that perl and MySQL isn't both compiled with gcc. In this case you can solve this by compile everything with gcc.
If you want to use the perl module on a system that doesn't support dynamic linking (like SCO) you can always make a static version of perl with DBI and DBD-mysql:
First you create a statically linked DBI by doing:
perl Makefile.PL LINKTYPE=static make make install make perl
After this you must install the new perl. The output of make perl will
contain the exact make command! On SCO this is
make -f Makefile.aperl inst_perl MAP_TARGET=perl
Next you create a statically linked DBD::mysql:
perl Makefile.PL LINKTYPE=static make make install make perl
You should also install the new perl (see the DBI perl installation above).
Sun native threads only work on Solaris 2.5 and higher. For 2.4 and earlier versions, you can use MIT-pthreads. See section 4.8 MIT-pthreads notes. (FreeBSD).
If there are too many processes that try to connect very rapidly to the
mysqld one will get Error in accept : Protocol error in
the mysql log.
If you have the Sun Workshop 4.2 compiler you can configure with:
CC=cc CFLAGS="-Xa -fast -xstrconst -mt" CXX=CC CXXFLAGS="-xsb -noex -fast -mt"
./configure
You may also have to change the row in configure:
#if !defined(__STDC__) || __STDC__ != 1 to #if
!defined(__STDC__) because if you turn on __STDC__ with the
-Xc switch, the Sun compiler can't compile with the Solaris
`pthread.h' header files anymore. This is a Sun bug (Broken
compiler or broken include file).
If the compiled mysqld gives a error like: libc internal
error: _rmutex_unlock: rmutex not held you have tried to compile
MySQL with the Sun compiler without enabling the multi thread switch
-mt. Add -mt to CFLAGS and CXXFLAGS and try again.
If you get the following error when compiling MySQL with gcc:
gcc -O3 -g -O2 -DDBUG_OFF -o thr_alarm ... ./thr_alarm.c: In function `signal_hand': ./thr_alarm.c:556: too many arguments to function `sigwait'
This means that your gcc is not configured for your version of Solaris! The proper thing to do in this case is to get the newest egcs or gcc version and compile this with your current gcc compiler! At least for Solaris 2.5, almost all binary versions of gcc have old, unusable include files that will break all programs that use threads (and possible other programs!)
The tar in Solaris can't handle long file names; You may get the following error (or something similar) when unpacking the MySQL distribution:
x mysql-3.21.21a-beta-sun-solaris2.6-sparc/perl/Mysql-modules/blib/lib/auto/Msql-Mysql-modules, 0 bytes, 0 tape blocks tar: directory checksum error
In this case you have to use gnu tar to unpack the distribution. You can find copy of precompiled gnu tar (gtar) for Solaris at http://www.mysql.com/Downloads/
On SunOS 4, MIT-pthreads is needed. You must have GNU make to compile
(because of MIT-pthreads).
In readline you may get warnings about duplicate defines. These may be ignored.
When compiling mysqld there will be some warnings about implicit
declaration of function. These may be ignored.
On Linux you should use the --skip-locking flag to mysqld
if you start it yourself. Normally the demon is started by safe_mysqld
and the flag is added automatically. This flag is needed because a bug
in Linux file locking calls. This bug is known to exist as recently as
Linux version 2.0.33.
If you can't start mysqld or if mysql_install_db doesn't work, please continue reading! This only happens on Linux system with problems in the Linuxthreads or libc/glibc library. There is a lot of simple workarounds to get MySQL to work! The simplest is to use the binary version (not the RPM) of MySQL for Linux x86; One nice aspect of this version is that it's probably 10% faster than any version you would compile yourself! See section 10.2 How compiling and linking affects the speed of MySQL
When using LinuxThreads you will see a minimum of three processes running. These are in fact threads. There will be one thread for the Linux Threads manager, one thread to handle connections, and one thread to handle alarms and signals.
if you are using RedHat you might get errors like:
/usr/bin/perl is needed... /usr/sh is needed... /usr/sh is needed...
If so upgrade rpm itself to rpm-2.4.11-1.i386.rpm & rpm-devel-2.4.11-1.i386.rpm (or later versions).
You can get the 4.2 updates from ftp://ftp.redhat.com/updates/4.2/i386. Or http://www.sunsite.unc.edu/pub/Linux/distributions/redhat/code/rpm/ for other distributions.
LinuxThreads should be installed before configuring MySQL!
MySQL requires libc version 5.4.12 or newer. glibc version 2.0.6 and later should also work. There has been some problems with the glibc rpms from RedHat so if you have problems, check if there are any updates!
On some older Linux distributions configure may give a error
about: Syntax error in sched.h. Change _P to __P in the
/usr/include/sched.h file.\
See the Installation chapter in the Reference Manual. Just do what the
error says and add a extra underscore to the _P macro that only
has one underscore. Then try again.
You may get some warnings when compiling: (these can be ignored)
mysqld.cc -o objs-thread/mysqld.o mysqld.cc: In function `void init_signals()': mysqld.cc:315: warning: assignment of negative value `-1' to `long unsigned int' mysqld.cc: In function `void * signal_hand(void *)': mysqld.cc:346: warning: assignment of negative value `-1' to `long unsigned int'
If you want in Debian GNU/Linux to get MySQL to auto start when system boots, do the following:
> cp scripts/mysql.server /etc/init.d/mysql.server > /usr/sbin/update-rc.d mysql.server defaults 99
If mysqld always core dumps when starting, the problem may be that you have an old `/lib/libc.a'. Try renaming this, remove sql/mysqld and do a new make install and try again. This problem has been reported on some Slackware installations. RedHat 5.0 has also a similar problem with some new glibc versions. More about this later.
If you install all the official redhat patches (including glibc-2.0.6-9 and glibc-devel-2.0.6-9) it should work out of the box (se above for how to configure).
The updates are needed since there is a bug in glibc 2.0.5 in how pthread_key_create variables are freed. With glibc 2.0.5 you must use the static-linked MySQL binary distribution. If you want to compile from source you must install the corrected version of Linuxthreads from http://www.tcx.se/Downloads/Linux or upgrade your glibc.
If you have a wrong glibc or linuxthread version the symptom is that
mysqld crashes after each connections. For example mysqladmin version
will crash mysqld when it finishes!
Another symptom of wrong libraries is that mysqld crashes at once when it
starts. One some Linux systems this can be fixed by configuring with
LDFLAGS=-static ./configure. On some RedHat 5.0 system it will only
work WITHOUT LDFLAGS=-static. This is known to happen even with
new versions as glibc 2.0.7-4 !
For the source distribution of glibc 2.0.7 you can find a patch at
http://www.tcx.se/Download/Linux/glibc-2.0.7-total-patch.tgz
that is easy to apply and is tested with MySQL!
If you experience crashes like these when you build MySQL, you can always download the newest binary version of MySQL. This is compiled staticly to avoid library conflicts and should work on all Linux systems!
If you want to compile this yourself, check how to debug mysqld! As MySQL comes with an internal debugger its very easy to get a trace file that probably will help solve your problem very quickly! See section 18.10 Debugging MySQL
In some implementations readdir_r is broken. This will be
noticed when SHOW DATABASES always returns an empty set. This
can be fixed by removing HAVE_READDIR_R from `config.h'.
Some problems will require patching your Linux installation. The patch can be found at http://www.tcx.se/patches/Linux-sparc-2.0.30.diff. This patch is against the Linux distribution `sparclinux-2.0.30.tar.gz', that is available at vger.rutgers.edu. This is a version Lf linux which was never merged with the official 2.0.30. You must also install linuxthreads 0.6 or newer.
Thanks to jacques@solucorp.qc.ca for the above information.
The first problem is linuxthreads. You must patch linuxthreads for alpha because the RedHat distribution uses an old (broken) linuxthreads version.
CC=gcc CCFLAGS="-Dalpha_linux_port" CXX=gcc CXXFLAGS="-O3 -Dalpha_linux_port" ./configure --prefix=/usr/local/mysql
Note that Alpha-Linux is still an alpha platform for MySQL. With RedHat 5.0 and the patched linuxthreads you have a very good chance of it working.
When compiling threaded programs under Digital UNIX using CC / CXX the
documentation recommends the switch to cc and cxx and the libraries
-lmach -lexc (in addition to -lpthread).
So you have to configure with something like this:
CC="cc -pthread" CXX="cxx -pthread -O" ./configure
-with-named-thread-libs="-lpthread -lmach -lexc -lc"
When compiling mysqld you will may this warning for mysqld for a couple of lines:
mysqld.cc: In function void handle_connections()': mysqld.cc:626: passing long unsigned int *' as argument 3 of accept(int,sockad ddr *, int *)'
You can safely ignore these. This is because configure can't detect warnings, only errors.
You may get problems with the server exiting directly. If so, try
starting it with nohup safe_mysqld [options]
nohup is a command that ignores any SIGHUP sent from the
terminal.
If you have problems compiling and have Dec CC and gcc installed you can try the following compile line (with sh or bash):
CC=cc CFLAGS=-O CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
On OSF1 V4.0D and compiler "DEC C V5.6-071 on Digital UNIX V4.0 (Rev. 878)" the compiler had some strange behaviour (One gets undefined 'asm' symbols). /bin/ld appears also to be broken (one gets _exit undefined when linking mysqld). On this we have managed to compile MySQL with the following configure line, after replacing /bin/ld from OSF 4.0C:
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
In some versions of OSF1, the alloca() functions is broken. Fix
this by removing 'HAVE_ALLOCA' from config.h.
The alloca() function can also have a wrong prototype in
/usr/include/alloca.h. This warning can be ignored.
Configure will automaticly use the following thread libraries:
-with-named-thread-libs="-lpthread -lmach -lexc -lc"
When using gcc you can also try to use:
CFLAGS=-D_PTHREAD_USE_D4 CXX=gcc CXXFLAGS=-O3 ./configure ....
You may have to undefine some things in `config.h' (generated by `./configure').
In some Irix implementations the alloca() function is broken. If
the mysqld server dies on some selects, remove HAVE_ALLOC &
HAVE_ALLOCA_H from `config.h'. If mysqladmin create doesn't
work, remove HAVE_READDIR_R from config.h. Also you may have to
remove HAVE_TERM_H.
Irix 6.2 doesn't support POSIX threads out of of the box. You have install these patches, available from SGI if you have support:
1403, 1404, 1644, 1717, 1918, 2000, 2044
If you get the something like the following error when compiling mysql.cc:
"/usr/include/curses.h", line 82: error(1084): invalid combination of type
Type the following in the mysql installation directory:
> extra/replace bool curses_bool < /usr/include/curses.h > include/curses.h > make
There have also been reports about scheduling problems. This is because if only one thread is running, things go slow. Avoid this by starting another client. This may lead to a 2-10 fold increase in execution speed thereafter for the other thread.
This is a poorly-understood problem with IRIS threads, so you may have to improvise to find solutions until this can be fixed.
If you are compiling with gcc, you can use the following configure line:
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-thread-safe-client
If you get a error on 'make install' that it can't find /usr/include/pthreads, configure didn't detect that one neads mit-threads on FreeBSD. This is fixed by doing:
rm config.cache ./configure --with-mit-threads
If you get link errors when compiling mysqlperl (Type
ient.a(my_getwd.o): RRS text relocation at 0x9a9f for
"__db_pargs_")
You must recompile the Perl code with -DPIC -fpic.
Do the following:
CFLAGS = -g -O2 to
CFLAGS = -O2 -DPIC -fpic
cd client
rm *.o
make libmysqlclient.a
cd ../perl
make
This shall hopefully be handled automatically in the future.
The FreeBSD make behaviour is slightly different from GNU
make. If you have a problem that `perl/Makefile'
doesn't get generated, you should install GNU make.
If mysql or mysqladmin takes a long time to respond, a user said the following:
Are you running the ppp user process? On ine FreeBSD box (2.2.5) MySQL clients takes a couple of seconds to connect to mysqld if the ppp process is running.
FreeBSD is also known to have a very low default file handle limit. See section 15.7 File not found
If you have a problem that select NOW() returns GMT and not your local time,
you have to set the TZ environment variable to your current timezone.
Make sure that you modify the /etc/hosts file so that the
localhost entry is correct (else you will have problems
connecting to the database).
If you are using FreeBSD 2.2.26:
Don't forget to apply the ttcp and mmap-22 patches to the OS (for security reasons). Please see http://www.freebsd.org for these CERT patches.
You have to configure with:
--with-named-thread-libs=-lc_r
The pthreads library for FreeBSD doesn't contain the sigwait
function and there is some bugs in it. To fix this, get the
`FreeBSD-3.0-libc_r-1.0.diff' file and apply this in the
`/usr/src/lib/libc_r/uthread' directory. Follow after this the
instructions that can be found with man pthread about how to
recompile the libc_r library.
You can test if you have a 'modern' libpthread.a with:
> nm /usr/lib/libc_r.a | grep sigwait.
If the above doesn't find sigwait you have to use the above patch
and recompile libc_r.
From Jan Legenhausen jleg@csl-gmbh.net.
I finally got mysqlperl working on BSDI2.1.
What i did was almost nothing:
GCC="shlicc2" per default;
perl5 automagically uses shlicc2 - you should use _one_ version (either
gcc or shlicc2) for both Mysql.c and
libmysqlclient.a!)
rm *.o
gmake libmysqlclient.a
cd ../perl/mysqlperl
make clean
$sysliblist=" -L$tmp -lgcc -lcompat"; to Makefile.PL line 45
(just to be sure - i didn't check if one could leave out this one)
perl Makefile.PL
libmysqlclient.a's in /usr/lib and
/usr/contrib/lib
make install
If you get the following error when compiling MySQL:
item_func.h: In method `Item_func_ge::Item_func_ge(const Item_func_ge &)': item_func.h:28: virtual memory exhausted make[2]: *** [item_func.o] Error 1
Then your ulimit for virtual memory is too low. Try using: ulimit
-v 80000 and do make again.
If you are using gcc you can also add the flag '-fno-inline' to the compile line when compiling sql_yacc.cc.
If you have a problem that select NOW() returns GMT and not your local time,
you have to set the TZ environment variable to your current timezone.
env CXX=shlicc++ CC=shlicc2 ./configure --prefix=/usr/local/mysql --localstatedir=/var/mysql --without-perl --with-unix-socket-path=/var/mysql/mysql.sockThe following is also known to work:
env CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-unix-socket-path=/var/mysql/mysql.sock
You can change the directory locations if you wish, or just use their defaults by not specifying them.
The current port is only tested on a 'sco3.2v5.0.4' system. There has also been a lot of progress on a port to 'sco 3.2v4.2'.
as.
make when making MySQL.
CFLAGS="-D_XOPEN_XPG4" CXX=gcc CXXFLAGS="-D_XOPEN_XPG4" ./configure --with-debug=yes --prefix=/usr/local/mysql --with-named-thread-libs="-lgthreads -lsocket -lgen -lgthreads" --with-named-curses-libs="-lcurses" --without-perlYou may get some problems with some include files. In this case you can find new SCO specific include files at: ftp://www.tcx.se/pub/mysql/Downloads/SCO/SCO-3.2v4.2-includes.tar.gz. You should unpack this in the mysql-source-distributions/include directory!
SCO development notes:
-lgthreads -lsocket -lgthreads
When using the IBM compiler, something like this is needed:
CC="xlc_r -ma -O3 -qstrict" CXX="xlC_r -ma -O3 -qstrict" ./configure
Automatic detection of xlC is missing from autoconf.
There is a couple of 'small' problems when compiling mysql on HPUX. Below we describe some problems and workarounds when using the HPUX compiler and gcc 2.8.0 .
gcc 2.8.0 can't compile readline on HPUX (internal compiler error). mit-pthreads can't be compiled with HPUX compiler, because it can't compile .S (assembler) files.
We got MySQL to compile on HPUX 10.20 by doing the following:
CC=cc CFLAGS="+z +e -Dhp9000s800 -D__hpux__" CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-low-memory cd mit-pthreads rm config.cache CC=gcc CXX=gcc ./configure cd .. make make install scripts/mysql_install_db
The MySQL-win32 version has by now proven itself to be very stable. The MySQL-win32 version has all the features as the corresponding Unix version of MySQL with the following exceptions:
ALTER TABLE
ALTER TABLE on file if it is hold open by another thread
or the table cache. On MySQL-win32 I have added code to close the
file owned by the thread that does ALTER TABLE but MySQL
can't yet close the the file descriptors used by other threads. We have to do
a major recode of the file lock system to handle this. For now, when using
ALTER TABLE, one must be sure that no other threads are using the table.
One can be sure of this by doing a 'mysqladmin refresh' before doing an
ALTER TABLE.
Increasing the table cache
Win95 and threads
Other win32 specific issues are described in the README file that comes with the MySQL-win32 distribution.
Please always use the mysqlbug script when posting questions to the mailinglist (mysql@tcx.se). Even if the problem isn't a bug, mysqlbug gathers some system information that will help other solve your problem! See section 2.3 I think I have found a bug. What information do you need to help me?.
> cd /usr/local > zcat /<where ever you put it>/mysql-3.20.0-SunOS5.tgz | tar xvf - > ln -s mysql-VERSION mysql
> scripts/mysql_install_dbSee section 4.13 Problems running mysql_install_db If you want to change things in the grant tables after installing you should use
mysql -u root mysql to connect to the grant tables
as the 'root' user.
The mysql_install_db script also starts the mysqld daemon.
> bin/safe_mysqld --log &
> bin/mysqladmin verThat should print something like this. The exact output depends on you platfrom and use.
bin/mysqladmin Ver 6.3 Distrib 3.21.15-alpha, for SOLARIS 2.5 on SPARCstation TCX Datakonsult AB, by Monty Server version 3.21.15-alpha Protocol version 9 Connection Localhost via UNIX socket TCP port 3306 UNIX socket /tmp/mysql.sock Uptime: 2 days 1 hour 42 min 3 sec Running threads: 2 Questions: 450378 Reloads: 17 Open tables: 64
cd perl/DBI perl Makefile.PL make make install cd ../Mysql-modules perl Makefile.PL make make install
You should use the safe_mysqld script to the server.
safe_mysqld expects one of two conditions to be true:
/usr/local/mysql)
/my/. To get it to run correctly,
you should cd to /usr/local/mysql and then execute
safe_mysqld or modify the script so that it expects the base
mysql directory to be `/usr/local/mysql' rather than the default
`/my/'.
When you execute this:
> bin/mysqld --help
You will get the options for mysqld (and safe_mysqld) and the current
paths. Normally you only should need to change the
--basedir=path. You can test the path switches by executing:
> bin/mysqld --basedir=/usr/local --help
If you would like to use mysqlaccess and have the mysql distribution in
some nonstandard place, you must change the path to mysql in
mysqlaccess. bin/mysqlaccess about line 308:
$MYSQL = '/usr/local/bin/mysql --batch --unbuffered';
If you don't change the path, you will get a 'broken pipe' error when using mysqlaccess.
If you would like MySQL to start when you boot your machine,
you can copy bin/mysql.server to where your system has it startup
files. More information can be bound in the bin/mysql.server script
itself.
CC=gcc CFLAGS="-O6 -mpentium -mstack-align-double -fomit-frame-pointer" CXX=gcc CXXFLAGS=-O6 -mpentium -mstack-align-double -fomit-frame-pointer -felide-constructors" LDFLAGS=-static ./configure --prefix=/usr/local/mysql -enable-assemblerAll of the above is typed as one long line!
The binary distribution of MySQL for HP/UX is distributed as an HP depot file. This means that you must be running at least HP/UX 10.x to have access to HP's software depot tools.
This version of MySQL was compiled on an HP 9000/8xx server under HP/UX 10.20, and uses MIT Pthreads. It is known to work well under this configuration. This version does not use HP's native thread package. It is highly unlikely that MySQL will use HP native threads on anything but HP/UX 10.30 or later.
Other configurations that may work:
9000/7xx - HP/UX 10.20+ 9000/8xx - HP/UX 10.30 (does not use HP native threads)
The following configurations almost definitely won't work:
9000/7xx or 8xx - HP/UX 10.x where x < 2 9000/7xx or 8xx - HP/UX 9.x
To install (everything, including server, client and development tools):
/usr/sbin/swinstall -s <full path to the depot file> mysql.full
To install server only:
/usr/sbin/swinstall -s <full path to the depot file> mysql.server
To install client pack only:
/usr/sbin/swinstall -s <full path to the depot file> mysql.client
To install development tools only:
/usr/sbin/swinstall -s <full path to the depot file> mysql.developer
The depot will place binaries/libraries in /opt/mysql and data in /var/opt/mysql. The depot will also create the appropriate entries in /sbin/init.d and /sbin/rc2.d to automatically start the server on boot. This obviously entails being root to install.
Clients have to be linked with: -lmysqlclient
The default priviliges is that anybody may create/use the databases
named test or starting with test_. root can do
anyting. See section 6.2 How does the privilege system work?.
To change the defaults edit the script before running it. If this is
the first time you install MySQL you must run this command. If
you don't do it you will get the error: Can't find file:
'./mysql/host.frm'. This script also starts the mysqld daemon the first
time.
If you want to change things in the grant tables after installing you
should use mysql -u root mysql to connect to the grant tables
as the 'root' user.
Normal start of the MySQL server daemon (not needed the first
time): 'installation_directory'/bin/safe_mysqld --log
It may happen that mysql_install_db doesn't install the privilige
tables but ends with:
Starting mysql server starting mysqld demon with databases from xxxxxx mysql demon ended
In this case you should examine the log in the xxxxxx directory very carefully! This contains the reason why mysqld didn't start. If you can't understand what happens, you should at least include the log when you post a bug report using mysqlbug!
Possible problems when running mysql_install_db are:
There is already a mysqld deamon running.
Installing a second mysqld daemon doesn't work when one daemon is running.
MYSQL_UNIX_PORT=/tmp/mysqld-new.sock MYSQL_TCP_PORT=3307 export MYSQL_UNIX_PORT MYSQL_TCP_PORT scripts/mysql_install_dbAfter this you should edit your server boot script to start both daemon with different sockets and ports (safe_mysqld --socket=... --port=....).
mysqld crashes at once.
MySQL mail archives.
See section 4.10.3 Linux notes for all versions
Can't connect to the server (when using mit-threads)
mysql_install_db can't connect to the server you should check
that you have an entry in `/etc/hosts' like:
127.0.0.1 localhostThe above is only a problem on system that doesn't have a thread library and MySQL has to use mit-threads.
You don't have write access to create a socket file (in /tmp ?)
mysqld --help.
You can also specify paths for safe_mysqld by doing the following:
MYSQL_UNIX_PATH=/some_directory_for_tmp_files/mysqld.sock MYSQL_TCP_PORT=3306 TMPDIR=/some_directory_for_tmp_files/ export MYSQL_UNIX_PATH MYSQL_TCP_PORT TMPDIR scripts/mysql_install_db or bin/mysqld --skip-grant bin/mysql -u root mysqlAfter this you can execute the sql commands in mysql_install_db.
The paths may be different from what mysqld expects.
mysqld --help for more information. You can edit
bin/safe_mysqld to reflect the paths for your installation.
A simple test to see that everything is working is:
bin/mysqladmin version
Check the log file to see if mysqld started up correctly.
mysqld daemon starts with a cd to 'mysql-data-dir'. After this,
mysqld-data-dir is changed to './' (current dir). All paths (databases,
pid file, and log file) have this directory as base path './'. If you
have any problems with wrong paths, try mysqld --help to see
your current paths. Every path can be changed by a startup option to
safe_mysqld or mysqld
cd <localstatedir default /usr/local/var> tail <your host name>.log
To verify that MySQL is working run the following tests:
> cd /usr/local/bin > ./mysqlshow +-----------+ | Databases | +-----------+ | mysql | +-----------+ > ./mysqlshow mysql Database: mysql +--------+ | Tables | +--------+ | db | | host | | user | +--------+ > ./mysql -e "select host,db,user from db" mysql +------+--------+------+ | host | db | user | +------+--------+------+ | % | test | | | % | test_% | | +------+--------+------+
There is also a benchmark suite so you can compare how MySQL performs on different platforms. In the near future this will also be used to compare MySQL to other SQL databases.
> cd bench > run-auto-increment-test
You can also run the tests in the test subdirectory. To run `auto_increment.tst':
./mysql -vf test < ./tests/auto_increment.tst
Expected results are shown in the file `./tests/auto_increment.res'.
The safe_mysqld script is written that it should be able to start a source and a binary version of mysqld, even if these have sligtly different paths!
You can install a binary release of MySQL anywhere as long as you start safe_mysqld from installation directory:
cd mysql_installation_directory bin/safe_mysqld &
If you want to change the startup options to mysqld you can always edit safe_mysqld! In this case you should copy safe_mysqld to some other location that it will not be overwritten if you sometimes decide to upgrade MySQL!
To start or stop MySQL use the following commands:
scripts/mysql.server stop scripts/mysql.server start
You might want to add these start and stop commands in the appropriate places in your `/etc/rc*' files when you start using MySQL for production applications. You can edit the mysql.server script to start safe_mysqld from some specific location and as some specific user. You can also add startup options to mysqld here.
The following are useful extensions in MySQL that you probably will not find in other SQL databases. Be warned that if you use them, your code will not be portable to other SQL servers.
MEDIUMINT, SET, ENUM and the
different BLOB and TEXT types.
AUTO_INCREMENT, BINARY,
UNSIGNED and ZEROFILL.
BINARY attribute.
INTO OUTFILE and STRAIGHT_JOIN in a SELECT
statement. See section 7.10 SELECT syntax.
EXPLAIN SELECT to get a description on how tables are joined.
INDEX or KEY in a CREATE TABLE
statement. See section 7.6 CREATE TABLE syntax.
DROP column or CHANGE column in an ALTER TABLE
statement. See section 7.7 ALTER TABLE syntax.
LOAD DATA INFILE. This syntax is in many cases compatible with
Oracle's LOAD DATA INFILE. See section 7.14 LOAD DATA INFILE syntax.
" instead of ' to enclose strings.
\ character.
SET OPTION statement. See section 7.20 SET OPTION syntax.
GROUP BY part.
See section 7.3.12 Functions for GROUP BY clause.
|| and && operators are, in MySQL, synonyms for
OR and AND, like in the C programming language.
Likewise | and & stand for bitwise OR and
AND. Because of this nice syntax, MySQL doesn't support
the ANSI SQL operator || for string concatenation, one has to use
CONCAT() instead. As CONCAT() takes any number
of arguments it's easy to convert use of the || operator to
MySQL.
CREATE DATABASE or DROP DATABASE.
See section 7.4 Create database syntax.
% instead of mod(). % is supported for C programmers and
for compatibility with PostgreSQL.
=, <>, <= ,<, >=,>, AND,
OR, or LIKE in a column statement
LAST_INSERT_ID.
See section 17.1.3 How can I get the unique ID for the last inserted row?
REGEXP or NOT REGEXP.
CONCAT() or CHAR() with one or more than two arguments. In
MySQL the above functions can take any number of arguments.
BIT_COUNT(), ELT(), FROM_DAYS(), FORMAT(),
IF(), PASSWORD(), ENCRYPT(),
PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(),
or WEEKDAY().
TRIM to trim substrings. ANSI SQL only supports removal
of single characters.
STD(), BIT_OR and BIT_AND group functions.
MIN() or MAX() as normal functions, not only group
functions.
REPLACE instead of DELETE + INSERT.
See section 7.13 REPLACE syntax
The following functionality is missing in the current version of MySQL. For the priority of new extensions you should consult: The MySQL Todo list. That is the latest version of the Todo list in this manual. See section F List of things we want to add to MySQL in the future (The TODO)..
The following will not work in MySQL:
SELECT * from table WHERE id IN (SELECT id from table2)
MySQL only supports INSERT ... SELECT... and REPLACE
... SELECT.... Independent sub-selects will be probably be available in
3.22.0. One can now use the function IN() in other context
however.
MySQL doesn't yet support SELECT ... INTO TABLE.... Currently
MySQL only supports SELECT ... INTO OUTFILE..., which is basicly the
same thing..
Transactions are not supported. MySQL will shortly support
atomic operations which is like transactions without rollback. With
atomic operations you can make a bunch of insert/select/whatever
commands and be guaranteed that no other thread will interfere. In this
context you won't usually need rollback. Currently you can do this with
the help of the LOCK TABLES/UNLOCK TABLES command.
See section 7.19 LOCK TABLES syntax
Triggers are not supported. The planned update language will be able to handle stored procedures, but without triggers. Triggers usually slow down everything, even for queries when they aren't needed.
The FOREIGN KEY syntax in MySQL exists only for compatibility
with other SQL vendors CREATE TABLE commands: It doesn't do anything.
The FOREIGN KEY syntax without ON DELETE .. is mostly used
for documentation purposes. Some ODBC applications may use this to
produce automatic WHERE clauses though, but this is usually
easy to override. FOREIGN KEY is sometimes used as a constraint check,
but this check is in practice unnecessary if rows are inserted into the tables
in the right order. MySQL only supports these commands because some
application require them to exists (but not work!).
In MySQL one can work around the problem that ON DELETE
... isn't implemented by adding the approative DELETE statement to
the application when one deletes records from a table that has
FOREIGN KEY. In practice this is as quick (in some case quicker)
and much more portable than using FOREIGN KEY.
Foreign keys are something that makes life very complicated, because the foreign key definition must be stored in a database and implementing them would mean that the whole 'nice approach' of using files that can be moved, copied and removed would be destroyed.
In the near future we will extend FOREIGN KEYS so that at least
the information will be saved and may be retrieved by mysqldump and
ODBC.
There are so many problems with the FOREIGN KEYs that we don't
know where to start.
INSERTING and
UPDATING records and in this case almost all FOREIGN KEY
checks are useless because one usually inserts records in the right
tables in the right order.
The only nice aspect of foreign key is that it gives ODBC and some other client programs the ability to see how a table is connected and use this to show connection diagrams and to help building applicatons.
MySQL will soon store the FOREIGN KEY definitions so that
a client can ask and receive an answer how the original connection was
made. The current .frm file format does not have any place for it.
MySQL doesn't support views, but this is on the TODO.
Some other SQL databases have -- as start comment. MySQL
has # as the start comment character, even if the mysql
command line tool removes all lines that starts with --.
You can also use the C comment style /* this is a comment */ with
MySQL.
See section 7.24 Comment syntax
MySQL will not support this degenerated comment style because we have had many problems with automatically generated SQL queries that have used something like the following code:
UPDATE table_name SET credit=credit-!payment!
Where instead of !payment! we automaticly insert the value of the payment.
What do you think will happen when 'payment' is negative ?
Because 1--1 is legal in SQL, we think is terrible that '--' means start comment.
If you have a sql program in a textfile that contains -- comments
you should use:
replace " --" " #" < text-file-with-funny-comments.sql | mysql database. instead of the normal mysql database < text-file-with-funny-comments.sql
You can also change the -- to # comments in the command file:
replace " --" " #" -- text-file-with-funny-comments.sql
Change them back with:
replace " #" " --" -- text-file-with-funny-comments.sql
Entry level SQL92. ODBC level 0-2.
GRANT. See section 7.21 GRANT syntax. (Compatibility function). This always succeeds. You should use the
MySQL privilege tables. See section 6.2 How does the privilege system work?
CREATE INDEX. See section 7.22 CREATE INDEX syntax (Compatibility function). This always succeeds. You
should create your index with CREATE TABLE. See section 7.6 CREATE TABLE syntax.
You can also use ALTER TABLE. See section 7.7 ALTER TABLE syntax.
DROP INDEX. See section 7.23 DROP INDEX syntax (Compatibility function). This always succeeds. You can use
ALTER TABLE to drop indexes. See section 7.7 ALTER TABLE syntax.
BLOB and TEXT types
If you want to GROUP BY or ORDER BY on a BLOB or
TEXT field, you must make the field into a fixed length
object. The standard way to do this is with the SUBSTRING
functions. If you don't do this only the first max_sort_length
(default=1024) will considered when sorting.
SELECT comment from table order by substring(comment,20);
MySQL doesn't support COMMIT-ROLLBACK. The problem with COMMIT-ROLLBACK is that to handle this efficiently it would require a completely different table layout than MySQL uses today. MySQL would also need extra threads that do automatic cleanups on the tables and the disk usage would be much higher. This would make MySQL about 2-4 times slower than it is today. One of the reasons that MySQL is so much faster than almost all other SQL databases (typical times are at least 2-3 times faster) is the lack of COMMIT-ROLLBACK.
For the moment, we are much more for implementing the SQL server language (someting like stored procedures). With this you would very seldom really need COMMIT-ROLLBACK. This would also give much better performance.
Loops that need transactions can normally be coded with the help of
LOCK TABLES, and one doesn't need cursors when one can update records
on the fly.
We have transactions and cursors on the TODO but not quite prioritised. If
it is implemented it will be as a option to CREATE TABLE. That
means that COMMIT-ROLLBACK will only work on those tables and only those
tables will be slower.
We at TcX have a greater need for a real fast database than a 100% general database. Whenever we find a way to implement these without any speed loss we will probably do it. For the moment there are many more important things to do. Check the TODO for how we prioritise things at the moment. Customers with higher levels of support can alter this, so things may be reprioritised.
The current problem is actually ROLLBACK. Without ROLLBACK
you can do anything with LOCK TABLES. To support ROLLBACK
MySQL would have to be changed to store all old records that were
updated and revert everything back to the starting point if
ROLLBACK was issued. For simple cases this isn't that hard to do
(the current isamlog could be used for this), but if one wants to have
ROLLBACK with ALTER/DROP/CREATE TABLE it would make
everything much harder to implement.
To avoid using ROLLBACK one can do:
LOCK TABLES ...
UNLOCK TABLES
This is usually much faster, but not always. The only thing this doesn't handle if someone does a kill on the process.
One can also use functions to update things in one operation. By doing all updates relatively and/or only updating those fields that actually have changed one can get a very efficient application.
For example, when we are doing updates on some customer information, we
only update the customer data that has changed and only test that none
of the changed data, or data that depends on the changed data, has
changed in the original row. The test for change is done with the
WHERE clause in the UPDATE statement. If the record wasn't
updated we give the client a message: "Some of the data you have changed
has been changed by another user", and then we show the old row versus
the new row in a window. The user can then decide which version of the
customer record he should use.
This gives us something like 'column locking' but actually even better, because we only update some of the columns with relative information. This means that a typical update statement looks something like:
UPDATE tablename SET pay_back=pay_back+'relative change' UPDATE customer set customer_date='current_date', address='new address', phone='new phone', money_he_owes_us=money_he_owes+'new_money' where customer_id=id and address='old address' and phone='old phone';
As you can see, this is very efficient and even if another client has changed the 'money_he_owes_us' or 'pay_back' amount this will still work.
In many cases, users have wanted ROLLBACK and/or LOCK
TABLES to manage unique identifiers for some tables. This can be
handled much more efficiently by using an AUTO_INCREMENT column
and the MySQL API function
mysql_insert_id. See section 17.1.3 How can I get the unique ID for the last inserted row?
At TcX we have never had any need for row level locking as we have always been able to code around it. I know some cases that really need row locking, but they are very few. If you want to have row level locking you can do something like:
UPDATE table_name SET row_flag=1 WHERE id=ID;
MySQL returns affected rows = 1 if the row was found and row_flag
wasn't 1 in the original document. On the TODO there is GET_LOCK and
RELEASE_LOCK for those that want to implement application level
locking.
MySQL has an advanced but non-standard security/privilege system.
The basic function of the MySQL privilege system is to give a username on a host select, insert, update and delete privileges on a database.
Extra functionality includes the ability to have a anonymous user and give
permission to use MySQL specific funtions like LOAD DATA
INFILE.
Please note that the user names for a SQL database, like MySQL, has
nothing to do with Unix users. As a convenience most MySQL clients
tries to log in with the current user name, but this may be changed
with the --user switch. This means that you can't in any way
make a database secure without having passwords for all users.
In MySQL the combination of host and user is the unique identity. Don't think of users, think of host+user and everything should be much clearer. You can for example have a user named 'Robb' at two different hosts (with different privileges) in MySQL without any conflicts.
The MySQL privilege system makes sure that each user may do exactly the things that they are supposed to be allowed to do. The system decides to grant different privileges depending on which xuser connects from which host to which database.
You can always test your privileges with the script mysqlaccess,
which Yves Carlier has provided for the MySQL distribution.
See section 6.7 Why do I get this Access denied? error.
See section 6.8 How to make MySQL secure against crackers.
All privileges are stored in three tables. user, host and
db.
Everything granted in the user table is valid for every database
that cannot be found in the db table. For this reason, it might
be wise to grant users (apart from superusers) privileges on a
per-database basis only.
The host table is mainly there to maintain a list of "secure"
servers. At TcX host contains a list of all machines on the
local network. These are granted all privileges.
The connecting user's privileges are calculated by the following algorithm:
| Table | Sorted by |
| host | host without wild/hosts with wild/empty hosts |
| db | host without wild/hosts with wild/empty hosts |
| user | host/user |
host = "". Within each host, sort by
user using the same rules. Finally, in the db table, sort by db
using the same rules. In the steps below, we will look through the
sorted tables and always use the first match found.
user table
using the first match found.
Call this set of privileges
Priv.
(Debian note: This should probably read "Get the privileges
for the connecting _host_ from the _host_ table...")
db table
using the first match found.
host = "" for the entry found in the db table, AND
Priv with the privileges for the host in the host table, i.e.
remove all privileges that are not "Y" in both. (If host <> "",
Priv is not affected. In such cases, host must have matched the
connecting host's name at least partially. Therefore it can be assumed
that the privileges found in this row match the connecting host's
profile.)
user
table, i.e. add all privileges that are "Y" in user.
mysqladmin reload to make the changes take effect.
The connecting user gets the set of privileges Priv.
Let's show an example of the sorting and matching! Suppose that the user
table contains this:
+-----------+----------+- | Host | User | ... +-----------+----------+- | % | root | ... | % | jeffrey | ... | localhost | root | ... | localhost | | ... +-----------+----------+-Then the search order will be:
localhost/any line, not by the any/jeffrey line. The
first match found is used!
So if you have access problems, print out the user table, sort it by
hand, and see where the match is being made.
Here follows an example to add a user 'custom' that can connect from hosts
'localhost', 'server.domain' and 'whitehouse.gov'. He wants to have password
'stupid'. The database 'bankaccount' he only want to use from 'localhost' and
the 'customer' database he wants to be able to reach from all three hosts.
shell> mysql 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
('%','customers','custom','Y','Y','Y','Y','Y','Y');
You can of course also use xmysqladmin, mysql_webadmin, mysqladmin and even
xmysql to insert/change and update values in the privilege tables.
You can find these utilities in the Contrib directory.
The grant tables privileges on rows are select, insert, update and delete.
The table and database privileges are create and drop. Create and drop are for both tables and databases. Since a user with a drop grant can delete any table, this is the same thing as a drop grant for the database.
Other privileges give the right to use files (for LOAD DATA INFILE and
SELECT INTO OUTFILE) and to use the administrative commands
shutdown, reload, refresh and process.
The privilege system is based on 3 tables.
user table
user
table has the following columns:
| Field | Type | Key | Default |
| Host | char(60) | PRI | "" |
| User | char(16) | PRI | "" |
| Password | char(16) | - | "" |
| Select_priv | enum('N','Y') | - | N |
| Insert_priv | enum('N','Y') | - | N |
| Update_priv | enum('N','Y') | - | N |
| Delete_priv | enum('N','Y') | - | N |
| Create_priv | enum('N','Y') | - | N |
| Drop_priv | enum('N','Y') | - | N |
| Reload_priv | enum('N','Y') | - | N |
| Shutdown_priv | enum('N','Y') | - | N |
| Process_priv | enum('N','Y') | - | N |
| File_priv | enum('N','Y') | - | N |
db table
Contains which databases a host+user is allowed to use, and what he can
do with the tables in each database. The db table has the
following columns:
| Field | Type | Key | Default |
| Host | char(60) | PRI | "" |
| Db | char(64) | PRI | "" |
| User | char(16) | PRI | "" |
| Select_priv | enum('N','Y') | - | N |
| Insert_priv | enum('N','Y') | - | N |
| Update_priv | enum('N','Y') | - | N |
| Delete_priv | enum('N','Y') | - | N |
| Create_priv | enum('N','Y') | - | N |
| Drop_priv | enum('N','Y') | - | N |
host table
Is only used in big networks as a lookup for empty host entries in the
db table. This means that if you want a user to be able to use the
database from all hosts in your network, you should put " as the host
name in the db table. In this case the host table should contain
a entry for every host in your network. The host table has the
following columns:
| Field | Type | Key | Default |
| Host | char(60) | PRI | "" |
| Db | char(64) | PRI | "" |
| Select_priv | enum('N','Y') | - | N |
| Insert_priv | enum('N','Y') | - | N |
| Update_priv | enum('N','Y') | - | N |
| Delete_priv | enum('N','Y') | - | N |
| Create_priv | enum('N','Y') | - | N |
| Drop_priv | enum('N','Y') | - | N |
% and _. Leaving any of these columns empty is
equivalent to setting it to '%'.
localhost, a hostname, an IP number or a
string with wildcards. An empty host in the db table means any host in
the host table. An empty host in the host or user table means any
host that can create a TCP connection to your server.
user table will be processed
as a no-name user.
db table.
This means that a superuser only needs to be in the user table with all
privilege-flags set to Y.
123.444.444.% in the host
table to give every user on an IP C-net access. To avoid the
possibility that somebody tries to fool this setup by naming a host
123.444.444.somewhere.com, MySQL disallows all hostnames
that start with digits and a dot. So if your host is named something like
1.2.foo.com it will never be allowed with name matching. Use the
IP number in this case.
To add privileges to the MySQL database:
This assumes the current user has insert privileges for the mysql db
table and reload privileges. The server (mysqld) has to be running. If
it is not, start it with safe_mysqld --log &.
> mysql mysql
insert into user values ('%','monty',password('something'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y') ;
insert into user (host,user,password) values('localhost','dummy',") ;
insert into user values ('%','admin',",'N','N','N','N','N','N','Y','N','Y','Y') ;
quit
> mysqladmin reload
This makes three new users:
Monty
admin
mysqladmin reload,
mysqladmin refresh and mysqladmin processlist. May be granted
individual database privileges through table db.
dummy
db.
The default privileges (set in `scripts/mysql_install_db')
let root do anything. Any user can do anything with any database whose
name is 'test' or starts with 'test_'. A normal user can't use
mysqladmin shutdown or mysqladmin processlist. See the
script (`scripts/mysql_install_db') for an example on how to add
other users.
The privilege tables are read into mysqld with mysqladmin reload.
A common mistake is to forget that passwords are stored encrypted, which leads to something like:
INSERT INTO user VALUES ('%','jeffrey','bLa81m0','Y','Y','Y','N','N','N','N','N', 'N','N');
Then (of course) a mysqladmin reload to make the authentication
change take effect, then trying to connect to the server:
$ ./mysql -h sqlserver -u jeffrey -p bLa81m0 test Access denied
Try this instead:
INSERT INTO user VALUES
('%','jeffrey',password('bLa81m0'),'Y','Y','Y','N','N','N','N','N','N','N');
As before, mysqladmin reload to make the authentication change
take effect.
Now things should work.
Access denied? error.mysql -u root test. This
should not give an error. You can also check if you have a file 'user.ISD'
in the mysql database directory (ordinary install_dir/var/mysql/user.ISD).
mysqladmin reload each time you change
the grant tables. Otherwise the old tables are still used!
--without-grant-tables option. Now you can change the MySQL
grant tables and use the script mysqlaccess to check if your
grant worked. mysqladmin reload tells the mysqld daemon to
start using the new grant tables.
mysql -u user database or
mysql -u user -ppassword database. Please notice that there is no
space between -p and the password. One can also use the
--password=your_password syntax to give the password!
(Debian note: Specifing a password on the command line is not secure!
Any user on your system may then find out your password by typing a command
like: ps auxww)
mysql -u user database then you have a problem with
the 'user' table. Check this by doing mysql -u root mysql and
select * from user. You should get an entry with 'hostname' and
'user' matching your computers hostname and your username. If the client
and the server are running on the same host and you haven't used the
--host option to mysql and you are not using MIT threads,
'localhost' is a synonym for your hostname.
Access denied error message will tell you who you are trying
to log in as, from which host you are trying to log in, and if you
were using a password or not. You should normally have one entry in the
user table that exactly matches your host and user, exactly as
given in the error message.
mysql -u root test works but mysql -h your_hostname -u
root test gives 'Access denied' then you don't have the right name for
your host in the user table. For example if you have an entry with host
'tcx' in the 'user' table, but your DNS tells MySQL that your
hostname is 'tcx.subnet.se' then the entry will not work. Test by adding
a record with the IP of your host in the 'user' table. You can,
natuarally, also add a host with a wildcard (for example 'tcx%') in the
'user table' (but using hostnames ending with % is pretty insecure).
mysql -u user database works on the server machine, but
mysql -u host -u user database doesn't work on another client
machine, then you don't have the client machine in the 'user' or the
'db' table.
mysql -u user test works but mysql -u user
other_database doesn't work, you don't have the other_database in the
'db' table.
SELECT
... INTO OUTFILE or LOAD DATA SQL commands then you probably
don't have the File_priv privilege set for you in the user table.
mysqld daemon with:
--debug=d,general,query. This will print info about the host and
user that tries to connect and also information about each command
issued. See section 18.10 Debugging MySQL.
mysqldump mysql command. As always, post your problem with the
mysqlbug script.
Can't connect to local mySQL server or
Can't connect to MySQL server on some_hostname this means that
the daemon mysqld is not running or you are trying to connect to the wrong
socket or port. Check that the socket exists (normally /tmp/mysql.sock) or
try to connect to the port with telnet: telnet hostname 3306.
You can also try mysqladmin version to get some more information.
To make a MySQL system secure you should think about the following:
mysql_install_db script or
only for the MySQL root user with:
mysql -u root -e "update user set password=password('new_password')
where user='root'" mysql
SELECT INTO OUTFILE
are generated readable by all and one can't overwrite existing files.
The following options to mysqld affects security:
--secure
get_hostbyname resolves
back to the original hostname. This is done to make it harder for
someone on the outside to get access by simulating another host.
This options also adds some sanity checks of hostnames.
This is turned off by default since it sometimes takes a long time to check this.
--skip-grant-tables
--skip-name-resolve
--skip-networking
A string may have ' or " around it.
\ is a escape character. The following escape characters are recognised:
\0
\n
\t
\r
\b
\'
' character.
\"
" character.
\\
\ character.
\%
% character. This is used in wildcard strings to search for
%.
\_
_ character. This is used in wildcard strings to search for
_.
A ' inside a string started with ' may be written as ". A " inside a string started with " may be written as "".
Some example selects that shows how it works.
MySQL> select 'hello', "'hello'", '""hello""', "'h"e"l"l"o"', "hel""lo"; 1 rows in set (0.00 sec) +-------+---------+-----------+-------------+--------+ | hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo | +-------+---------+-----------+-------------+--------+ | hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo | +-------+---------+-----------+-------------+--------+
mysql> select 'hello', "hello", '""hello""', "'ello", 'e"l"lo', '\'hello'; 1 rows in set (0.00 sec) +-------+-------+-----------+-------+--------+--------+ | hello | hello | ""hello"" | 'ello | e'l'lo | 'hello | +-------+-------+-----------+-------+--------+--------+ | hello | hello | ""hello"" | 'ello | e'l'lo | 'hello | +-------+-------+-----------+-------+--------+--------+
mysql> select "This\nIs\nFour\nlines"; 1 rows in set (0.00 sec) +--------------------+ | This Is Four lines | +--------------------+ | This Is Four lines | +--------------------+
If you want to insert binary data into a blob the following characters must be represented by escape sequences:
\0
\
'
"
One doesn't have to escape " inside ' and " inside
'.
If you write C code you can use the C API function
mysql_escape_string(char *to,char *from,uint length) to escape
characters for the INSERT clause. (Note that 'to' must be at least
2 times bigger than from). In perl you can use the quote function.
You should run the escape function on every possible string that may have a one of the above special characters!
Integers are just a sequence of digits. Floats use . as a decimal
separator.
Examples of valid numbers are: 1221, 294.42,
-32032.6809e+10.
NULL
When using the text file export formats, NULL may be represented
by \N. See section 7.14 LOAD DATA INFILE syntax
Database, table, index and column names all follow the same rules in MySQL.
A name may use alphanumeric characters from the default character set. This is by default ISO-8859-1 Latin1 but may be changed when compiling MySQL.
Since MySQL needs to be able to decide if something is a name or a number the following special cases occurs.
1e. This is because
expressions like 1e+1 may be interpreted like the expression
1e + 1 or the number 1e+1.
Punctuation characters like . and @ are not allowed in names
since they will be used to extend MySQL.
In MySQL you can refer to a column with some of the following syntaxes:
If you are using 'column' or 'table.column' you will get an error if the name is not unique among the used tables!
The following column types are supported:
| Name | Description | Size |
| TINYINT[(M)] [UNSIGNED] [ZEROFILL] | A very small integer. Signed range -128 - 127. Unsigned range 0 - 255. | 1 |
| SMALLINT[(M)]. [UNSIGNED] [ZEROFILL] | A small integer. Signed range -32768 - 32767. Unsigned range 0 - 65535. | 2 |
| MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] | A medium integer. Signed range -8388608-8388607. Unsigned range 0 - 16777215. | 3 |
| INT[(M)] [UNSIGNED] [ZEROFILL] | A normal integer. Signed range -2147483648 - 2147483647. Unsigned range 0 - 4294967295. | 4 |
| BIGINT[(M)] [UNSIGNED] [ZEROFILL] | A large integer. Signed range -9223372036854775808 - 9223372036854775807. Unsigned Range 0 - 18446744073709551615. Because all arithmetic is done with signed BIGINT or DOUBLE, one shouldn't use unsigned big integers bigger than 9223372036854775807 (63 bits) with anything else than bit functions! | 8 |
| FLOAT(Precision) | A small floating point number. Precision can be 4 or 8. FLOAT(4) is a single precision number and FLOAT(8) is a double precision number (se the DOUBLE entry). This syntax is for ODBC compatibility. Range -3.402823466E+38F - -1.175494351E-38, 0, -1.175494351E-38 - 3.402823466E+38F. | 4 |
| FLOAT[(M,D)] | A small floating point number. Cannot be unsigned. Range -3.402823466E+38F - -1.175494351E-38, 0, -1.175494351E-38 - 3.402823466E+38F. | 4 |
| DOUBLE PRECISION[(M,D)] | A normal floating point number. Cannot be unsigned. Range -1.7976931348623157E+308 - -2.2250738585072014E-308, 0, 2.2250738585072014E-308 - 1.7976931348623157E+308. | 8 |
| REAL[(M,D)] | Same as DOUBLE | 8 |
| DECIMAL [(M,D)] | An unpacked floating point number. Cannot be unsigned. Currently the same range maximum range as a double. Behaves as a CHAR column | M+D |
| NUMERIC [(M,D)] | Same as DECIMAL | M+D |
| TIMESTAMP [(M)] | An automatic timestamp. If you have many TIMESTAMP columns only the first one is automatic | 4 |
| DATE | A type to store date information. Uses the "YYYY-MM-DD" syntax, but may be updated with a number or a string. Understands at least the following syntaxes: 'YY-MM-DD', 'YYYY-MM-DD', 'YYMMDD' and full timestamps (YYYYMMDDHHMMDD). Range 0000-00-00 to 9999-12-31. | 3 |
| TIME | A type to store time information. Uses the "HH:MM:SS" syntax, but may be updated with a number or a string. Understands at least the following syntaxes: 'HH:MM:SS, 'HHMMSS', 'HHMM', 'HH'. | 3 |
| DATETIME | A type to store date and time information. Format "YYYY-MM-DD HH:MM:SS". Takes 8 bytes. Range '0000-01-01 00:00:00' - '9999-12-31 23:59:59'. | 8 |
| YEAR | A type to store years. Format "YYYY" or "YY". Takes 1 byte. Range 0, 1901-2155. 2 digits years in the range 00-69 is assumed to be 2000-2069 and will be sorted correctly. (now type for MySQL 3.22) | 1 |
| CHAR(M) [binary] | A fixed length string that is always filled up with spaces to the specified length. Range 1 - 255 characters. All end space are removed when retrieved. Is sorted and compared case insensitively unless the binary keyword is given. | M |
| VARCHAR(M) [binary] | A variable length string that is stored with its length. All end space are removed when storing it. Maximum range 1 - 255 characters. Is sorted and compared case insensitively unless the binary keyword is given. | L+1 |
| TINYTEXT and TINYBLOB |
A TEXT/BLOB with max length of 255 characters. | L+1 |
| TEXT and BLOB |
A TEXT/BLOB with max length of 65535 characters. | L+2 |
| MEDIUMTEXT and MEDIUMBLOB |
A TEXT/BLOB with max length of 16777216 characters. | L+3 |
| LONGTEXT and LONGBLOB |
A TEXT/BLOB with max length of 4294967295 characters. | L+4 |
| ENUM('value','value2',...) | A string object that can have only one set of allowed values (or NULL). See section 7.2.1 More about data types | 1 or 2 |
| SET('value','value2',...) | A string object that can have one or many values of a set of allowed values. See section 7.2.1 More about data types. | 1-8 |
In the above table L means the actual length of a instance and M the maximum length. So L+1 for "abcd" means 5 bytes in the database.
If you use any data type with an L in the length field you will get a variable length record format.
All integer types can have an optional argument unsigned. This
can be used when you only want to allow positive numbers in the column
or you need a little bigger numerical range for the column.
Also for all integer columnsn, the optional argument ZEROFILL means
that the column will be padded with zeroes up to the maximum length.
Max display size and decimals are for formatting and calculation of maximum column width.
When storing a value in an integer that is outside its range,
MySQL stores the maximum (or minimum) possible value. When
doing an ALTER TABLE or LOAD DATA INFILE one gets these
conversions as 'warnings'. We have on the TODO to fix INSERT and
UPDATE so they can return warnings, but this is scheduled for the next
protocol change.
For example when storing -999999999999999 into an int column the
value ends up as -2147483648. And 9999999999999999 ends up
as 2147483647.
And if the int is unsigned the stored values above becomes 0 and
4294967296.
The same rules go for all other integer types.
When returning data for an int(4) column that exceeds the allocated space,
MySQL will return 9.99. If the operation is an UPDATE
a warning will be issued.
Note that a type like decimal(4,2) means maximum 4 characters
with two decimal points. That gives a range between -.99 ->
9.99.
To avoid some rounding problems, MySQL always rounds everything
that it stores in any floating point column according to the number of
decimals. This means that 2.333 stored into float(8,2) is
stored as 2.33.
TIMESTAMP type
Has a range of 1 Dec 1970 time 0.00 to sometime in the year 2106 and a
resolution of one second. A TIMESTAMP column will automatically be updated
on INSERT and UPDATE statements if set to NULL or if
the column is not updated in the statement. Can be (part of) an index.
Note that if you have many timestamp columns in a row, then only the
first timestamp column will be automatically updated. Any timestamp column
will be set to the current time if set to NULL. Depending on
the display size one gets one of the following formats: "YYYY-MM-DD HH:MM:SS",
"YY-MM-DD HH:MM:SS", "YYYY-MM-DD" or "YY-MM-DD".
TEXT and BLOB types
These are objects that can have a variable length without upper limit.
All TEXT and BLOB objects are stored with their length (saved in 1 to 4
bytes depending on the type of object). The maximum TEXT and
BLOB length you can use is dependent on available memory and
client buffers. The only differences between TEXT and BLOB
is that TEXT is sorted and compared case insensitively while
BLOB is compared case sensitively (by character
values). TEXT and BLOB objects CANNOT be an index.
A BLOB is a binary large object which can hold any amount of data. There are 4 kinds of blobs See section 7.2 Column types.. Normally one can regard a BLOB as a VARCHAR without a specified limit.
TEXT is a BLOB that is sorted and compared case
insensitively.
A BLOB/TEXT column may not be bigger that the message
buffer. Note that you have to change the message buffer on both the
server and the client. See section 10.1 How does one change the size of MySQL buffers?.
MyODBC defines BLOBs as LONGVARBINARY and
TEXTs as LONGVARCHAR.
Restrictions for BLOB and TEXT columns:
BLOB or TEXT cannot be an index or a part of an index
BLOB or TEXT, only the first
max_sort_length (default 1024) of the blob is used. This value
can be changed by the -O option when starting the mysqld
daemon. One can group on an expression involving a BLOB/
TEXT: SELECT id,SUBSTR(blob,1,100) GROUP BY 2
BLOB and TEXT as
there is for CHAR and VARCHAR.
ENUM type
A string object that can have only one of a set of allowed values. The value
to be stored may be given case independently. If one tries to store a
non-existing value, "" is stored. If used in a number context this
object returns/stores the value index. If there is less than 255
possible values this object occupies 1 byte, else two bytes (with a
maximum of 65535 different values). Note that if an integer is put in the
ENUM you get the corresponding string with the first counting as
number 1. (0 is reserved for wrong enum values). Sorting on ENUM
types are done according to the order of the strings in the enum.
If declared NOT NULL the default value is the first value, else
the default value is NULL.
For example the column test ENUM("one","two", "three") can have
any of these values:
NULL "one" "two" "three"
SET type
A string object that can have one or many values from a set of allowed
values. Each value is separated by a ','. If used in a number context
this object returns/stores the bit positions of the used values. This
object occupies (number_of_different_values-1)/8+1 bytes, rounded up to
1,2,3,4 or 8. One can't have more than 64 different values. Note that if
an integer is put in the SET you get the corresponding string with
the first bit corresponding to the first string. Sorting on SET
types are done numerically.
For example the column test SET("one","two") NOT NULL can have any of
these values:
"" "one" "two" "one,two"
Normally on SELECT on a SET column with LIKE or FIND_IN_SET():
SELECT * from banner where banner_group LIKE '%value%';
SELECT * from banner where FIND_IN_SET('value',banner_group)>0;
But the following will also work:
SELECT * from banner where banner_group = 'v1,v2'; ;Exact match SELECT * from banner where banner_group & 1; ;Is in first group
Try to use the most precise type in all cases. For example for an integer
between 1-99999 a unsigned mediumint is the best type.
A common problem is representing monetary values accurately. In
MySQL you should use the DECIMAL type. This is stored as
a string so no loss of accuracy should occur. If accuracy is not to
too important the DOUBLE type may also be good enough.
For high precision you can always convert to a fixed point type
stored in a BITINT. This allows you to do all calculations with
integers and only convert the result back to floating point.
See section 10.12 What are the different row formats? Or when to use VARCHAR/CHAR?.
You can have indexes on all MySQL columns except BLOB
and TEXT types. Using indexes on the relevant columns is the best
way to improve the performance of selects.
For CHAR and VARCHAR columns you can have an index on a
prefix. The example below shows how to create an index for the first 10
characters of a column. This is much faster and requires less disk space
than having an index on the whole column.
CREATE TABLE test ( name CHAR(200) NOT NULL, KEY index_name (name(10));
MySQL can have one index on parts of different columns.
A multiple-column index can be considered a sorted array where the columns are concatenated. This makes for fast queries where the first column in the index is a known quantity and the other columns are not.
Suppose that you have a table:
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));
Then the index name is an index over last_name and first_name.
The name index will be used in the following queries:
SELECT * FROM test WHERE last_name="Widenius";
SELECT * FROM test WHERE last_name="Widenius" AND first_name="Michael";
SELECT * FROM test WHERE last_name="Widenius" AND
(first_name="Michael" OR first_name="Monty");
SELECT * FROM test WHERE last_name="Widenius" and
first_name >="M" and first_name < "N";
The name index will NOT be used in the following queries:
SELECT * FROM test WHERE first_name="Michael"; SELECT * FROM test WHERE last_name="Widenius" or first_name="Michael";
To support easier use of code from different SQL vendors, MySQL does supports the following mappings:
| binary(num) | char(num) binary |
| char varying | varchar |
| float4 | float |
| float8 | double |
| int1 | tinyint |
| int2 | smallint |
| int3 | mediumint |
| int4 | int |
| int8 | bigint |
| long varbinary | blob |
| long varchar | text |
| middleint | mediumint |
| varbinary(num) | varchar(num) binary |
SELECT and WHERE clauses
A select_expression or where_definition can consist of any
expression using the following functions:
In the examples below the output of the mysql program has been
shortened. So this:
mysql> select mod(29,9); 1 rows in set (0.00 sec) +-----------+ | mod(29,9) | +-----------+ | 2 | +-----------+
Has been converted to:
mysql> select mod(29,9); -> 2
(
)
mysql> select 1+2*3; -> 7 mysql> select (1+2)*3; -> 9
+
-
*
/
NULL.
mysql> select 102/(1-1); -> NULL
These have a range of maximum 64 bits because MySQL uses bigint (64 bit) arithmetic.
|
mysql> select 29 | 15; -> 31
&
mysql> select 29 & 15; -> 13
BIT_COUNT()
mysql> select bit_count(29); -> 4
All logical function return 1 (TRUE) or 0 (FALSE).
NOT
!
mysql> select NOT 1; -> 0 mysql> select NOT NULL; -> NULL mysql> select ! (1+1); -> 0 mysql> select ! 1+1; -> 1
OR
||
mysql> select 1 || 0; -> 1 mysql> select 0 || 0; -> 0 mysql> select 1 || NULL; -> 1
AND
&&
mysql> select 1 && NULL; -> 0 mysql> select 1 && 0; -> 0
Returns 1 (TRUE), 0 (FALSE) or NULL. These functions work for both
numbers and strings. MySQL uses the following rules to decide how the
compare is done:
TIMESTAMP or DATETIME column and
the other argument is a constant. In this case the constant is converted
to a timestamp before the comparasion. This is to be more ODBC
friendly.
If one or both of the arguments are NULL the result of the comparison
is NULL.
=
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
<>
!=
mysql> select '.01' <> '0.01'; -> 1 mysql> select .01 <> '0.01'; -> 0 mysql> select 'zapp' <> 'zappp'; -> 1
<=
mysql> select 0.1 <= 2; -> 1
<
mysql> select 2 <= 2; -> 1
>=
mysql> select 2 >= 2; -> 1
>
mysql> select 2 > 2; -> 0
ISNULL(A)
A is NULL else 0.
mysql> select isnull(1+1); -> 0 mysql> select isnull(1/0); -> 1
A BETWEEN B AND C
A is bigger or equal as B and A is smaller or equal
to C. Does the same thing as (A >= B AND A <= C) if
all arguments are of the same type. It's the first argument (A)
that decides how the comparison should be done! If A is a string
expression, compare as case insensitive strings. If A is a binary
string, compare as binary strings. If A is an integer expression
compare as integers, else compare as reals.
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
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
NOT (expr REGEXP expr).
mysql> select strcmp('text', 'text2'); -> -1
mysql> select strcmp('text2', 'text'); -> 1
mysql> select strcmp('text', 'text'); -> 0
IFNULL(A,B)
A is not NULL it returns A, else B.
mysql> select ifnull(1,0); -> 1 mysql> select ifnull(0,10); -> 0 mysql> select ifnull(1/0,10); -> 10
IF(A,B,C)
A is true (A <> 0 and A <> NULL) then return
B, else return C. A is evaluated as an INTEGER, which means
that if you are using floats you should also use a comparison operation.
mysql> select if(1>2,2,3); -> 3
All mathematical functions returns NULL in the case of a error.
-
mysql> select - 2; -> -2
ABS()
mysql> select abs(2); -> 2 mysql> select abs(-32); -> 32
SIGN()
mysql> select sign(-32); -> -1 mysql> select sign(0); -> 0 mysql> select sign(234); -> 1
MOD()
%
mysql> select mod(234, 10); -> 4 mysql> select 253 % 7; -> 1 mysql> select mod(29,9); -> 2
FLOOR()
mysql> select floor(1.23); -> 1 mysql> select floor(-1.23); -> -2
CEILING()
mysql> select ceiling(1.23); -> 2 mysql> select ceiling(-1.23); -> -1
ROUND(N)
N to an integer.
mysql> select round(-1.23); -> -1 mysql> select round(-1.58); -> -2 mysql> select round(1.58); -> 2
ROUND(Number,Decimals)
Number to a number with Decimals decimals.
mysql> select ROUND(1.298, 1); -> 1.3
EXP(N)
e (the base of natural logarithms) raised to
the power of N.
mysql> select exp(2); -> 7.389056 mysql> select exp(-2); -> 0.135335
LOG(X)
X.
mysql> select log(2); -> 0.693147 mysql> select log(-2); -> NULL
LOG10(X)
X.
mysql> select log10(2); -> 0.301030 mysql> select log10(100); -> 2.000000 mysql> select log10(-100); -> NULL
POW(X,Y)
POWER(X,Y)
X raised to the power of Y.
mysql> select pow(2,2); -> 4.000000 mysql> select pow(2,-2); -> 0.250000
sqrt(X)
X.
mysql> select sqrt(4); -> 2.000000 mysql> select sqrt(20); -> 4.472136
PI()
mysql> select PI(); -> 3.141593
COS(X)
X, where X is given in radians.
mysql> select cos(PI()); -> -1.000000
SIN(X)
X, where X is given in radians.
mysql> select sin(PI()); -> 0.000000
TAN(X)
X, where X is given in radians.
mysql> select tan(PI()+1); -> 1.557408
ACOS(X)
X; that is the value whose cosine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ACOS(1); -> 0.000000 mysql> select ACOS(1.0001); -> NULL mysql> select ACOS(0); -> 1.570796
ASIN(X)
X; that is the value whose sine is
X. If X is not in the range -1 to 1 NULL is
returned.
mysql> select ASIN(0.2); -> 0.201358
mysql> select ASIN('foo'); -> 0.000000
ATAN(X)
X; that is the value whose tangent is
X.
mysql> select ATAN(2); -> 1.107149 mysql> select ATAN(-2); -> -1.107149
ATAN2(X,Y)
X and Y. It is
similar to calculating the arc tangent of Y / X, except that the
signs of both arguments are used to determine the quadrant of the
result.
mysql> select ATAN(-2,2); -> -0.785398 mysql> select ATAN(PI(),0); -> 1.570796
COT(N)
N.
mysql> select COT(12); -> -1.57267341 mysql> select COT(0); -> NULL
RAND([X])
0 <= x <= 1.0, using the integer
expression X as the optional seed value.
mysql> SELECT RAND(); -> 0.5925 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(20); -> 0.1811 mysql> SELECT RAND(); -> 0.2079 mysql> SELECT RAND(); -> 0.7888One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
MIN(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MIN(2,0); -> 0 mysql> SELECT MIN(34,3,5,767); -> 3 mysql> SELECT MIN(a) from table where 1=0; -> NULL
MAX(X,Y...)
GROUP BY functions. The arguments are compared as numbers. If no
records are found NULL is returned.
mysql> SELECT MAX(34,3,5,767); -> 767 mysql> SELECT MAX(2,0,4,5,34); -> 34 mysql> SELECT MAX(a) from table where 1=0; -> NULL
DEGREES(N)
N converted from radians to degrees.
mysql> select DEGREES(PI()); -> 180.000000
RADIANS(N)
N converted from degrees to radians.
mysql> select RADIANS(90); -> 1.570796
TRUNCATE(Number, Decimals)
Number to Decimals decimals.
mysql> select TRUNCATE(1.223,1); -> 1.2 mysql> select TRUNCATE(1.999,1); -> 1.9 mysql> select TRUNCATE(1.999,0); -> 1
ASCII(S)
S. If
S is NULL return NULL.
mysql> SELECT ascii(2); -> 50
mysql> SELECT ascii('dx'); -> 100
CHAR(X,...)
NULLs are skipped.
mysql> SELECT char(77,121,83,81,'76'); -> 'MySQL'
CONCAT(X,Y...)
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
LENGTH(S)
OCTET_LENGTH(S)
CHAR_LENGTH(S)
CHARACTER_LENGTH(S)
mysql> SELECT length('text'); -> 4
mysql> SELECT octet_length('text'); -> 4
LOCATE(A,B)
POSITION(B IN A)
A substring in B. The first position
is 1. Returns 0 if A is not in B.
mysql> select locate('bar', 'foobarbar'); -> 4
mysql> select locate('xbar', 'foobar'); -> 0
INSTR(A,B)
B in string A. This is
the same as LOCATE with swapped parameters.
mysql> select instr('foobarbar', 'bar'); -> 4
mysql> select instr('xbar', 'foobar'); -> 0
LOCATE(A,B,C)
A in string B starting
at C.
mysql> select locate('bar', 'foobarbar',5); -> 7
LEFT(str,length)
mysql> select left('foobarbar', 5); -> 'fooba'
RIGHT(A,B)
SUBSTRING(A FROM B)
B characters from end of string A.
mysql> select right('foobarbar', 5); -> 'arbar'
mysql> select substring('foobarbar' from 5); -> 'arbar'
LTRIM(str)
mysql> select ltrim(' barbar'); -> 'barbar'
RTRIM(str)
TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
A prefixes and/or suffixes
removed from B. If BOTH, LEADING and
TRAILING isn't used BOTH are assumed. If A is not
given, then spaces are removed.
mysql> select trim(' bar '); -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx'); -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx'); -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz'); -> 'barx'
SOUNDEX(S)
S. Two strings that sound 'about the
same' should have identical soundex strings. A 'standard' soundex string
is 4 characters long, but this function returns an arbitrary long
string. One can use SUBSTRING on the result to get a 'standard'
soundex string. All non alpha characters are ignored in the given
string. All characters outside the A-Z range are treated as vocals.
mysql> select soundex('
expr IN (value,...)
IN list, else it
returns 0. If all values are constants, then all values are evaluated
according to the type of expr and sorted. The search for the item is
then done by using a binary search. This means IN is very quick
when used with constants in the IN part.
mysql> select 2 in (0,3,5,'wefwf'); -> 0 mysql> select 'wefwf' in (0,3,5,'wefwf'); -> 1
expr NOT IN (value,...)
NOT (expr IN (value,...))
expr LIKE expr
LIKE you have two wild characters.
% | Matches any number of characters, even zero characters. |
_ | Matches exactly one character. |
\% | Matches one %.
|
\_ | Matches one _.
|
mysql> select 'David!' like 'David_'; -> 1 mysql> select 'David!' like 'David\_'; -> 0 mysql> select 'David_' like 'David\_'; -> 1 mysql> select 'David!' like '%D%v%'; -> 1 mysql> select 10 like '1%'; -> 1
LIKE is allowed on numerical expressions! (Extension)
NOT (expr LIKE expr).
RLIKE
is for mSQL compatibility. NOTE: Because MySQL uses the
C escape syntax in strings (\n) You must double any '\'
that you uses in your REGEXP strings.
mysql> select 'Monty!' regexp 'm%y%%'; -> 0 mysql> select 'Monty!' regexp '.*'; -> 1 mysql> select 'new*\n*line' regexp '