MySQL中的信息函数

2020年2月26日
| 没有评论
摘要：MySQL中的常用信息函数介绍及实测。

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

Name Description
BENCHMARK() Repeatedly execute an expression
CHARSET() Return the character set of the argument
COERCIBILITY() Return the collation coercibility value of the string argument
COLLATION() Return the collation of the string argument
CONNECTION_ID() Return the connection ID (thread ID) for the connection
CURRENT_USER(), CURRENT_USER The authenticated user name and host name
DATABASE() Return the default (current) database name
FOUND_ROWS() For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause
LAST_INSERT_ID() Value of the AUTOINCREMENT column for the last INSERT
ROW_COUNT() The number of rows updated
SCHEMA() Synonym for DATABASE()
SESSION_USER() Synonym for USER()
SYSTEM_USER() Synonym for USER()
USER() The user name and host name provided by the client
VERSION() Return a string that indicates the MySQL server version

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

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

 

© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。

MySQL
| Tags: , , , , , ,