Home > Database > Mysql Tutorial > body text

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

黄舟
Release: 2017-05-28 09:58:10
Original
2086 people have browsed it

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)
)
Copy after login

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)
)
Copy after login

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)
Copy after login

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)
Copy after login

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

ALTER TABLE Persons DROP INDEX uc_PersonID
Copy after login

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', ...
Copy after login

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', ...)
Copy after login

. If the record exists, Record, then execute

UPDATE field1='value1', field2='value2', field3='value3', ...
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template