MySQL學習筆記19：系統資訊函數

系統資訊函數用來查詢mysql資料庫的系統資訊

 VERSION()返回資料庫版本號碼

mysql> SELECT VERSION();+-------------------------+| VERSION()               |+-------------------------+| 5.5.28-0ubuntu0.12.10.2 |+-------------------------+1 row in set (0.00 sec)

我這裡用的是基於ubuntu發行版，Linux Mint

 

CONNECTION_ID()返回資料庫的串連次數

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

其實每次串連到mysql的時候就會有顯示

 

DATABASE()、SCHEMA()返回當前資料庫名

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

 

USER()、SYSTEM_USER()、SESSION_USER()返回目前使用者

mysql> SELECT USER(), SYSTEM_USER(), SESSION_USER();+----------------+----------------+----------------+| USER()         | SYSTEM_USER()  | SESSION_USER() |+----------------+----------------+----------------+| root@localhost | root@localhost | root@localhost |+----------------+----------------+----------------+1 row in set (0.00 sec)

 

CURRENT_USER()、CURRENT_USER返回目前使用者

mysql> SELECT CURRENT_USER(), CURRENT_USER;+----------------+----------------+| CURRENT_USER() | CURRENT_USER   |+----------------+----------------+| root@localhost | root@localhost |+----------------+----------------+1 row in set (0.00 sec)

上面的三個和這兩個功能是一樣的

 

CHARSET(str)返回字串str的字元集

mysql> SELECT CHARSET('張三');+-------------------+| CHARSET('張三')   |+-------------------+| utf8              |+-------------------+1 row in set (0.00 sec)

 

COLLATION(str)返回字串str的字元相片順序

mysql> SELECT COLLATION('張三');+---------------------+| COLLATION('張三')   |+---------------------+| utf8_general_ci     |+---------------------+1 row in set (0.00 sec)

 

LAST_INSERT_ID()返回最後產生的AUTO_INCREMENT值

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 |+----+3 rows in set (0.00 sec)mysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                3 |+------------------+1 row in set (0.00 sec)

上面的語句首先建立了一張表t1，其中有一個自增欄位id

然後分三次插入NULL，使其自增

確認已經存在資料之後，使用LAST_INSERT_ID()擷取最後自動產生的值