Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how to understand the discrepancy between the number of rows in Navicat statistics and the actual number of rows in the table". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Background

Recently, in order to ensure the stability of the online database, I decided to backup and migrate the historical data of some large tables in a planned way. However, I found a strange phenomenon that the number of Navicat rows is not consistent with the count statistics of the table itself. 0.0

Navicat

Navicat, as a database management tool, is very popular in the industry, regardless of whether the Navicat running on your computer is genuine or pirated (you don't have to say I know it), it is undeniable that in the 17 years since I have been engaged in back-end development, I have tried many similar tools. Navicat completely crushes other database management tools in terms of function, especially in terms of details. It is easy to use (do not accept rebuttal, unless you say a tool that convinced me).

The whole story.

The overall idea of this large table migration backup is: first, use Navicat to sort all the tables in the library in descending order according to the number of rows, and then select Top10 for migration backup. But as always, I found that the number of statistical rows in its interface is not the same as that in my own count table. Do you want to subvert my approval of Navicat?

Select count (1) from big_table_name; Why?

This made me very surprised. At one point, I thought I was hallucinating. After confirming again and again that I was not wearing VR glasses, I embarked on a journey to find the answer. I began to think that Mysql, as a database, must have statistics of its own tables, while Navicat only serves as a visual interface to make the data visible to the naked eye.

Navicat: I can't memorize this pot.

To confirm my conjecture, I checked the official documents and other relevant materials, and sure enough, MySQL stored all the table information in the information_ schema.TABLES table.

Select * from information_schema.TABLES

After looking at this table, it is found that the TABLE_ROWS field of the statistical record in the table does not match the fact count.

And why is that?

Lost in thought and puzzled, I continued to flip through the document when suddenly I saw the official MySQL document's explanation of TABLE_ROWS:

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT (*) to obtain an accurate count.

After reading this passage, I had an epiphany. Do you also understand what's going on? What? Don't you get it? Well, it doesn't matter, you may need to understand the true meaning of the translation software through literal translation + understanding. It turns out that the counting rules of the TABLE_ROWS field vary with different storage engines. For example, the MyISAM engine stores the exact number of rows in TABLE_ROWS, while for other storage engines, such as InnoDB, this value is only an approximate value, with a difference of about 40% and 50% from the actual value. So, in this case, if we want to get an accurate count, we can only get it using SELECT COUNT (*).

Then how to correct it?

Although the doubt has been answered. However, friends who have obsessive-compulsive disorder like me will certainly ask, how to correct this value? It is true that the more you know, the more unknown you are. it is said on the Internet that you can pass.

Analyze table big_table_name

I was able to correct this data, but after I did it, I found that I could not correct the data, and the operation was not only time-consuming but also locked the table, and it was not recommended to use. Speaking of which, my obsessive-compulsive disorder unexpectedly cured itself.

This is the end of the content of "how to understand the discrepancy between the number of rows counted by Navicat and the actual number of rows in the table". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.