Mysql Notes System Information function detailed _mysql

System Information function to query the MySQL database system Information

Version () returns the database edition number

Copy Code code as follows:

Mysql> SELECT VERSION ();
+-------------------------+
| VERSION () |
+-------------------------+
| 5.5.28-0ubuntu0.12.10.2 |
+-------------------------+
Row in Set (0.00 sec)

I'm here using the Ubuntu distribution, Linux Mint

CONNECTION_ID () returns the number of connections to the database

Copy Code code as follows:

Mysql> SELECT connection_id ();
+-----------------+
| connection_id () |
+-----------------+
| 36 |
+-----------------+
Row in Set (0.00 sec)

In fact, every time you connect to MySQL, there will be a display

Database (), SCHEMA () returns the current name

Copy Code code as follows:

Mysql> SELECT DATABASE (), SCHEMA ();
+------------+----------+
| DATABASE () | SCHEMA () |
+------------+----------+
| person | person |
+------------+----------+
Row in Set (0.00 sec)

User (), system_user (), Session_user () returns the current users

Copy Code code as follows:

Mysql> SELECT USER (), System_user (), Session_user ();
+----------------+----------------+----------------+
| USER () | System_user () | Session_user () |
+----------------+----------------+----------------+
| Root@localhost | Root@localhost | Root@localhost |
+----------------+----------------+----------------+
Row in Set (0.00 sec)

Current_User (), CURRENT_USER returns the current user

Copy Code code as follows:

Mysql> SELECT current_user (), Current_User;
+----------------+----------------+
| Current_User () | Current_User |
+----------------+----------------+
| Root@localhost | Root@localhost |
+----------------+----------------+
Row in Set (0.00 sec)

The top three and these two functions are the same

CHARSET (str) returns the character set of the string str

Copy Code code as follows:

mysql> SELECT CHARSET (' John ');
+-------------------+
| CHARSET (' John ') |
+-------------------+
| UTF8 |
+-------------------+
Row in Set (0.00 sec)

Collation (str) returns the character permutation of the string str

Copy Code code as follows:

Mysql> SELECT collation (' John ');
+---------------------+
| Collation (' John ') |
+---------------------+
| Utf8_general_ci |
+---------------------+
Row in Set (0.00 sec)

last_insert_id () returns the last generated Auto_increment value

Copy Code code as follows:

mysql> CREATE TABLE T1 (id INT PRIMARY KEY auto_increment);
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT into T1 VALUES (NULL);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT into T1 VALUES (NULL);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT into T1 VALUES (NULL);
Query OK, 1 row affected (0.04 sec)

Mysql> SELECT * from T1;
+----+
| ID |
+----+
| 1 |
| 2 |
| 3 |
+----+
Rows in Set (0.00 sec)

Mysql> SELECT last_insert_id ();
+------------------+
| last_insert_id () |
+------------------+
| 3 |
+------------------+
Row in Set (0.00 sec)

The above statement first creates a table T1, which has a self-added field ID

And then insert null three times to make it self-increasing

After confirming that the data already exists, use last_insert_id () to get the last auto-generated value