MySQL Study Notes 19: system information functions

The system information function is used to query the system information of the MySQL database.

Version () returns the database version number.

MySQL >   Select  Version ();  +  --  ----------------------- +  | Version () |  +  --  ----------------------- +  |   5.5 . 28 - 0ubuntu0. 12.10 . 2   |  +  --  ----------------------- +  1 Row In   Set ( 0.00 Sec)

I am using the Ubuntu-based release, Linux mint

 

Connection_id () returns the number of database connections

 mysql >  select   connection_id ();  +   --   --------------- +  | connection_id () |  +   --   --------------- +  |  36  |  +   --   --------------- +   1  row  in   set  ( 0.00  Sec) 

In fact, each connection to MySQL will display

 

Database (), schema () returns the current database name

MySQL >   Select   Database (), Schema  ();  +  --  ---------- +  |   Database () |   Schema () |  + --  ---------- +  | Person | Person |  +  --  ---------- +  1 Row In   Set ( 0.00 Sec)

 

User (), system_user (), session_user () returns the current user

MySQL >  Select   User (), System_user (), Session_user  ();  +  --  -------------- + ---------------- +  |   User () |   System_user () |   Session_user () | +  --  -------------- + ---------------- +  | Root @ Localhost   | Root @ Localhost   | Root @ Localhost   |  +  --  -------------- + ---------------- +  1 RowIn   Set ( 0.00 Sec)

 

CURRENT_USER (), CURRENT_USER returns the current user

MySQL >   Select   CURRENT_USER (), CURRENT_USER  ;  +  --  -------------- + ---------------- +  |   CURRENT_USER () |  CURRENT_USER     |  +  --  -------------- + ---------------- +  | Root @ Localhost   | Root @ Localhost   |  +  --  -------------- + ---------------- +  1 Row In  Set ( 0.00 Sec)

The above three functions are the same as the two functions.

 

Charset (STR) returns the character set of the STR string

MySQL >   Select Charset ( '  Zhang San  '  );  +  --  ----------------- +  | Charset ( '  Zhang San ' ) |  +  --  ----------------- +  | Utf8 |  +  --  ----------------- +  1 Row In   Set ( 0.00 Sec)

 

Collation (STR) returns the character arrangement of the STR string.

MySQL >   Select Collation ( '  Zhang San  '  );  +  --  ------------------- +  | Collation ( '  Zhang San  ' ) |  +  -- ------------------- +  | Utf8_general_ci |  +  --  ------------------- +  1 Row In   Set ( 0.00 Sec)

 

Last_insert_id () returns the final auto_increment value.

MySQL >   Create   Table T1 (IDInt   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   |  +  --  -- +  3 Rows In   Set ( 0.00  Sec) MySQL  >   Select  Last_insert_id ();  +  --  ---------------- +  | Last_insert_id () |  +  --  ---------------- +  |                  3   | +  --  ---------------- +  1 Row In   Set ( 0.00 Sec)

The preceding statement first creates a table T1 with an auto-increment field ID.

Insert null three times to enable auto-increment.

After confirming that the data already exists, use last_insert_id () to obtain the last automatically generated value.