Ten useful and simple MySQL functions

function

0. Display current time

Command: select now().

Function: Display the current time.

Application scenarios: default values ​​such as creation time and modification time.

example:

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2017-12-27 20:14:56 |
+---------------------+
1 row in set (0.00 sec)

1. Character length

Command: select char_length('andyqan').

Function: Display the specified character length.

Application scenario: When checking the character length.

example:

mysql> select char_length('andyqian');
+-------------------------+
| char_length('andyqian') |
+-------------------------+
| 8 |
+-------------------------+
1 row in set (0.00 sec)

2. Date formatting

Command: select date_format(now(),'%y-%m-%d).

Function: Format date.

Application scenario: when formatting dates.

example:

mysql> select date_format(now(),'%y-%m-%d');
+-------------------------------+
| date_format(now(),'%y-%m-%d') |
+-------------------------------+
| 17-12-28 |
+-------------------------------+
1 row in set (0.00 sec)

The supported formats are:

%y: represents the year (two digits), for example: 17.
%Y: represents the year in 4 digits, for example: 2017
%m: indicates month (1-12)
%d: represents the day of the month
%H: hour (0-23)
%i: minute (0-59)
%s: seconds (0-59)

Year, month, day, hour, minute, second: %y-%m-%d %H:%i:%s,

As shown below:

mysql> select DATE_FORMAT(now(),'%y-%m-%d %H:%i:%s');
+----------------------------------------+
| DATE_FORMAT(now(),'%y-%m-%d %H:%i:%s') |
+----------------------------------------+
| 17-12-27 20:28:54 |
+----------------------------------------+
1 row in set (0.00 sec)

3. Add/subtract date and time

Order:

DATE_ADD(date,interval expr unit)
DATE_SUB(date,interval expr unit)

Function: Increase/decrease date and time

Application scenario: one day or a few minutes before the current time. Often used in data statistics.

example:

mysql> select date_add(now(),interval 1 day);
+--------------------------------+
| date_add(now(),interval 1 day) |
+--------------------------------+
| 2017-12-28 20:10:17 |
+--------------------------------+
1 row in set (0.00 sec)

Date represents the date format, including:

2017-12-27, now() and other formats.

expr: represents quantity.

unit: indicates the unit, supporting milliseconds (microsecond), seconds (second), hours (hour), days (day), weeks (week), years (year), etc.

4. Type conversion

Command: CAST(expr AS type)

Function: Mainly used for display type conversion

Application scenario: display type conversion

example:

mysql> select cast(18700000000 as char);
+---------------------------+
| cast(18700000000 as char) |
+---------------------------+
| 18700000000 |
+---------------------------+
1 row in set (0.00 sec)

It should be noted that type does not support all basic data types. For detailed supported types, please refer to the previous article "Talking about MySQL Display Type Conversion".

5. Encryption Function

Command: md5(data)

Purpose: Used to encrypt data

Application scenario: Encryption, some private data, such as bank card numbers, ID cards, etc. need to be stored in ciphertext (of course, it is not recommended to use database layer encryption, it should be encrypted at the application layer)

example:

mysql> select md5("andyqian");
+----------------------------------+
| md5("andyqian") |
+----------------------------------+
|8a6f60827608e7f1ae29d1abcecffc3a|
+----------------------------------+
1 row in set (0.00 sec)

Note: If the data in your database is still in plain text, you can use the database encryption algorithm to encrypt it.

For example: (for demonstration only):

update t_base_user set name=md5(name),updated_time=now() where id=1;

Supported encryption functions are:

md5()
des_encrypt(encryption) / des_decrypt(decryption);
sha1()
password() etc.

I will not introduce them one by one here. Students who are interested can go to the official website for detailed information.

6. String Concatenation

Command: concat(str,str2,str3)

Function: concatenate strings

Application scenario: concatenating strings, for example, adding a specified string to certain fields.

example:

mysql> select concat("andy","qian");
+-----------------------+
| concat("andy","qian") |
+-----------------------+
| andyqian |
+-----------------------+
1 row in set (0.00 sec)

This function is still used quite frequently. Basically, the scenario is to add a specific string to some data. Here’s how:

7. JSON function (supported only in version 5.7)

Command: json_object(function)

Function: Convert json string

Application scenario: specify data conversion json string

example:

mysql> select json_object("name","andyqian","database","MySQL");
+---------------------------------------------------+
| json_object("name","andyqian","database","MySQL") |
+---------------------------------------------------+
| {"name": "andyqian", "database": "MySQL"} |
+---------------------------------------------------+
1 row in set (0.00 sec)

Among them is json_array:

mysql> select json_array("name","andyqian","database","MySQL");
+--------------------------------------------------+
| json_array("name","andyqian","database","MySQL") |
+--------------------------------------------------+
| ["name", "andyqian", "database", "MySQL"] |
+--------------------------------------------------+
1 row in set (0.00 sec)

json_valid() to determine whether it is a json string:

select json_valid('{"name": "andyqian", "database": "MySQL"}');

The value is 1 if it is a valid JSON string.

0 for invalid json string.

There are many more methods, which I will not demonstrate one by one.

8. Aggregate Functions

Commands: sum(), count(), avg(), max(), min()

Function: statistics, average, maximum and minimum values

Application scenarios: This type of function is very common and is mainly used for data statistics and is also applicable to SQL optimization.

example:

mysql> select max(id) from t_base_user;
+---------+
| max(id) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)

Here is a little trick. If the primary key is ordered and ascending, when you need to know the number of users, you can use max(id) instead of count(*) function.

9. distinct()

Command: distinct

Function: Deduplication

Application scenario: when statistical type, status, and discrimination calculation are required.

example:

mysql> select count(distinct(name))/count(*) from t_base_user;
+--------------------------------+
| count(distinct(name))/count(*) |
+--------------------------------+
| 0.6667 |
+--------------------------------+
1 row in set (0.00 sec)

Summarize

The above are ten practical and simple MySQL functions introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!