程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql處理存在則更新，不存在則插入（多列唯一索引）

mysql處理存在則更新，不存在則插入（多列唯一索引）

編輯：MySQL綜合教程

mysql處理存在則更新，不存在則插入（多列唯一索引） mysql處理某個唯一索引時存在則更新，不存在則插入的情況應該是很常見的，網上也有很多類似的文章，我今天就講講當這個唯一的索引是多列唯一索引時可能會遇到的問題和方法。方法一：使用

 INSERT INTO ON ... DUPLICATE KEY UPDATE ...

：表的創建如下：

[sql] 
CREATE TABLE `test_table` (  
  `id`  int(11) NOT NULL AUTO_INCREMENT ,  
  `var1`  varchar(100) CHARACTER SET utf8 DEFAULT NULL,  
  `var2`  tinyint(1) NOT NULL DEFAULT '0',  
  `var3`  varchar(100) character set utf8 default NULL,  
  `value1`  int(11) NOT NULL DEFAULT '1',  
  `value2`  int(11) NULL DEFAULT NULL,  
  `value3`  int(5) DEFAULT NULL,  
  PRIMARY KEY (`Id`),  
  UNIQUE INDEX `index_var` (`var1`, `var2`, `var3`)  
) ENGINE=MyISAM DEFAULT CHARACTER SET=latin1 AUTO_INCREMENT=1;

其中該表中var1、var2和var3完全相同的記錄只能有一條，所以建了一個多列唯一索引index_var，這樣一來我們就可以使用 INSERT INTO ON ... DUPLICATE KEY UPDATE ... 來實現插入數據時存在則更新，不存在則插入的功能了，如下：

[sql] 
INSERT INTO `test_table`   
(`var1`, `var2`, `var3`, `value1`, `value2`, `value3`) VALUES   
('abcd', 0, 'xyz', 1, 2, 3)   
ON DUPLICATE KEY UPDATE `value1` = `value1` + 1 AND   
`value2` = `value2` + 2 AND `value3` = `value3` + 3;

該條插入語句的含義是：向test_table表中插入，如果不存在val1 = 'abcd'，val2 = 0, val3 = ‘xyz’的記錄，那就插入 val1 = 'abcd'，val2 = 0, val3 = ‘xyz’，value1 = 1, value2 = 2, value3 = 3的記錄，如果存在，那就更新value1的值為value1+1，更新value2的值為value2+2，更新value3的值為value3+3。這樣，的確是沒有問題的，但是，如果表的創建如下：

[sql] 
CREATE TABLE `test_table` (  
  `id`  int(11) NOT NULL AUTO_INCREMENT ,  
  `var1`  varchar(1024) CHARACTER SET utf8 DEFAULT NULL,  
  `var2`  tinyint(1) NOT NULL DEFAULT '0',  
  `var3`  varchar(1024) character set utf8 default NULL,  
  `value1`  int(11) NOT NULL DEFAULT '1',  
  `value2`  int(11) NULL DEFAULT NULL,  
  `value3`  int(5) DEFAULT NULL,  
  PRIMARY KEY (`Id`),  
  UNIQUE INDEX `index_var` (`var1`, `var2`, `var3`)  
) ENGINE=MyISAM DEFAULT CHARACTER SET=latin1 AUTO_INCREMENT=1;

注意：var1和var3的最大長度由100變成了1024，此時執行該創建語句時會報如下錯誤：

[sql] 
Specified key was too long; max key length is 1000 bytes

這是由於index_var索引的為1024 * 3 + 1 + 1024 * 3 > 1000導致的，如果遇到這種情況怎麼辦？有兩種解決辦法。第一，將數據庫的engine由MyISAM換成InnoDB就可以了，那麼這兩個引擎有什麼區別呢？看這裡不過，這樣換有一個缺點，就是InnoDB的性能沒有MyISAM的好，那麼如果想要不犧牲性能的話，那就只有用第二個方法了，也就是我們這裡說的方法二！方法二：使用dual虛擬表來實現。使用dual虛擬表來實現的話就不需要創建多列唯一索引了，表的創建如下：

[sql] 
CREATE TABLE `test_table` (  
  `id`  int(11) NOT NULL AUTO_INCREMENT ,  
  `var1`  varchar(1024) CHARACTER SET utf8 DEFAULT NULL,  
  `var2`  tinyint(1) NOT NULL DEFAULT '0',  
  `var3`  varchar(1024) character set utf8 default NULL,  
  `value1`  int(11) NOT NULL DEFAULT '1',  
  `value2`  int(11) NULL DEFAULT NULL,  
  `value3`  int(5) DEFAULT NULL,  
  PRIMARY KEY (`Id`)  
) ENGINE=MyISAM DEFAULT CHARACTER SET=latin1 AUTO_INCREMENT=1;

插入語句則是形如：

[sql] 
INSERT INTO table  
(primarykey, field1, field2, ...)  
SELECT key, value1, value2, ...  
FROM dual  
WHERE not exists (select * from table where primarykey = id);

的語句，此時我們可以用以下語句代替：

[sql] 
INSERT INTO `test_table` SELECT 0, 'abcd', 0, 'xyz', 1, 2, 3  
FROM dual WHERE NOT EXISTS (  
SELECT * FROM `test_table` WHERE   
`var1` = 'abcd' AND `var2` = 0 AND `var3` = 'xyz');

此時，如果val1 = 'abcd'，val2 = 0, val3 = ‘xyz’的記錄不存在，那麼就會執行該插入語句插入該記錄，如果存在，那就需要我們再使用相應的更新語句來更新記錄：

[sql] 
UPDATE `test_table` SET   
`value1` = `value1` + 1, `value2` = `value2` + 2, `value3` = `value3` + 3  
WHERE `val1` = 'abcd' AND `val2` = 0 AND `val3` = 'xyz';

OK！到這裡，基本上講完了。