mysql System information function of function summary

mysql The system information in has the version number of the database 、 Current user name and number of connections 、 System character set 、 The last one is automatically generated ID It's worth waiting for .

obtain mysql Version number 、 Functions of connection number and database name

varsion() Return instructions mysql Server version string .

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.29    |
+-----------+
1 row in set (0.00 sec)

mysql>

obtain mysql The number of connections

connection_id() return mysql The number of times the server is currently connected , Each connection has its own unique id;

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              24 |
+-----------------+
1 row in set (0.00 sec)

mysql>

PS： The return value will vary according to the number of login .

obtain mysql Running threads

show processlist： Shows which threads are running , You can not only view the current number of connections , You can also view the current connection status , You can use this to find out the problem sql Statement etc. .

Be careful ：

1、 If it is root Account , Can see all users At present Connect . Other users can only see their own .

2、show processlist Only 100 strip , If you want to see it all, you can use show full processlist command .

mysql> show processlist;
+----+-----------------+-----------------+---------+---------+--------+------------------------+------------------+
| Id | User            | Host            | db      | Command | Time   | State                  | Info             |
+----+-----------------+-----------------+---------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL    | Daemon  | 946721 | Waiting on empty queue | NULL             |
| 24 | root            | localhost:58414 | test_db | Query   |      0 | init                   | show processlist |
+----+-----------------+-----------------+---------+---------+--------+------------------------+------------------+
2 rows in set (0.01 sec)

mysql>

Use show processlist The meaning of each field of the result found by the command ：

• ID： The user login mysql when , The system assigns coeenction id;
• User： Show The current user ;
• Host： Show which IP On which port of , It can be used to track the user of the problem statement ;
• db： Shows which database the process is currently connected to ;
• Command： Displays the commands executed by the current connection , Generally, the value is sleep ( sleep)、 Inquire about ( query)、 Connect ( connect);
• Time： Shows the duration of this state , The unit is seconds ;
• State： Shows the... Using the current connection sql The state of the statement . state It's just a state in statement execution . One sql sentence , Take the query as an example , May need to go through copying to tmp table、sorting result、sending date Wait for the status to complete ;
• Info： Show this sql sentence , It is an important basis to judge the problem statement .

View usage utf8 The default database name of the character set

mysql> select database(), schema();
+------------+----------+
| database() | schema() |
+------------+----------+
| test_db    | test_db  |
+------------+----------+
1 row in set (0.00 sec)

mysql>

Function to get the user name

The following functions can return the current mysql User name and host combination verified by the server :

• user()
• current_user
• current_user()
• system_user()
• session_user()

mysql> select user(), current_user, system_user();
+----------------+----------------+----------------+
| user()         | current_user   | system_user()  |
+----------------+----------------+----------------+
| [email protected] | [email protected] | [email protected] |
+----------------+----------------+----------------+
1 row in set (0.00 sec)

mysql>

The returned result value shows the user name when the current account connects to the server and the connected client host , The rule is ： Currently logged in user name @ Currently logged in host .

Get the character set and sorting method of the string

charset(str) Return string str The character set of the argument ;

mysql> select charset('yunweijia') as roll, charset(convert('abc' using latin1)) as roll_1, charset(version()) as roll_2;
+---------+--------+---------+
| roll    | roll_1 | roll_2  |
+---------+--------+---------+
| utf8mb3 | latin1 | utf8mb3 |
+---------+--------+---------+
1 row in set (0.00 sec)

mysql>

collation(str) Return string str The character arrangement of ;

mysql> select collation('yunweijia') as coll, collation(convert('yunweijia' using latin1)) as coll_1;
+-----------------+-------------------+
| coll            | coll_1            |
+-----------------+-------------------+
| utf8_general_ci | latin1_swedish_ci |
+-----------------+-------------------+
1 row in set (0.00 sec)

mysql>

Get the last automatically generated ID The value of the function

last_insert_id() Function returns the last generated automatically incremented value ;

（1） First we need to create a table ;

mysql> create table test_1 (id int auto_increment not null primary key, name varchar(100));
Query OK, 0 rows affected (0.05 sec)

mysql>

（2） Insert several pieces of data into the table we just created ;

mysql> insert into test_1 values(null, 'zhangsan'), (null, 'lisi'), (null, 'wangwu');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

（3） Check the data in the following table ;

mysql> select * from test_1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+
3 rows in set (0.00 sec)

mysql>

（4） Use last_insert_id() View the last automatically generated id value ;

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql>

Why would it be 1, instead of 3 Well ？

Keep looking down ;

（5） Insert data again ;

mysql> insert into test_1 values (null, 'yunwei');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_1 values (null, 'yunweijia');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_1 values (null, '0909');
Query OK, 1 row affected (0.01 sec)

mysql>

（6） View the contents of the data table

mysql> select * from test_1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | zhangsan  |
|  2 | lisi      |
|  3 | wangwu    |
|  4 | yunwei    |
|  5 | yunweijia |
|  6 | 0909      |
+----+-----------+
6 rows in set (0.00 sec)

mysql>

（7） see auto_increment() Value

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

mysql>

Why is that ？

This is because the three pieces of data inserted for the first time , It uses a sql, Use last_insert_id() Use , Only return this one sql Get the first auto_increment Value , That is, although 3 Data , But what he got was only the first piece of data inserted auto_increment Value , That is to say 1;

Why return to 6, Because the three pieces of data inserted later were inserted separately , So what he gets every time belongs to him auto_increment, So I returned 6.

tip ：

last_insert_id It has nothing to do with the data table , If you look at the table test_1 Insert data into the table test_2 insert data , that last_insert_id Go back to the table test_2 Medium auto_increment Value .

thus , In this paper, the end .