Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the system information functions in MySQL, I hope you have something to gain after reading this article, let's discuss it together!

System information function

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

(2) the function to get the user name

(3) the function of getting the character set and sorting mode of a string

(4) the function that gets the last automatically generated ID value

(recommended for free study: mysql video tutorial)

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

1.version ()

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

[example] to view the current MySQL version number, the SQL statement is as follows:

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

2.connection_id ()

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

[example] 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.05sec)

3.show processlist and show full processlist

The output of the 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 connections of all users, and if it is other ordinary accounts, you can only see the connections you occupy. 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 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.05sec)

The meaning of each column:

Column meaning id column when a user logs in to MySQL, the "connection id" User assigned by the system shows the current user. If it is not root, it shows the MySQL statement within the user's authority. Host shows which port of the IP this statement is issued from, and can be used to track the user with the problem statement. Db shows which database the process is currently connected to. Command shows the execution command of the current connection. Generally, the values are sleep (sleep), Query (query), Connect (Time). The duration of this state is displayed in seconds. State displays the status of the SQL statement using the current connection. State is just a state in the execution of the statement. Info displays this SQL statement, which is an important statement to judge the problem statement.

4.database () and schema ()

[example] to view the database currently in use, 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

The functions user (), current_user (), system_user (), and session_user () return the combination of username and hostname currently authenticated by the MySQL server. This value is consistent with the MySQL account that determines the access rights of the currently logged-in user.

[example] get the name of the currently logged-in user. The SQL statement is as follows:

The returned value shows the user name and the connected client host when the current account connects to the server. Root is the user name of the current login, and localhost is the host name of the login.

Mysql > select user (), current_user,system_user () +-+ | user () | current_user | system_user () | +- -- + | root@localhost | root@localhost | root@localhost | +-+ 1 row in set (0.05sec) (3), A function that gets the character set and sorting 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.05sec) (4), the function to get the last automatically generated ID value

[example] use select last_insetr_id to view the last automatically generated column value, as follows:

① inserts one record at a time

First, create the table worker, whose id field is constrained by auto_increment. 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 table worker separately:

Mysql > insert into worker values (null,'jimy'); Query OK, 1 row affected (0.07sec) mysql > insert into worker values (null,'Tom'); Query OK, 1 row affected (0.05sec) mysql > select * from worker;+----+-+ | id | Name | +-- +-+ | 1 | jimy | 2 | Tom | +-- +-+ 2 rows in set (0.00 sec)

Look at the data that has been inserted 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.05sec)

As you can see, when you insert one record at a time, the return value is the id value of the last inserted record.

② inserts multiple records at the same time

Insert multiple records into the table:

Mysql > insert into worker values-> (null,'Kevin'),-> (null,'Michal'),-> (null,'NICK'); Query OK, 3 rows affected (0.05sec) Records: 3 Duplicates: 0 Warnings: 0mysql > SELECT * FROM WORKER +-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 you insert multiple rows using an insert statement, last_insert_id () returns only the value generated when the first row of data is inserted, in this case, the third record. This is because it makes it easy to rely on other servers to copy the same insert statements.

Tip:

Last_insert_id is independent of table, inserting data into table an and then into table b, and last_insert_id returns the id value in table b.

After reading this article, I believe you have a certain understanding of "what are the system information functions in MySQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.