mysql处理存在则更新,不存在则插入(多列唯一索引)

原创 2013年07月10日 19:28:59

mysql处理某个唯一索引时存在则更新,不存在则插入的情况应该是很常见的,网上也有很多类似的文章,我今天就讲讲当这个唯一的索引是多列唯一索引时可能会遇到的问题和方法。


方法一:

使用 INSERT INTO ON ... DUPLICATE KEY UPDATE ... :

表的创建如下:

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 ... 来实现插入数据时存在则更新,不存在则插入的功能了,如下:

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。


这样,的确是没有问题的,但是,如果表的创建如下:

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,此时执行该创建语句时会报如下错误:

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虚拟表来实现的话就不需要创建多列唯一索引了,表的创建如下:

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;
插入语句则是形如:

INSERT INTO table
(primarykey, field1, field2, ...)
SELECT key, value1, value2, ...
FROM dual
WHERE not exists (select * from table where primarykey = id);
的语句,此时我们可以用以下语句代替:

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’的记录不存在,那么就会执行该插入语句插入该记录,如果存在,那就需要我们再使用相应的更新语句来更新记录:

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



OK!到这里,基本上讲完了。


注:转载请注明出处!


参考:

http://thobian.info/?p=1035

http://blog.51yip.com/mysql/1515.html  





博主所有文章已转自私人博客 Joe 的个人博客,谢谢关注!





MySQL insert 存在则更新,不存在则插入

有时候我们需要用一个表去记录某些经常变动的数据,比如现在有一个表,是用来记录页面被访问的IP和访问次数的。你可以用传统的方法,只需要一个IP字段,只要客户端访问,就把IP写入表中。查询的时候用grou...
  • qingguiyu
  • qingguiyu
  • 2016-12-30 13:07:56
  • 5688

mysql 有则更新无则插入

表结构如下: CREATE TABLE `test` ( `name` varchar(255) DEFAULT NULL, `id` int(11) NOT NULL AUTO_INCRE...
  • qq_27292113
  • qq_27292113
  • 2017-10-25 14:01:24
  • 1403

数据库--SQL--有就更新,没有就插入

IF EXISTS (SELECT 1            FROM t_event_message_cache             WHERE msg_key = #{msgKey}) ...
  • liwenjie001
  • liwenjie001
  • 2014-03-12 20:55:25
  • 2473

mysql 如果数据不存在,则插入新数据,否则更新的实现方法

CREATE TABLE `table_test` ( `my_key` int(11) NOT NULL default '0', `value` varchar(21) NOT NULL d...
  • zxk364961978
  • zxk364961978
  • 2017-05-09 13:41:15
  • 3784

MySQL插入数据时,如果记录不存在则insert,如果存在则update

MySQL 当记录不存在时insert,当记录存在时更新 网上基本有三种解决方法。 第一种: 示例一:insert多条记录 假设有一个主键为 client_id 的 clients 表,可以使...
  • AAA123524457
  • AAA123524457
  • 2015-06-19 09:47:57
  • 21776

MySql_插入记录时,存在就更新(或不做任何动作),不存在就添加

今天本码农在批量插入数据的时候,遇到一个问题,由于数据是经常更新的,但是又不想出现冗余数据,所以我希望达到的目的是:插入一条数据的时候,先判断是否存在这条记录,如果存在,我就更新这条记录,不存在就插入...
  • BuptZhengChaoJie
  • BuptZhengChaoJie
  • 2016-03-27 18:37:48
  • 19209

update 多列更新

drop table test;  create table test       (q1  varchar2(200),       q2  varchar2(200),       q3  var...
  • pzasdq
  • pzasdq
  • 2015-11-17 08:34:04
  • 875

MySQL 以UPDATE更新数据

UPDATE 可以更新一列或多列的值,与SELECT 和DELETE,一样,UPDATE也能通过使用WHERE子句来精确指定要更新的行。UPDATE doughnut_ratings SET type...
  • Vincentlmeng
  • Vincentlmeng
  • 2017-04-17 20:11:07
  • 3290

mysql优化,导致查询不走索引的原因总结

最近公司让我做SQL优化的工作,用explain发了一些问题。常见的像OR ,IN,>= ,或者是嵌套等导致索引失效,导致查询性能降低的问题在这里就不做陈述了,网上的文章一搜一 大片。我只是写点个人工...
  • m0_37808356
  • m0_37808356
  • 2017-05-19 13:07:32
  • 5382

避免写出不走索引的SQL, MySQL

在MySQL中,并不是你建立了索引,并且你在SQL中使用到了该列,MySQL就肯定会使用到那些索引的,有一些情况很可能在你不知不觉中,你就“成功的避开了”MySQL的所有索引。 现假设有t_...
  • zjt1388
  • zjt1388
  • 2016-11-23 09:34:07
  • 2922
收藏助手
不良信息举报
您举报文章:mysql处理存在则更新,不存在则插入(多列唯一索引)
举报原因:
原因补充:

(最多只允许输入30个字)