MySQL introduces system information function

System information function

(1) Function to get the MySQL version number, number of connections and database name
(2) Function to get the user name
(3) Function to obtain the character set and sorting method of a string
(4) Function to obtain the last automatically generated ID value

(Related free learning recommendations: mysql video tutorial)

(1), function to obtain the MySQL version number, number of connections and database name

1.version()

• version() returns the instruction MySQL Server version of the string, this string uses the utf8 character set.

[Example] View 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 current MySQL server The number of connections, each connection has its own unique ID.

[Example] To check 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

• processlist command The output results show which threads are running. Not only can you view the number of current connections, but you can also view the current connection status to help identify problematic query statements, etc.
• If you have a root account, you can see the current connections of all users. If you have other ordinary accounts, you can only see the connections you occupy. show processlist only lists the first 100 entries. If you want to list them all, 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)

The meaning of each column:

Column	Meaning
id column	The "connection id" assigned by the system when the user logs in to MySQL
User	Displays the current user. If it is not root, displays the MySQL statements within the user's permissions
Host	Display which IP and which port this statement was issued from, which can be used to track the user who issued the problematic statement
db	Display the current status of this process Which database is connected
Command	Displays the execution command of the current connection. The general values ​​are sleep, query, and connect
Time	Displays the duration of this state, the unit is seconds
State	Displays the current use The state of the connected SQL statement, State is just a certain state in the execution of the statement
Info	Displaying this SQL statement is an important statement for judging the problem statement

4.database() and schema()
[Example] 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 ), functions to obtain user names

• user(), current_user(), system_user() and session_user() functions return the username and hostname combination currently verified by the MySQL server. This value corresponds to the MySQL account that determines the access rights of the currently logged in user.

[Example] Get the current login user name, the SQL statement is as follows:

• The return 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), function to obtain the character set and sorting 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 obtain the last automatically generated ID value

[Example] Use select last_insetr_id checks the last automatically generated column value. The execution process is as follows:

①Insert one record at a time
First create the table worker, whose id field has auto_increment constraints, and 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 table worker 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() to 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.

②Insert multiple records at once
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: 0mysql> 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 to view the last automatically generated id value:

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

When using an insert statement to insert multiple rows, last_insert_id() only returns the value generated when the first row of data is inserted, here the third record is inserted. The reason for this is that it makes it easy to rely on other servers to replicate the same insert statement.

Tips:

• last_insert_id is independent of the table. First insert data into table a, then insert data into table b, last_insert_idReturn the id value in table b.

Related free learning recommendations: mysql database(Video)

The above is the detailed content of MySQL introduces system information function. For more information, please follow other related articles on the PHP Chinese website!