What are the mysql system information functions?-Mysql Tutorial-php.cn

What are the mysql system information functions?

coldplay.xixi

Release： 2020-06-29 14:13:48

Original

2104 people have browsed it

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.

What are the mysql system information functions?

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)

Copy after login

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)

Copy after login

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)

Copy after login

4. View the currently used database

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

Copy after login

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)

Copy after login

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)

Copy after login

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)

Copy after login

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)

Copy after login

(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)

Copy after login

(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)

Copy after login

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

Copy after login

(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)

Copy after login

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!