Ten useful and simple MySQL functions

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!

You may also be interested in:
  • A brief discussion on ifnull() function similar to nvl() function in MySQL
  • MySQL interception and split string function usage examples
  • PHP mysqli_free_result() and mysqli_fetch_array() functions explained
  • How to use MySQL rand function to implement random numbers
  • Tips for using MySQL's specified range random number function rand()
  • Detailed explanation of JSON series operation functions in Mysql
  • Usage and precautions of Mysql row number() sorting function
  • Summary of mysql group_concat() function usage

<<:  JS Object constructor Object.freeze

>>:  Tomcat class loader implementation method and example code

Recommend

Differences and usage examples of for, for...in, for...of and forEach in JS

for loop Basic syntax format: for(initialize vari...

How to implement on-demand import and global import in element-plus

Table of contents Import on demand: Global Import...

HTML Tutorial: Collection of commonly used HTML tags (6)

Related articles: Beginners learn some HTML tags ...

How to uninstall MySQL cleanly (tested and effective)

How to uninstall Mysql perfectly? Follow the step...

Methods and steps for deploying multiple war packages in Tomcat

1 Background JDK1.8-u181 and Tomcat8.5.53 were in...

A brief discussion on the corresponding versions of node node-sass sass-loader

Table of contents The node version does not corre...

Native JavaScript carousel implementation method

This article shares the implementation method of ...

CSS to achieve dynamic secondary menu

Dynamically implement a simple secondary menu Whe...

Memcached method for building cache server

Preface Many web applications store data in a rel...

MySQL 5.7.17 latest installation tutorial with pictures and text

mysql-5.7.17-winx64 is the latest version of MySQ...

The connection between JavaScript and TypeScript

Table of contents 1. What is JavaScript? 2. What ...