MySQL 5.5常用信息函數有哪些

這篇文章主要介紹了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)

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MySQL 5.5常用信息函數有哪些”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!