Shulou(Shulou.com)05/31 Report--

This article mainly introduces what MySQL 5.5 common information functions have, which have a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

CONNECTION_ID ()

Show connection ID (thread ID)

Mysql > SELECT CONNECTION_ID ()

+-+

| | CONNECTION_ID () |

+-+

| | 50 |

+-+

1 row in set (0.00 sec)

CURRENT_USER ()

Show the user name and hostname of the current client connection

Mysql > SELECT CURRENT_USER ()

+-+

| | CURRENT_USER () |

+-+

| | system@localhost |

+-+

1 row in set (0.00 sec)

DATABASE ()

Displays the database name of the current connection

Mysql > SELECT DATABASE ()

+-+

| | DATABASE () |

+-+

| | information_schema |

+-+

1 row in set (0.00 sec)

FOUND_ROWS ()

Display the number of rows returned by the SELECT statement, ignoring the LIMIT statement, which is useful in stored procedures.

SQL_CALC_FOUND_ROWS tells MySQL to calculate the number of rows in the result set, ignoring the LIMIT statement, and the number of rows can be queried by 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 ()

Prior to MySQL 5.5.5, ROW_COUNT () returned the number of rows of the previous UPDATE, DELETE, or INSERT statement, which was meaningless for other statements.

In MySQL version 5.5.5, ROW_COUNT () returns the following values:

DDL statements, such as CREATE TABLE or DROP TABLE:0.

Rows actually affected by DML statements, such as UPDATE, INSERT, or DELETE,ALTER TABLE, LOAD DATA INFILE, and SELECT * FROM table_name INTO OUTFILE 'file_name':.

SELECT statement:-1

SIGNAL statement: 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 ()

Displays the database name of the connection

Mysql > SELECT SCHEMA ()

+-+

| | SCHEMA () |

+-+

| | fire |

+-+

1 row in set (0.00 sec)

USER (), SESSION_USER (), SYSTEM_USER ()

Show the user name and hostname of the current client connection

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

Show database version

Mysql > SELECT VERSION ()

+-+

| | VERSION () |

+-+

| | 5.5.48-log |

+-+

1 row in set (0.00 sec)

LAST_INSERT_ID ()

Increment the value of the last INSERT statement executed by the field

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)

Thank you for reading this article carefully. I hope the article "what are the common information functions of MySQL 5.5" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.