[游戏开发] How to handling duplicates in MySQL

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 游戏开发 -> How to handling duplicates in MySQL -> 正文阅读

[游戏开发]How to handling duplicates in MySQL

Operations involved in handling duplicate rows include the following:

Preventing duplicates from being created in the frist place.
Counting the number of duplicates to determine whether they are present and to what extent.
Identifying duplicated values(or the rows containing them) so you can see where they occur.
Eliminating duplicates to ensure that each row is unique.

Several tools are at your disposal for dealing with duplicate rows.choose them according to the objective that you want to achieve:

When you create a table, include a primary key or unique index to prevent duplicates from being added to the table.MySQL uses the index as a constraint to enforce the requirement that each row in the table contains a unique key in the indexed column or columns.
In conjunction with a unique index,the INSERT IGNORE and REPLACE statements enable you to handle insertion of duplicate rows gracefully without generating errors.
To determine whether a table contains duplicates ,use group by to categorize rows into groups,and count() to see how many rows are in each group.
SELECT DISTINCT removes duplicate row from a result set.

1.How to preventing duplicates from occurring in a table?

Problem

prevent? a table from ever containing duplicates.

Solution:

Use a PRIMARY KEY or a UNIQUE index

permits duplicate rows:

mysql> CREATE TABLE person
? ? -> (
? ? -> ? last_name char(20),
? ? -> ? first_name char(20),
? ? -> ? address ?char(40)
? ? -> );
Query OK, 0 rows affected (0.01 sec)

prevent multiple rows, add a primary key to its fefinition. and then a primary key prohibits NULL values:

mysql> CREATE TABLE person
? ? -> (
? ? -> ? last_name char(20) not null,
? ? -> ? first_name char(20) not null,
? ? -> ? address char(40),
? ? -> ? PRIMARY KEY (last_name,first_name)
? ? -> );
Query OK, 0 rows affected (0.01 sec)

mysql>?

Another way to enforce uniqueness is to add a UNIQUE index rather than a PRIMARY KEY to a table.The two types of indexes are similar,but a UNIQUE index can be created on columns that permit NULL values?

mysql> CREATE TABLE person ( ? ?
? ? -> ? last_name char(20) NOT NULL, ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? -> ? first_name char(20) NOT NULL,
? ? -> ? address ? char(40),
? ? -> ? UNIQUE ?(last_name,first_name)
? ? -> );
Query OK, 0 rows affected (0.02 sec)

mysql>?

Each person must be assigned some sort of unique identifier,which becomes the value that distingishes one row from another.In MySQL,it's common to accomplish this by using an AUTO_INCREMENT column:

mysql>?
mysql> CREATE TABLE person
? ? -> (
? ? -> ? id ? ? ? ? INT UNSIGNED NOT NULL AUTO_INCREMENT,
? ? -> ? last_name ?CHAR(20),
? ? -> ? first_name CHAR(20),
? ? -> ? address ? ?CHAR(40),
? ? -> ? PRIMARY KEY (id)
? ? -> );
Query OK, 0 rows affected (0.01 sec)

mysql>?

2.How to dealing with duplicates when loading rows into a table.

Problem:

You have created a table with a unique index to prevent duplicate values in the indexed column or columns. But this results in an error if you attempt to insert a duplicate row, and you want to avoid having to deal with such errors.

Solution:

One approach is to just ignore the error.
Another is to use an INSERT IGNORE,REPLACE or INSERT ... ON DUPLICATE KEY UPDATE statement

mysql> Drop table if exists person;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE person
? ? -> (
? ? -> ?last_name CHAR(20) NOT NULL,
? ? -> ?first_name CHAR(20) NOT NULL,
? ? -> ?address CHAR(40),
? ? -> ?PRIMARY KEY (last_name, first_name)
? ? -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO person (last_name, first_name)
? ? -> values('X1','Y1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO person (last_name, first_name)?
? ? -> values('X1','Y1'); ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
ERROR 1062 (23000): Duplicate entry 'X1-Y1' for key 'PRIMARY'
mysql>?

MySQL Provides three single-query solutions to the problem of handling duplicate row.Choose from among them depending on the duplicate-handling behavior you want:

To keep the original row when a duplicate occurs,use INSERT IGNORE than INSERT.

mysql> INSERT IGNORE INTO person (last_name, first_name)?
? ? -> VALUES('X2','Y2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person (last_name, first_name)?
? ? -> VALUES('X2','Y2'); ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

To replace the original row with the new one when a duplicate occurs,use REPLACE rather than INSERT.

mysql>?
mysql> REPLACE INTO person (last_name,first_name)
? ? -> VALUES ('X3','Y3');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person (last_name,first_name)?
? ? -> VALUES ('X3','Y3'); ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
Query OK, 1 row affected (0.00 sec)

To modify columns of an existing row when a duplicate occurs,use INSERT ... ON DUPLICATE KEY UPDATE.

mysql> CREATE TABLE poll_vote
? ? -> (
? ? -> ?poll_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
? ? -> ?candidate_id INT UNSIGNED,
? ? -> ?vote_count INT UNSIGNED,
? ? -> ?PRIMARY KEY (poll_id, candidate_id)
? ? -> );
Query OK, 0 rows affected (0.01 sec)

mysql> select * from poll_vote;
Empty set (0.00 sec)

mysql> INSERT INTO poll_vote (poll_id,candidate_id,vote_count) VALUES(14,3,1)
? ? -> ON DUPLICATE KEY UPDATE vote_count = vote_count + 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from poll_vote;
+---------+--------------+------------+
| poll_id | candidate_id | vote_count |
+---------+--------------+------------+
| ? ? ?14 | ? ? ? ? ? ?3 | ? ? ? ? ?1 |
+---------+--------------+------------+
1 row in set (0.00 sec)

mysql> INSERT INTO poll_vote (poll_id,candidate_id,vote_count) VALUES(14,3,1)
? ? -> ON DUPLICATE KEY UPDATE vote_count = vote_count + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from poll_vote;
+---------+--------------+------------+
| poll_id | candidate_id | vote_count |
+---------+--------------+------------+
| ? ? ?14 | ? ? ? ? ? ?3 | ? ? ? ? ?2 |
+---------+--------------+------------+
1 row in set (0.00 sec)

mysql>?

3.How to counting and Identifying duplicates

Problem

You want to determine whether a table contains duplicates,and to what extent they occur.Or you want to see the rows that contain the duplicated values.

Solution

Use a counting summary that displays duplicated values.To see the rows in which the duplicated values occur.join the summary to the original table to display the matching rows.

mysql> CREATE TABLE catalog_list (
? ? -> last_name CHAR(20) NOT NULL,
? ? -> first_name CHAR(20) NOT NULL,
? ? -> street CHAR(40), ? ? ? ?
? ? -> PRIMARY KEY (last_name, first_name)
? ? -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE catalog_list ( last_name CHAR(20) NOT NULL, first_name CHAR(20) NOT NULL, street CHAR(40));
ERROR 1050 (42S01): Table 'catalog_list' already exists
mysql> Drop table if exists catalog_list;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE catalog_list ( last_name CHAR(20) NOT NULL, first_name CHAR(20) NOT NULL, street CHAR(40));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO catalog_list (last_name,first_name,street) values ('Isaacson','Jim','515 Fordam St., Apt. 917')
? ? -> ;
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO catalog_list (last_name,first_name,street) values ('Baxter','Wallace','57 3rd Ave.');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO catalog_list (last_name,first_name,street) values ('Baxter','Wallace','57 3rd Ave.');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO catalog_list (last_name,first_name,street) values ('McTavish','Taylor','432 River Run');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO catalog_list (last_name,first_name,street) values ('Pinter','Marlene','9 Sunset Trail');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO catalog_list (last_name,first_name,street) values ('BAXTER','WALLACE','57 3rd Ave.');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO catalog_list (last_name,first_name,street) values ('Brown','Bartholowmew','432 River Run');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO catalog_list (last_name,first_name,street) values ('Baxter','Wallace','57 3rd Ave., Apt 102');
Query OK, 1 row affected (0.01 sec)

mysql> select count(*) as rows from catalog_list;
+------+
| rows |
+------+
| ? ?9 |
+------+
1 row in set (0.00 sec)

mysql> select count(*) - count(distinct last_name,first_name)?
? ? -> as 'duplicate names'
? ? -> from catalog_list;
+-----------------+
| duplicate names |
+-----------------+
| ? ? ? ? ? ? ? 4 |
+-----------------+
1 row in set (0.00 sec)

mysql> select count(distinct last_name,first_name)/ count(*)
? ? -> as 'unique',
? ? -> 1 - (count(distinct last_name,first_name) / count(*))
? ? -> as 'nonunique'
? ? -> from catalog_list;
+--------+-----------+
| unique | nonunique |
+--------+-----------+
| 0.5556 | ? ?0.4444 |
+--------+-----------+
1 row in set (0.00 sec)

mysql>?

3.How to eliminating duplicates from a Table.

Problem:?

You want to remove duplicate rows from a table, leaving only unique rows.

Solution:

Select the unique rows from the table into a second table, then use it to replace the original one.Or use DELETE ...LIMIT n to remove all but one instance of a specific set of duplicates rows.

mysql> INSERT INTO tmp SELECT DISTINCT * FROM catalog_list;
Query OK, 6 rows affected (0.01 sec)
Records: 6 ?Duplicates: 0 ?Warnings: 0

mysql> CREATE TABLE catalog_list_original LIKE catalog_list; ? ? ? ? ? ? ? ? ? ?
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO catalog_list_original SELECT * FROM catalog_list; ? ? ? ? ? ??
Query OK, 9 rows affected (0.00 sec)
Records: 9 ?Duplicates: 0 ?Warnings: 0

mysql> drop table catalog_list;
Query OK, 0 rows affected (0.01 sec)

mysql> rename table tmp to catalog_list;
Query OK, 0 rows affected (0.00 sec)

mysql>?

Removing duplicates using table replacement

mysql> drop table catalog_list;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from catalog_list;
ERROR 1146 (42S02): Table 'cookbook.catalog_list' doesn't exist
mysql> create table catalog_list like catalog_list_original;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from catalog_list;
Empty set (0.00 sec)
mysql> insert into catalog_list select * from catalog_list_original; ? ?
Query OK, 9 rows affected (0.01 sec)
Records: 9 ?Duplicates: 0 ?Warnings: 0

mysql> CREATE TABLE tmp like catalog_list;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE tmp ADD PRIMARY KEY (last_name,first_name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 ?Duplicates: 0 ?Warnings: 0

mysql> INSERT IGNORE INTO tmp SELECT * FROM catalog_list;
Query OK, 5 rows affected, 4 warnings (0.00 sec)
Records: 9 ?Duplicates: 4 ?Warnings: 4

mysql> drop table catalog_list;
Query OK, 0 rows affected (0.01 sec)
mysql> RENAME TABLE tmp to catalog_list;
Query OK, 0 rows affected (0.01 sec)

Removing duplicates of a particular row

Use Limit to restrict the effect of a delete statement to a subset of the rows that it otherwise would delete.

mysql> drop table catalog_list;
Query OK, 0 rows affected (0.02 sec)

mysql> create table catalog_list like catalog_list_original;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from catalog_list;
Empty set (0.00 sec)

mysql> insert into catalog_list select * from catalog_list_original;
Query OK, 9 rows affected (0.01 sec)
Records: 9 ?Duplicates: 0 ?Warnings: 0

mysql> DELETE FROM catalog_list where last_name = 'Baxter'
? ? -> AND first_name = 'Wallace' limit 2;
Query OK, 2 rows affected (0.00 sec)

mysql> DELETE FROM catalog_list where last_name = 'Pinter'
? ? -> AND first_name = 'Marlene' limit 1;
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM catalog_list where last_name = 'Baxter' AND first_name = 'Wallace' limit 1;
Query OK, 1 row affected (0.01 sec)

mysql>?

游戏开发最新文章

6、英飞凌-AURIX-TC3XX： PWM实验之使用 GT

泛型自动装箱

CubeMax添加Rtthread操作系统组件STM32F10

python多线程编程：如何优雅地关闭线程

数据类型隐式转换导致的阻塞

WebAPi实现多文件上传，并附带参数

from origin ‘null‘ has been blocked by

UE4 蓝图调用C++函数（附带项目工程）

Unity学习笔记（一）结构体的简单理解与应用

【Memory As a Programming Concept in C a

加:2022-03-24 00:54:10 更:2022-03-24 00:55:15

360图书馆购物三丰科技阅读网日历万年历 2024年11日历

-2024/11/24 17:46:44-

图片自动播放器
↓图片自动播放器↓

TxT小说阅读器
↓语音阅读,小说下载,古典文学↓

一键清除垃圾
↓轻轻一点,清除系统垃圾↓

图片批量下载器
↓批量下载图片,美女图库↓

网站联系: qq:121756557 email:121756557@qq.com IT数码