MySQL 5.5常用信息函数

墨蓝 2024-04-17 23:58 57阅读 0赞

CONNECTION\_ID()  
 显示连接ID（线程ID）  
  
 mysql> SELECT CONNECTION\_ID();  
 \+-----------------+  
 | CONNECTION\_ID() |  
 \+-----------------+  
 |              50 |  
 \+-----------------+  
 1 row in set (0.00 sec)  
  
 CURRENT\_USER()  
 显当前客户端连接的用户名和主机名  
  
 mysql> SELECT CURRENT\_USER();  
 \+------------------+  
 | CURRENT\_USER()   |  
 \+------------------+  
 | system@localhost |  
 \+------------------+  
 1 row in set (0.00 sec)  
  
 DATABASE()  
 显示当前连接的数据库名称  
  
 mysql> SELECT DATABASE();  
 \+--------------------+  
 | DATABASE()         |  
 \+--------------------+  
 | information\_schema |  
 \+--------------------+  
 1 row in set (0.00 sec)  
  
 FOUND\_ROWS()  
 显示SELECT语句的返回行数，忽略LIMIT语句，在存储过程里面很有用。  
 SQL\_CALC\_FOUND\_ROWS告诉MySQL计算结果集中的行数，忽略LIMIT语句，行数可以通过SELECT FOUND\_ROWS()来查询出  
  
 mysql> select \* from dept;  
 \+--------+------------+----------+  
 | DEPTNO | DNAME      | LOC      |  
 \+--------+------------+----------+  
 |     10 | ACCOUNTING | NEW YORK |  
 |     20 | RESEARCH   | DALLAS   |  
 |     30 | SALES      | CHICAGO  |  
 |     40 | OPERATIONS | BOSTON   |  
 \+--------+------------+----------+  
 4 rows in set (0.00 sec)  
  
 mysql> SELECT SQL\_CALC\_FOUND\_ROWS \* FROM dept ORDER BY 1 limit 2;  
 \+--------+------------+----------+  
 | DEPTNO | DNAME      | LOC      |  
 \+--------+------------+----------+  
 |     10 | ACCOUNTING | NEW YORK |  
 |     20 | RESEARCH   | DALLAS   |  
 \+--------+------------+----------+  
 2 rows in set (0.00 sec)  
  
 mysql> SELECT FOUND\_ROWS();  
 \+--------------+  
 | FOUND\_ROWS() |  
 \+--------------+  
 |            4 |  
 \+--------------+  
 1 row in set (0.00 sec)  
  
 ROW\_COUNT()  
 在MySQL 5.5.5版本之前，ROW\_COUNT()返回上一条UPDATE, DELETE,或INSERT语句的行数，对于其他语句，这个返回值没有意义。  
 在MySQL 5.5.5版本，ROW\_COUNT()返回下列值：  
 DDL语句，例如CREATE TABLE 或 DROP TABLE：0。  
 DML语句，例如UPDATE, INSERT, 或 DELETE，ALTER TABLE 、 LOAD DATA INFILE和SELECT \* FROM table\_name INTO OUTFILE 'file\_name'：实际影响的行。  
 SELECT语句:-1  
 SIGNAL 语句: 0  
  
 mysql> select \* from t20;  
 \+------+  
 | id   |  
 \+------+  
 |  200 |  
 |  100 |  
 \+------+  
 2 rows in set (0.28 sec)  
  
 mysql> insert into t20 select \* from t20;  
 Query OK, 2 rows affected (0.20 sec)  
 Records: 2  Duplicates: 0  Warnings: 0  
  
 mysql> SELECT ROW\_COUNT();  
 \+-------------+  
 | ROW\_COUNT() |  
 \+-------------+  
 |           2 |  
 \+-------------+  
 1 row in set (0.00 sec)  
  
 SCHEMA()  
 显示连接的数据库名称  
 mysql> SELECT SCHEMA();  
 \+----------+  
 | SCHEMA() |  
 \+----------+  
 | fire     |  
 \+----------+  
 1 row in set (0.00 sec)  
  
 USER()、SESSION\_USER()、SYSTEM\_USER()  
 显当前客户端连接的用户名和主机名  
  
 mysql> SELECT SESSION\_USER();  
 \+------------------+  
 | SESSION\_USER()   |  
 \+------------------+  
 | system@localhost |  
 \+------------------+  
 1 row in set (0.00 sec)  
  
 mysql> SELECT SYSTEM\_USER();  
 \+------------------+  
 | SYSTEM\_USER()    |  
 \+------------------+  
 | system@localhost |  
 \+------------------+  
 1 row in set (0.00 sec)  
  
 mysql> SELECT USER();  
 \+------------------+  
 | USER()           |  
 \+------------------+  
 | system@localhost |  
 \+------------------+  
 1 row in set (0.00 sec)  
  
 VERSION()  
 显示数据库版本  
  
 mysql> SELECT VERSION();  
 \+------------+  
 | VERSION()  |  
 \+------------+  
 | 5.5.48-log |  
 \+------------+  
 1 row in set (0.00 sec)  
  
LAST\_INSERT\_ID()  
自增字段执行上一次 的 INSERT语句的值  
  
 mysql> create table test(id int auto\_increment not null primary key, name varchar(15));  
Query OK, 0 rows affected (0.08 sec)  
  
mysql> desc test;  
\+-------+-------------+------+-----+---------+----------------+  
| Field | Type        | Null | Key | Default | Extra          |  
\+-------+-------------+------+-----+---------+----------------+  
| id    | int(11)     | NO   | PRI | NULL    | auto\_increment |  
| name  | varchar(15) | YES  |     | NULL    |                |  
\+-------+-------------+------+-----+---------+----------------+  
2 rows in set (0.00 sec)  
  
mysql> insert into test(name) values('Neo');  
Query OK, 1 row affected (0.00 sec)  
  
mysql> insert into test(name) values('Lily');  
Query OK, 1 row affected (0.00 sec)  
  
mysql> select \* from test;  
\+----+------+  
| id | name |  
\+----+------+  
|  1 | Neo  |  
|  2 | Lily |  
\+----+------+  
2 rows in set (0.00 sec)  
  
mysql> select last\_insert\_id();  
\+------------------+  
| last\_insert\_id() |  
\+------------------+  
|                2 |  
\+------------------+  
1 row in set (0.00 sec)  
  
mysql> insert into test(name) values('Trinity');  
Query OK, 1 row affected (0.00 sec)  
  
mysql> select last\_insert\_id();  
\+------------------+  
| last\_insert\_id() |  
\+------------------+  
|                3 |  
\+------------------+  
1 row in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2108479/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2108479/