MySQL Function summary

MySQL function

The MySQL database provides a number of functions including:

mathematical functions; String functions; Date and time functions; conditional judgment functions; system information functions; cryptographic functions; formatting functions;

First, mathematical functions

Mathematical functions are mainly used for processing numbers, including integers, floating-point numbers, and so on.

function	function
ABS (x)	Returns the absolute value of X SELECT ABS (-1)--return 1
Ceil (x), CEILING (x)	Returns the smallest integer greater than or equal to X SELECT Ceil (1.5)--Return 2
Floor (x)	Returns the largest integer less than or equal to X SELECT Floor (1.5)--return 1
RAND ()	Returns the random number of 0->1 SELECT RAND ()--0.93099315644334
RAND (x)	Returns the random number of 0->1 with the same number of random numbers returned at the same X value SELECT RAND (2)--1.5865798029924
Sign (x)	Returns the x symbol, x is negative, 0, positive numbers are returned-1, 0, and 1, respectively. SELECT sign (-10)--(-1)
PI ()	return Pi (3.141593) SELECT PI ()--3.141593
TRUNCATE (x, y)	Returns the value of x reserved to the Y-bit after the decimal point (the biggest difference from round is that it will not be rounded) SELECT TRUNCATE (1.23456,3)--1.234
ROUND (x)	Returns the nearest integer from X SELECT ROUND (1.23456)--1
ROUND (x, y)	The value of the Y-bit after the decimal point is retained, but is rounded when truncated SELECT ROUND (1.23456,3)--1.235
POW (x, y), POWER (x, y)	Returns the Y-order of X SELECT POW (2,3)--8
SQRT (x)	Returns the square root of X SELECT SQRT (25)--5
EXP (x)	Returns the X-square of E SELECT EXP (3)--20.085536923188
MOD (x, y)	Returns the remainder after x divided by y SELECT MOD (5,2)--1
LOG (x)	Returns the natural logarithm (base e logarithm) SELECT LOG (20.085536923188)--3
LOG10 (x)	Returns the base 10 logarithm SELECT LOG10 (100)--2
RADIANS (x)	Convert an angle to radians --3.1415926535898
DEGREES (x)	Convert radians to angles SELECT DEGREES (3.1415926535898)--180
SIN (x)	To find the sine value (the parameter is radians) SELECT SIN (RADIANS (30))--0.5
ASIN (x)	To find the sine value (parameter is radians)
COS (x)	Calculate cosine (parameter is radians)
ACOS (x)	Inverse cosine value (parameter is radians)
TAN (x)	To find the tangent value (the parameter is radians)
ATAN (x) ATAN2 (x)	Calculate the inverse tangent value (parameter is radians)
COT (x)	Cotangent value (parameter is radians)

Second, String function

String functions are the most common type of function in MySQL, and string functions are primarily used to manipulate strings in a table.

function	Description
Char_length (s)	Returns the number of characters in the string s SELECT char_length (' Hello 123 ')--5
LENGTH (s)	Returns the length of the string s SELECT LENGTH (' Hello 123 ')--9
CONCAT (S1,s2,...)	Combine strings s1,s2 and more into a single string SELECT CONCAT (' 12 ', ' 34 ')--1234
Concat_ws (X,s1,s2,...)	Tong Concat (S1,s2,...) function, but each string is added directly to the X SELECT concat_ws (' @ ', ' a ', ' "') '-- [email protected]
INSERT (S1,X,LEN,S2)	Replace string S2 with S1 x position starting at Len length SELECT INSERT (' 12345 ', 1,3, ' abc ')--abc45
UPPER (s), ucaase (s)	Converts all letters of the string s to uppercase SELECT UPPER (' abc ')--ABC
LOWER (s), LCASE (s)	Turn all the letters of the string s into lowercase letters SELECT LOWER (' abc ')--ABC
Left (S,n)	Returns the first n characters of a string s SELECT left (' ABCDE ', 2)--AB
Right (S,n)	Returns the second n characters of the string s SELECT right (' ABCDE ', 2)--DE
Lpad (S1,LEN,S2)	String S2 to fill the beginning of the S1, so that the string length reaches Len SELECT lpad (' abc ', 5, ' xx ')--XXABC
Rpad (S1,LEN,S2)	String S2 to fill the end of the S1, so that the length of the string reaches Len SELECT rpad (' abc ', 5, ' xx ')--abcxx
LTRIM (s)	Remove the space at the beginning of the string s
RTRIM (s)	Remove the space at the end of the string s
TRIM (s)	Remove the space at the beginning and end of the string s
TRIM (S1 from S)	Removes the string at the beginning and end of the string s S1 SELECT TRIM (' @ ' from ' @@ [email protected]@ ')--ABC
REPEAT (S,n)	Repeats the string s n times SELECT REPEAT (' AB ', 3)--Ababab
SPACE (N)	Returns n Spaces
REPLACE (S,S1,S2)	S2 the string to a string in an alternate string s S1 SELECT REPLACE (' abc ', ' A ', ' X ')--XBC
STRCMP (S1,S2)	Compare strings S1 and S2
SUBSTRING (S,n,len)	Gets a string starting from the nth position in the string s with the length Len
MID (S,n,len)	With substring (s,n,len)
LOCATE (S1,s), POSITION (S1 in s)	Gets the starting position of the S1 from the string s SELECT LOCATE (' B ', ' abc ')--2
INSTR (S,S1)	Gets the starting position of the S1 from the string s SELECT INSTR (' abc ', ' B ')--2
REVERSE (s)	Reverse the order of the string s SELECT REVERSE (' abc ')--CBA
ELT (N,s1,s2,...)	Returns the nth string SELECT ELT (2, ' A ', ' B ', ' C ')--B
Export_set (X,S1,S2)	Returns a string where you get an "on" string for each bit in "bits", and for each reset (reset) bit, you get a "off" string. Each string is delimited with "separator" (Default ","), and only "bits" of "number_of_bits" (default 64) bits are used. SELECT Export_set (5, ' Y ', ' N ', ', ', 4)--Y,n,y,n
FIELD (S,s1,s2 ...)	Returns the first string position that matches the string s SELECT FIELD (' C ', ' A ', ' B ', ' C ')--3
Find_in_set (S1,S2)	Returns the position of a string that matches S1 in the string s2
Make_set (X,S1,S2)	Returns a collection (a string consisting of substrings separated by "," characters), consisting of a string of corresponding bits in the bits collection. The str1 corresponds to bit 0,str2 corresponding to bit 1, and so on. SELECT Make_set (1|4, ' A ', ' B ', ' C ')--a,c
Substring_index	Returns the substring that appears after the delimiter Delim from the count of the string str. If Count is a positive number, returns the string to the left of the count of characters. If count is a negative number, returns the string (the absolute value of count (from the right)) to the right of the character. SELECT Substring_index (' ab ', ', 1)--A SELECT Substring_index (' ab ', ', -1)--B SELECT Substring_index (Substring_index (' abcde ', ' ', 3), '', -1)--C
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 path name to the file, and you must have file permissions. The file must have all the content readable and less than max_allowed_packet. If the file does not exist or because one of the above reasons cannot be read, the function returns NULL.

Three, date Time function

MySQL's date and time functions are primarily used to process DateTime.

function	Description
Curdate (), Current_date ()	Returns the current date SELECT curdate ()->2014-12-17
Curtime (), Current_time	Return Current time SELECT curtime ()->15:59:02
Now (), Current_timestamp (), localtime (), Sysdate (), Localtimestamp ()	Returns the current date and time SELECT now ()->2014-12-17 15:59:02
Unix_timestamp ()	Returns the current time as a Unix timestamp SELECT Unix_timestamp ()->1418803177
Unix_timestamp (d)	Returns time d as a Unix timestamp SELECT unix_timestamp (' 2011-11-11 11:11:11 ')->1320981071
From_unixtime (d)	Time to convert the time of the Unix timestamp to the normal format SELECT from_unixtime (1320981071)->2011-11-11 11:11:11
Utc_date ()	Returns the UTC date SELECT utc_date ()->2014-12-17
Utc_time ()	return UTC time SELECT utc_time ()->08:01:45 (8 hours slow)
MONTH (d)	Returns the month value in Date D, 1->12 SELECT MONTH (' 2011-11-11 11:11:11 ')->11
MONTHNAME (d)	Returns the month name in the date, such as Janyary SELECT MONTHNAME (' 2011-11-11 11:11:11 ')->november
Dayname (d)	Return Date D is the day of the week, such as Monday,tuesday SELECT dayname (' 2011-11-11 11:11:11 ')->friday
DAYOFWEEK (d)	Date d today is the day of the week, 1 weeks, 2 weeks a SELECT DAYOFWEEK (' 2011-11-11 11:11:11 ')->6
WEEKDAY (d)	Date d is today the day of the week, 0 means Monday, 1 means Tuesday
WEEK (d), WeekOfYear (d)	Calculated Date d is the week ordinal of the year, the range is 0->53 SELECT WEEK (' 2011-11-11 11:11:11 ')->45
DayOfYear (d)	Calculated Date D is the day ordinal of this year SELECT dayofyear (' 2011-11-11 11:11:11 ')->315
DayOfMonth (d)	Calculated Date D is the day of the month SELECT dayofmonth (' 2011-11-11 11:11:11 ')->11
QUARTER (d)	Return Date D is the first season, return 1->4 SELECT QUARTER (' 2011-11-11 11:11:11 ')->4
HOUR (t)	Returns the hour value in T SELECT HOUR (' 1:2:3 ')->1
MINUTE (t)	Returns the minute value in T SELECT MINUTE (' 1:2:3 ')->2
SECOND (t)	Returns the seconds value in T SELECT SECOND (' 1:2:3 ')->3
EXTRACT (type from D)	Gets the specified value from Date D, type specifies the value returned SELECT EXTRACT (MINUTE from ' 2011-11-11 11:11:11 ')->11 The value of type is: Microsecond SECOND MINUTE HOUR Day WEEK MONTH QUARTER Year Second_microsecond Minute_microsecond Minute_second Hour_microsecond Hour_second Hour_minute Day_microsecond Day_second Day_minute Day_hour Year_month
Time_to_sec (t)	Convert time t to seconds SELECT time_to_sec (' 1:12:00 ')->4320
Sec_to_time (s)	Converts the time in seconds to the format of the last seconds SELECT sec_to_time (4320)->01:12:00
To_days (d)	Calculate Date D for days from January 1, 00 SELECT to_days (' 0001-01-01 01:01:01 ')->366
From_days (N)	Calculates the date of n days from January 1, 00 SELECT from_days (1111)->0003-01-16
DATEDIFF (D1,D2)	Calculate the number of days separated by date d1->d2 SELECT DATEDIFF (' 2001-01-01 ', ' 2001-02-02 ')->-32
Adddate (D,n)	Calculate Date d plus n days of date
Adddate (D,interval expr type)	Calculate the start Date D plus the date after a time period SELECT adddate (' 2011-11-11 11:11:11 ', 1)->2011-11-12 11:11:11 (default is days) SELECT adddate (' 2011-11-11 11:11:11 ', INTERVAL 5 MINUTE)->2011-11-11 11:16:11 (the value of type is similar to the one listed above)
Date_add (D,interval expr type)	Ditto
Subdate (D,n)	Date d minus n days after day SELECT subdate (' 2011-11-11 11:11:11 ', 1)->2011-11-10 11:11:11 (default is days)
Subdate (D,interval expr type)	Date d minus the date after one time period SELECT subdate (' 2011-11-11 11:11:11 ', INTERVAL 5 MINUTE)->2011-11-11 11:06:11 (the value of type is similar to the one listed above)
Addtime (T,n)	Time t plus n seconds SELECT addtime (' 2011-11-11 11:11:11 ', 5)->2011-11-11 11:11:16 (sec)
Subtime (T,n)	Time t minus n seconds SELECT subtime (' 2011-11-11 11:11:11 ', 5)->2011-11-11 11:11:06 (sec)
Date_format (D,F)	Display Date d as required by expression F SELECT date_format (' 2011-11-11 11:11:11 ', '%y-%m-%d%r ')->2011-11-11 11:11:11 AM
Time_format (T,F)	Show time t as required by expression F SELECT Time_format (' 11:11:11 ', '%r ') 11:11:11 AM
Get_format (Type,s)	Get time Format function for national area Select Get_format (date, ' USA ')->%m.%d.%y (note that this strange string is returned (format string))

Four, conditional judgment function

1. IF (EXPR,V1,V2) function

If the expression expr is true, returns the result V1; otherwise, the result v2.

SELECT IF(1 > 0,‘正确‘,‘错误‘)    ->正确

1. Fnull (V1,V2) function

If the value of V1 is not NULL, V1 is returned, otherwise v2 is returned.

SELECT IFNULL(null,‘Hello Word‘) ->Hello Word

1. Case

• Syntax 1:

  CASE    WHEN e1    THEN v1    WHEN e2    THEN e2    ...    ELSE vnEND

The case represents the start of the function and end indicates the end of the function.

If the E1 is established, then return to V1, if E2 is established, then return to V2, when all is not established return to VN, and when there is a set up, the latter will not be executed.

Cases:

SELECT CASE    　　WHEN 1 > 0    　　THEN ‘1 > 0‘    　　WHEN 2 > 0    　　THEN ‘2 > 0‘    　　ELSE ‘3 > 0‘    　　END  1 > 0   

• Syntax 2:

  CASE expr　　WHEN e1 THEN v1　　WHEN e1 THEN v1　　...　　ELSE vnEND

Returns v1 if the value of the expression expr equals e1, or E2 if it equals E2. Otherwise, the VN is returned.

Cases:

SELECT CASE 1　　WHEN 1 THEN ‘我是1‘　　WHEN 2 THEN ‘我是2‘ELSE ‘你是谁‘

Five, System Information function

System information functions are used to query the MySQL database for system information.

function	function
VERSION ()	Returns the version number of the database SELECT VERSION ()->5.0.67-community-nt
CONNECTION_ID ()	Returns the number of connections to the server
DATABASE (), SCHEMA	Returns the current database name
USER (), System_user (), Session_user (), Current_User (), Current_User	Returns the current user
CHARSET (str)	Returns the character set of the string str
COLLATION (str)	Returns the character arrangement of the string str
LAST_INSERT_ID ()	Returns the most recently generated auto_increment value

Six, encryption function

Cryptographic functions are functions that MySQL uses to encrypt data.

1. PASSWORD (str)

The function can encrypt the string str, in general, PASSWORD (str) is used to encrypt the user's password.

 SELECT PASSWORD(‘123‘)     ->*23AE809DDACAF96AF0FD78ED04B6A265E05AA257

1. MD5

The MD5 (str) function hashes the string str and can be used for some common data encryption that does not require decryption.

    SELECT md5(‘123‘)    ->202cb962ac59075b964b07152d234b70

1. ENCODE (STR,PSWD_STR) and decode (CRYPT_STR,PSWD_STR)

The Encode function can use the encrypted password Pswd_str to encrypt the string str, and the result of the encryption is a binary number that needs to be saved with a BLOB type of field. This function is a pair with decode and requires the same password to decrypt it.

SELECT ENCODE(‘123‘,‘xxoo‘)    ->;vxSELECT DECODE(‘;vx‘,‘xxoo‘)    ->123

Vii. other functions

1. Formatted function format (x,n)

The format (x,n) function formats the number x and retains the x to the N-bit after the decimal point.

SELECT FORMAT(3.1415926,3)    ->3.142

1. Conversion of different numbers to the binary

   • ASCII (s) returns the ASCII code of the first character of the string s;

   • BIN (x) returns the binary encoding of x;

   • Hex (x) returns the hexadecimal encoding of x;

   • The OCT (x) returns the octal encoding of X;

   • CONV (X,F1,F2) returns the F1 binary number into the F2 number of binary;

2. Functions for converting IP addresses to numbers

   • The Inet_aton (IP) function can convert an IP address to a digital representation, and the IP value needs to be quoted;

   • The Inet_ntoa (n) function converts the number n to the IP form.

SELECT INET_ATON(‘192.168.0.1‘) ->3232235521 SELECT INET_NTOA(3232235521) ->192.168.0.1

1. lock function and unlock function

   • The Get_lock (name,time) function defines a lock named Nam with a duration of time seconds. Returns 1 if the lock succeeds, 0 if an attempt times out, or null if an error is encountered.

   • The Release_lock (name) function unlocks a lock named name. Returns 1 if the unlock succeeds, or 0 if the attempt times out, and returns null if the unlock fails;

   • The Is_free_lock (name) function determines whether a lock named name has been used. If used, returns 0, otherwise, returns 1;

SELECT GET_LOCK(‘MySQL‘,10) ->1 (持续10秒) SELECT IS_FREE_LOCK(‘MySQL‘) ->1 SELECT RELEASE_LOCK(‘MySQL‘) ->1

1. function that repeats the specified action

   • The BENCHMARK (count.expr) function repeats the expression expr to count this, and then returns the execution time. This function can be used to determine the speed of the MySQL processing expression.

SELECT BENCHMARK(10000,NOW()) ->0 返回系统时间1万

1. Functions that change the character set

   • The CONVERT (S USING CS) function changes the character set of the string S to CS.
     SELECT CHARSET(‘ABC‘) ->utf-8 SELECT CHARSET(CONVERT(‘ABC‘ USING gbk)) ->gbk

2. converting data types

   • CAST (x as type)

   • VERT (X,type)

These two functions are only for binary, CHAR, DATE, DATETIME, Time, signed integer, UNSIGNED Integer.
SELECT CAST(‘123‘ AS UNSIGNED INTEGER) + 1 ->124 SELECT ‘123‘ + 1 ->124 其实MySQL能默认转换 SELECT CAST(NOW() AS DATE) 　　->2014-12-18

MySQL Function summary