1 問題來源
這幾天在項目中遇到一個問題：由于前期設計不合理，導緻後期使用者錄入資料時，基礎資料表中有重複多餘資料。如下：
出現兩個
2G網絡測試手機
這樣類似的基礎資料，直接後果就是使用者在使用這個基礎資料時，明明選擇的是同一類型的基礎資料，但是資料庫中的關聯ID卻不一緻，這在後期做統計等功能的時候帶來巨大的不必要的困難，并且随着時間的增長，這樣的時間開銷越來越大，系統維護難度也增大。
表結構如下：
功能資料表：
tb_instruments_info
基礎資料表：
tb_instrument_mapping_info
其中，功能資料表中的instru_mid，pro_id，major_id都關聯基礎資料表中的ID，上圖中的
2G網絡測試手機
為基礎資料表中的mapping_name字段。
頁面顯示的mapping_name相同，但是ID卻不同，進而功能表中相同名稱的基礎資料，但是instru_mid/pr_id/major_id卻不同，是以在統計
tb_instruments_info
表中的資料的時候，會有不必要的麻煩。
解決這個問題，我想到的解決辦法如下：
1. 修改代碼，使得在添加基礎資料的時候，如果有此項資料了之後，不能重複添加；
2. 清除基礎資料表中還沒有使用過的基礎資料；
3. 查詢出基礎資料中重複多餘的基礎資料；
4. 根據3查詢出的重複資料，在功能資料表中查詢出使用了重複資料的資料；
5. 更新功能資料表中重複的資料，使得所選擇的基礎資料關聯ID相同；
6. 更新資料之後，清除基礎資料表中的備援資料。
以下是步驟：
第一步這裡不說，主要說得是後面幾步資料庫操作。
2 解決過程
1.清除基礎資料中沒有使用過的資料
思路： 建立一張中間表temp，查詢出使用過的基礎資料，放在temp中。
SQL：
!#查詢出使用過的基礎資料，并且插入到臨時表temp中
create table temp
select * from tb_instrument_mapping_info
where id in (select instru_mid from tb_instruments_info )
UNION
select * from tb_instrument_mapping_info
where id in (select major_id from tb_instruments_info )
UNION
select * from tb_instrument_mapping_info
where id in (select pro_id from tb_instruments_info )
!# 删除原來的基礎資料表
drop table tb_instrument_mapping_info
!#把臨時表temp重命名為基礎資料表
rename table temp to tb_instrument_mapping_info
select * from tb_instrument_mapping_info
2.查詢出基礎資料中重複的資料，并且按照mapping_name分組
思路： 基礎資料中設計為不重複，是以同一類型下count(mapping_name)>1的資料為重複資料
SQL：
!#查詢出類型=3的重複mapping_name資料
select * from tb_instrument_mapping_info
where mapping_type=3
GROUP BY mapping_name
HAVING COUNT(mapping_name)>1
3.在功能資料表中根據查詢出使用了多餘資料的功能資料
思路： 在基礎資料中查詢出同一類型的重複資料之後，inner join 功能資料表，即可查詢出使用了重複資料的資料
SQL：
select t3.* from
(select t1.id,t1.instru_code,t1.spec_type,t1.major_id,t1.instru_mid,c.mapping_name,t1.pro_id
from tb_instruments_info t1
LEFT JOIN tb_instrument_mapping_info c on t1.pro_id=c.id) t3
INNER JOIN
(select * from tb_instrument_mapping_info
where mapping_type=3
GROUP BY mapping_name
HAVING COUNT(mapping_name)>1) t2
ON t3.mapping_name = t2.mapping_name
4.更新查詢的重複資料，使得在mapping_name相同的同一類型資料，ID一緻
思路：根據查詢出的基礎資料中的重複資料，如果功能資料表中的名稱一緻，則更新此條資料的基礎資料關聯字段值
SQL：
update tb_instruments_info t4
INNER JOIN
(select t3.*,t2.id as mpid from
(select t1.id,t1.instru_code,t1.spec_type,t1.major_id,t1.instru_mid,c.mapping_name,t1.pro_id
from tb_instruments_info t1
LEFT JOIN tb_instrument_mapping_info c on t1.pro_id=c.id) t3
INNER JOIN
(select id,mapping_name from tb_instrument_mapping_info
where mapping_type=3
GROUP BY mapping_name
HAVING COUNT(mapping_name)>1) t2
ON t3.mapping_name = t2.mapping_name) t5
on t4.id=t5.id
set t4.pro_id=t5.mpid
經過以上幾步，可以得出想要的結果。
3.技術難點
1.create table Table select
根據查詢出來的結果，建立一張表。此用法大多用在建立臨時表，和遷移資料時使用。需注意預設值的改變。
2.update TableA inner join TableB on TableA.id=TableB.id set TableA.name=TableB.name
根據TableB中的表的值，更新TableA中對應表的值，在MySQL中使用，其他資料庫沒有測試。
4.其他
根據一張表的資料更新另一張表：
https://stackoverflow.com/questions/11709043/mysql-update-column-with-value-from-another-table