Article Directory
Preface
Functions in MySQL include mathematical functions, string functions, date and time functions, conditional judgement functions, system information functions, and so on. The functions and usages of these functions in MySQL are described below.
1. Mathematical Functions
MySQL supports a variety of mathematical types of functions, including absolute value functions, random functions, and so on.The following table lists the array functions commonly used in MySQL.
Function Name interpretative statement ABS (num) Returns the absolute value of num CEIL(num) Returns the smallest integer value greater than num FLOOR(num) Returns the largest positive integer less than num MOD(num1,num2) Returns the value of num1% num2 RAND() Returns a random number of 0-1 TRUNCATE(num1,num2) Returns num1 truncated num2 decimal places ROUND(num1,num2) Returns the value of the parameter num1 rounded to a num2 decimal POW(x,y) Returns the y-th power of x1.1 ABS
The following shows the results of using the absolute value function for positive and negative numbers.
mysql> mysql> select abs(-1.2),abs(1.2); +-----------+----------+ | abs(-1.2) | abs(1.2) | +-----------+----------+ | 1.2 | 1.2 | +-----------+----------+ 1 row in set (0.00 sec)
1.2 CEIL
CEIL(X) returns a smallest integer greater than x
mysql> select ceil(1.6),ceil(1.2),ceil(-1.2); +-----------+-----------+------------+ | ceil(1.6) | ceil(1.2) | ceil(-1.2) | +-----------+-----------+------------+ | 2 | 2 | -1 | +-----------+-----------+------------+ 1 row in set (0.00 sec)
1.3 FLOOR
FLOOR(x) returns a maximum integer smaller than x
mysql> select FLOOR(1.8),FLOOR(-1.8); +------------+-------------+ | FLOOR(1.8) | FLOOR(-1.8) | +------------+-------------+ | 1 | -2 | +------------+-------------+ 1 row in set (0.00 sec)
1.4 MOD
MOD(x,y) returns the module of x%y
mysql> select mod(10,3); +-----------+ | mod(10,3) | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)
1.5 RAND
RAND() returns a random number of 0-1
mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.8903573117129107 | +--------------------+ 1 row in set (0.00 sec)
1.6 ROUND
round(x,y) returns the rounded y-bit tail of parameter x
mysql> select round(1.23,2),round(1.67,2),round(1.44,2); +---------------+---------------+---------------+ | round(1.23,2) | round(1.67,2) | round(1.44,2) | +---------------+---------------+---------------+ | 1.23 | 1.67 | 1.44 | +---------------+---------------+---------------+ 1 row in set (0.00 sec)
1.7 TRUNCATE
TRUNCATE(x,y) returns the result where x is truncated to a y-bit decimal
mysql> select round(1.23,2),round(1.67,2),round(1.44,2),truncate(1.23,2),truncate(1.67,2),truncate(1.44,2); +---------------+---------------+---------------+------------------+------------------+------------------+ | round(1.23,2) | round(1.67,2) | round(1.44,2) | truncate(1.23,2) | truncate(1.67,2) | truncate(1.44,2) | +---------------+---------------+---------------+------------------+------------------+------------------+ | 1.23 | 1.67 | 1.44 | 1.23 | 1.67 | 1.44 | +---------------+---------------+---------------+------------------+------------------+------------------+ 1 row in set (0.00 sec)
1.8 POW
POW(x,y) returns the y-th power of X
mysql> select pow(2,3); +----------+ | pow(2,3) | +----------+ | 8 | +----------+ 1 row in set (0.00 sec)
MySQL also supports trigonometric (sine, cosine, tangent, cotangent) and logarithmic functions. Normally, there are few scenarios where mathematical functions are used in daily development. Usually, we store them in MySQL after processing the relevant mathematical calculations in Java programs.
2. String Functions
String functions are the most common functions in MySQL. The following list of commonly used string functions:
2.1 CHAR_LENGTH and LENGTH
mysql> select char_length('abc'); +--------------------+ | char_length('abc') | +--------------------+ | 3 | +--------------------+ 1 row in set (0.00 sec) mysql> select length('abc'); +---------------+ | length('abc') | +---------------+ | 3 | +---------------+ 1 row in set (0.00 sec)
LENGTH(str) returns string byte length
mysql> select length('I'); +---------------+ | length('I') | +---------------+ | 3 | +---------------+ 1 row in set (0.00 sec) mysql> select char_length('I'); +--------------------+ | char_length('I') | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec)
2.2 CONCAT
CONCAT(s1,s2...) Splice the incoming string, note that any string spliced with NULL returns NULL
mysql> select concat(1,'a'),concat(null,'a'); +---------------+------------------+ | concat(1,'a') | concat(null,'a') | +---------------+------------------+ | 1a | NULL | +---------------+------------------+ 1 row in set (0.00 sec)
2.3 INSERT
INSERT(str,x,y,str1) inserts STR1 at the x position of the STR and ends at the Y position
mysql> select insert('abc',1,2,'haha123'); +-----------------------------+ | insert('abc',1,2,'haha123') | +-----------------------------+ | haha123c | +-----------------------------+ 1 row in set (0.00 sec)
2.4 LOWER and UPPER
LOWER(str), UPPER(str) functions: Convert a string to lowercase or uppercase:
mysql> select UPPER('abc'),LOWER('ABC'); +--------------+--------------+ | UPPER('abc') | LOWER('ABC') | +--------------+--------------+ | ABC | abc | +--------------+--------------+ 1 row in set (0.00 sec)
2.5 LEFT and RIGHT
The LEFT(str,x) and RIGHT(str,x) functions return the leftmost or rightmost x characters of a string, respectively
mysql> select LEFT('i love china,because i am a chinese',12),RIGHT('i love china,because i am a chinese',15); +------------------------------------------------+-------------------------------------------------+ | LEFT('i love china,because i am a chinese',12) | RIGHT('i love china,because i am a chinese',15) | +------------------------------------------------+-------------------------------------------------+ | i love china | i am a chinese | +------------------------------------------------+-------------------------------------------------+ 1 row in set (0.00 sec)
2.6 REPLACE
REPLACE(str,s1,s2) replaces the character sequence containing S1 in str with S2
mysql> select replace('abc','ab','bb'); +--------------------------+ | replace('abc','ab','bb') | +--------------------------+ | bbc | +--------------------------+ 1 row in set (0.01 sec)
2.7 TRIM
TRIM(str) removes spaces at the beginning and end of a string
mysql> select trim(' a bc '); +----------------+ | trim(' a bc ') | +----------------+ | a bc | +----------------+ 1 row in set (0.00 sec)
2.8 SUBSTRING
SUBSTRING(str,x,y) intercept string STR starts at x and ends at Y
mysql> select substring('abcd',1,1); +-----------------------+ | substring('abcd',1,1) | +-----------------------+ | a | +-----------------------+ 1 row in set (0.00 sec)
2.9 LOCATE,POSITION,INSTR
LOCATE(s,str), POSITION(s in str), INSTR(str,s) function the same, that is, return the string s at the beginning of str, we need to pay attention to the order of entry among the three functions
mysql> select locate('a','abc'),position('a' in 'abc'),instr('abc','a'); +-------------------+------------------------+------------------+ | locate('a','abc') | position('a' in 'abc') | instr('abc','a') | +-------------------+------------------------+------------------+ | 1 | 1 | 1 | +-------------------+------------------------+------------------+ 1 row in set (0.00 sec)
2.10 REVERSE
REVERSE(str) inverts the string sequence
mysql> select REVERSE('abc'); +----------------+ | REVERSE('abc') | +----------------+ | cba | +----------------+ 1 row in set (0.00 sec)
2.11 FIELD
FIELD(s,s1,s2...) Returns the string s in s1, s2...If not, return 0.
mysql> select FIELD('a','ab','a'); +---------------------+ | FIELD('a','ab','a') | +---------------------+ | 2 | +---------------------+ 1 row in set (0.00 sec)
2.12 FIND_IN_SET
FIND_IN_SET(s1,s2) returns the position of string S1 at string s2, similar to FIELD, but note the parameters
mysql> select FIND_IN_SET('a','caba,aa,a'); +------------------------------+ | FIND_IN_SET('a','caba,aa,a') | +------------------------------+ | 3 | +------------------------------+ 1 row in set (0.00 sec)
Next we'll end MySQL's time function.