Funkcje MySQL 4.1

NazwaOpisPrzykład
ASIN ASIN(X)
Returns the arc sine of X, that is, the...
mysql> SELECT ASIN(0.2);
-> 0.201358
mysql...
ASTEXT AsText(g)
Converts a value in internal geometry...
mysql> SELECT AsText(g) FROM geom;
+--------------...
ATAN ATAN(X)
Returns the arc tangent of X, that is, ...
mysql> SELECT ATAN(2);
-> 1.107149
mysql> ...
ATAN2 ATAN(Y,X)
ATAN2(Y,X)
Returns the arc tangent...
mysql> SELECT ATAN(-2,2);
-> -0.785398
mys...
AUTO_INCREMENTThe AUTO_INCREMENT attribute can be used to genera...CREATE TABLE animals (
id MEDIUMINT N...
AVG AVG([DISTINCT] expr)
Returns the average value ...
mysql> SELECT student_name, AVG(test_score)
->...
BACKUP TABLENote: This statement is deprecated. We are working...BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/t...
BENCHMARK BENCHMARK(count,expr)
The BENCHMARK() function ...
mysql> SELECT BENCHMARK(1000000,ENCODE('hello','go...
BETWEEN AND expr BETWEEN min AND max
If expr is greater tha...
mysql> SELECT 1 BETWEEN 2 AND 3;
-> 0
mysq...
BIGINT BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integ...
BIN BIN(N)
Returns a string representation of the b...
mysql> SELECT BIN(12);
-> '1100'
BINARY BINARY(M)

The BINARY type is similar to the CH...

BINARY OPERATOR BINARY
The BINARY operator casts the string fol...
mysql> SELECT 'a' = 'A';
-> 1
mysql> SELEC...
BIT BIT[(M)]

A bit-field type. M indicates the num...

BIT_AND BIT_AND(expr)
Returns the bitwise AND of all bi...
mysql> SELECT order.custid, customer.name, MAX(pay...
BIT_COUNT BIT_COUNT(N)
Returns the number of bits that ar...
mysql> SELECT BIT_COUNT(29);
-> 4
BIT_LENGTH BIT_LENGTH(str)
Returns the length of the strin...
mysql> SELECT BIT_LENGTH('text');
-> 32
BIT_OR BIT_OR(expr)
Returns the bitwise OR of all bits...
BIT_XOR BIT_XOR(expr)
Returns the bitwise XOR of all bi...
BLOBA BLOB is a binary large object that can hold a va...
BLOB TYPE BLOB[(M)]

A BLOB column with a maximum length ...

BOOLEAN BOOL
BOOLEAN
These are synonyms for TINYINT(...
BOUNDARY Boundary(g)
Returns a geometry that is the clos...
CACHE INDEXThe CACHE INDEX statement assigns table indexes to...CACHE INDEX
tbl_index_list [, tbl_index_list] .....
CASE CASE value WHEN [compare-value] THEN result [WH...mysql> SELECT CASE 1 WHEN 1 THEN 'one'
-> ...
CASTThe CAST() and CONVERT() functions can be used to ...SELECT enum_col FROM tbl_name ORDER BY CAST(enum_c...
CEILING CEILING(X)
CEIL(X)
Returns the smallest inte...
mysql> SELECT CEILING(1.23);
-> 2
mysql> S...
CHANGE MASTER TOCHANGE MASTER TO master_def [, master_def] ...

m...

CHAR [NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]

...

CHAR BYTECHAR BYTE is an alias for CHAR BINARY.
CHAR FUNCTION CHAR(N,...)
CHAR() interprets the arguments as ...
mysql> SELECT CHAR(77,121,83,81,'76');
-> ...
CHARACTER_LENGTH CHARACTER_LENGTH(str)

CHARACTER_LENGTH() is a ...

CHARSET CHARSET(str)
Returns the character set of the s...
mysql> SELECT CHARSET('abc');
-> 'latin1'
...
CHAR_LENGTH CHAR_LENGTH(str)

Returns the length of the str...

CHECKChecks a table or tables for errors. CHECK TABLE ...CHECK TABLE tbl_name [, tbl_name] ... [option] ......
CHECKSUMReports a table checksum.

If QUICK is specified,...

CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK |...
COALESCE COALESCE(value,...)
Returns the first non-NULL ...
mysql> SELECT COALESCE(NULL,1);
-> 1
mysql...
COERCIBILITY COERCIBILITY(str)
Returns the collation coercib...
mysql> SELECT COERCIBILITY('abc' COLLATE latin1_sw...
COLLATION COLLATION(str)
Returns the collation for the ch...
mysql> SELECT COLLATION('abc');
-> 'latin1...
COMPRESS COMPRESS(string_to_compress)
Compresses a strin...
mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
...
CONCAT CONCAT(str1,str2,...)
Returns the string that r...
mysql> SELECT CONCAT('My', 'S', 'QL');
-> ...
CONCAT_WS CONCAT_WS(separator,str1,str2,...)

CONCAT_WS()...

mysql> SELECT CONCAT_WS(',','First name','Second n...
CONNECTION_ID CONNECTION_ID()
Returns the connection ID (thre...
mysql> SELECT CONNECTION_ID();
-> 23786
CONSTRAINTThe syntax of a foreign key constraint definition ...CREATE TABLE product (category INT NOT NULL, id IN...
CONTAINS Contains(g1,g2)
Returns 1 or 0 to indicate whet...
CONV CONV(N,from_base,to_base)
Converts numbers betw...
mysql> SELECT CONV('a',16,2);
-> '1010'
my...
CONVERT_TZ CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ()
conv...

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GM...
COS COS(X)
Returns the cosine of X, where X is give...
mysql> SELECT COS(PI());
-> -1.000000
COT COT(X)
Returns the cotangent of X.
mysql> SELECT COT(12);
-> -1.57267341
mysq...
COUNT COUNT(expr)
Returns a count of the number of no...
mysql> SELECT student.student_name,COUNT(*)
->...
COUNT DISTINCT COUNT(DISTINCT expr,[expr...])
Returns a count ...
mysql> SELECT COUNT(DISTINCT results) FROM student...
CRC32 CRC32(expr)
Computes a cyclic redundancy check ...
mysql> SELECT CRC32('MySQL');
-> 325939755...
CREATE DATABASECREATE DATABASE creates a database with the given ...CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name...
CREATE INDEXIn MySQL 3.22 or later, CREATE INDEX is mapped to ...CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
...
CREATE TABLECREATE TABLE creates a table with the given name.
...
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
...
CREATE USERThe CREATE USER statement creates new MySQL accoun...CREATE USER user [IDENTIFIED BY [PASSWORD] 'passwo...
CREATE VIEWThis statement creates a new view, or replaces an ...
CREATE VIEW ALGORITHM MERGE TEMPTABLE WITH CHECK ...CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERG...
CROSSES Crosses(g1,g2)
Returns 1 if g1 spatially crosse...
CURDATE CURDATE()

Returns the current date as a value ...

mysql> SELECT CURDATE();
-> '1997-12-15'
m...
CURRENT_DATE CURRENT_DATE
CURRENT_DATE()

CURRENT_DATE an...

CURRENT_TIME CURRENT_TIME
CURRENT_TIME()

CURRENT_TIME an...

CURRENT_TIMESTAMP CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()

CURRE...

CURRENT_USER CURRENT_USER()
Returns the username and hostnam...
mysql> SELECT USER();
-> 'davida@localhost...
CURTIME CURTIME()

Returns the current time as a value ...

mysql> SELECT CURTIME();
-> '23:50:26'
mys...
DATABASE DATABASE()
Returns the default (current) databa...
mysql> SELECT DATABASE();
-> 'test'
DATEA date. The supported range is '1000-01-01' to '9...
DATE FUNCTION DATE(expr)

Extracts the date part of the date ...

mysql> SELECT DATE('2003-12-31 01:02:03');
...
DATE OPERATIONS DATE_ADD(date,INTERVAL expr type)
DATE_SUB(d...
mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 S...
DATEDIFF DATEDIFF(expr,expr2)

DATEDIFF() returns the n...

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997...
DATETIMEA date and time combination. The supported range ...
DATE_FORMAT DATE_FORMAT(date,format)
Formats the date value...
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '...
DAY DAY(date)

DAY() is a synonym for DAYOFMONTH()....

DAYNAME DAYNAME(date)
Returns the name of the weekday f...
mysql> SELECT DAYNAME('1998-02-05');
-> 'T...
DAYOFMONTH DAYOFMONTH(date)
Returns the day of the month f...
mysql> SELECT DAYOFMONTH('1998-02-03');
->...
DAYOFWEEK DAYOFWEEK(date)
Returns the weekday index
for d...
mysql> SELECT DAYOFWEEK('1998-02-03');
-> ...
DAYOFYEAR DAYOFYEAR(date)
Returns the day of the year for...
mysql> SELECT DAYOFYEAR('1998-02-03');
-> ...
DECIMAL DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

For My...

DECODE DECODE(crypt_str,pass_str)
Decrypts the encrypt...
DEFAULT DEFAULT(col_name)
Returns the default value for...
mysql> UPDATE t SET i = DEFAULT(i)+1 WHERE id
DEGREES DEGREES(X)
Returns the argument X, converted fr...
mysql> SELECT DEGREES(PI());
-> 180.000000
DELETEDELETE deletes rows from tbl_name that satisfy the...DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_na...
DESCRIBE{DESCRIBE | DESC} tbl_name [col_name | wild]

DESC...

DES_DECRYPT DES_DECRYPT(crypt_str[,key_str])

Decrypts a st...

DES_ENCRYPT DES_ENCRYPT(str[,(key_num|key_str)])

Encrypts ...

key_num des_key_str
DIMENSION Dimension(g)
Returns the inherent dimension of ...
mysql> SELECT Dimension(GeomFromText('LineString(1...
DISJOINT Disjoint(g1,g2)
Returns 1 or 0 to indicate whet...
DIVInteger division.
Similar to FLOOR() but safe with...
mysql> SELECT 5 DIV 2;
-> 2
DODO executes the expressions but doesn't return any...DO expr [, expr] ...
DOUBLE DOUBLE[(M,B)] [UNSIGNED] [ZEROFILL]

A normal-s...

DROP DATABASEDROP DATABASE drops all tables in the database and...DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
DROP INDEXDROP INDEX drops the index named index_name from t...DROP INDEX index_name ON tbl_name
DROP TABLEDROP TABLE removes one or more tables. You must ha...DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [,...
DROP VIEWDROP VIEW removes one or more views. You must have...DROP VIEW [IF EXISTS]
view_name [, view_name] ...
DUALSELECT ... FROM DUAL is an alias for SELECT ....
(...
ELT ELT(N,str1,str2,str3,...)
Returns str1 if N = 1...
mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
...
ENCODE ENCODE(str,pass_str)
Encrypt str using pass_str...
ENCRYPT ENCRYPT(str[,salt])
Encrypt str using the Unix ...
mysql> SELECT ENCRYPT('hello');
-> 'VxuFAJ...
ENDPOINT EndPoint(ls)
Returns the Point that is the end ...
mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql>...
ENUM ENUM('value1','value2',...)

An enumeration. A...

ENVELOPE Envelope(g)
Returns the Minimum Bounding Rectan...
EQUALS Equals(g1,g2)
Returns 1 or 0 to indicate whethe...
EXP EXP(X)
Returns the value of e (the base of natu...
mysql> SELECT EXP(2);
-> 7.389056
mysql> S...
EXPLAINThe EXPLAIN statement can be used either as a syno...EXPLAIN tbl_name
EXPORT_SET EXPORT_SET(bits,on,off[,separator[,number_of_bi...mysql> SELECT EXPORT_SET(5,'Y','N',',',4);
...
EXTERIORRING ExteriorRing(poly)
Returns the exterior ring of...
mysql> SET @poly =
-> 'Polygon((0 0,0 3,3 3,3 ...
EXTRACT EXTRACT(type FROM date)

The EXTRACT() function...

mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');
...
FIELD FIELD(str,str1,str2,str3,...)
Returns the index...
mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'he...
FIND_IN_SET FIND_IN_SET(str,strlist)
Returns a value 1 to N...
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
...
FLOAT FLOAT(p) [UNSIGNED] [ZEROFILL]

A floating-poin...

FLOOR FLOOR(X)
Returns the largest integer value not ...
mysql> SELECT FLOOR(1.23);
-> 1
mysql> SEL...
FLUSHYou should use the FLUSH statement if you want to ...FLUSH [LOCAL | NO_WRITE_TO_BINLOG] flush_option [,...
FLUSH QUERY CACHEYou can defragment the query cache to better utili...
FORMAT FORMAT(X,D)
Formats the number X to a format li...
mysql> SELECT FORMAT(12332.123456, 4);
-> ...
FOUND_ROWSA SELECT statement may include a LIMIT clause to ...mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
...
FROM_DAYS FROM_DAYS(N)
Given a daynumber N, returns a DAT...
mysql> SELECT FROM_DAYS(729669);
-> '1997-...
FROM_UNIXTIME FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(...
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
...
FUNCTIONA user-defined function (UDF) is a way to extend M...CREATE [AGGREGATE] FUNCTION function_name RETURNS ...
GEOMCOLLFROMTEXT GeomCollFromText(wkt[,srid])
GeometryCollect...
GEOMCOLLFROMWKB GeomCollFromWKB(wkb[,srid])
GeometryCollecti...
GEOMETRYMySQL provides a standard way of creating spatial ...mysql> CREATE TABLE geom (g GEOMETRY);
Query OK, 0...
GEOMETRY HIERARCHYGeometry is the base class. It's an abstract class...
GEOMETRYCOLLECTION GeometryCollection(g1,g2,...)
Constructs a WKB ...
GEOMETRYN GeometryN(gc,n)
Returns the n-th geometry in th...
mysql> SET @gc = 'GeometryCollection(Point(1 1),Li...
GEOMETRYTYPE GeometryType(g)
Returns as a string the name of...
mysql> SELECT GeometryType(GeomFromText('POINT(1 1...
GEOMFROMTEXT GeomFromText(wkt[,srid])
GeometryFromText(wk...
GEOMFROMWKB GeomFromWKB(wkb[,srid])
GeometryFromWKB(wkb[...
GET_FORMAT GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS...mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(...
GET_LOCK GET_LOCK(str,timeout)
Tries to obtain a lock wi...
mysql> SELECT GET_LOCK('lock1',10);
-> 1
m...
GLENGTH GLength(ls)
Returns as a double-precision numbe...
mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql>...
GRANT TYPESFor the GRANT and REVOKE statements, priv_type can...
GREATEST GREATEST(value1,value2,...)
With two or more ar...
mysql> SELECT GREATEST(2,0);
-> 2
mysql> S...
GROUP_CONCAT GROUP_CONCAT(expr)
This function returns a stri...
mysql> SELECT student_name,
-> GROUP_CONCA...
HANDLERThe HANDLER statement provides direct access to ta...HANDLER tbl_name OPEN [ AS alias ]
HANDLER tbl_nam...
HEX HEX(N_or_S)

If N_OR_S is a number, returns a s...

mysql> SELECT HEX(255);
-> 'FF'
mysql> SEL...
HOUR HOUR(time)
Returns the hour for time. The range...
mysql> SELECT HOUR('10:05:03');
-> 10
IF IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <...>
mysql> SELECT IF(1>2,2,3);
-> 3
mysql> SEL...
IFNULL IFNULL(expr1,expr2)
If expr1 is not NULL, IFNUL...
mysql> SELECT IFNULL(1,0);
-> 1
mysql> SEL...
IN expr IN (value,...)
Returns 1 if expr is any of...
mysql> SELECT 2 IN (0,3,5,'wefwf');
-> 0
m...
INET_ATON INET_ATON(expr)
Given the dotted-quad represent...
mysql> SELECT INET_ATON('209.207.224.40');
...
INET_NTOA INET_NTOA(expr)
Given a numeric network address...
mysql> SELECT INET_NTOA(3520061480);
-> '2...
INSERT INSERT(str,pos,len,newstr)
Returns the string s...
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
...
INSERT DELAYEDThe DELAYED option for the INSERT statement is a
M...
INSERT DELAYED ...
INSERT INTOINSERT inserts new rows into an existing table. T...INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [I...
INSERT SELECTINSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
...
INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp...
INSTR INSTR(str,substr)
Returns the position of the f...
mysql> SELECT INSTR('foobarbar', 'bar');
-...
INT INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size i...

INTERIORRINGN InteriorRingN(poly,n)
Returns the n-th interior...
mysql> SET @poly =
-> 'Polygon((0 0,0 3,3 3,3 ...
INTERSECTS Intersects(g1,g2)
Returns 1 or 0 to indicate wh...
INTERVAL INTERVAL(N,N1,N2,N3,...)
Returns 0 if N
mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200)...
IS IS boolean_value
IS NOT boolean_value
Tests ...
mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNO...
IS NULL IS NULL
IS NOT NULL
Tests whether a value is...
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
...
ISCLOSED IsClosed(ls)
Returns 1 if the LineString value ...
mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql>...
ISEMPTY IsEmpty(g)
Returns 1 if the geometry value g is...
ISNULL ISNULL(expr)
If expr is NULL, ISNULL() returns ...
mysql> SELECT ISNULL(1+1);
-> 0
mysql> SEL...
ISOLATIONSET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL...
ISSIMPLE IsSimple(g)

Currently, this function is a plac...

IS_FREE_LOCK IS_FREE_LOCK(str)
Checks whether the lock named...
IS_USED_LOCK IS_USED_LOCK(str)
Checks whether the lock named...
JOINMySQL supports the following JOIN syntaxes for the...mysql> SELECT table1.* FROM table1
-> L...
KILLEach connection to mysqld runs in a separate threa...KILL [CONNECTION | QUERY] thread_id
LAST_DAY LAST_DAY(date)

Takes a date or datetime value ...

mysql> SELECT LAST_DAY('2003-02-05');
-> '...
LAST_INSERT_ID LAST_INSERT_ID()
LAST_INSERT_ID(expr)
Return...
mysql> SELECT LAST_INSERT_ID();
-> 195
LCASE LCASE(str)

LCASE() is a synonym for LOWER().

LEAST LEAST(value1,value2,...)
With two or more argum...
mysql> SELECT LEAST(2,0);
-> 0
mysql> SELE...
LEFT LEFT(str,len)
Returns the leftmost len characte...
mysql> SELECT LEFT('foobarbar', 5);
-> 'fo...
LENGTH LENGTH(str)
Returns the length of the string st...
mysql> SELECT LENGTH('text');
-> 4
LIKE expr LIKE pat [ESCAPE 'escape-char']
Pattern ma...
mysql> SELECT 'David!' LIKE 'David_';
-> 1...
LINEFROMTEXT LineFromText(wkt[,srid])
LineStringFromText(...
LINEFROMWKB LineFromWKB(wkb[,srid])
LineStringFromWKB(wk...
LINESTRING LineString(pt1,pt2,...)
Constructs a WKB LineSt...
LN LN(X)
Returns the natural logarithm of X.
mysql> SELECT LN(2);
-> 0.693147
mysql> SE...
LOAD DATAThe LOAD DATA INFILE statement reads rows from a t...LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFI...
LOAD DATA FROM MASTERLOAD DATA FROM MASTER

Takes a snapshot of the mas...

LOAD INDEXThe LOAD INDEX INTO CACHE statement preloads a tab...LOAD INDEX INTO CACHE
tbl_index_list [, tbl_inde...
LOAD TABLE FROM MASTERLOAD TABLE tbl_name FROM MASTER

Transfers a copy ...

LOAD_FILE LOAD_FILE(file_name)
Reads the file and returns...
mysql> UPDATE tbl_name
SET blob_column=...
LOCALTIME LOCALTIME
LOCALTIME()

LOCALTIME and LOCALTI...

LOCALTIMESTAMP LOCALTIMESTAMP
LOCALTIMESTAMP()

LOCALTIMEST...

LOCATE LOCATE(substr,str)
LOCATE(substr,str,pos)

T...

LOCKLOCK TABLES locks tables for the current thread. ...LOCK TABLES
tbl_name [AS alias] {READ [LOCAL] ...
LOG LOG(X)
LOG(B,X)
If called with one parameter...
mysql> SELECT LOG(2);
-> 0.693147
mysql> S...
LOG10 LOG10(X)
Returns the base-10 logarithm of X.
mysql> SELECT LOG10(2);
-> 0.301030
mysql>...
LOG2 LOG2(X)
Returns the base-2 logarithm of X.
mysql> SELECT LOG2(65536);
-> 16.000000
my...
LONGBLOB LONGBLOB

A BLOB column with a maximum length o...

LONGTEXT LONGTEXT

A TEXT column with a maximum length o...

LOWER LOWER(str)
Returns the string str with all char...
mysql> SELECT LOWER('QUADRATICALLY');
-> '...
LPAD LPAD(str,len,padstr)
Returns the string str, le...
mysql> SELECT LPAD('hi',4,'??');
-> '??hi'...
LTRIM LTRIM(str)
Returns the string str with leading ...
mysql> SELECT LTRIM(' barbar');
-> 'barba...
MAKEDATE MAKEDATE(year,dayofyear)

Returns a date, given...

mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32)...
MAKETIME MAKETIME(hour,minute,second)

Returns a time va...

mysql> SELECT MAKETIME(12,15,30);
-> '12:1...
MAKE_SET MAKE_SET(bits,str1,str2,...)
Returns a set valu...
mysql> SELECT MAKE_SET(1,'a','b','c');
-> ...
MASTER_POS_WAIT MASTER_POS_WAIT(log_name,log_pos[,timeout])

Th...

SELECT MASTER_POS_WAIT('master_log_file', master_l...
MATCH AGAINSTAs of MySQL 3.23.23, MySQL has support for full-te...mysql> SELECT id, body, MATCH (title,body) AGAINST...
MBR DEFINITIONEvery geometry occupies some position in space. Th...((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX...
MBRCONTAINS MBRContains(g1,g2)
Returns 1 or 0 to indicate w...
mysql> SET @g1 = GeomFromText('Polygon((0 0,0 3,3 ...
MBRDISJOINT MBRDisjoint(g1,g2)
Returns 1 or 0 to indicate w...
MBREQUAL MBREqual(g1,g2)
Returns 1 or 0 to indicate whet...
MBRINTERSECTS MBRIntersects(g1,g2)
Returns 1 or 0 to indicate...
MBROVERLAPS MBROverlaps(g1,g2)
Returns 1 or 0 to indicate w...
MBRTOUCHES MBRTouches(g1,g2)
Returns 1 or 0 to indicate wh...
MBRWITHIN MBRWithin(g1,g2)
Returns 1 or 0 to indicate whe...
mysql> SET @g1 = GeomFromText('Polygon((0 0,0 3,3 ...
MD5 MD5(str)
Calculates an MD5 128-bit checksum for...
mysql> SELECT MD5('testing');
-> 'ae2b1fca...
MEDIUMBLOB MEDIUMBLOB

A BLOB column with a maximum length...

MEDIUMINT MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

A medium-...

MEDIUMTEXT MEDIUMTEXT

A TEXT column with a maximum length...

MERGE@menu
* MERGE table problems:: MERGE Table...
mysql> CREATE TABLE t1 (
-> a INT NOT NULL ...
MICROSECOND MICROSECOND(expr)

Returns the microseconds fro...

mysql> SELECT MICROSECOND('12:00:00.123456');
...
MID MID(str,pos,len)

MID(str,pos,len) is a synonym...

MIN MAX MIN([DISTINCT] expr)
MAX([DISTINCT] expr)
Re...
mysql> SELECT student_name, MIN(test_score), MAX(t...
MINUTE MINUTE(time)
Returns the minute for time, in th...
mysql> SELECT MINUTE('98-02-03 10:05:03');
...
MLINEFROMTEXT MLineFromText(wkt[,srid])
MultiLineStringFro...
MLINEFROMWKB MLineFromWKB(wkb[,srid])
MultiLineStringFrom...
MONTH MONTH(date)
Returns the month for date, in the ...
mysql> SELECT MONTH('1998-02-03');
-> 2
MONTHNAME MONTHNAME(date)
Returns the full name of the mo...
mysql> SELECT MONTHNAME('1998-02-05');
-> ...
MPOINTFROMTEXT MPointFromText(wkt[,srid])
MultiPointFromTex...
MPOINTFROMWKB MPointFromWKB(wkb[,srid])
MultiPointFromWKB(...
MPOLYFROMTEXT MPolyFromText(wkt[,srid])
MultiPolygonFromTe...
MPOLYFROMWKB MPolyFromWKB(wkb[,srid])
MultiPolygonFromWKB...
MULTILINESTRING MultiLineString(ls1,ls2,...)
Constructs a WKB M...
MULTIPOINT MultiPoint(pt1,pt2,...)
Constructs a WKB MultiP...
MULTIPOLYGON MultiPolygon(poly1,poly2,...)
Constructs a WKB ...
NOT BETWEEN expr NOT BETWEEN min AND max
This is the same a...
NOT IN expr NOT IN (value,...)
This is the same as NOT...
NOT LIKE expr NOT LIKE pat [ESCAPE 'escape-char']

This ...

NOT REGEXP expr NOT REGEXP pat
expr NOT RLIKE pat

This...

NOW NOW()

Returns the current date and time as a v...

mysql> SELECT NOW();
-> '1997-12-15 23:50:...
NULLIF NULLIF(expr1,expr2)
Returns NULL if expr1 = exp...
mysql> SELECT NULLIF(1,1);
-> NULL
mysql> ...
NUMGEOMETRIES NumGeometries(gc)
Returns the number of geometr...
mysql> SET @gc = 'GeometryCollection(Point(1 1),Li...
NUMINTERIORRINGS NumInteriorRings(poly)
Returns the number of in...
mysql> SET @poly =
-> 'Polygon((0 0,0 3,3 3,3 ...
NUMPOINTS NumPoints(ls)
Returns the number of points in t...
mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql>...
OCT OCT(N)
Returns a string representation of the o...
mysql> SELECT OCT(12);
-> '14'
OCTETLENGTH OCTET_LENGTH(str)

OCTET_LENGTH() is a synonym ...

OLD_PASSWORD OLD_PASSWORD(str)

OLD_PASSWORD() is available ...

ORD ORD(str)
If the leftmost character of the strin...
mysql> SELECT ORD('2');
-> 50
OVERLAPS Overlaps(g1,g2)
Returns 1 or 0 to indicate whet...
PASSWORDmysql> SELECT PASSWORD('badpwd');
-> '7f84...
PERIOD_ADD PERIOD_ADD(P,N)
Adds N months to period P (in t...
mysql> SELECT PERIOD_ADD(9801,2);
-> 19980...
PERIOD_DIFF PERIOD_DIFF(P1,P2)
Returns the number of months...
mysql> SELECT PERIOD_DIFF(9802,199703);
->...
PI PI()
Returns the value of PI. The default numbe...
mysql> SELECT PI();
-> 3.141593
mysql> SEL...
POINT Point(x,y)
Constructs a WKB Point using its coo...
POINTFROMTEXT PointFromText(wkt[,srid])
Constructs a POINT va...
POINTFROMWKB PointFromWKB(wkb[,srid])
Constructs a POINT val...
POINTN PointN(ls,n)
Returns the n-th point in the Line...
mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql>...
POLYFROMTEXT PolyFromText(wkt[,srid])
PolygonFromText(wkt...
POLYFROMWKB PolyFromWKB(wkb[,srid])
PolygonFromWKB(wkb[,...
POLYGON Polygon(ls1,ls2,...)
Constructs a WKB Polygon v...
POSITION POSITION(substr IN str)

POSITION(substr IN str...

mysql> SELECT LOCATE('bar', 'foobarbar');
...
POWER POW(X,Y)
POWER(X,Y)
Returns the value of X r...
mysql> SELECT POW(2,2);
-> 4.000000
mysql>...
PURGEPURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER...
PURGE MASTER LOGS BEFORE TOPURGE {MASTER | BINARY} LOGS TO 'log_name'
PURGE {...
QUARTER QUARTER(date)
Returns the quarter of the year f...
mysql> SELECT QUARTER('98-04-01');
-> 2
QUOTE QUOTE(str)
Quotes a string to produce a result ...
mysql> SELECT QUOTE('Don\'t!');
-> 'Don\'t...
RADIANS RADIANS(X)
Returns the argument X, converted fr...
mysql> SELECT RADIANS(90);
-> 1.570796
RAND RAND()
RAND(N)
Returns a random floating-poi...
mysql> SELECT RAND();
-> 0.9233482386203
m...
RELEASE_LOCK RELEASE_LOCK(str)
Releases the lock named by th...
RENAME TABLERENAME TABLE tbl_name TO new_tbl_name
[, tbl_n...
CREATE TABLE new_table (...);
RENAME TABLE old_tab...
RENAME USERThe RENAME USER statement renames existing MySQL a...RENAME USER old_user TO new_user
[, old_user T...
REPAIRREPAIR TABLE repairs a possibly corrupted table.
B...
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE
tbl_...
REPEAT REPEAT(str,count)
Returns a string consisting o...
mysql> SELECT REPEAT('MySQL', 3);
-> 'MySQ...
REPLACE REPLACE(str,from_str,to_str)
Returns the string...
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');...
REPLACE INTOREPLACE works exactly like INSERT, except that if ...REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_na...
RESETThe RESET statement is used to clear the state of ...RESET reset_option [, reset_option] ...
RESET MASTERRESET MASTER

Deletes all binary logs listed in th...

RESET SLAVERESET SLAVE

Makes the slave forget its replicatio...

RESTORERESTORE TABLE tbl_name [, tbl_name] ... FROM '/pa...
REVERSE REVERSE(str)
Returns the string str with the or...
mysql> SELECT REVERSE('abc');
-> 'cba'
RIGHT RIGHT(str,len)
Returns the rightmost len charac...
mysql> SELECT RIGHT('foobarbar', 4);
-> 'r...
RLIKE expr REGEXP pat
expr RLIKE pat

Performs a p...

mysql> SELECT 'Monty!' REGEXP 'm%y%%';
-> ...
ROUND ROUND(X)
ROUND(X,D)
Returns the argument X, ...
mysql> SELECT ROUND(-1.23);
-> -1
mysql> S...
ROW_COUNT ROW_COUNT()

ROW_COUNT() returns the number of ...

mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, ...
RPAD RPAD(str,len,padstr)
Returns the string str, ri...
mysql> SELECT RPAD('hi',5,'?');
-> 'hi???'...
RTRIM RTRIM(str)
Returns the string str with trailing...
mysql> SELECT RTRIM('barbar ');
-> 'barb...
SAVEPOINTSAVEPOINT identifier
ROLLBACK TO SAVEPOINT identif...
SECOND SECOND(time)
Returns the second for time, in th...
mysql> SELECT SECOND('10:05:03');
-> 3
SEC_TO_TIME SEC_TO_TIME(seconds)
Returns the seconds argume...
mysql> SELECT SEC_TO_TIME(2378);
-> '00:39...
SELECTSELECT is used to retrieve rows selected from one ...SELECT
[ALL | DISTINCT | DISTINCTROW ]
[...
SESSION_USER SESSION_USER()

SESSION_USER() is a synonym for...

SETSET sets different types of variables that affect ...SET variable_assignment [, variable_assignment] .....
SET GLOBAL SQL_SLAVE_SKIP_COUNTERSET GLOBAL SQL_SLAVE_SKIP_COUNTER = n

Skip the ne...

SET PASSWORDSET PASSWORD = PASSWORD('some password')
SET PASSW...
SET SQL_LOG_BINSET SQL_LOG_BIN = {0|1}

Disables or enables binar...

SHA SHA1(str)
SHA(str)
Calculates an SHA1 160-bi...
mysql> SELECT SHA1('abc');
-> 'a9993e36470...
SHOWSHOW has many forms that provide information abou...
SHOW BINLOGSHOW BINLOG EVENTS
[IN 'log_name'] [FROM pos] [...
SHOW CREATE VIEWThis statement shows a CREATE VIEW statement that ...SHOW CREATE VIEW view_name
SHOW ENGINESHOW ENGINE displays log or status information abo...SHOW ENGINE engine_name {LOGS | STATUS }
SHOW MASTER LOGSSHOW MASTER LOGS
SHOW BINARY LOGS

Lists the binar...

SHOW MASTER STATUSSHOW MASTER STATUS

Provides status information on...

SHOW SLAVE HOSTSSHOW SLAVE HOSTS

Displays a list of slaves curren...

SHOW SLAVE STATUSSHOW SLAVE STATUS

Provides status information on
...

SIGN SIGN(X)
Returns the sign of the argument as -1,...
mysql> SELECT SIGN(-32);
-> -1
mysql> SELE...
SIN SIN(X)
Returns the sine of X, where X is given ...
mysql> SELECT SIN(PI());
-> 0.000000
SMALLINT SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

A small in...

SOUNDEX SOUNDEX(str)
Returns a soundex string from str....
mysql> SELECT SOUNDEX('Hello');
-> 'H400'
...
SOUNDS LIKE expr1 SOUNDS LIKE expr2

This is the same as SO...

SPACE SPACE(N)
Returns a string consisting of N space...
mysql> SELECT SPACE(6);
-> ' '
SPATIALMySQL can create spatial indexes using syntax simi...
SQRT SQRT(X)
Returns the non-negative square root of...
mysql> SELECT SQRT(4);
-> 2.000000
mysql> ...
SRID SRID(g)
Returns an integer indicating the Spati...
mysql> SELECT SRID(GeomFromText('LineString(1 1,2 ...
START SLAVESTART SLAVE [thread_type [, thread_type] ... ]
STA...
START TRANSACTIONBy default, MySQL runs with autocommit mode enable...START TRANSACTION;
SELECT @A:=SUM(salary) FROM tab...
STARTPOINT StartPoint(ls)
Returns the Point that is the st...
mysql> SET @ls = 'LineString(1 1,2 2,3 3)';
mysql>...
STDDEV STD(expr)
STDDEV(expr)
Returns the populatio...
STDDEV_POP STDDEV_POP(expr)
Returns the population standar...
STDDEV_SAMP STDDEV_SAMP(expr)
Returns the sample standard d...
STOP SLAVESTOP SLAVE [thread_type [, thread_type] ... ]

thr...

STRCMP STRCMP(expr1,expr2)
STRCMP()
returns 0 if the s...
mysql> SELECT STRCMP('text', 'text2');
-> ...
STR_TO_DATE STR_TO_DATE(str,format)
This is the reverse fun...
@c next example commented out until format string ...
SUBDATE SUBDATE(date,INTERVAL expr type)
SUBDATE(exp...
SUBSTRING SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
...
mysql> SELECT SUBSTRING('Quadratically',5);
...
SUBSTRING_INDEX SUBSTRING_INDEX(str,delim,count)
Returns the su...
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.'...
SUBTIME SUBTIME(expr,expr2)

SUBTIME() subtracts expr2...

mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999'...
SUM SUM([DISTINCT] expr)
Returns the sum of expr. ...
SYSDATE SYSDATE()

SYSDATE() is a synonym for NOW().

SYSTEM_USER SYSTEM_USER()

SYSTEM_USER() is a synonym for U...

TAN TAN(X)
Returns the tangent of X, where X is giv...
mysql> SELECT TAN(PI()+1);
-> 1.557408
TEXT TYPE TEXT[(M)]

A TEXT column with a maximum length ...

TIME TIME

A time. The range is '-838:59:59' to '83...

TIME FUNCTION TIME(expr)

Extracts the time part of the time ...

mysql> SELECT TIME('2003-12-31 01:02:03');
...
TIMEDIFF TIMEDIFF(expr,expr2)

TIMEDIFF() returns the t...

mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',
...
TIMESTAMP TIMESTAMP[(M)]

A timestamp. The range is '197...

TIMESTAMP FUNCTION TIMESTAMP(expr)
TIMESTAMP(expr,expr2)

With ...

mysql> SELECT TIMESTAMP('2003-12-31');
-> ...
TIMESTAMPADD TIMESTAMPADD(interval,int_expr,datetime_expr)

...

TIMESTAMPADD FUNCTIONmysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');...
TIMESTAMPDIFF TIMESTAMPDIFF(interval,datetime_expr1,datetime_...
TIMESTAMPDIFF FUNCTIONmysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','20...
TIME_FORMAT TIME_FORMAT(time,format)
This is used like the ...
TIME_TO_SEC TIME_TO_SEC(time)
Returns the time argument, co...
mysql> SELECT TIME_TO_SEC('22:23:00');
-> ...
TINYBLOB TINYBLOB

A BLOB column with a maximum length o...

TINYINT TINYINT[(M)] [UNSIGNED] [ZEROFILL]

A very smal...

TINYTEXT TINYTEXT

A TEXT column with a maximum length o...

TOUCHES Touches(g1,g2)
Returns 1 or 0 to indicate wheth...
TO_DAYS TO_DAYS(date)
Given a date date, returns a dayn...
mysql> SELECT TO_DAYS(950501);
-> 728779
m...
TRIM TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM...mysql> SELECT TRIM(' bar ');
-> 'bar'
m...
TRUE FALSETRUE and FALSE added as alias for 1 and 0, respect...
TRUNCATE TRUNCATE(X,D)
Returns the number X, truncated t...
mysql> SELECT TRUNCATE(1.223,1);
-> 1.2
my...
TRUNCATE TABLETRUNCATE TABLE empties a table completely.
Logical...
TRUNCATE TABLE tbl_name
TYPE SET SET('value1','value2',...)

A set. A string ob...

UCASE UCASE(str)

UCASE() is a synonym for UPPER().

UNCOMPRESS UNCOMPRESS(string_to_uncompress)
Uncompresses a...
mysql> SELECT UNCOMPRESS(COMPRESS('any string'));
...
UNCOMPRESSED_LENGTH UNCOMPRESSED_LENGTH(compressed_string)
Returns ...
mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT(...
UNHEX UNHEX(str)

Does the opposite of HEX(str). That...

mysql> SELECT UNHEX('4D7953514C');
-> 'MyS...
UNIONUNION is used to combine the result from many SELE...SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UN...
UNIX_TIMESTAMP UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
If cal...
mysql> SELECT UNIX_TIMESTAMP();
-> 8822263...
UPDATEThe UPDATE statement updates columns in existing t...UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET co...
UPPER UPPER(str)
Returns the string str with all char...
mysql> SELECT UPPER('Hej');
-> 'HEJ'
USER USER()

Returns the current MySQL username and ...

mysql> SELECT USER();
-> 'davida@localhost...
UTC_DATE UTC_DATE
UTC_DATE()
Returns the current UTC ...
mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
...
UTC_TIME UTC_TIME
UTC_TIME()
Returns the current UTC ...
mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
...
UTC_TIMESTAMP UTC_TIMESTAMP
UTC_TIMESTAMP()
Returns the cu...
mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0...
UUID UUID()

Returns a Universal Unique Identifier (...

mysql> SELECT UUID();
-> '6ccd780c-baba-10...
VARBINARY VARBINARY(M)

The VARBINARY type is similar to ...

VARCHAR [NATIONAL] VARCHAR(M) [BINARY]

A variable-leng...

VARIANCE VARIANCE(expr)
Returns the population standard ...
VAR_POP VAR_POP(expr)
Returns the population standard v...
VAR_SAMP VAR_SAMP(expr)
Returns the sample variance of e...
VERSION VERSION()
Returns a string that indicates the M...
mysql> SELECT VERSION();
-> '4.1.3-beta-lo...
WEEK WEEK(date[,mode])
The function returns the week...
mysql> SELECT WEEK('1998-02-20');
-> 7
mys...
WEEKDAY WEEKDAY(date)
Returns the weekday index for
dat...
mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
...
WEEKOFYEAR WEEKOFYEAR(date)

Returns the calendar week of ...

mysql> SELECT WEEKOFYEAR('1998-02-20');
->...
WITHIN Within(g1,g2)
Returns 1 or 0 to indicate whethe...
WKT DEFINITIONThe Well-Known Text (WKT) representation of Geomet...
X X(p)
Returns the X-coordinate value for the poi...
mysql> SELECT X(GeomFromText('Point(56.7 53.34)'))...
XOR XOR
Logical XOR.
Returns NULL if either operand...
mysql> SELECT 1 XOR 1;
-> 0
mysql> SELECT ...
Y Y(p)
Returns the Y-coordinate value for the poi...
mysql> SELECT Y(GeomFromText('Point(56.7 53.34)'))...
YEAR YEAR(date)
Returns the year for date, in the ra...
mysql> SELECT YEAR('98-02-03');
-> 1998
YEAR TYPE YEAR[(2|4)]

A year in two-digit or four-digit ...

YEARWEEK YEARWEEK(date)
YEARWEEK(date,start)
Returns ...
mysql> SELECT YEARWEEK('1987-01-01');
-> 1...
^ ^
Bitwise XOR:
mysql> SELECT 1 ^ 1;
-> 0
mysql> SELECT 1 ...
| |
Bitwise OR:
mysql> SELECT 29 | 15;
-> 31
|| OR
||
Logical OR.
When both operands are non...
mysql> SELECT 1 || 1;
-> 1
mysql> SELECT 1...
~ ~
Invert all bits.
mysql> SELECT 5 & ~1;
-> 4