Big data - MySQL system information function

1. Function to obtain MySQL version number, number of connections and database name

(1)version()

version() returns a string indicating the MySQL server version, which uses the utf8 character set.
[example] check the current MySQL version number. The SQL statement is as follows:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.16    |
+-----------+
1 row in set (0.05 sec)

(2)connection_id()

connection_id() returns the number of current connections to the MySQL server. Each connection has its own unique ID.
[example] view the number of connections of the current user. The SQL statement is as follows:

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              18 |
+-----------------+
1 row in set (0.05 sec)

(3) show processlist and show full processlist

The output result of the processlist command shows which threads are running. You can view not only the current number of connections, but also the current connection status to help identify problematic query statements.
If it is a root account, you can see the current connections of all users. If it is other ordinary accounts, you can only see the connections occupied by yourself. show processlist only lists the first 100 items. If you want to list them all, you can use the show full processlist command.
[example] use the showprocesslist command to output the connection information of the current user. The SQL statement is as follows:

mysql> show processlist;
+----+-----------------+-----------------+---------+---------+--------+------------------------+------------------+
| Id | User            | Host            | db      | Command | Time   | State                  | Info             |
+----+-----------------+-----------------+---------+---------+--------+------------------------+------------------+
|  4 | event_scheduler | localhost       | NULL    | Daemon  | 381487 | Waiting on empty queue | NULL             |
| 18 | root            | localhost:60272 | company | Query   |      0 | starting               | show processlist |
+----+-----------------+-----------------+---------+---------+--------+------------------------+------------------+
2 rows in set (0.05 sec)

Meaning of each column:

column meaning
id "connection id" assigned by the system when a user logs in to MySQL
User The current user is displayed. If it is not root, the MySQL statements within the user's permission range are displayed
Host Displays the IP address and port from which the statement is sent. It can be used to track the users with the statement in question
db Displays which database this process is currently connected to
Command Displays the execution commands of the current connection. The general values are sleep, query and connect
Time Displays the duration of this state in seconds
State Displays the state of the SQL statement using the current connection. State is only a state in the execution of the statement
Info Displaying this SQL statement is an important statement to judge the problem statement

(4)database() ,schema()

[example] to view the currently used database, the SQL statement is as follows:

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

2. Function to get user name

user(),current_user(),system_user(),session_user()

These functions return the combination of user name and host name currently verified by MySQL server. This value is consistent with the MySQL account that determines the access rights of the currently logged in user.
[example] obtain the name of the current login user, and the SQL statement is as follows:
The returned result value shows the user name when the current account connects to the server and the connected client host. root is the currently logged in user name and localhost is the logged in host name.

mysql> select user(),current_user,system_user();
+----------------+----------------+----------------+
| user()         | current_user   | system_user()  |
+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+
1 row in set (0.05 sec)

3. A function that gets the character set and sort method of a string

(1)charset(str)

[example] use the charset() function to return the character set used by the string. The SQL statement is as follows:

mysql> select charset('abc'),
    -> charset(convert('abc' USING latin1 ) ),
    -> charset(version());
+----------------+----------------------------------------+--------------------+
| charset('abc') | charset(convert('abc' USING latin1 ) ) | charset(version()) |
+----------------+----------------------------------------+--------------------+
| gbk            | latin1                                 | utf8               |
+----------------+----------------------------------------+--------------------+
1 row in set (0.00 sec)

(2)collation(str)

[example] use the collation() function to return the string arrangement. The SQL statement is as follows:

mysql> select collation('abc'),collation(convert('abc' USING utf8 ));
+------------------+---------------------------------------+
| collation('abc') | collation(convert('abc' USING utf8 )) |
+------------------+---------------------------------------+
| gbk_chinese_ci   | utf8_general_ci                       |
+------------------+---------------------------------------+
1 row in set, 1 warning (0.05 sec)

4. Function to get the last automatically generated ID value

select last_insetr_id

[example] select last_insetr_id view the last automatically generated column value. The execution process is as follows:

(1) Insert one record at a time

First create the table worker with the id field auto_ For the increment constraint, the SQL statement is as follows:

mysql> create table worker
    -> (
    -> id int auto_increment not null primary key,
    -> Name varchar(30)
    -> );
Query OK, 0 rows affected (0.21 sec)

Insert two records into the worker table separately:

mysql> insert into worker values(null,'jimy');
Query OK, 1 row affected (0.07 sec)

mysql> insert into worker values(null,'Tom');
Query OK, 1 row affected (0.05 sec)

mysql> select * from worker;
+----+------+
| id | Name |
+----+------+
|  1 | jimy |
|  2 | Tom  |
+----+------+
2 rows in set (0.00 sec)

Check the inserted data and find that the id field value of the last inserted record is 2. Use last_insert_id() view the last automatically generated id value:

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

You can see that when inserting one record at a time, the return value is the id value of the last inserted record.

(2) Insert multiple records at one time

Insert multiple records into the table:

mysql> insert into worker values
    -> (null,'Kevin'),
    -> (null,'Michal'),
    -> (null,'NICK');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM WORKER;
+----+--------+
| id | Name   |
+----+--------+
|  1 | jimy   |
|  2 | Tom    |
|  3 | Kevin  |
|  4 | Michal |
|  5 | NICK   |
+----+--------+
5 rows in set (0.00 sec)

Use last_insert_id view the last automatically generated id value:

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

When inserting multiple rows with one insert statement, last_insert_id() only returns the value generated when the first row of data is inserted. Here is the third record inserted. This is because it makes it easy to rely on other servers to copy the same insert statement.
Tips:
last_insert_id is independent of table. First insert data into table a, and then insert data into table b. last_insert_id returns the id value in table b.

Tags: MySQL Big Data

Posted by galvin on Sat, 21 May 2022 07:51:54 +0300