MySQL function

Source: Internet
Author: User

Tags: time function alt Common Insert replace MMU span data encryption random number

The MySQL database provides a number of functions including:

    • Mathematical functions;
    • String functions;
    • Date and time functions;
    • conditional judgment function;
    • System Information function;
    • cryptographic functions;
    • Format function;
First, mathematical functions

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

Function Role
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


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


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


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 ( 2 )


Convert an angle to radians

SELECT RADIANS (180)--3.1415926535898


Convert radians to angles

SELECT DEGREES (3.1415926535898) --

SIN (x)

To find the sine value (the parameter is radians)


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


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 (' A ', ')- -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]


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


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


Repeats the string s n times

SELECT REPEAT (' AB ', 3) --Ababab

SPACE (N) Returns n Spaces

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


Gets the starting position of the S1 from the string s

SELECT INSTR (' abc ', ' B ')--2


Reverse the order of the string s


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 containing the“,”

A string of characters separated by substrings, consisting of a string of corresponding bits in the bits collection. str1corresponds to bit 0, str2 corresponds to bit 1, and so on.

SELECT Make_set (1|4, ' A ', ' B ', ' C '); --A,c


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 (' a*b ', ' * ', 1)--A
SELECT substring_index (' a*b ', ' * ', -1)--B
SELECT Substring_index (Substring_index (' a*b*c*d*e ', ' * ', 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 ()

Curtime (), Current_time

Return Current time

SELECT Curtime ()

Now (), Current_timestamp (), localtime (),

Sysdate (), Localtimestamp ()

Returns the current date and time

->2014-12-17 15:59:02

Unix_timestamp ()

Returns the current time as a Unix timestamp

SELECT Unix_timestamp ()

Unix_timestamp (d)

Returns time d as a Unix timestamp

SELECT unix_timestamp (' 2011-11-11 11:11:11 ')

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 ()

Utc_time ()

return UTC time

SELECT Utc_time ()
->08:01:45 (8 hours slow)


Returns the month value in Date D, 1->12

SELECT MONTH (' 2011-11-11 11:11:11 ')


Returns the month name in the date, such as Janyary

SELECT MONTHNAME (' 2011-11-11 11:11:11 ')

Dayname (d)

Return Date D is the day of the week, such as Monday,tuesday

SELECT dayname (' 2011-11-11 11:11:11 ')


Date d today is the day of the week, 1 weeks, 2 weeks a

SELECT DAYOFWEEK (' 2011-11-11 11:11:11 ')


Date d today is 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 ')

DayOfYear (d)

Calculated Date D is the day ordinal of this year

SELECT dayofyear (' 2011-11-11 11:11:11 ')

DayOfMonth (d)

Calculated Date D is the day of the month

SELECT dayofmonth (' 2011-11-11 11:11:11 ')


Return Date D is the first season, return 1->4

SELECT QUARTER (' 2011-11-11 11:11:11 ')

HOUR (t)

Returns the hour value in T

SELECT HOUR (' 1:2:3 ')


Returns the minute value in T

SELECT MINUTE (' 1:2:3 ')


Returns the seconds value in T

SELECT SECOND (' 1:2: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 ')

The value of type is:


Time_to_sec (t)

Convert time t to seconds

SELECT time_to_sec (' 1:12:00 ')

Sec_to_time (s)

Converts the time in seconds to the format of the last seconds

SELECT Sec_to_time (4320)

To_days (d)

Calculate Date D for days from January 1, 00

SELECT to_days (' 0001-01-01 01:01:01 ')

From_days (N)

Calculates the date of n days from January 1, 00

SELECT from_days (1111)


Calculate the number of days separated by date d1->d2

SELECT DATEDIFF (' 2001-01-01 ', ' 2001-02-02 ')

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 Day)

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 Day)

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, ' correct ', ' Error '),    correct

  2, ifnull (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

  3. Case

  Syntax 1:

When the case is E1 then v1 the 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.

SELECT case to 1 > 0 Then ' 1 > 0 ' when 2 > 0 Then ' 2 > 0 ' ELSE ' 3 > 0 ' end->1 > 0

  Syntax 2:

When the case expr is E1 then v1 the 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.

SELECT Case 1 If 1 then ' I am 1 ' when 2 Then ' I am 2 ' else ' who are you '

Five, System Information function

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

Function Role

Returns the version number of the database


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

  2, 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


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

  2, different binary numbers to convert

    • 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;

  3, the function of the IP address and the number mutual conversion

    • 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 (' ')    ->3232235521select inet_ntoa (3232235521)    ->

  4. 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 ', ten)    ->1    (lasts 10 seconds) Select Is_free_lock (' mysql ')    ->1    Select Release_ LOCK (' MySQL ')    ->1

  5. Functions that perform the specified operation repeatedly

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    return to system time 10,000

  6, changing the character set function

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

  7. Convert data type

    • CAST (x as type)
    • CONVERT (X,type)

These two functions are only for binary, CHAR, DATE, DATETIME, Time, signed integer, UNSIGNED Integer.

Select CAST (' 123 ' as UNSIGNED INTEGER) + 1    ->124select ' 123 ' + 1    ->124 In fact MySQL can default to convert select CAST (now () as DA TE)

MySQL function

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: