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

This article mainly introduces the relevant knowledge of "how to query the data amount of the table by mysql". The editor shows you the operation process through the actual case. The operation method is simple, fast and practical. I hope that this article "how to query the data amount of the table by mysql" can help you solve the problem.

Mysql query table data quantity method: 1, use "SELECT COUNT (*) FROM table name" statement query, COUNT can return the number of retrieved data; 2, use "SELECT SQL_CALC_FOUND_ROWS 1 FROM table name limit 1" statement query.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How does mysql query the amount of data in a table

1. The most commonly used

SELECT COUNT (*) FROM table name

The check is accurate, but if the amount of data is large (more than 1 million), it is relatively slow.

two。 Found one on the Internet, it is said to be faster than count (*), self-test did not verify.

SELECT SQL_CALC_FOUND_ROWS 1 FROM table name limit 1th select found_rows () AS rowcount

Both of the above have drawbacks: you can only query one table at a time.

3. Query the amount of data of all tables in the current library

SELECT TABLE_NAME,TABLE_ROWS FROM information_ schema.`TABLES`Were TABLE_SCHEMA = (SELECT database ()) ORDER BY TABLE_ROWS DESC

TABLE_ROWS is the amount of data in the table, but you will find that the value is not the same as that obtained by select count (*)!

Reason:

By default, when mysql adds or deletes a table, it does not automatically update the table_rows field of the tables table in the information_schema library. After searching the Internet, it is found that only 10% of the rows will be automatically collected (to be verified)

This is the end of the content about "how to query the amount of data in a table by mysql". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.