This article was first published on my wechat official account andyqian, looking forward to your attention!

preface

Following on from the previous “10 useful MySQL commands”, here are 10 useful MySQL functions. The following are some of the more common and simple functions that are very common at work.

function

0. Run the select now() command to display the current time. Function: Displays the current time. Application scenario: Create time, modify time and other default values. Example:

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

1. Character length Command: select char_length(‘andyqan’). Function: Displays the specified character length. Application scenario: Viewing the character length. Example:

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

Select date_format(now(),’%y-%m-%d). Function: Formats a date. Application scenario: Formatting dates. Example:

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

The supported formats are as follows: %y: indicates the year (two digits), for example, 17 years. For example, 2017. % M: month (1-12) % D: day in the month %H: hour (0-23) % I: minute (0-59) %s: second (0-59)

Year month day hour minute second :% Y -%m-%d %H:% I :%s, as follows:

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)Copy the code

3. Run the DATE_ADD(date,interval expr unit) DATE_SUB(date,interval expr unit) command to add or subtract the date and time. Application scenario: One day earlier than the current time, several minutes earlier than the current time. Often used in 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 the set (0.00 SEC)Copy the code

Date indicates the Date format, such as 2017-12-27 and now(). Expr: indicates the quantity. Unit: indicates the unit. The value can be microsecond, second, hour, day, week, and year.

4. Type conversion command: CAST(expr AS type) Function: mainly used to display type conversion Application scenario: Display type conversion example:

mysql> select cast(18700000000 as char); +---------------------------+ | cast(18700000000 as char) | +---------------------------+ | 18700000000 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Note that type does not support all basic data types. Please refer to the previous article on MySQL display type conversion for details.

5. Encryption function command: md5(data) Function: used to encrypt data Application scenario: Encrypt private data, such as the bank card number and ID card, in ciphertext. (Of course, database layer encryption is not recommended.

mysql> select md5("andyqian"); +----------------------------------+ | md5("andyqian") | +----------------------------------+ | 8 a6f60827608e7f1ae29d1abcecffc3a | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

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

For example: (demo only):

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

The supported encryption functions are:

  1. md5()

  2. Des_encrypt/des_decrypt;

  3. sha1()

  4. The password (s), and so on

There will be no more introductions here. If you are interested, you can go to the official website for a detailed understanding.

6. String concatenation command: concat(STR,str2,str3) Function: Concatenation of strings Application scenario: Concatenation of strings, for example, concatenation of specified strings on certain fields. Example:

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

This function is usually used a lot. Basically, the scenario is to add a particular string to some data. The method is as follows:

7. JSON functions (only supported in 5.7)

Json_object (function) Function: Convert JSON string Application scenario: Convert data to JSON string Example:

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

These include jSON_array:

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

Json_valid ():

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

The value is 1 if it is a valid JSON string. 0 for invalid JSON strings. There are more methods than I’ll show you.

8. Aggregate function commands: sum(), count(), avg(), Max (), min() Functions: statistics, average, Max (), min() Application scenario: This kind of function is very common, mainly used for data statistics, SQL optimization, also applicable. Example:

mysql> select max(id) from t_base_user; + -- -- -- -- -- -- -- -- -- + | Max (id) | + -- -- -- -- -- -- -- -- -- + | 2 | + -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

A trick here is to use Max (id) instead of count(*) when the number of users is required if the primary key is ordered incremented.

9. Distinct () Command: distinct Function: Deduplication Application scenario: When you need to collect statistics on types and status and calculate the distinctness. Example:

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

The above is an example of calculating differentiation. If you do not understand, you can refer to the previous article “Write MySQL index”.

Finally: Good night to all!

Related reading:

Discussion on the structure design of MySQL table

Talk about MySQL display type conversion

Talk about the MySQL JSON data type

MySQL Online DDL

 

Scan code attention, progress together

Personal blog: www.andyqian.com