从API中防止MySQL中的多个INSERT
I have a MySQL MyISAM Table "transaction".
Schema is similar to:
+-----+--------+-------------------+---------------------+
| id | amount | duplication_check | date |
+-----+--------+-------------------+---------------------+
| 123 | 80 | 123456789 | 2017-06-05 11:12:23 |
| 124 | 80 | 123456788 | 2017-06-05 11:12:23 |
+-----+--------+-------------------+---------------------+
duplication_check can be empty/null but if it is passed from a script (for example from an API call) my PHP code performs a simple check:
SELECT id FROM transaction WHERE duplication_check == 'passed_from_external'.
If this returns a result, of course the transaction failed.
Now, I have an Android app developed myself that uses - when submit a transaction via API - this duplication_check (I generate it with a time() function).
When the smartphone lose the connection, I can read in the API log (another table to log the API call) 2 or 3 times in different seconds that the API called my server 2-3 times, with same duplication code.
Unfortunately, the INSERT call is done three times. But, from a PHP test, I cannot replicate the bug (following call with same duplication_check are failed).
API log example (note the 3 different dates):
+--------+--------+-------------------+---------------------+
| id_api | amount | duplication_check | date |
+--------+--------+-------------------+---------------------+
| 123 | 80 | AAAAAAA | 2017-06-05 11:12:23 |
| 124 | 80 | AAAAAAA | 2017-06-05 11:12:25 |
| 125 | 80 | AAAAAAA | 2017-06-05 11:12:27 |
+--------+--------+-------------------+---------------------+
Transaction result (note the 3 same dates, next to the API Log):
+--------+--------+-------------------+---------------------+
| id | amount | duplication_check | date |
+--------+--------+-------------------+---------------------+
| 123111 | 80 | AAAAAAA | 2017-06-05 11:12:28 |
| 124112 | 80 | AAAAAAA | 2017-06-05 11:12:28 |
| 125113 | 80 | AAAAAAA | 2017-06-05 11:12:28 |
+--------+--------+-------------------+---------------------+
Probably, I need to edit the Android app to block multiple call, but, is this possibly a multiple INSERT into a table and is it possible to prevent them?
Why don´t you make a UNIQUE index with your duplication_check row? This will prevent to write it more than once. You´ll need a UNIQUE and NULL column, so when you don´t have any value the null is admited (and don´t generates a duplicated value). And yes, you can ALTER (or edit) the table and ADD (or modify) your column with a random selected unique code.