A detailed introduction to how MySQL uses UNIQUE to achieve non-duplicate data insertion

When a unique column inserts a record containing duplicate values ​​on a UNIQUE key, we can control MySQLhow to handle this situation: use the IGNORE keyword or ON DUPLICATE KEY UP DATE clause skips INSERT, interrupt operations, or updates old records to new values.

SQL UNIQUE Constraints

UNIQUE constraints uniquely identify each record in a database table.
Both UNIQUE and PRIMARY KEY constraints provide uniqueness guarantees for columns or column sets.
PRIMARY KEY has automatically defined UNIQUE constraints.

Please note that each table can have multiple UNIQUE constraints, but there can only be 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 the UNIQUE constraint, and define UNIQUE constraints for multiple columns, Please 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, if you need to create a UNIQUE constraint on the "Id_P" column, please use the following SQL:

ALTER TABLE Persons ADD UNIQUE (Id_P)

To name the UNIQUE constraint, And define UNIQUE constraints for multiple columns, please use the following SQL syntax:

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

If you need to revoke UNIQUE constraints, please use the following SQL:

ALTER TABLE Persons DROP INDEX uc_PersonID

This way each When inserting duplicate records for the first time, MySQL will prompt Duplicate entry value1-value2 for key uni_que. Of course, you can add ignore when inserting to ignore it.
Now that there are no duplicate records, we have to start If the record does not exist, insert it, and if it does, update the operation

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 the value. If there is no such record, execute the section

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

. If the record exists, Record, then execute

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

The above is the detailed content of A detailed introduction to how MySQL uses UNIQUE to achieve non-duplicate data insertion. For more information, please follow other related articles on the PHP Chinese website!