关闭

测试各场景下replace into的用法

328人阅读 评论(0) 收藏 举报
分类:

mysql版本:5.6.24

一.场景一:对没有主键或唯一键的表进行replace into操作

CREATE TABLE `student` (
  `sid` varchar(6) NOT NULL,
  `name` varchar(10) NOT NULL DEFAULT '',
  `class` varchar(10) NOT NULL DEFAULT '',
  `score` int(3) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into student(sid,name,class,score) value('0001','a','1',56),('0002','b','1',61),('0003','c','2',78),('0004','d','2',45),('0005','e','3',76);


(product)root@localhost [test]> select * from student;
+------+------+-------+-------+
| sid  | name | class | score |
+------+------+-------+-------+
| 0001 | a    | 1     |    56 |
| 0002 | b    | 1     |    61 |
| 0003 | c    | 2     |    78 |
| 0004 | d    | 2     |    45 |
| 0005 | e    | 3     |    76 |
+------+------+-------+-------+
5 rows in set (0.00 sec)

(product)root@localhost [test]> replace into student (sid,name,class,score) value('0001','f','3',66);
Query OK, 1 row affected (0.00 sec)

(product)root@localhost [test]>  select * from student;
+------+------+-------+-------+
| sid  | name | class | score |
+------+------+-------+-------+
| 0001 | a    | 1     |    56 |
| 0002 | b    | 1     |    61 |
| 0003 | c    | 2     |    78 |
| 0004 | d    | 2     |    45 |
| 0005 | e    | 3     |    76 |
| 0001 | f    | 3     |    66 |
+------+------+-------+-------+
6 rows in set (0.00 sec)

replace into 相当于insert into一样,将新记录直接插入到表中。

二.场景二:对有主键表进行replace into操作
CREATE TABLE `student` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sid` varchar(6) NOT NULL,
  `name` varchar(10) NOT NULL DEFAULT '',
  `class` varchar(10) NOT NULL DEFAULT '',
  `score` int(3) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(product)root@localhost [test]> insert into student(sid,name,class,score) value('0001','a','1',56),('0002','b','1',61),('0003','c','2',78),('0004','d','2',45),('0005','e','3',76);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

(product)root@localhost [test]> select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  1 | 0001 | a    | 1     |    56 |
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
+----+------+------+-------+-------+
5 rows in set (0.00 sec)

(product)root@localhost [test]> replace into student (sid,name,class,score) value('0001','f','3',66);
Query OK, 1 row affected (0.00 sec)

(product)root@localhost [test]> select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  1 | 0001 | a    | 1     |    56 |
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
|  6 | 0001 | f    | 3     |    66 |
+----+------+------+-------+-------+
6 rows in set (0.00 sec)

replace into 相当于insert into一样,将新记录直接插入到表中。

三.场景三:对有唯一键的表进行replace into操作

CREATE TABLE `student` (
  `sid` varchar(6) NOT NULL,
  `name` varchar(10) NOT NULL DEFAULT '',
  `class` varchar(10) NOT NULL DEFAULT '',
  `score` int(3) NOT NULL DEFAULT '0',
 unique (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
(product)root@localhost [test]> insert into student(sid,name,class,score) value('0001','a','1',56),('0002','b','1',61),('0003','c','2',78),('0004','d','2',45),('0005','e','3',76);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

(product)root@localhost [test]>  select * from student;
+------+------+-------+-------+
| sid  | name | class | score |
+------+------+-------+-------+
| 0001 | a    | 1     |    56 |
| 0002 | b    | 1     |    61 |
| 0003 | c    | 2     |    78 |
| 0004 | d    | 2     |    45 |
| 0005 | e    | 3     |    76 |
+------+------+-------+-------+
5 rows in set (0.00 sec)

(product)root@localhost [test]> replace into student (sid,name,class,score) value('0001','f','3',66);
Query OK, 2 rows affected (0.00 sec)

(product)root@localhost [test]> select * from student;
+------+------+-------+-------+
| sid  | name | class | score |
+------+------+-------+-------+
| 0001 | f    | 3     |    66 |
| 0002 | b    | 1     |    61 |
| 0003 | c    | 2     |    78 |
| 0004 | d    | 2     |    45 |
| 0005 | e    | 3     |    76 |
+------+------+-------+-------+
5 rows in set (0.00 sec)

由于用replace into插入的记录'0001'重复,所以replace操作会先将第一行'0001'的记录删除,再插入新记录。

四.场景四:对有主键和唯一键的表进行replace into操作

CREATE TABLE `student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sid` varchar(6) NOT NULL,
  `name` varchar(10) NOT NULL DEFAULT '',
  `class` varchar(10) NOT NULL DEFAULT '',
  `score` int(3) NOT NULL DEFAULT '0',
 primary key(`id`),
 unique (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
(product)root@localhost [test]> insert into student(sid,name,class,score) value('0001','a','1',56),('0002','b','1',61),('0003','c','2',78),('0004','d','2',45),('0005','e','3',76);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

(product)root@localhost [test]> select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  1 | 0001 | a    | 1     |    56 |
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
+----+------+------+-------+-------+
5 rows in set (0.00 sec)

(product)root@localhost [test]> replace into student (sid,name,class,score) value('0001','f','3',66);
Query OK, 2 rows affected (0.00 sec)

(product)root@localhost [test]> select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
|  6 | 0001 | f    | 3     |    66 |
+----+------+------+-------+-------+
5 rows in set (0.00 sec)
由于用replace into插入的记录'0001'重复,所以replace操作会先将第一行'0001'的记录删除,再插入新记录,注意自增主键值的变化,原来的id=1那行记录被删除了,新插入记录的id值为6。

所以在对有自增主键和唯一键存在的表进行replace into操作时,会有如下问题:

1.新记录与老记录的主键值不同,所以其他表中所有与本表老数据主键id建立的关联全部会被破坏。
2.频繁的replace into会造成新记录的主键的值迅速增大。当达到最大值后就会因为数据太大溢出,而没法再插入新记录。

五.场景五:对有主键和唯一键的表进行insert into+ON DUPLICATE KEY UPDATE操作
(product)root@localhost [test]> CREATE TABLE `student` (
    -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `sid` varchar(6) NOT NULL,
    ->   `name` varchar(10) NOT NULL DEFAULT '',
    ->   `class` varchar(10) NOT NULL DEFAULT '',
    ->   `score` int(3) NOT NULL DEFAULT '0',
    ->  primary key(`id`),
    ->  unique (`sid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

(product)root@localhost [test]> insert into student(sid,name,class,score) value('0001','a','1',56),('0002','b','1',61),('0003','c','2',78),('0004','d','2',45),('0005','e','3',76);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

(product)root@localhost [test]>  select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  1 | 0001 | a    | 1     |    56 |
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
+----+------+------+-------+-------+
5 rows in set (0.00 sec)

(product)root@localhost [test]> insert into student (sid,name,class,score) value('0001','f','3',66) ON DUPLICATE KEY UPDATE name=values(name),class=values(class),score=values(score);
Query OK, 2 rows affected (0.00 sec)

(product)root@localhost [test]> select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  1 | 0001 | f    | 3     |    66 |
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
+----+------+------+-------+-------+
5 rows in set (0.00 sec)

用insert into+on duplicate key update操作解决了场景四中遇到的问题,鉴于此,很多使用replace into 的场景,实际上需要的是insert into… on duplicate key update,
在正确理解replace into行为和副作用的前提下,谨慎使用replace into。


 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:152019次
    • 积分:3298
    • 等级:
    • 排名:第10747名
    • 原创:184篇
    • 转载:2篇
    • 译文:1篇
    • 评论:26条
    最新评论