13518219792
这篇文章主要介绍了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常用信息函数有哪些”这篇文章对大家有帮助，同时也希望大家多多支持创新互联，关注创新互联行业资讯频道，更多相关知识等着你来学习!
