mysql 1243_MySQL 5.5常用信息函数

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)