Avoid duplicate records in a given MySQL column [duplicate]

Question

Avoid duplicate records in a given MySQL column [duplicate]

Navigation

#1 by (3 votes)
#2 by (-1 votes)
#3 by (-3 votes)

-1

I have an auction system where multiple users can bid, but I'm having a problem when bids occur at the same time because they are inserted into the database with the same value in column bidding_price , eg:

0.02
0.02 0.02

I've mounted a select that returns all bids with equal values (duplicity) for a particular auction:

SELECT bidding_price, count(*)
    FROM bid_account
    WHERE auction_id = 1335
    GROUP BY bidding_price
    Having Count(*) > 1

In this case it will return the duplicities for the auction with ID 1335.

The structure of the bid_account table looks like this:

id | int(255) - índice - primária - AUTO INCREMENT
auction_id | int(11) - id do leilão
bidding_price | float(20,2) - valor do lance, ex. 0.02

I got to create a DELETE that can erase the duplicates, but it would be unfair to some user, to have the bid erased by a system error. Following:

DELETE ba
    FROM bid_account ba JOIN
         (SELECT ba2.auction_id, ba2.bidding_price, MAX(ba2.id) as max_id
          FROM bid_account ba2
          WHERE ba2.auction_id = ?
          GROUP BY ba2.auction_id, ba2.bidding_price
         ) ba2
         ON ba2.auction_id = ba.auction_id AND
            ba2.bidding_price = ba.bidding_price AND
            ba2.max_id > ba.id
WHERE ba.auction_id = ?;

This is with ? ali because it receives the value by a parameter.

Does anyone have any idea how I can solve this problem, avoiding to insert the equal value in the bidding_price table, but for a given auction identified by the column auction_id ?

In the table there are several equal values in bidding_price , but what can not occur are equal values for the same auction, that is, for the same value in auction_id .

I thought about creating an update, which checks beforehand if there are duplicates, if it exists, it updates the values by inserting 1 cent more in each duplicity. But I think it would go away from the problem when there were 3 bids at the same time.

This SQL command will run on node, and will run in a cron every second, that is, every time it is performing this check. If there is a duplicate bid now, in the next second it will identify and fix.

The bidding system works in tandem with a 15-second countdown timer, and when someone makes a bid the timer returns to its initial count of 15 seconds.

php mysql sql

asked by anonymous 15.04.2018 / 21:07

3 answers

-1

You can check in SQL if there are any bids with the same value using CASE :

CASE  
    WHEN ? IN (SELECT bidding_price FROM bid_account WHERE auction_id = ?) 
    THEN INSERT ...

15.04.2018 / 21:24

-3

What matters is who commits first. And this never happens at the same time, difference of milliseconds. What happens is you need to increase the mysql insert storage cache to create a queue. After that, each new request should reload the client page or at least throw the updated data with the timestamp into a json file and modify the div (or any other tag) dynamically and only the part of the price. You can do this with an api rest or ajax.

15.04.2018 / 21:33

How to put the style of my template in extra module? Register user with Laravel

score 3 · Accepted Answer

Just use UNIQUE , such that:

ALTER TABLE tabela ADD UNIQUE INDEX('bidding_price', 'auction_id');

In this way the pair of bidding_price and auction_id are unique, so if it is {0, 0} there can not be another equal, but there may still be {0, 1} or {1, 0} . Then for each% w / of% there will only be a single% w / w of%.

You can test this in SQLFiddle .