Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to prevent data duplication in MySQL. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

In many cases, in daily projects, some data are not allowed to be repeated, such as the login name in user information. Once there is the same login name, we must not know which user performs the login operation, which leads to system exception.

Often in the case of preventing data duplication, we use a unique index to solve the problem, as follows

CREATE TABLE `login` (

`id` bigint unsigned NOT NULL AUTO_INCREMENT

`name` varchar (255) DEFAULT NULL

`password` varchar (255) DEFAULT NULL

PRIMARY KEY (`id`)

UNIQUE KEY `idx_ name` (`name`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8

When we execute the same name data, the following error is reported

Do you know anything else besides this method?

Let's introduce several other methods.

Insert ignore into

When inserting data, ignore the insert if the data exists

INSERT ignore INTO login (`name`, `password`) VALUES ("ganhuojun", "password")

Replace into

When inserting data, delete and insert if the data exists

REPLACE INTO login (`name`, `password`) VALUES ("ganhuojun", "password")

After execution, it is found that 2 lines are effective, of which one is deleted and one is added.

Insert if not exists

The syntax of sql is insert into. Select... Where not exist., this statement first determines whether the data exists in the mysql database, and if it does not exist, it is inserted normally, and if so, it is ignored.

INSERT INTO login (`name`, `password`) SELECT

'ganhuojun'

'password'

FROM

Login

WHERE

NOT EXISTS (

SELECT

`name`

FROM

Login

WHERE

`name` = 'ganhuojun'

)

On duplicate key update

When inserting data, an update operation is performed if the data exists

INSERT INTO login (`name`, `password`)

VALUES

("ganhuojun", "password")

ON DUPLICATE KEY UPDATE PASSWORD = 'passwd'

After reading the above, have you mastered how to prevent data duplication in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.