MySQL查詢全體數據集成果紛歧致成績處理計劃。本站提示廣大學習愛好者:(MySQL查詢全體數據集成果紛歧致成績處理計劃)文章只能為提供參考,不一定能成為您想要的結果。以下是MySQL查詢全體數據集成果紛歧致成績處理計劃正文
比來湧現一個很奇異的MySQL成績,應用分歧select語句查詢全體數據集竟然獲得分歧的記載數。select * 獲得4筆記錄,select 字段獲得的是3筆記錄。
詳細成績可以看上面的查詢成果:
[sql]
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)
經由過程select uid只獲得3行記載,喪失了個中uid='uid_3'的記載。原來百思不得其解,後來在同事的提示下應用了check table,才找到成績的地點。
[sql]
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 |
+--------------------+-------+----------+-------------------------------------------------------+
查詢數據紛歧致的緣由是table_myisam的索引文件破壞了,對應的索引文件table_myisam.MYI與數據文件table_myisam.MYD紛歧致。select *其實不須要遍歷每一個索引項,只須要獲得第一筆記錄,依據鏈表次序拜訪,是以以後的索引破壞並沒有影響到select *的應用。而select uid須要遍歷一切索引項,因此只獲得到破壞狀況,三條索引記載。
處理計劃是應用repair table停止表索引的修復。
[sql]
mysql> repair table table_myisam;
+--------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+--------+----------+----------+
| qitai.table_myisam | repair | status | OK |
+--------------------+--------+----------+----------+
1 row in set (0.00 sec)
修復後應用check table可以看到表狀況釀成正常,應用select *與select uid都能獲得到4筆記錄。
[sql]
mysql> check table table_myisam;
+--------------------+-------+----------+----------+
| Table | Op| Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| qitai.table_myisam | check | status | OK |
+--------------------+-------+----------+----------+
1 row in set (0.00 sec)