Recommended MySQL common functions + benefits

I have always wanted to write something about MySQL functions so that students can get started quickly. There are many built-in functions in MySQL. Students who are new to MySQL will be frightened at first glance and don’t know where to start. Of course, there are also many students who are very interested in memorizing the entire function list bit by bit like memorizing words (but I may have forgotten it later), and expressed my admiration. Here I would like to recommend five functions that are frequently used to those students who have poor memory, and take a look at them with cases. Finally, I present the PDF version of "Zhishutang-MySQL Built-in Function Manual" compiled by Zhishutang student: Wing.

Start getting to the point:

The first one: substring_index()

is used to return N strings after the specified character interval, the specified number of characters, official example:

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
            -> 'www.mysql'
    mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
            -> 'mysql.com'

Let’s take an actual case, it is possible I will remember it more clearly. For example, if you look at MySQL statistics, you will see which IPs are connected to MySQL, and each IP has multiple connections

select count(*) , substring_index(host,':',1) as ip , db from information_schema.processlist group by substring_index(host,':',1) ,db;

The second one: concat()

Character splicing function, look at the case:

mysql> select concat('zhishuedu.','com');
-> 'zhishuedu.com'

Look at the actual case: kill all the connections inside MySQL and let it connect to the DB

>select concat('kill ', id,';')  into outfile '/tmp/kthd.sql'  from information_schema.processlist  where user!='system';
>source /tmp/kthd.sql;

The third one: repeat()

is used to generate the length of the specified field string. It is particularly useful in stress testing and generating data. Directly Look at the case

root@localhost [(none)]>select repeat('zhishuedu.com ', 3);
+--------------------------------------------+
| repeat('zhishuedu.com ', 3)                   |
+--------------------------------------------+
| zhishuedu.com zhishuedu.com zhishuedu.com |
+--------------------------------------------+
1 row in set (0.00 sec)

Fourth: now()

Return to the current time, just type:
select now()
Field writing time is more useful. Because it is simple, we will not run this corresponding function: from_unixtime(), unix_timestamp(). Also pay attention to

The fifth one: length()

Return the string length, look at the case, sometimes it is helpful to count the field length and do some analysis

select length('wubx'), char_length('wubx'), length('知数堂'),char_length('知数堂')\G;
*************************** 1. row ***************************
length('wubx'): 4
char_length('wubx'): 4
length('知数堂'): 9
char_length('知数堂'): 3
1 row in set (0.00 sec)

This content is quite a lot, but it still needs to be done To finish, if you close your eyes and write down the functions you have typed before, there are also:

 crc32(), floor(), ceil(), pow(), isnull(), strcmp(), ifnull(), char_length(),adddate(), 
 date_format(), date(),year(),current_time(), user(), current_user()，password(), cast()

and so on.

If you are interested, you can also download the manual we provide and read it. Of course, this manual is also a first edition and there are still areas that need to be improved. Based on the principle from 0 to 1, let’s put it first Out. If you want to improve this, or have updates, contact me.

The above is the recommended MySQL common functions + benefits. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!