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

This article mainly introduces the MySQL query data inconsistent how to do, the article is very detailed, has a certain reference value, interested friends must read it!

Recently, there is a very strange MySQL problem, which uses different select statements to query all data sets and get different records. Select * gets four records, and the select field gets three records.

For specific questions, please see the query results below:

Mysql > select * from table_myisam

+-+

| | datetime | uid | content | type | |

+-+

| | 1 | uid_1 | content_1 | 1 |

| | 2 | uid_2 | content_2 | 1 |

| | 4 | uid_4 | content_4 | 1 |

| | 3 | uid_3 | content_3 | 1 |

+-+

4 rows in set (0.00 sec)

Mysql > select uid from table_myisam

+-+

| | uid |

+-+

| | uid_1 |

| | uid_2 |

| | uid_4 |

+-+

3 rows in set (0.00 sec)

Only 3 lines of records were obtained through select uid, and the record of uid='uid_3' was lost. I was puzzled at first, but then I used check table under the reminder of my colleagues to find out where the problem lies.

Mysql > check table table_myisam

+-- +

| | Table | Op | Msg_type | Msg_text | |

+-- +

| | qitai.table_myisam | check | warning | 1 client is using or hasn't closed the table properly |

| | qitai.table_myisam | check | warning | Size of indexfile is: 2049 Should be: 2048 |

| | qitai.table_myisam | check | error | Found 3 keys of 4 |

| | qitai.table_myisam | check | error | Corrupt | |

+-- +

The reason for the inconsistency of query data is that the index file of table_myisam is corrupted, and the corresponding index file table_myisam.MYI is inconsistent with the data file table_myisam.MYD. Select * does not need to traverse every index item, but only needs to obtain the first record, which is accessed according to the linked list order, so the current index corruption does not affect the use of select *. On the other hand, select uid needs to traverse all the index entries, so it only gets the damaged state, three index records.

The solution is to use repair table to repair the table index.

Mysql > repair table table_myisam

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | qitai.table_myisam | repair | status | OK | |

+-+

1 row in set (0.00 sec)

After repair, you can use check table to see that the table state becomes normal, and you can get 4 records using select * and select uid.

Mysql > check table table_myisam

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | qitai.table_myisam | check | status | OK | |

+-+

1 row in set (0.00 sec

The above is all the contents of the article "what to do if the query data is inconsistent in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.