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

标签: mysql 存在 更新 | 发表时间:2013-07-10 19:28 | 作者:Sky_qing
出处:http://blog.csdn.net

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  










作者:Sky_qing 发表于2013-7-10 19:28:59 原文链接
阅读:64 评论:0 查看评论

相关 [mysql 存在 更新] 推荐:

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

- - CSDN博客数据库推荐文章
mysql处理某个唯一索引时存在则更新,不存在则插入的情况应该是很常见的,网上也有很多类似的文章,我今天就讲讲当这个唯一的索引是多列唯一索引时可能会遇到的问题和方法. 使用 INSERT INTO ON. 其中该表中var1、var2和var3完全相同的记录只能有一条,所以建了一个多列唯一索引index_var,这样一来我们就可以使用  INSERT INTO ON.

Linux Ksplice,MySQL and Oracle

- Syn - DBA Notes
Oracle 在 7 月份收购了 Ksplice. 使用了 Ksplice 的 Linux 系统,为 Kernel 打补丁无需重启动,做系统维护的朋友应该明白这是一个杀手级特性. 现在该产品已经合并到 Oracle Linux 中. 目前已经有超过 700 家客户,超过 10 万套系统使用了 Ksplice (不知道国内是否已经有用户了.

MySQL Replication 线程

- - CSDN博客推荐文章
Replication 线程. Mysql 的Replication 是一个异步的复制过程,从一个Mysql instace(我们称之为Master)复制到另一个Mysql instance(我们称之Slave). 在Master 与Slave 之间的实现整个复制过程主. 要由三个线程来完成,其中两个线程(Sql 线程和IO 线程)在Slave 端,另外一个线程(IO 线程)在Master 端.

mysql backup 脚本

- - ITeye博客
网上备份脚本很多,但考虑都不周全. 保证创建备份文件只能是创建者跟root可以访问,其他用户没有权限,保证了数据库备份的安全. 上面脚本是负责备份的份数管理,. 已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

Oracle MySQL Or NoSQL续

- - Sky.Jian 朝阳的天空
接前面一篇,这里再将之前在“中国系统架构师大会”5周年的时候发布的纪念册“IT架构实录”上的一篇文章发出来,也算是前面博文中PPT的一个文字版解读吧. Oracle,MySQL 还是 NoSQL. 随着阿里系的“去IOE”运动在社区的宣传声越来越大,国内正在掀起一股“去xxx”的技术潮. 不仅仅是互联网企业,包括运营商以及金融机构都已经开始加入到这个潮流之中.

mysql优化

- - 数据库 - ITeye博客
公司网站访问量越来越大,MySQL自然成为瓶颈,因此最近我一直在研究 MySQL  的优化,第一步自然想到的是 MySQL 系统参数的优化,作为一个访问量很大的网站(日20万人次以上)的数据库系统,不可能指望 MySQL  默认的系统参数能够让 MySQL运行得非常顺畅. 在Apache, PHP,  MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.

MySql动态SQL

- - SQL - 编程语言 - ITeye博客
13.7. 用于预处理语句的SQL语法. MySQL 5.1对服务器一方的预制语句提供支持. 如果您使用合适的客户端编程界面,则这种支持可以发挥在MySQL 4.1中实施的高效客户端/服务器二进制协议的优势. 候选界面包括MySQL C API客户端库(用于C程序)、MySQL Connector/J(用于Java程序)和MySQL Connector/NET.

MySQL 性能

- - 谁主沉浮
这里罗列了一些基本的 MySQL 性能提示,但不是放之四海而皆准,需要根据实际的应用情况而决定. 使用标准化设计(数据库三范式),记住表的联合查询(join)性能不会差. 选择合适的字符集,虽然UTF16无所不能,但需要两倍的存储;UTF8适合各种字符,但比latin1慢,尽可能选用latin1(此条不适合中文).

mysql explain 解析

- - SQL - 编程语言 - ITeye博客
Mysql Explain 详解. 例如: explain select * from t3 where id=3952602;. 二.explain输出解释. | id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |.

mysql profile使用

- - 数据库 - ITeye博客
mysql的sql语句优化都使用explain,但是这个没有办法知道详细的Memory/CPU等使用量. MySQL Query Profiler, 可以查询到此 SQL 语句会执行多少, 并看出 CPU/Memory 使用. 量, 执行过程 System lock, Table lock 花多少时间等等.