有些 MySQL 數(shù)據(jù)表中可能存在重復(fù)的記錄,有些情況我們?cè)试S重復(fù)數(shù)據(jù)的存在,但有時(shí)候我們也需要?jiǎng)h除這些重復(fù)的數(shù)據(jù)。
本章節(jié)我們將為大家介紹如何防止數(shù)據(jù)表出現(xiàn)重復(fù)數(shù)據(jù)及如何刪除數(shù)據(jù)表中的重復(fù)數(shù)據(jù)。
防止表中出現(xiàn)重復(fù)數(shù)據(jù)
你可以在 MySQL 數(shù)據(jù)表中設(shè)置指定的字段為 PRIMARY KEY(主鍵) 或者 UNIQUE(唯一) 索引來(lái)保證數(shù)據(jù)的唯一性。
讓我們嘗試一個(gè)實(shí)例:下表中無(wú)索引及主鍵,所以該表允許出現(xiàn)多條重復(fù)記錄。
CREATE TABLE person_tbl
(
first_name CHAR(20),
last_name CHAR(20),
sex CHAR(10)
);
如果你想設(shè)置表中字段 first_name,last_name 數(shù)據(jù)不能重復(fù),你可以設(shè)置雙主鍵模式來(lái)設(shè)置數(shù)據(jù)的唯一性, 如果你設(shè)置了雙主鍵,那么那個(gè)鍵的默認(rèn)值不能為 NULL,可設(shè)置為 NOT NULL。如下所示:
CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
PRIMARY KEY (last_name, first_name)
);
如果我們?cè)O(shè)置了唯一索引,那么在插入重復(fù)數(shù)據(jù)時(shí),SQL 語(yǔ)句將無(wú)法執(zhí)行成功,并拋出錯(cuò)。
INSERT IGNORE INTO 與 INSERT INTO 的區(qū)別就是 INSERT IGNORE 會(huì)忽略數(shù)據(jù)庫(kù)中已經(jīng)存在的數(shù)據(jù),如果數(shù)據(jù)庫(kù)沒(méi)有數(shù)據(jù),就插入新的數(shù)據(jù),如果有數(shù)據(jù)的話就跳過(guò)這條數(shù)據(jù)。這樣就可以保留數(shù)據(jù)庫(kù)中已經(jīng)存在數(shù)據(jù),達(dá)到在間隙中插入數(shù)據(jù)的目的。
以下實(shí)例使用了 INSERT IGNORE INTO,執(zhí)行后不會(huì)出錯(cuò),也不會(huì)向數(shù)據(jù)表中插入重復(fù)數(shù)據(jù):
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
-> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
-> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)
INSERT IGNORE INTO 當(dāng)插入數(shù)據(jù)時(shí),在設(shè)置了記錄的唯一性后,如果插入重復(fù)數(shù)據(jù),將不返回錯(cuò)誤,只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的記錄,則先刪除掉。再插入新記錄。
另一種設(shè)置數(shù)據(jù)的唯一性方法是添加一個(gè) UNIQUE 索引,如下所示:
CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
UNIQUE (last_name, first_name)
);
統(tǒng)計(jì)重復(fù)數(shù)據(jù)
以下我們將統(tǒng)計(jì)表中 first_name 和 last_name的重復(fù)記錄數(shù):
mysql> SELECT COUNT(*) as repetitions, last_name, first_name
-> FROM person_tbl
-> GROUP BY last_name, first_name
-> HAVING repetitions > 1;
以上查詢語(yǔ)句將返回 person_tbl 表中重復(fù)的記錄數(shù)。 一般情況下,查詢重復(fù)的值,請(qǐng)執(zhí)行以下操作:
- 確定哪一列包含的值可能會(huì)重復(fù)。
- 在列選擇列表使用COUNT(*)列出的那些列。
- 在GROUP BY子句中列出的列。
- HAVING子句設(shè)置重復(fù)數(shù)大于1。
過(guò)濾重復(fù)數(shù)據(jù)
如果你需要讀取不重復(fù)的數(shù)據(jù)可以在 SELECT 語(yǔ)句中使用 DISTINCT 關(guān)鍵字來(lái)過(guò)濾重復(fù)數(shù)據(jù)。
mysql> SELECT DISTINCT last_name, first_name
-> FROM person_tbl;
你也可以使用 GROUP BY 來(lái)讀取數(shù)據(jù)表中不重復(fù)的數(shù)據(jù):
mysql> SELECT last_name, first_name
-> FROM person_tbl
-> GROUP BY (last_name, first_name);
刪除重復(fù)數(shù)據(jù)
如果你想刪除數(shù)據(jù)表中的重復(fù)數(shù)據(jù),你可以使用以下的SQL語(yǔ)句:
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;
當(dāng)然你也可以在數(shù)據(jù)表中添加 INDEX(索引) 和 PRIMAY KEY(主鍵)這種簡(jiǎn)單的方法來(lái)刪除表中的重復(fù)記錄。方法如下:
mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name);
以上就是MySQL 處理重復(fù)數(shù)據(jù)的方法(防止、刪除)的詳細(xì)內(nèi)容,更多關(guān)于MySQL 處理重復(fù)數(shù)據(jù)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
您可能感興趣的文章:- mysql判斷表是否存在然后批量刪除的操作
- MySQL刪除數(shù)據(jù),表文件大小依然沒(méi)變的原因
- MySQL刪除表的三種方式(小結(jié))
- MySQL 如何查找并刪除重復(fù)記錄的實(shí)現(xiàn)
- MySQL 快速刪除大量數(shù)據(jù)(千萬(wàn)級(jí)別)的幾種實(shí)踐方案詳解
- 刪除mysql服務(wù)的具體方法
- MySQL對(duì)數(shù)據(jù)庫(kù)操作(創(chuàng)建、選擇、刪除)
- Window下如何恢復(fù)被刪除的Mysql8.0.17 Root賬戶及密碼
- Linux下徹底刪除Mysql 8.0服務(wù)的方法
- MySQL刪除表操作實(shí)現(xiàn)(delete、truncate、drop的區(qū)別)
- MySQL刪除了記錄不生效的原因排查