MySQL插入唯一值

I have a MySQL Table that has a column that will AUTO_INCREMENT, another to store user-submitted data, and another column that defaults to CURRENT_TIMESTAMP.

My INSERT query is:

$query = $db->prepare("INSERT IGNORE INTO `UserData` (`user_data`) VALUES(?)");
$query->bind_param('s', $commentdata);
$query->execute();

However it is still inserting the duplicate values (if a user clicks submit multiple times). What is the best way to prevent this?

MySQL will not create duplicate auto increment ids (unless you have a very badly configured cluster) so presumably the duplicates you refer to are in a different attribute - you've only told us about user_data.

If you don't want duplicates in there then add a unique index on the column. You should also add error handling to deal with failures when the situation arises and remove the 'IGNORE'.

However you also need to think about your controlling logic (this hints that you probably have csrf vulnerabilities) and your user interface (why are you allowing users to submit the same form twice?)

The best way to prevent duplicate values in a MySQL table is for the table definition to use the UNIQUE or PRIMARY constraint. See the documentation for table creation syntax.

You may also want the value to be a KEY if you plan on performing lookups using that value.

I ended up using the following query:

INSERT INTO `UserData` (`post_num`, `user_data`)
SELECT ?, ? FROM `UserData`
WHERE NOT EXISTS (
    SELECT * FROM `UserData` WHERE `post_num`= ? AND `user_data`=?
) LIMIT 1

Then I do $query->bind_param('isis', $post_number, $comment, $post_number, $comment); to bind the values.

This will check to make sure there are no duplicate comments on a post.

Note that my actual example inserts more information such as the user information and I check to make sure there are no duplicate comments from that certain user on a specific post.