使用MySQL检查查询数据是否全部输入

Question

here's small part of table

+-------------+-------------+----------+----------+------------------------+
| sourceindex | targetindex | source   | target   | new_count              |
+-------------+-------------+----------+----------+------------------------+
|           0 |           0 | this     | this     |  4.514337716384391e-18 |
|           0 |           1 | this     | is       |  5.501850344983498e-17 |
|           0 |           2 | this     | a        |  5.501850344983498e-17 |
|           0 |           3 | this     | book     |  1.805735523541796e-17 |
|           0 |           4 | this     | ,        |  5.501850344983498e-17 |
|           0 |           5 | this     | that     |  1.805735523541796e-17 |
|           0 |           6 | this     | is       |  5.501850344983498e-17 |
|           0 |           7 | this     | a        |  5.501850344983498e-17 |
|           0 |           8 | this     | pen      |  1.805735523541796e-17 |
|           0 |           9 | this     | .        |  5.501850344983498e-17 |
|           0 |          10 | this     | 這      |  1.805735523541796e-17 |
|           0 |          11 | this     | 是      |  5.501850344983498e-17 |
|           0 |          12 | this     | 一      |  5.501850344983498e-17 |
|           0 |          13 | this     | 本      |  1.805735523541796e-17 |
|           0 |          14 | this     | 書      |  1.805735523541796e-17 |
|           0 |          15 | this     | ,        |  5.501850344983498e-17 |
|           0 |          16 | this     | é‚£      |  1.805735523541796e-17 |
|           0 |          17 | this     | 是      |  5.501850344983498e-17 |
|           0 |          18 | this     | 一      |  5.501850344983498e-17 |
|           0 |          19 | this     | æž      |  1.805735523541796e-17 |
|           0 |          20 | this     | ç­†      |  1.805735523541796e-17 |
|           0 |          21 | this     | .        |  5.501850344983498e-17 |
|           1 |           0 | is       | this     |  5.501850344983498e-17 |
|           1 |           1 | is       | is       |  3.780758595799811e-17 |
|           1 |           2 | is       | a        | 1.5123035298142912e-16 |
|           1 |           3 | is       | book     |  5.501850344983498e-17 |
|           1 |           4 | is       | ,        | 1.5123035298142912e-16 |
|           1 |           5 | is       | that     |  5.501850344983498e-17 |
|           1 |           6 | is       | is       |  3.780758595799811e-17 |
|           1 |           7 | is       | a        | 1.5123035298142912e-16 |
|           1 |           8 | is       | pen      |  5.501850344983498e-17 |
|           1 |           9 | is       | .        | 1.5123035298142914e-16 |
|           1 |          10 | is       | 這      |  5.501850344983498e-17 |
|           1 |          11 | is       | 是      | 1.5123035298142912e-16 |
|           1 |          12 | is       | 一      | 1.5123035298142912e-16 |
|           1 |          13 | is       | 本      |  5.501850344983498e-17 |
|           1 |          14 | is       | 書      |  5.501850344983498e-17 |
|           1 |          15 | is       | ,        | 1.5123035298142912e-16 |
|           1 |          16 | is       | é‚£      |  5.501850344983498e-17 |
|           1 |          17 | is       | 是      | 1.5123035298142912e-16 |
|           1 |          18 | is       | 一      | 1.5123035298142912e-16 |
|           1 |          19 | is       | æž      |  5.501850344983498e-17 |
|           1 |          20 | is       | ç­†      | 5.5018503449834965e-17 |
|           1 |          21 | is       | .        | 1.5123035298142914e-16 |

---

i run the program to insert large data and i don't know whether data correctly input or not ,the rule is sourceindex will be from 0 to 21 and each sourceindex follow the targetindex from 0 to 21 , i want to check which row isn't insert by query,how can i do

Answer 1

To do that in a single query may not be the optimal way to do it (why not use several queries?), but this should work:

$combinations = array();
for ($s = 0; $s < 22; $s++) {
    for ($t = 0; $t < 22; $t++) {
        $combinations[] = $s . '-' . $t;
    }
}
$sql = " SELECT CONCAT(sourceindex, '-', targetindex)
         FROM table ";
// run your sql query depending on your db layer (e.g. PDO)
// get results as array $results
print_r(array_diff($combinations, $results);

The combinations that get printed are not in the database.

Since there are 22 rows for each sourceindex as well as 22 rows for each targetindex, you could first check, if the number of rows for each index is correct:

SELECT sourceindex, COUNT(*) FROM table GROUP BY sourceindex;
SELECT targetindex, COUNT(*) FROM table GROUP BY targetindex;

If any result row shows a number less than 22 for the count column, there is something missing there. You can than examine further or just combine the possibilities.