Shulou(Shulou.com)06/01 Report--

This article will explain in detail about the system information function in mysql, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

System information function

The system information in MySQL includes the version number of the database, the current user name and number of connections, the system character set, the last automatically generated ID value, and so on.

1. A function that gets the MySQL version number, number of connections, and database name.

A.version () returns a string indicating the version of the MySQL server. This string uses the utf8 character set.

Case: check the current MySQL version number. The SQL statement is as follows:

SELECT VERSION ()

B.connection_id () returns the number of current connections to the MySQL server, each with its own unique ID.

Case: check the number of connections of the current user. The SQL statement is as follows:

SELECT CONNECTION_ID ()

The output of the c.processlist command shows which threads are running, not only the current number of connections, but also the current connection status, helping to identify problematic query statements, and so on. If it is a root account, you can see the current connection of all users. If it is an ordinary account, you can only see the connection occupied by yourself. Show processlist; lists only the first 100 items. If you want to list them all, you can use the show full processlist; command.

Example: use the show processlist command to output the connection information of the current user. The SQL statement is as follows:

SHOW PROCESSLIST

The d.datebase () and schema () functions return the default (current) database name that uses the utf8 character set.

Case study: view the database currently in use. The SQL statement is as follows:

SELECT DATABASE (), SCHEMA ()

The mysql system information functions are:

1. Check the current MySQL version number

Mysql > SELECT VERSION (); +-+ | VERSION () | +-+ | 5.7.22 | +-+ 1 row in set (0.00 sec)

Second, check the number of connections of the current user

Mysql > SELECT CONNECTION_ID (); +-+ | CONNECTION_ID () | +-+ | 2 | +-+ 1 row in set (0.00 sec)

Use the SHOW PROCESSLIST command to output the connection information of the current user

Mysql > SHOW PROCESSLIST +-+-+ | Id | User | Host | db | Command | Time | State | Info | +-+-- -+ | 2 | root | | test | Query | 0 | starting | SHOW PROCESSLIST | +-+-- -+-+ 1 row in set (0.00 sec)

4. View the database currently in use

Mysql > SELECT DATABASE (), SCHEMA (); +-+-+ | DATABASE () | SCHEMA () | +-+-+ | test | test | +-+-+ 1 row in set (0.00 sec)

5. Get the name of the currently logged-in user

Mysql > SELECT USER (), CURRENT_USER (), SYSTEM_USER () +-+-+ | USER () | CURRENT_USER () | SYSTEM_USER () | +-+-- -+-+ | root@ | skip-grants user@skip-grants host | root@ | +-+-- +-+ 1 row in set (0.00 sec)

Use the CHARSET () function to return the character set used by the string

SELECT CHARSET ('abc'), CHARSET (CONVERT (' abc' USING latin1)), CHARSET (VERSION ()) +-+-+ | CHARSET ('abc') | CHARSET (CONVERT (' abc' USING latin1)) | CHARSET (VERSION ()) | +- +-+-+ | utf8 | latin1 | utf8 | +-+- -+-+ 1 row in set (0.00 sec)

Use the COLLATION () function to return the string arrangement

Mysql > SELECT COLLATION ('abc'), COLLATION (CONVERT (' abc' USING utf8)) +-+ | COLLATION ('abc') | COLLATION (CONVERT (' abc' USING utf8)) | +-+-- -+ | utf8_general_ci | utf8_general_ci | +-+-- + 1 row in set (0.00 sec)

Use SELECT LAST_INSERT_ID to view the last automatically generated column value

1. Insert one record at a time

(1) first create the table worker, whose Id field is constrained by AUTO_INCREMENT.

CREATE TABLE worker (Id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, Name VARCHAR (30)); Query OK, 0 rows affected (0.23 sec)

(2) insert 2 records into table worker separately:

Mysql > INSERT INTO worker VALUES (NULL, 'jimy'); Query OK, 1 row affected (0.03 sec) mysql > INSERT INTO worker VALUES (NULL,' Tom'); Query OK, 1 row affected (0.02 sec) mysql > SELECT * FROM worker;+----+-+ | Id | Name | +-- +-+ | 1 | jimy | 2 | Tom | +-+-+ 2 rows in set (0.00 sec)

(3) if you look at the inserted data, you can 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.01sec)

2. Insert multiple records at the same time

(1) next, insert multiple records into the table

INSERT INTO worker VALUES (NULL,' Kevin'), (NULL,'Michal'), (NULL,'Nick'); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0

(2) query the inserted records

Mysql > SELECT * FROM worker;+----+-+ | Id | Name | 1 | jimy | | 2 | Tom | | 3 | Kevin | 4 | Michal | | 5 | Nick | +-+-+ 5 rows in set (0.00 sec) mysql > SELECT LAST_INSERT_ID () +-+ | LAST_INSERT_ID () | +-+ | 3 | +-+ 1 row in set (0.00 sec) so much for sharing the system information function in mysql. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.