select

select_priv

Tables

Allow you to perform operations on rows in existing tables in a database.

insert

insert_priv

update

update_priv

delete

delete_priv

index

index_priv

Allow to create or remove indexes.

alter

alter_priv

Allows to use ALTER TABLE

create

create_priv

Databases, tables or indexes

Allows to create new databases and tables

drop

drop_priv

Databases or tables

Allows to drop databases and tables

grant

grant_priv

Alow you to give to other users those privileges you yourself possess.

reference

reference_priv

reload

reload_priv

Server administration

Are used for administrative operations.

shutdown

shutdown_priv

process

process _priv

file

file_priv

File access on server

Gives you permission to read and write files on the server using the LOAD INFILE and SELECT ... INTO OUTFILE.

Privilege

Commands permitted to privilege holders

reload

reload, refresh, flush-privileges, flush-hosts, flush-logs, flush-tables

shutdown

shutdown

Process

processlist, kill

Scope fields

host

host

host

user

db

db

password

user

Privilege fields

select_priv

select_priv

select_priv

insert_priv

insert_priv

insert_priv

update_priv

update_priv

update_priv

delete_priv

delete_priv

delete_priv

index_priv

index_priv

index_priv

alter_priv

alter_priv

alter_priv

create_priv

create_priv

create_priv

drop_priv

drop_priv

drop_priv

grant_priv

grant_priv

grant_priv

reload_priv

shutdown_priv

process_priv

file_priv

Scope fields

host

host

db

db

user

user

table_name

table_name

column_name

Privilege fields

table_priv

column_priv

column_priv

Other fields

timestamp

timestamp

grantor

host

CHAR(60)

user

CHAR(16)

password

CHAR(16)

db

CHAR(64) and CHAR(60) for tables_priv and columns_priv tables.

The default value for each string is the empty string.

tables_priv

table_priv

'Select', 'Insert', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'

tables_priv

column_priv

'Select', 'Insert', 'Delete', 'References'

columns_priv

column_priv

'Select', 'Insert', 'Delete', 'References'

\0

An ASCII 0 (NUL) character.

\n

A newline character.

\t

A tab character.

\r

A carriage return character.

\b

A backspace character.

\'

A single quote (´ ' ´) character.

\“

A double quote (´ ” ´) character.

\\

A backslash (´\´) character.

\%

A ´%´ character. This is used to search for literal instances of ´%´ in contexts where ´%´ would otherwise be intercepted as a wildcard character.

\_

A '_' character. This is used to search for literal instances of '_' in contexts where '_' would otherwise be interpreted as a wildcard character.

Database

64

Any character that is allowed

Table

64

Column

64

All characters

Alias

255

All characters

You can't have ASCII(0) or ASCII(255) in an identifier.

col_name

Column col_name from whichever table used in the query contains a column of that name.

tbl_name.col_name

Column col_name from table tbl_name of the current database.

db_name.tbl_name.col_name

Column name from table tbl_name of the database db_name. This form is available in MySQL 3.22 or later.

'column_name'

A column that is that a keyword od contains special characters.

D

Applies to floating-point types and indicate the number of digits following the decimal point. The maximum possible value is 30, but should be no greater than M-2.

Square brackets indicate parts of type specifiers that are optional.

If you specify ZEROFILL for a column, MySQL will automatically ad the UNSIGNED attribute to the column.

Type

Description

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

SMALLINT [(M)] [UNSIGNED] [ZEROFILL]
2 bytes

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

INT [(M)] [UNSIGNED] [ZEROFILL]
4 bytes

INTEGER [(M)] [UNSIGNED] [ZEROFILL]
4 bytes

BIGINT [(M)] [UNSIGNED] [ZEROFILL]
8 bytes

FLOAT [(M,D)] [ZEROFILL]
4 if X <= 24 or 8 if 25 <= X <= 53
FLOAT
4 bytes

DOUBLE [(M,D)] [ZEROFILL]
8 bytes

DOUBLEPRECISION [(M,D)] [ZEROFILL]

REAL [(M,D)] [ZEROFILL]

DECIMAL [(M,D)] [ZEROFILL]
M bytes (D+2, if M < D)

NUMERIC [(M,D)] [ZEROFILL]
M bytes (D+2, if M < D)

Type

Description

DATE
3 bytes

DATETIME
8 bytes

TIMESTAMP [(M)]
4 bytes

TIME
3 bytes

YEAR [(2/4)]
1 byte

Type

Description

CHAR(M) [BINARY]
M bytes, 1 <= M <= 255

[NATIONAL] VARCHAR(M) [BINARY]
L+1 bytes, where L <= M and 1 <= M <= 255

TINYBLOB, TINYTEXT
L+1 bytes, where L < 2^8

BLOB, TEXT
L+2 bytes, where L < 2^16

MEDIUMBLOB, MEDIUMTEXT
L+3 bytes, where L < 2^24

LONGBLOB, LONGTEXT
L+4 bytes, where L < 2^32

ENUM('value1','value2',...)
1 or 2 bytes, depending on the number of enumeration values (65535 values maximum)

SET('value1','value2',...)
1, 2, 3, 4 or 8 bytes, depending on the number of set members (64 members maximum)

BINARY (NUM)

CHAR(NUM) BINARY

CHAR VARYING(NUM)

VARCHAR(NUM)

FLOAT4

FLOAT

FLOAT8

DOUBLE

INT1

TINYINT

INT2

SMALLINT

INT3

MEDIUMINT

INT4

INT

INT8

BIGINT

LONG VARBINARY

MEDIUMBLOB

LONG VARCHAR

MEDIUMTEXT

MIDDLEINT

MEDIUMINT

VARBINARY(NUM)

VARCHAR(NUM) BINARY

|

mysql> select 29 | 15;
        -> 31

mysql> select 29 & 15;
        -> 13

<<

mysql> select 1 << 2
        -> 4

>>

mysql> select 4 >> 2
        -> 1

~

mysql> select 5 & ~1
        -> 4

BIT_COUNT(N)

mysql> select BIT_COUNT(29);
        -> 4

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

=

Equal 
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 0.1 <= 2;
        -> 1

<

mysql> select 2 <= 2;
        -> 1

>=

mysql> select 2 >= 2;
        -> 1

>

mysql> select 2 > 2;
        -> 0

<=>

mysql> select 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1 1 0

IS NULL

IS NOT NULL

Test whether or not a value is or is not NULL  
mysql> select 1 IS NULL, 0 IS NULL, NULL IS NULL:
        -> 0 0 1
mysql> select 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1 1 0

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. The first argument (expr) determines how the comparison is performed. If expr is a case-insensitive string expression, a case-insensitive string comparison is done. If expr is a case-sensitive string expression, a case-sensitive string comparison is done. If expr is an integer expression, an integer comparison is done. Otherwise, a floating-point (real) comparison is done.  
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,...)

Returns 1 if expr is any of the values in the IN list, else 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 using a binary search. This means IN is very quick if the IN value list consists entirely of constants. If expr is a case-sensitive string expression, the string comparison is performed in case-sensitive fashion.  
mysql> select 2 IN (0,3,5,'wefwf');
        -> 0
mysql> select 'wefwf' IN (0,3,5,'wefwf');
        -> 1

expr NOT IN (value,...)

ISNULL(expr)

COALESCE(list)

mysql> select COALESCE(NULL,1);
        -> 1
mysql> select COALESCE(NULL,NULL,NULL);
        -> NULL

INTERVAL(N,N1,N2,N3,...)

Returns 0 if N < N1, 1 if N < N2 and so on. All arguments are treated as integers. It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast).  
mysql> select INTERVAL(23, 1, 15, 17, 30, 44, 200);
        -> 3
mysql> select INTERVAL(10, 1, 10, 100, 1000);
        -> 2
mysql> select INTERVAL(22, 23, 30, 44, 200);
        -> 0

\%

Matches one % character

\_

Matches one _ character

ABS(X)

SIGN(X)

MOD(N,M)

%

FLOOR(X)

CEILING(X)

ROUND(X)

ROUND(X,D)

EXP(X)

LOG(X)

LOG10(X)

POW(X,Y)

POWER(X,Y)

SQRT(X)

PI()

COS(X)

SIN(X)

TAN(X)

ACOS(X)

ASIN(X)

ATAN(X)

ATAN2(X,Y)

COT(X)

RAND()

RAND(N)

LEAST(X,Y,...)

• If the return value is used in an INTEGER context, or all arguments are integer-valued, they are compared as integers.
• If the return value is used in a REAL context, or all arguments are real-valued, they are compared as reals.
• If any argument is a case-sensitive string, the arguments are compared as case-sensitive strings.
• In other cases, the arguments are compared as case-insensitive strings.

GREATEST(X,Y,...)

DEGREES(X)

RADIANS(X)

TRUNCATE(X,D)

ASCII(str)
Returns the ASCII code value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL.

ORD(str)
If the leftmost character of the string str is a multi-byte character, returns the code of multi-byte character by returning the ASCII code value of the character in the format of: ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]. If the leftmost character is not a multi-byte character, returns the same value as the like ASCII() function does.

CONV(N,from_base,to_base)
Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV works with 64-bit precision.

BIN(N)
Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL.

OCT(N)
Returns a string representation of the octal value of N, where N is a longlong number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL.

HEX(N)
Returns a string representation of the hexadecimal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,16). Returns NULL if N is NULL

CHAR(N,...)
CHAR() interprets the arguments as integers and returns a string consisting of the characters given by the ASCII code values of those integers. NULL values are skipped.

CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. May have more than 2 arguments. A numeric argument is converted to the equivalent string form.

LENGTH(str)

OCTET_LENGTH(str)

CHAR_LENGTH(str)

CHARACTER_LENGTH(str)

LOCATE(substr,str)

POSITION(substr IN str)

LOCATE(substr,str,pos)
Returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str

INSTR(str,substr)
Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the arguments are swapped.

LPAD(str,len,padstr)
Returns the string str, left-padded with the string padstr until str is len characters long.

RPAD(str,len,padstr)
Returns the string str, right-padded with the string padstr until str is len characters long.

LEFT(str,len)

RIGHT(str,len)

SUBSTRING(str,pos,len)

SUBSTRING(str FROM pos FOR len)

MID(str,pos,len)

SUBSTRING(str,pos)

SUBSTRING(str FROM pos)

SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str after count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.

LTRIM(str)

RTRIM(str)

TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)

SOUNDEX(str)
Returns a soundex string from str. Two strings that sound ``about the same'' should have identical soundex strings. A ``standard'' soundex string is 4 characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a ``standard'' soundex string. All non-alphanumeric characters are ignored in the given string. All international alpha characters outside the A-Z range are treated as vowels.

SPACE(N)

REPLACE(str,from_str,to_str)
Returns the string str with all all occurrences of the string from_str replaced by the string to_str.

REPEAT(str,count)
Returns a string consisting of the string str repeated count times. If count <= 0, returns an empty string. Returns NULL if str or count are NULL.

REVERSE(str)

INSERT(str,pos,len,newstr)
Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr.

ELT(N,str1,str2,str3,...)
Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD().

FIELD(str,str1,str2,str3,...)
Returns the index of str in the str1, str2, str3, ... list. Returns 0 if str is not found. FIELD() is the complement of ELT().

FIND_IN_SET(str,strlist)
Returns a value 1 to N if the string str is in the list strlist consisting of N substrings. A string list is a string composed of substrings separated by `,' characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic! Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a `,'.

MAKE_SET(bits,str1,str2,...)
Returns a set (a string containing substrings separated by `,' characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, etc. NULL strings in str1, str2, ... are not appended to the result.

EXPORT_SET(bits,on,off,[separator,[number_of_bits]])
Returns a string where for every bit set in 'bit', you get a 'on' string and for every reset bit you get an 'off' string. Each string is separated with 'separator' (default ',') and only 'number_of_bits' (default 64) of 'bits' is used.

LCASE(str)

LOWER(str)

UCASE(str)

UPPER(str)

LOAD_FILE(file_name)
Reads the file and returns the file contents as a string. The file must be on the server, you must specify the full pathname to the file, and you must have the file privilege. The file must be readable by all and be smaller than max_allowed_packet. If the file doesn't exist or can't be read due to one of the above reasons, the function returns NULL.

DAYOFWEEK(date)

Returns the weekday index for date (1 = Sunday, 2 = Monday, ... 7 = Saturday). These index values correspond to the ODBC standard.

WEEKDAY(date)

Returns the weekday index for date (0 = Monday, 1 = Tuesday, ... 6 = Sunday).

DAYOFMONTH(date)

Returns the day of the month for date, in the range 1 to 31.

DAYOFYEAR(date)

Returns the day of the year for date, in the range 1 to 366.

MONTH(date)

Returns the month for date, in the range 1 to 12.

DAYNAME(date)

Returns the name of the weekday for date.

MONTHNAME(date)

Returns the name of the month for date.

QUARTER(date)

Returns the quarter of the year for date, in the range 1 to 4.

WEEK(date)

WEEK(date,first)

With a single argument, returns the week for date, in the range 0 to 52, for locations where Sunday is the first day of the week. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday. The week starts on Sunday if the second argument is 0, on Monday if the second argument is 1.

YEAR(date)

Returns the year for date, in the range 1000 to 9999.

HOUR(time)

Returns the hour for time, in the range 0 to 23.

MINUTE(time)

Returns the minute for time, in the range 0 to 59.

SECOND(time)

Returns the second for time, in the range 0 to 59.

PERIOD_ADD(P,N)

Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value.

PERIOD_DIFF(P1,P2)

Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values.

DATE_ADD(date,INTERVAL expr type)

DATE_SUB(date,INTERVAL expr type)

ADDDATE(date,INTERVAL expr type)

SUBDATE(date,INTERVAL expr type)

These functions perform date arithmetic. They are new for MySQL 3.22. ADDDATE() and SUBDATE() are synonyms for DATE_ADD() and DATE_SUB(). In MySQL 3.23, you can use + and - instead of DATE_ADD() and DATE_SUB(). (See example) date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or substracted from the starting date. expr is a string; it may start with a `-' for negative intervals. type is a keyword indicating how the expression should be interpreted. The EXTRACT(type FROM date) function returns the 'type' interval from the date. The following table shows how the type and expr arguments are related:

type value

Meaning

Expected expr format

SECOND

Seconds

SECONDS

MINUTE

Minutes

MINUTES

HOUR

Hours

HOURS

DAY

Days

DAYS

MONTH

Months

MONTHS

YEAR

Years

YEARS

MINUTE_SECOND

Minutes and seconds

"MINUTES:SECONDS"

HOUR_MINUTE

Hours and minutes

"HOURS:MINUTES"

DAY_HOUR

Days and hours

"DAYS HOURS"

YEAR_MONTH

Years and months

"YEARS-MONTHS"

HOUR_SECOND

Hours, minutes,

"HOURS:MINUTES:SECONDS"

DAY_MINUTE

Days, hours, minutes

"DAYS HOURS:MINUTES"

DAY_SECOND

Days, hours, minutes, seconds

"DAYS HOURS:MINUTES:SECONDS"

MySQL allows any punctuation delimiter in the expr format. The ones shown in the table are the suggested delimiters. If the date argument is a DATE value and your calculations involve only YEAR, MONTH and DAY parts (that is, no time parts), the result is a DATE value. Otherwise the result is a DATETIME value.

If you specify an interval value that is too short (does not include all the interval parts that would be expected from the type keyword), MySQL assumes you have left out the leftmost parts of the interval value. For example, if you specify a type of DAY_SECOND, the value of expr is expected to have days, hours, minutes and seconds parts. If you specify a value like "1:10", MySQL assumes that the days and hours parts are missing and the value represents minutes and seconds. In other words, "1:10" DAY_SECOND is interpreted in such a way that it is equivalent to "1:10" MINUTE_SECOND. This is analogous to the way that MySQL interprets TIME values as representing elapsed time rather than as time of day. If you use really incorrect dates, the result is NULL. If you add MONTH, YEAR_MONTH or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month.

Note from the preceding example that the word INTERVAL and the type keyword are not case sensitive.

TO_DAYS(date)

Given a date date, returns a daynumber (the number of days since year 0).

TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582).

FROM_DAYS(N)

Given a daynumber N, returns a DATE value.

FROM_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582).

DATE_FORMAT(date,format)

Formats the date value according to the format string. The following specifiers may be used in the format string:

All other characters are just copied to the result without interpretation.

As of MySQL 3.23, the % is required before a format specifier characters. In earlier versions of MySQL, % was optional.

%M

Month name (January..December)

%W

Weekday name (Sunday..Saturday)

%D

Day of the month with english suffix (1st, 2nd, 3rd, etc.)

%Y

Year, numeric, 4 digits

%y

Year, numeric, 2 digits

%a

Abbreviated weekday name (Sun..Sat)

%d

Day of the month, numeric (00..31)

%e

Day of the month, numeric (0..31)

%m

Month, numeric (01..12)

%c

Month, numeric (1..12)

%b

Abbreviated month name (Jan..Dec)

%j

Day of year (001..366)

%H

Hour (00..23)

%k

Hour (0..23)

%h

Hour (01..12)

%I

Hour (01..12)

%l

Hour (1..12)

%i

Minutes, numeric (00..59)

%r

Time, 12-hour (hh:mm:ss [AP]M)

%T

Time, 24-hour (hh:mm:ss)

%S

Seconds (00..59)

%s

Seconds (00..59)

%p

AM or PM

%w

Day of the week (0=Sunday..6=Saturday)

%U

Week (0..52), where Sunday is the first day of the week

%u

Week (0..52), where Monday is the first day of the week

%%

A literal `%'.

TIME_FORMAT(time,format)

This is used like the DATE_FORMAT() function above, but the format string may contain only those format specifiers that handle hours, minutes and seconds. Other specifiers produce a NULL value or 0.

CURDATE()

CURRENT_DATE

Returns today's date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

CURTIME()

CURRENT_TIME

Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context.

NOW()

SYSDATE()

CURRENT_TIMESTAMP

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

UNIX_TIMESTAMP()

UNIX_TIMESTAMP(date)

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT). If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD in local time.

When UNIX_TIMESTAMP is used on a TIMESTAMP column, the function will receive the value directly, with no implicit ``string-to-unix-timestamp'' conversion.

FROM_UNIXTIME(unix_timestamp)

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

FROM_UNIXTIME(unix_timestamp,format)

Returns a string representation of the Unix timestamp, formatted according to the format string. format may contain the same specifiers as those listed in the entry for the DATE_FORMAT() function.

SEC_TO_TIME(seconds)

Returns the seconds argument, converted to hours, minutes and seconds, as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context.

TIME_TO_SEC(time)

Returns the time argument, converted to seconds.

DATABASE()

Returns the current database name.
If there is no current database, DATABASE() returns the empty string.

USER()

SYSTEM_USER()

SESSION_USER()

Returns the current MySQL user name.
In MySQL 3.22.11 or later, this includes the client hostname as well as the username. You can extract just the username part like this (which works whether or not the value includes a hostname part):

PASSWORD(str)

Calculates a password string from the plaintext password str. This is the function that is used for encrypting MySQL passwords for storage in the Password column of the user grant table.
PASSWORD() encryption is non-reversible. PASSWORD() does not perform password encryption in the same way that Unix passwords are encrypted. You should not assume that if your Unix password and your MySQL password are the same, PASSWORD() will result in the same encrypted value as is stored in the Unix password file. See ENCRYPT().

ENCRYPT(str[,salt])

Encrypt str using the Unix crypt() system call. The salt argument should be a string with two characters. (As of MySQL 3.22.16, salt may be longer than two characters.)
If crypt() is not available on your system, ENCRYPT() always returns NULL. ENCRYPT() ignores all but the first 8 characters of str, at least on some systems. This will be determined by the behavior of the underlying crypt() system call.

ENCODE(str,pass_str)

Encrypt str using pass_str as the password. To decrypt the result, use DECODE(). The results is a binary string. If you want to save it in a column, use a BLOB column type.

DECODE(crypt_str,pass_str)

Descrypts the encrypted string crypt_str using pass_str as the password. crypt_str should be a string returned from ENCODE().

MD5(string)

Calculates a MD5 checksum for the string. Value is returned as a 32 long hex number that may, for example, be used as a hash key.
This is a "RSA Data Security, Inc. MD5 Message-Digest Algorithm".

LAST_INSERT_ID([expr])

Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column.
The last ID that was generated is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). If expr is given as an argument to LAST_INSERT_ID() in an UPDATE clause, then the value of the argument is returned as a LAST_INSERT_ID() value. This can be used to simulate sequences: First create the table:

mysql> create table sequence (id int not null);
mysql> insert into sequence values (0);
                                        
                                        

					

mysql> update sequence set id=LAST_INSERT_ID(id+1);
                                        
                                        

					

FORMAT(X,D)

Formats the number X to a format like '#,###,###.##', rounded to D decimals. If D is 0, the result will have no decimal point or fractional part.

VERSION()

Returns a string indicating the MySQL server version.

GET_LOCK(str,timeout)

Tries to obtain a lock with a name given by the string str, with a timeout of timeout seconds. Returns 1 if the lock was obtained successfully, 0 if the attempt timed out, or NULL if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill). A lock is released when you execute RELEASE_LOCK(), execute a new GET_LOCK() or the thread terminates. This function can be used to implement application locks or to simulate record locks. It blocks requests by other clients for locks with the same name; clients that agree on a given lock string name can use the string to perform cooperative advisory locking.

Note that the second RELEASE_LOCK() call returns NULL because the lock "lock1" was automatically released by the second GET_LOCK() call.

RELEASE_LOCK(str)

Releases the lock named by the string str that was obtained with GET_LOCK(). Returns 1 if the lock was released, 0 if the lock wasn't locked by this thread (in which case the lock is not released) and NULL if the named lock didn't exist. The lock will not exist if it was never obtained by a call to GET_LOCK() or if it already has been released.

BENCHMARK(count,expr)

The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how fast MySQL processes the expression. The result value is always 0. The intended use is in the mysql client, which reports query execution times.
The time reported is elapsed time on the client end, not CPU time on the server end. It may be advisable to execute BENCHMARK() several times, and interpret the result with regard to how heavily loaded the server machine is.

COUNT(expr)

Returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement.
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values. COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved and there is no WHERE clause.

COUNT(DISTINCT expr,[expr...])

Returns a count of the number of different values.
In MySQL you can get the number of distinct expressions combinations by giving a list of expressions. In ANSI SQL you would have to do a concatenation of all expressions inside CODE(DISTINCT ..).

AVG(expr)

Returns the average value of expr.

MIN(expr)

MAX(expr)

Returns the minimum or maximum value of expr. MIN() and MAX() may take a string argument; in such cases they return the minimum or maximum string value.

SUM(expr)

Returns the sum of expr. Note that if the return set has no rows, it returns NULL!

STD(expr)

STDDEV(expr)

Returns the standard deviation of expr. This is an extension to ANSI SQL. The STDDEV() form of this function is provided for Oracle compatability.

BIT_OR(expr)

Returns the bitwise OR of all bits in expr. The calculation is performed with 64-bit (BIGINT precision.

BIT_AND(expr)

Returns the bitwise AND of all bits in expr. The calculation is performed with 64-bit (BIGINT precision.

HOSTS

Empties the host cache tables. You should flush the host tables if some of your hosts change IP number or if you get the error message Host ... is blocked. When more than max_connect_errors errors occur in a row for a given host while connection to the MySQL server, MySQL assumes something is wrong and blocks the host from further connection requests. Flushing the host tables allows the host to attempt to connect again. You can start mysqld with -O max_connection_errors=999999999 to avoid this error message.

LOGS

Closes and reopens the standard and update log files. If you have specified the update log file without an extension, the extension number of the new update log file will be incremented by one relative to the previous file.

PRIVILEGES

Reloads the privileges from the grant tables in the mysql database.

TABLES

Closes all open tables.

STATUS

Resets most status variables to zero.

action

add

aggregate

all

alter

after

and

as

asc

avg

avg_row_length

auto_increment

between

bigint

bit

binary

blob

bool

both

by

cascade

case

char

character

change

check

checksum

column

columns

comment

constraint

create

cross

current_date

current_time

current_timestamp

data

database

databases

date

datetime

day

day_hour

day_minute

day_second

dayofmonth

dayofweek

dayofyear

dec

decimal

default

delayed

delay_key_write

delete

desc

describe

distinct

distinctrow

double

drop

end

else

escape

escaped

enclosed

enum

explain

exists

fields

file

first

float

float4

float8

flush

foreign

from

for

full

function

global

grant

grants

group

having

heap

high_priority

hour

hour_minute

hour_second

hosts

identified

ignore

in

index

infile

inner

insert

insert_id

int

integer

interval

int1

int2

int3

int4

int8

into

if

is

isam

join

key

keys

kill

last_insert_id

leading

left

length

like

lines

limit

load

local

lock

logs

long

longblob

longtext

low_priority

max

max_rows

match

mediumblob

mediumtext

mediumint

middleint

min_rows

minute

minute_second

modify

month

monthname

myisam

natural

numeric

no

not

null

on

optimize

option

optionally

or

order

outer

outfile

pack_keys

partial

password

precision

primary

procedure

process

processlist

privileges

read

real

references

reload

regexp

rename

replace

restrict

returns

revoke

rlike

row

rows

second

select

set

show

shutdown

smallint

soname

sql_big_tables

sql_big_selects

sql_low_priority_updates

sql_log_off

sql_log_update

sql_select_limit

sql_small_result

sql_big_result

sql_warnings

straight_join

starting

status

string

table

tables

temporary

terminated

text

then

time

timestamp

tinyblob

tinytext

tinyint

trailing

to

type

use

using

unique

unlock

unsigned

update

usage

values

varchar

variables

varying

varbinary

with

write

when

where

year

year_month

zerofill