MySQL entry (12) MySQL internal function _ MySQL

A variety of functions can simplify user operations and make operations more flexible. In addition, because the function execution speed is very fast, it can also improve the processing speed of MySQL. The Select statement and its conditional expressions described earlier in this section allow the Insert, Update, and Delete statements and their conditional expressions to provide functions that simplify your operations and make the operations more flexible, because the function execution speed is very fast, MySQL processing speed can also be improved.
The Select statements and their conditional expressions described earlier can be used in Insert, Update, Delete statements and their conditional expressions.
MySQL functions include mathematical functions, string functions, date and time functions, conditional judgment functions, Xu Tong information functions, encryption functions, and formatting functions. The following describes how to use these functions.

1. mathematical functions

Mathematical functions are mainly used to process numbers, including integer and floating point types.

2. string functions

String functions are mainly used to process strings in a table.

(1) assume that the string '+-' is filled at the beginning of 'jiangjing' by using the LPAD ('jiangjing ', 15,' +-') function, so that the string length reaches 15, the result is '+-+ Jiangjing '.
(2) assume that you use the TRIM ('AB' from 'ababdddabdddab') function to remove 'ababdddabddb' from the beginning and end of 'ababdddabb' and get 'dddababddd '.
(3) assume that the Make_set (11, 'A', 'B', 'C', 'D') function is used to select a string. first, 11 is converted into a decimal number of 1011, because the order for reading binary numbers is from right to left, it indicates that the 1st, 2nd, and 4th bits from right to left are 1, so select 'A ', 'B' and 'D '. Pay attention to the sequence.

3. date and time functions

The date interval type of MySQL is as follows:

The date and time formats in MySQL are as follows:

4. conditional judgment functions

The condition judgment function is used to determine conditions in SQL statements.

4.1 IF (expr, v1, v2) function

If the expression expr is true, return result v1; otherwise, return result v2.

4.2 IFNULL (v1, v2) function

If v1 is not empty, the v1 value is displayed; otherwise, the v2 value is displayed.

4.3 CASE function

(1) case when exp1 Then v1 [WHEN expr2 THEN v2 ·] [ELSE vn] END
Similar to the If else statement
(2) CASE expr WHEN e1 Then v1 [WHEN e2 THEN v2 ·] [ELSE vn] END
Similar to Case statements

5. system information functions

The system information function is used to query the system information of the MySQL database.

6. encryption functions

MySQL is used to encrypt data when the encryption function is used.

6.1 encryption function PASSWORD (str)

It is usually used to encrypt the user's password.
Note that the encryption of the password () function is irreversible.

6.2 encryption function MD5 (str)

It is mainly used to encrypt common data.

6.3 encryption function ENCODE (str, pswd_str)

You can use the string pswd_str to encrypt the string 'str'. the encrypted result is a binary number and must be saved using BLOB fields.

6.4 decryption function DECODE (crypt_str, pswd_str)

You can use the string pswd_str to decrypt crypt_str. the crypt_str is encrypted using the 6.3 function. Note that the string pswd_str should be the same as the encrypted string pswd_str.

7. other functions: Format (x, n)

You can format the number x and keep it to n digits after the decimal point. this process will be rounded down.
Similar to the ROUND (x, y) function.

7.2 functions for conversion of numbers in different Hexadecimal formats

(1) ASCII (s) returns the ASCII code of the first character of string s.
(2) BIN (x) returns the binary code of x.
(3) HEX (x) returns the hexadecimal code of x.
(4) OCT (x) returns the octal code of x.
(5) CONV (x, f1, f2) converts x from the f1 hexadecimal number to the f2 hexadecimal number.

7.3 functions for converting IP addresses and numbers

(1) INET_ATON (IP) can convert an IP address to a digital representation.
(2) INET_NTOA (n) can convert the number n into an IP address.
Note that the IP value in the INET_ATON (IP) function must be enclosed by quotation marks.

7.4 locking and unlocking functions

(1) GET_LOCT (name, time) defines a lock with the name and continuous length of the sword as time seconds. if the lock is successful, 1 is returned. if the attempt times out, 0 is returned, if an error occurs, NULL is returned.
(2) RELEASE_LOCT (name) removes the lock named name. if the unlock succeeds, 1 is returned. if the attempt times out, 0 is returned. If an error occurs, NULL is returned.
(3) IS_FREE_LOCK (name) checks whether a lock named name is used. if so, 0 is returned; otherwise, 1 is returned.

7.5 The function that repeats the specified operation

BENCHMARK (count, expr) repeats the expression expr for count times, returns the execution time after meals, and can be used to judge the speed of MySQL processing expression.

7.6 changing character set functions

CONVERT (s USING cs) converts the character set of string s to cs

7.7 function for changing the field data type

CAST (x AS type) and CONVERT (x, type)