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

In this issue, the editor will bring you about how to prevent repeated data insertion in MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

New form

CREATE TABLE `person` (`id` int NOT NULL COMMENT 'primary key', `name` varchar (64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT 'name', `age`person` age', `address`varchar (512) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT 'address', PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic

Add three pieces of data as follows:

We can avoid it according to the way it is inserted:

1. Insert ignore

Insert ignore automatically ignores data that already exists in the database (based on the primary key or unique index), inserts data if there is no data, and skips inserting this data if there is any data.

Insert SQL as follows: insert ignore into person (id,name,age,address) values (3 'who', 23 'Gansu'), (4'my God', 25 'Zhejiang')

If you look at the database again, you will find that only data with an id of 4 is inserted, which is ignored because there is data with an id of 3 in the database.

2. Replace into

Replace into first tries to insert data into the table, 1. If you find that there is already this row of data in the table (based on the primary key or unique index), delete the row first, and then insert the new data. two。 Otherwise, insert the new data directly.

Insert SQL as follows: replace into person (id,name,age,address) values (3 'who', 23 'Gansu'), (4'my God', 25 'Zhejiang')

First of all, we restore the data in the table, and then after the insert operation, we find that the data with id 3 has changed and the data with id 4 has been added.

3. Insert on duplicate key update

Insert on duplicate key update if the on duplicate key update + field update is specified at the end of the insert into statement, the information will be updated according to the description of the subsequent field update when duplicate data occurs (based on the primary key or unique index).

Insert SQL as follows: insert into person (id,name,age,address) values (3 on duplicate key update name=' 'who', 23) on duplicate key update name=' 'who', age=23, address=' Gansu Province'

First of all, we restore the data in the table, and then during the insert operation, we find that the data with an id of 3 has changed and updated.

The above is how to prevent repeated insertion of data in the MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.