Shulou(Shulou.com)05/31 Report--

This article will give you a detailed introduction to other functions in MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Other functions

(1) formatting function format (xrecom n)

(2) A function for converting numbers with different binary values

(3) the function of translation between IP address and number

(4) locking function and unlocking function

(5) function that repeats the specified operation

(6) the function of changing the character set

(7) functions that change data types

(1), formatting function format (xPowern)

Format (xQuery n) formats the number x and rounds it to n digits after the decimal point, returning the result as a string. If n is 0, the result function does not contain a decimal part.

Mysql > select format (12332.123456), format (12332.1), format (12332.2) +-- + | format (12332.123456) | format (12332.1) | format (12332.2) | + -+ | 12332.1235 | 12332.1000 | 12332 | +-+ 1 Row in set (0.00 sec) (2) A function that converts numbers in different bases.

The conv function converts between different binary numbers. If one parameter is null, the return value is null.

[example] use the conv function to convert between different binary values. The SQL statement is as follows:

Mysql > select conv.-> conv. +-+ | conv ('a') 16Power2) | conv (15recover10reagio 2) | conv (15Magazine 10Magin8) | conv (15Power10page16) | +-+ | 1010 | 1111 | 17 | F | +- -+ 1 row in set (0. 01 sec) indicates that the binary number is represented by the numbers 0 and 1. With 2 as the base, eight digits from 0 to 7 are used for every binary octal and one for every octal. The decimal system starts with 0 and is represented by ten digits in total. Every decimal 16 system is composed of 0-9 meme AMF. The corresponding relationship with the decimal system is: 0-9 corresponds to 0-9 Magi F corresponds to 10-15, the hexadecimal number begins with 0x (3), and the IP address and number convert each other.

1.inet_aton ()

Inet_aton (expr) gives a point address representation of the network address as a string and returns an integer representing the value of the address, which can be a 4bit or 8bit address.

[example] use the inet_aton () function to convert a string network point address into a numeric network address. The SQL statement is as follows:

The resulting numbers are calculated in network byte order, and this example is calculated as: 209 * 256 ^ 3 + 207 * 256 ^ 2 + 224 * 256 ^ 40.

Mysql > select inet_aton ('209.207.224.40') +-- + | inet_aton ('209.207.224.40') | +-+ | 3520061480 | +-+ 1 row in set (0.00 sec)

2.inet_ntoa ()

Inet_ntoa (expr) given a numeric network address (4bit or 8bit), returns a dot representation of that address as a string.

The inet_ntoa function and inet_aton are inverse functions to each other.

[example] use the iner_ntoa function to convert a numeric network address into a string network point address. The SQL statement is as follows:

Mysql > select inet_ntoa (3520061480); +-+ | inet_ntoa (3520061480) | +-- + | 209.207.224.40 | +-- + 1 row in set (0.00 sec) (4), lock function and unlock function

1.get_lock (str,timeout) tries to get a lock using the name given by the string str, with a timeout of timeout seconds. 1 is returned if the lock is successfully obtained, 0 is returned if the operation times out, and null is returned if an error occurs.

If you have a lock obtained with get_lock (), the lock will be released when you execute release_lock () or when the link is broken (normal or abnormal).

2.release_lock (str) unlocks the lock acquired by get_lock () and named with the string str. Returns 1 if the lock is unlocked, 0 if the thread has not created the lock, or null if the named lock does not exist. If the lock has never been acquired by a call to get_lock (), or if the lock has been unlocked in advance, the lock does not exist.

3.is_free_lock (str) checks whether the lock named str is available (not blocked). Returns 1 if the lock is available (no one is using the lock), 0 if the lock is in use, and null (such as incorrect parameters) if an error occurs.

4.is_used_lock (str) checks whether the lock named str is in use (blocked). If blocked, the connection identifier (connectionID) of the client using the lock is returned; otherwise, null is returned.

[example] using locking and unlocking functions, the SQL statement is as follows:

Mysql > select get_lock ('lock1',10) as getlock,-> is_used_lock (' lock1') as isusedlock,-> is_free_lock ('lock1') as isfreelock,-> release_lock (' lock1') as releaselock +-+ | getlock | isusedlock | isfreelock | releaselock | +-+ | 1 | 21 | 0 | 1 | +-+ 1 row in set (0.05 sec) (5), A function that repeats a specified operation

The-benchmark (count,expr) function repeats the expression (expr) count times. It can be used to calculate the speed at which MySQL processes expressions. The result value is usually 0, (0 just means that the process is fast, not time-consuming). Another function is that it can report the execution time of the statement inside the MySQL client.

Use benchmark to repeat the specified function

You can see that the following statement is executed 500000 times in 0.38sec, which is significantly longer than the time it takes to execute once.

Mysql > select md5 ('Hudie') +-- + | md5 ('Hudie') | +-+ | 3fe2017e5cb984400c5271ef77a840f6 | +-- -+ 1 row in set (0.00 sec) mysql > select benchmark (500000) Md5 ('Hudie')) +-+ | benchmark (500000 Md5 ('Hudie')) | +-- + | 0 | +-- + 1 row in set (0.38 sec)

Note:

The time reported by benchmark is the elapsed time on the client side, not the CPU time on the server side, and the reported time is not necessarily the same after each execution.

(6) the function of changing the character set

Convert (… Using...) The convert () function with using is used to convert data between different character sets.

[example] use the convert () function to change the default character set of a string. The SQL statement is as follows

Mysql > select charset ('string'), charset (convert ('string' using latin1)) +-+ | charset ('string') | charset (convert ('string' using latin1)) | +-+- -- + | gbk | latin1 | + -+ 1 row in set (0.00 sec)

The default is the gbk character set, and the default character set of the string "strng" is changed to latin1 through convert.

(7) functions that change data types

The-case (x _ type as type) and convert (x _ type) functions convert the value of one type to another. The type values that can be converted are binary, char (n), date, time, datetime, decimal, signed, and unsigned.

[example] use case and convert functions to convert data types. The SQL statement is as follows:

Mysql > select cast (100as char (2)), convert ('2019-08-20 00:32:01', time) +-- + | cast (100 as char (2)) | convert ('2019-08-20 00:32:01' Time) | +-- +-- + | 10 | 00:32:01 | +-- + -- + 1 row in set 1 warning (0.05sec)

As you can see, case (100 as char (2)) converts the integer data type 100 to a string type with two display widths, and the result is "10"; convert ('2010-08-20 00 convert 32 convert') converts the value of type datetime to type time, resulting in 00:32:01.

This is the end of this article on "introduction to other functions in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.