1、處理重復(fù)

<a>、創(chuàng)建表包含 primary key 或 unique index，阻止重復(fù)數(shù)據(jù)寫入

<b>、聯(lián)合 <a>，使用 INSERT IGNORE 或 REPLACE

<c>、檢查表是否有重復(fù)行的方法，通過 group by 和 count( x ) 來統(tǒng)計

<d>、通過 SELECT DISTINCT 來過濾重復(fù)

a——示例

CREATE TABLE person (last_name CHAR(20) NOT NULL,first_name CHAR(20) NOT NULL,address CHAR(40),PRIMARY KEY (last_name, first_name) ); CREATE TABLE person (last_name CHAR(20) NOT NULL,first_name CHAR(20) NOT NULL,address CHAR(40),UNIQUE (last_name, first_name) );

b——示例

一般情況下，插入重復(fù) primary key 或 union index 會報錯

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 1 使用 INSERT IGNORE

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 (0.00 sec) 使用 REPLACE

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, 2 rows affected (0.00 sec) 使用?INSERT ... ON DUPLICATE KEY UPDATE?

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) );mysql> SELECT * FROM poll_vote; Empty set (0.01 sec) mysql> INSERT INTO poll_vote (poll_id,candidate_id,vote_count) VALUES(14,2,1)-> ON DUPLICATE KEY UPDATE vote_count = vote_count + 1; Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM poll_vote; +---------+--------------+------------+ | poll_id | candidate_id | vote_count | +---------+--------------+------------+ | 14 | 2 | 1 | +---------+--------------+------------+ 1 row in set (0.01 sec) mysql> INSERT INTO poll_vote (poll_id,candidate_id,vote_count) VALUES(14,2,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 | 2 | 2 | +---------+--------------+------------+ 1 row in set (0.00 sec)c——示例

mysql> SELECT * FROM catalog_list; +-----------+-------------+--------------------------+ | last_name | first_name | street | +-----------+-------------+--------------------------+ | Isaacson | Jim | 515 Fordam St., Apt. 917 | | Baxter | Wallace | 57 3rd Ave. | | McTavish | Taylor | 432 River Run | | Pinter | Marlene | 9 Sunset Trail | | BAXTER | WALLACE | 57 3rd Ave. | | Brown | Bartholomew | 432 River Run | | Pinter | Marlene | 9 Sunset Trail | | Baxter | Wallace | 57 3rd Ave., Apt 102 | +-----------+-------------+--------------------------+mysql> select count(*) as total_rows,-> count(distinct last_name, first_name) as distince_names,-> count(*) - count(distinct last_name, first_name) as duplicate_names-> from catalog_list; +------------+----------------+-----------------+ | total_rows | distince_names | duplicate_names | +------------+----------------+-----------------+ | 8 | 5 | 3 | +------------+----------------+-----------------+查詢出重復(fù)的行：

mysql> select count(*) as repetitions, last_name, first_name-> from catalog_list-> group by last_name, first_name-> having repetitions > 1; +-------------+-----------+------------+ | repetitions | last_name | first_name | +-------------+-----------+------------+ | 3 | Baxter | Wallace | | 2 | Pinter | Marlene | +-------------+-----------+------------+2、從表中刪除重復(fù)

（Recipe 14.4. Eliminating Duplicates from a Table）

<a>、Removing duplicates using table replacement

<b>、Removing duplicates by adding an index

<c>、Removing duplicates of a particular row

a——示例

mysql> CREATE TABLE tmp LIKE catalog_list; mysql> ALTER TABLE tmp ADD PRIMARY KEY (last_name, first_name); mysql> INSERT IGNORE INTO tmp SELECT * FROM catalog_list; mysql> SELECT * FROM tmp ORDER BY last_name, first_name; +-----------+-------------+--------------------------+ | last_name | first_name | street | +-----------+-------------+--------------------------+ | Baxter | Wallace | 57 3rd Ave. | | Brown | Bartholomew | 432 River Run | | Isaacson | Jim | 515 Fordam St., Apt. 917 | | McTavish | Taylor | 432 River Run | | Pinter | Marlene | 9 Sunset Trail | +-----------+-------------+--------------------------+ mysql> DROP TABLE catalog_list; mysql> RENAME TABLE tmp TO catalog_list; b——示例

mysql> ALTER IGNORE TABLE catalog_list-> ADD PRIMARY KEY (last_name, first_name); mysql> SELECT * FROM catalog_list ORDER BY last_name, first_name; +-----------+-------------+--------------------------+ | last_name | first_name | street | +-----------+-------------+--------------------------+ | Baxter | Wallace | 57 3rd Ave. | | Brown | Bartholomew | 432 River Run | | Isaacson | Jim | 515 Fordam St., Apt. 917 | | McTavish | Taylor | 432 River Run | | Pinter | Marlene | 9 Sunset Trail | +-----------+-------------+--------------------------+ c——示例

+-------+ | color | +-------+ | blue | | green | | blue | | blue | | red | | green | | red | +-------+mysql> DELETE FROM t WHERE color = 'blue' LIMIT 2; mysql> DELETE FROM t WHERE color = 'green' LIMIT 1; mysql> DELETE FROM t WHERE color = 'red' LIMIT 1; mysql> SELECT * FROM t; +-------+ | color | +-------+ | blue | | green | | red | +-------+

3、刪除“語義重復(fù)”但行數(shù)據(jù)不重復(fù)的行

（Recipe 14.5. Eliminating Duplicates from a Self-Join Result）

mysql> SELECT YEAR(s1.statehood) AS year,-> s1.name AS name1, s1.statehood AS statehood1,-> s2.name AS name2, s2.statehood AS statehood2-> FROM states AS s1 INNER JOIN states AS s2-> ON YEAR(s1.statehood) = YEAR(s2.statehood) AND s1.name != s2.name-> ORDER BY year, s1.name, s2.name; +------+----------------+------------+----------------+------------+ | year | name1 | statehood1 | name2 | statehood2 | +------+----------------+------------+----------------+------------+ | 1787 | Delaware | 1787-12-07 | New Jersey | 1787-12-18 | | 1787 | Delaware | 1787-12-07 | Pennsylvania | 1787-12-12 | | 1787 | New Jersey | 1787-12-18 | Delaware | 1787-12-07 | | 1787 | New Jersey | 1787-12-18 | Pennsylvania | 1787-12-12 | | 1787 | Pennsylvania | 1787-12-12 | Delaware | 1787-12-07 | | 1787 | Pennsylvania | 1787-12-12 | New Jersey | 1787-12-18 | ... | 1912 | Arizona | 1912-02-14 | New Mexico | 1912-01-06 | | 1912 | New Mexico | 1912-01-06 | Arizona | 1912-02-14 | | 1959 | Alaska | 1959-01-03 | Hawaii | 1959-08-21 | | 1959 | Hawaii | 1959-08-21 | Alaska | 1959-01-03 | +------+----------------+------------+----------------+------------+ 由上數(shù)據(jù)分析：

1787年有3個州（Delaware，New Jersey，Pennsylvania），兩兩配對應(yīng)該是3個才對。

但是這里卻又6條數(shù)據(jù)，一半重復(fù)（語義上）！

將“語義重復(fù)”調(diào)整為“行數(shù)據(jù)重復(fù)”

+------+----------------+------------+----------------+------------+ | year | name1 | statehood1 | name2 | statehood2 | +------+----------------+------------+----------------+------------+ | 1787 | Delaware | 1787-12-07 | New Jersey | 1787-12-18 | | 1787 | New Jersey | 1787-12-18 | Delaware | 1787-12-07 | 也就是：

如果，name1 < name2，成立保持不變

如果，name1 < name2，不成立。name1 與 name2 互換；statehood1 與 statehood2 互換。

mysql> SELECT YEAR(s1.statehood) AS year,-> IF(s1.name<s2.name,s1.name,s2.name) AS name1,-> IF(s1.name<s2.name,s1.statehood,s2.statehood) AS statehood1,-> IF(s1.name<s2.name,s2.name,s1.name) AS name2,-> IF(s1.name<s2.name,s2.statehood,s1.statehood) AS statehood2-> FROM states AS s1 INNER JOIN states AS s2-> ON YEAR(s1.statehood) = YEAR(s2.statehood) AND s1.name != s2.name-> ORDER BY year, name1, name2; +------+----------------+------------+----------------+------------+ | year | name1 | statehood1 | name2 | statehood2 | +------+----------------+------------+----------------+------------+ | 1787 | Delaware | 1787-12-07 | New Jersey | 1787-12-18 | | 1787 | Delaware | 1787-12-07 | New Jersey | 1787-12-18 | | 1787 | Delaware | 1787-12-07 | Pennsylvania | 1787-12-12 | | 1787 | Delaware | 1787-12-07 | Pennsylvania | 1787-12-12 | | 1787 | New Jersey | 1787-12-18 | Pennsylvania | 1787-12-12 | | 1787 | New Jersey | 1787-12-18 | Pennsylvania | 1787-12-12 | ... | 1912 | Arizona | 1912-02-14 | New Mexico | 1912-01-06 | | 1912 | Arizona | 1912-02-14 | New Mexico | 1912-01-06 | | 1959 | Alaska | 1959-01-03 | Hawaii | 1959-08-21 | | 1959 | Alaska | 1959-01-03 | Hawaii | 1959-08-21 | +------+----------------+------------+----------------+------------+ 再用 DISTINCT 語句刪除重復(fù)：

mysql> SELECT DISTINCT YEAR(s1.statehood) AS year,-> IF(s1.name<s2.name,s1.name,s2.name) AS name1,-> IF(s1.name<s2.name,s1.statehood,s2.statehood) AS statehood1,-> IF(s1.name<s2.name,s2.name,s1.name) AS name2,-> IF(s1.name<s2.name,s2.statehood,s1.statehood) AS statehood2-> FROM states AS s1 INNER JOIN states AS s2-> ON YEAR(s1.statehood) = YEAR(s2.statehood) AND s1.name != s2.name-> ORDER BY year, name1, name2; +------+----------------+------------+----------------+------------+ | year | name1 | statehood1 | name2 | statehood2 | +------+----------------+------------+----------------+------------+ | 1787 | Delaware | 1787-12-07 | New Jersey | 1787-12-18 | | 1787 | Delaware | 1787-12-07 | Pennsylvania | 1787-12-12 | | 1787 | New Jersey | 1787-12-18 | Pennsylvania | 1787-12-12 | ... | 1912 | Arizona | 1912-02-14 | New Mexico | 1912-01-06 | | 1959 | Alaska | 1959-01-03 | Hawaii | 1959-08-21 | +------+----------------+------------+----------------+------------+

4、選擇支持事務(wù)的存儲引擎

（Recipe 15.1. Choosing a Transactional Storage Engine）

mysql> SHOW ENGINES\G *************************** 1. row ***************************Engine: MyISAM Support: DEFAULT Comment: Default engine as of MySQL 3.23 with great performance *************************** 2. row ***************************Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables *************************** 3. row ***************************Engine: InnoDB Support: YES Comment: Supports transactions, row-level locking, and foreign keys *************************** 4. row ***************************Engine: BerkeleyDB Support: YES Comment: Supports transactions and page-level locking ... 創(chuàng)建表：

CREATE TABLE t1 (i INT) ENGINE = InnoDB; CREATE TABLE t2 (i INT) ENGINE = BDB; 修改表：

ALTER TABLE t ENGINE = InnoDB; 5、使用 SQL 執(zhí)行事務(wù)

（Recipe 15.2. Performing Transactions Using SQL）

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB; mysql> START TRANSACTION; mysql> INSERT INTO t (i) VALUES(1); mysql> INSERT INTO t (i) VALUES(2); mysql> COMMIT; mysql> SELECT * FROM t; +------+ | i | +------+ | 1 | | 2 | +------+ 關(guān)閉自動提交：

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB; mysql> SET autocommit = 0; mysql> INSERT INTO t (i) VALUES(1); mysql> INSERT INTO t (i) VALUES(2); mysql> COMMIT; mysql> SELECT * FROM t; +------+ | i | +------+ | 1 | | 2 | +------+ 開啟自動提交：

mysql> SET autocommit = 1; 6、在程序中使用事務(wù)（Java 示例）

try {conn.setAutoCommit (false);Statement s = conn.createStatement ();// move some money from one person to the others.executeUpdate ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'");s.executeUpdate ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'");s.close ();conn.commit ();conn.setAutoCommit (true); } catch (SQLException e) {System.err.println ("Transaction failed, rolling back.");Cookbook.printErrorMessage (e);// empty exception handler in case rollback failstry{conn.rollback ();conn.setAutoCommit (true);}catch (Exception e2) { } }

7、存儲過程

Navicat 中創(chuàng)建存儲過程注意事項：

<a>、在Navicat里面不需要寫頭，直接從BEGIN開始即可。

<b>、過程名將在保存的時候填寫，參數(shù)則可以填在代碼定義框下面的參數(shù)輸入框中，如果要使用返回值，則可以設(shè)定類型為FUNCTION，回傳框即可填寫。

總結(jié)

以上是生活随笔為你收集整理的MySQL Cookbook 学习笔记-04的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。