Avoid duplicate records in a particular MySQL column

Question

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

0.02
0.02
0.02

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

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

In that case he will return the duplicities for the auction with ID 1335.

The structure of the bid_account table looks like this:

id | int(255) - index - primary - AUTO INCREMENT
auction_id | int(11) - id of auction
bidding_price | float(20,2) - bid amount, eg. 0.02

I even created a DELETE that can erase the duplicates, but it would be unfair to some user to have the bid erased due to a system error. Follow:

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 = ?;

Is with "?" there because it receives the value by a parameter.

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

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 of 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, the next second it will identify and fix.

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

Rather than loading the server with busy work, avoid the duplicates when saving the bids. Add a unique key on auction_id and bidding_price. When you make the INSERT query, test for errors. If you get a duplicate key error, inform the bidder their bid failed, let them know the new minimum bid amount and let them re-bid. — Sloan Thrasher, Apr 15 '18 at 20:53
@SloanThrasher...Would not it give a problem setting "unique" to the `auction_id` column? Because each row entered in the DB will be with the same auction ID, then it will be repeated multiple times for each bid. When someone makes a bid, it registers in the `bid_account` table with the value and the auction ID, in the next bids it will have the same ID, changing only the value. — Wendler, Apr 15 '18 at 23:37
@SloanThrasher...Unfortunately a problem has arisen, it would even work but what happens is this: Each bid made it creates a new line that contains the value and the ID of the auction. If I put `UNIQUE` for the two columns, how will it insert the next line with the same auction ID? And there are cases where there will be different auctions of the same value. Then `UNIQUE` would not work for any of the columns. Or is there something that can be done to get around this situation? — Wendler, Apr 16 '18 at 01:27

score 0 · Answer 1 · answered Apr 15 '18 at 20:40

0

I think you can solve this on the SQL side by giving that column a "UNIQUE" option.

You can read more here - Mysql - UNIQUE

EDIT :

I understand that you have multiple bid values but for different auctions. You can specify the column to be UNIQUE based on another column - that means that it will allow multiple of the same values but not if the same "other-column" is identical.

Read more here - How do I specify unique constraint for multiple columns in MySQL?

answered Apr 15 '18 at 20:40

Niv Apo

806
1
8
16

1

Which means that this question is a duplicate. You should have flagged it as such instead of answering. – Shadow Apr 15 '18 at 20:57
I agree with @Shadow here. That edit is plagiarizing an existing Q&A. Btw, that link to W3schools; I'd of used the official manual itself. – Funk Forty Niner Apr 15 '18 at 22:29
Unfortunately a problem has arisen, it would even work but what happens is this: Each bid made it creates a new line that contains the value and the ID of the auction. If I put `UNIQUE` for the two columns, how will it insert the next line with the same auction ID? And there are cases where there will be different auctions of the same value. Then `UNIQUE` would not work for any of the columns. Or is there something that can be done to get around this situation? – Wendler Apr 16 '18 at 01:27
Sorry @Shadow, Didn't know how to do it before. Now I know. – Niv Apo Apr 16 '18 at 15:19

score 0 · Accepted Answer · answered Apr 15 '18 at 20:43

0

if you are using the database you can try using a unique for your bid_amount

CREATE TABLE bidding system (
bid_amount int NOT NULL UNIQUE,
LastName varchar(15) NOT NULL,
FirstName varchar(15)
);

answered Apr 15 '18 at 20:43

caseyprogramming

62
7

A unique key for two columns (at minimum) on ```auction_id``` and ```bidding_price``` would be required. Duplicate bid prices would occur and be OK as long as they are on different auctions. Also, in the code the OP would need to check for errors when inserting the bid and notify the bidder their bid was not saved and to bid again. – Sloan Thrasher Apr 15 '18 at 20:50
Unfortunately a problem has arisen, it would even work but what happens is this: Each bid made it creates a new line that contains the value and the ID of the auction. If I put `UNIQUE` for the two columns, how will it insert the next line with the same auction ID? And there are cases where there will be different auctions of the same value. Then `UNIQUE` would not work for any of the columns. Or is there something that can be done to get around this situation? – Wendler Apr 16 '18 at 01:27

Avoid duplicate records in a particular MySQL column

2 Answers2