mysql的replace语句,很多同学都用过,有个细节,不是谁都能说的清楚.
mysql中的replace实验:
1.创建表
CREATE TABLE `test` (
`id` bigint(20) NOT NULL DEFAULT '0',
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
2.插入数据,看结果.
insert into test(id,name,age) values(2,'gb',23);
replace into test(id,name) values(2,'xx');
root@beidou 04:22:01>select * from test ;
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | xx | NULL |
+----+------+------+
1 row in set (0.00 sec)
OceanBase中replace语句的实验:
userview@(none) 03:47:39>CREATE TABLE test (
-> id int,
-> name varchar(20),
-> age int,
-> PRIMARY KEY (id)
-> );
Query OK, 1 row affected (0.41 sec)
userview@(none) 04:05:06>insert into test(id,name,age) values(2,'gb',23);
Query OK, 1 row affected (0.01 sec)
userview@(none) 04:05:17>replace into test(id,name) values(2,'xx');
Query OK, 1 row affected (0.00 sec)
userview@(none) 04:05:30>select * from test;
+------+------+------+
| id | name | age |
+------+------+------+
| 2 | xx | 23 |
+------+------+------+
1 row in set (0.01 sec)
总结:
1.mysql的replace 语句,在源码层,走的是delete后insert. 只要遇到 唯一约束的错误,就会 删除该行,然后插入新行.
源码层的分析,后面附上.
2.OceanBase在updateServer,即那个内存大的机器,使用稠密表存储,而mysql 是稀疏表,故在updateServer上,可以按照列来更新.
这种方式更符合用户的期望,给使用带来便捷。