MySQL中的信息函数

信息函数，顾名思义，就是通过这些函数可以获得各种各样的信息，方便使用。下表就是MySQL 5.7中支持的信息函数。

我们通过实例来研究下用法。

• CHARSET(str)，返回字符串str的字符集

root@database-one 22:56:  [gftest]> SELECT CHARSET('abc'),CHARSET(CONVERT('abc' USING latin1)),CHARSET(USER());
+----------------+--------------------------------------+-----------------+
| CHARSET('abc') | CHARSET(CONVERT('abc' USING latin1)) | CHARSET(USER()) |
+----------------+--------------------------------------+-----------------+
| utf8           | latin1                               | utf8            |
+----------------+--------------------------------------+-----------------+
1 row in set (0.01 sec)

• CONNECTION_ID()，返回connection的ID（线程ID）

root@database-one 23:05:  [gftest]> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|        31311271 |
+-----------------+
1 row in set (0.01 sec)

root@database-one 23:05:  [gftest]> select * from INFORMATION_SCHEMA.PROCESSLIST where id=connection_id();
+----------+------+-----------------+--------+---------+------+-----------+-----------------------------------------------------------------------+
| ID       | USER | HOST            | DB     | COMMAND | TIME | STATE     | INFO                                                                  |
+----------+------+-----------------+--------+---------+------+-----------+-----------------------------------------------------------------------+
| 31311271 | root | 127.0.0.1:52438 | gftest | Query   |    0 | executing | select * from INFORMATION_SCHEMA.PROCESSLIST where id=connection_id() |
+----------+------+-----------------+--------+---------+------+-----------+-----------------------------------------------------------------------+
1 row in set (0.03 sec)

• CURRENT_USER(), CURRENT_USER，返回当前登陆用户在user表中对应的哪一个

root@database-one 23:21:  [gftest]> select current_user();
+----------------+
| current_user() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.00 sec)

root@database-one 23:21:  [gftest]> select user,host from mysql.user where user='root' order by host;
+------+------+
| user | host |
+------+------+
| root | %    |
+------+------+
1 row in set (0.03 sec)

• USER()，返回前登陆的用户名与它对应的host

root@database-one 23:29:  [gftest]> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@127.0.0.1 |
+----------------+
1 row in set (0.02 sec)

上面可以看到，user()返回的是当前用户登录的来源信息，它跟current_user()是不一样的。

• DATABASE()，返回当前数据库的名字

root@database-one 23:34:  [gftest]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| gftest     |
+------------+
1 row in set (0.03 sec)

• LAST_INSERT_ID()，返回AUTO_INCREMENT列为最后一个INSERT生成的值。

root@database-one 23:46:  [gftest]> CREATE TABLE t (
    -> id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> name VARCHAR(10) NOT NULL
    -> );
Query OK, 0 rows affected (0.32 sec)

root@database-one 23:46:  [gftest]> INSERT INTO t VALUES (NULL, 'Bob');
Query OK, 1 row affected (0.01 sec)

root@database-one 23:46:  [gftest]> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
|  1 | Bob  |
+----+------+
1 row in set (0.00 sec)

root@database-one 23:47:  [gftest]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.02 sec)

root@database-one 23:47:  [gftest]> INSERT INTO t VALUES
    -> (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@database-one 23:47:  [gftest]> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
|  1 | Bob  |
|  2 | Mary |
|  3 | Jane |
|  4 | Lisa |
+----+------+
4 rows in set (0.00 sec)

root@database-one 23:47:  [gftest]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.02 sec)

从上面可以看到，当INSERT语句插入多个行时，LAST_INSERT_ID() 返回本批插入第一行产生的值。

• VERSION()，返回MySQL服务器软件版本

root@database-one 23:52:  [gftest]> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.7.16-log |
+------------+
1 row in set (0.00 sec)