What are the mysql system information functions?

mysql system information functions include: 1. Use the [SHOW PROCESSLIST] command to output the current user’s connection information; 2. Use the [CHARSET()] function to return the character set used in the string; 3. Use [COLLATION( )】The function returns the string arrangement.

mysql system information functions are:

1. View the current MySQL version number

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

Related learning recommendations: mysql video tutorial

## 2. Check the number of connections of the current user

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

3. Use the SHOW PROCESSLIST command to output the current user’s connection information

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 currently used database

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)

6. Use the CHARSET() function to return the character set used in 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)

7. Use 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)

8. Use SELECT LAST_INSERT_IDView the last automatically generated column value

1. Insert one record at a time

(1). First create the table worker, whose Id field has

AUTO_INCREMENTConstraints

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 the 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) Check the inserted data to find out, finally The Id field value of an 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.01 sec)

2. Insert multiple records at one 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)

The above is the detailed content of What are the mysql system information functions?. For more information, please follow other related articles on the PHP Chinese website!