MySQL uses UNIQUE to implement non-duplicate data insertion

SQL UNIQUE constraint

The UNIQUE constraint uniquely identifies each record in a database table.
Both UNIQUE and PRIMARY KEY constraints provide uniqueness guarantees for a column or set of columns.
A PRIMARY KEY has a UNIQUE constraint automatically defined.

Note that you can have multiple UNIQUE constraints per table, but you can have only one PRIMARY KEY constraint per table.

The following SQL creates a UNIQUE constraint on the "Id_P" column when the "Persons" table is created:

CREATE TABLE Persons
(
  Id_P int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),
  UNIQUE (Id_P)
)

If you need to name a UNIQUE constraint and define a UNIQUE constraint for multiple columns, use the following SQL syntax:

CREATE TABLE Persons
(
  Id_P int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),
  CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)

When the table has been created, to create a UNIQUE constraint on the "Id_P" column, use the following SQL:

ALTER TABLE Persons ADD UNIQUE (Id_P)

To name a UNIQUE constraint and define a UNIQUE constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)


To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE Persons DROP INDEX uc_PersonID

In this way, every time you insert a duplicate record, MySQL will prompt Duplicate entry value1-value2 for key uni_que. Of course, you can add ignore to ignore when inserting. Now that there are no duplicate records, we want to start inserting if the record does not exist and updating if it exists.

INSERT INTO tablename (field1, field2, field3, ...) VALUES ('value1', 'value2','value3', ...) ON DUPLICATE KEY UPDATE field1='value1', field2='value2', field3='value3', ...

This statement means to insert a value. If there is no record, execute

INSERT INTO tablename (field1, field2, field3, ...) VALUES ('value1', 'value2','value3', ...)

In this section, if the record exists, execute

UPDATE field1='value1', field2='value2', field3='value3', ...