Recently, a very strange MySQL problem, using different SELECT statements to query all datasets actually get different number of records. SELECT * Gets 4 records, and the Select field gets 3 records.
Specific questions can be seen in the following query results:
[SQL]
Mysql> select * from Table_myisam;
+----------+-------+-----------+------+
| datetime | UID | Content | Type |
+----------+-------+-----------+------+
|1 | Uid_1 | Content_1 |1 |
| 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 rows were recorded through the Select UID, and a record of uid= ' Uid_3 ' was lost. Could not think of the solution, and later in the colleague's reminder to use the check table, only to find the problem.
[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 |
+--------------------+-------+----------+-------------------------------------------------------+
The query data is inconsistent because the Table_myisam index file is corrupted and the corresponding index file TABLE_MYISAM. Myi and Data file Table_myisam. MyD inconsistent. SELECT * does not need to traverse each index entry, only to get the first record, accessed according to the list order, so the current index corruption does not affect the use of select *. The select UID needs to traverse all the index entries, thus obtaining only the corrupted state and three index records.
The solution is to use the repair table to fix the table index.
[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)
After repair, use check table to see that the table state becomes normal and 4 records can be obtained using the SELECT * and the Select UID.
[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)