mysql的replace into和on duplicate key update测试笔记
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql的replace into和on duplicate key update测试笔记,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含8242字,纯文字阅读大概需要12分钟。
内容图文
mysql的replace into和on duplicate key update测试笔记
mysql> create table tbl_insert_tmp(id int(5),addr_number int(10), name varchar(20),primary key (id),unique key udx_addr_number (addr_number));
Query OK, 0 rows affected (0.05 sec)
mysql> show create table tbl_insert_tmp\G
*************************** 1. row ***************************
Table: tbl_insert_tmp
Create Table: CREATE TABLE `tbl_insert_tmp` (
`id` int(5) NOT NULL DEFAULT ‘0‘,
`addr_number` int(10) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `udx_addr_number` (`addr_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>
1、测试on duplicate key update(是根据主键或唯一键如果有对应键则更新,没有对应键就插入,所以使用起来不会删除正常数据)
mysql> insert into tbl_insert_tmp values(1,100,‘a‘),(2,200,‘b‘),(3,300,‘c‘);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 | 100 | a |
| 2 | 200 | b |
| 3 | 300 | c |
+----+-------------+------+
3 rows in set (0.00 sec)
mysql>
主键id和addr_number唯一键没有重复,直接插入
mysql> insert into tbl_insert_tmp(id,addr_number,name) values(4,400,‘d‘) on duplicate key update name=‘haha‘;
Query OK, 1 row affected (0.00 sec)
mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 | 100 | a |
| 2 | 200 | b |
| 3 | 300 | c |
| 4 | 400 | d |
+----+-------------+------+
4 rows in set (0.00 sec)
mysql>
因为id是主键有重复1直接更新
mysql> insert into tbl_insert_tmp(id,addr_number,name) values(1,500,‘d‘) on duplicate key update name=‘haha‘;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 | 100 | haha |
| 2 | 200 | b |
| 3 | 300 | c |
| 4 | 400 | d |
+----+-------------+------+
4 rows in set (0.00 sec)
因为addr_number唯一键有重复400直接更新
mysql> insert into tbl_insert_tmp(id,addr_number,name) values(5,400,‘d‘) on duplicate key update name=‘hehe‘;
Query OK, 2 rows affected (0.03 sec)
mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 | 100 | haha |
| 2 | 200 | b |
| 3 | 300 | c |
| 4 | 400 | hehe |
+----+-------------+------+
4 rows in set (0.00 sec)
mysql>
删除addr_number唯一键就可以直接插入重复值
mysql> alter table tbl_insert_tmp drop key udx_addr_number;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tbl_insert_tmp\G
*************************** 1. row ***************************
Table: tbl_insert_tmp
Create Table: CREATE TABLE `tbl_insert_tmp` (
`id` int(5) NOT NULL DEFAULT ‘0‘,
`addr_number` int(10) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>
mysql> insert into tbl_insert_tmp(id,addr_number,name) values(5,400,‘d‘) on duplicate key update name=‘hehe‘;
Query OK, 1 row affected (0.00 sec)
mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 | 100 | haha |
| 2 | 200 | b |
| 3 | 300 | c |
| 4 | 400 | hehe |
| 5 | 400 | d |
+----+-------------+------+
5 rows in set (0.00 sec)
mysql>
2、测试replace into (是根据主键或者唯一键先删除对应键值再插入,所以会删除正常数据,使用时要特别小心)
mysql> truncate table tbl_insert_tmp;
Query OK, 0 rows affected (0.03 sec)
mysql> show create table tbl_insert_tmp\G
*************************** 1. row ***************************
Table: tbl_insert_tmp
Create Table: CREATE TABLE `tbl_insert_tmp` (
`id` int(5) NOT NULL DEFAULT ‘0‘,
`addr_number` int(10) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table tbl_insert_tmp add unique key udx_addr_number(addr_number);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tbl_insert_tmp\G
*************************** 1. row ***************************
Table: tbl_insert_tmp
Create Table: CREATE TABLE `tbl_insert_tmp` (
`id` int(5) NOT NULL DEFAULT ‘0‘,
`addr_number` int(10) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `udx_addr_number` (`addr_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>
mysql>
mysql> insert into tbl_insert_tmp values(1,100,‘a‘),(2,200,‘b‘),(3,300,‘c‘),(4,400,‘d‘);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 | 100 | a |
| 2 | 200 | b |
| 3 | 300 | c |
| 4 | 400 | d |
+----+-------------+------+
4 rows in set (0.00 sec)
mysql>
mysql> replace into tbl_insert_tmp values(1,500,‘e‘);
Query OK, 2 rows affected (0.00 sec)
mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 | 500 | e |
| 2 | 200 | b |
| 3 | 300 | c |
| 4 | 400 | d |
+----+-------------+------+
4 rows in set (0.00 sec)
mysql>
mysql> replace into tbl_insert_tmp values(2,500,‘f‘);
Query OK, 3 rows affected (0.00 sec)
mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 2 | 500 | f |
| 3 | 300 | c |
| 4 | 400 | d |
+----+-------------+------+
3 rows in set (0.00 sec)
mysql>
mysql> replace into tbl_insert_tmp values(5,500,‘h‘);
Query OK, 2 rows affected (0.00 sec)
mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 3 | 300 | c |
| 4 | 400 | d |
| 5 | 500 | h |
+----+-------------+------+
3 rows in set (0.00 sec)
mysql>
删除唯一键udx_addr_number
mysql> alter table tbl_insert_tmp drop key udx_addr_number;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> replace into tbl_insert_tmp values(1,500,‘g‘);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 | 500 | g |
| 3 | 300 | c |
| 4 | 400 | d |
| 5 | 500 | h |
+----+-------------+------+
4 rows in set (0.00 sec)
mysql>
mysql> replace into tbl_insert_tmp values(2,300,‘i‘);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 | 500 | g |
| 2 | 300 | i |
| 3 | 300 | c |
| 4 | 400 | d |
| 5 | 500 | h |
+----+-------------+------+
5 rows in set (0.00 sec)
mysql> replace into tbl_insert_tmp values(2,200,‘b‘);
Query OK, 2 rows affected (0.00 sec)
mysql> select * from tbl_insert_tmp ;
+----+-------------+------+
| id | addr_number | name |
+----+-------------+------+
| 1 | 500 | g |
| 2 | 200 | b |
| 3 | 300 | c |
| 4 | 400 | d |
| 5 | 500 | h |
+----+-------------+------+
5 rows in set (0.00 sec)
mysql>
本文出自 “心愿” 博客,请务必保留此出处http://xinyuan8.blog.51cto.com/677906/1655416
mysql的replace into和on duplicate key update测试笔记
标签:mysql的replace into和on duplicate key update测试笔记
本文系统来源:http://xinyuan8.blog.51cto.com/677906/1655416
内容总结
以上是互联网集市为您收集整理的mysql的replace into和on duplicate key update测试笔记全部内容,希望文章能够帮你解决mysql的replace into和on duplicate key update测试笔记所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。