什么时候使用replace into
在向表中插入数据的时候,经常遇到这样的情况:1. 首先判断数据是否存在; 2. 如果不存在,则插入;3.如果存在,则更新。
在 SQL Server 中可以这样处理:
if not exists (select 1 from t where id = 1)
insert into t(id, update_time) values(1, getdate())
else
update t set update_time = getdate() where id = 1
那么 mysql 中如何实现这样的逻辑呢?别着急 mysql中有更简单的方法: replace into
replace into t(id, update_time) values(1, now());
或
replace into t(id, update_time) select 1, now();
replace into原理
replace into 跟 insert 功能类似,不同点在于:replace into 首先尝试插入数据到表中,
如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据。 2. 否则没有此行数据的话,直接插入新数据。
replace into的应用注意事项
1)插入数据的表必须有主键或者是唯一索引!否则的话,replace into 会直接插入数据,这将导致表中出现重复的数据。
2)如果数据库里边有这条记录,则直接修改这条记录;如果没有则,则直接插入,在有外键的情况下,对主表进行这样操作时,因为如果主表存在一条记录,被从表所用时,直接使用replace into是会报错的,这和replace into的内部原理是相关(ps.它会先删除然后再插入)。
3)正确做法是- 即先删除该条存在的数据,然后再次插入这条数据,这和外键约束相悖呢,因此只能采用update和insert这样的组合,来应对外键约束
sql_select_1='''select * from one_and_two_stars where kn_id = %d ''' %( int(one_level_id))
res_num_1= self.execute_kg(sql_select_1)
if res_num_1 > 0:
# 修改该条记录
sql_update_one_and_two_stars='''update one_and_two_stars set kn_name = %s, parent_kn_id = %s where kn_id = %s''' ("'"+str(kn_name_1)+"'", str(parent_kn_id_1), int(one_level_id))
self.execute_kg(sql_update_one_and_two_stars)
self.commit_kg()
else:
# 直接插入这条数据
sql_insert_one_and_two_stars= '''insert into one_and_two_stars(kn_id,kn_name,parent_kn_id,ctime)
values('%s','%s','%s','%s')
''' % (str(one_level_id), str(kn_name_1),str(parent_kn_id_1),str(dt))
self.execute_kg(sql_insert_one_and_two_stars)
self.commit_kg()
replace into的使用形式
MySQL replace into 有三种形式:
1) replace into tbl_name(col_name, ...) values(...)
2) replace into tbl_name(col_name, ...) select ...
3) replace into tbl_name set col_name=value, ...
前两种形式用的多些。其中 “into” 关键字可以省略,不过最好加上 “into”,这样意思更加直观。另外,对于那些没有给予值的列,MySQL 将自动为这些列赋上默认值。
语法:replace into table( col1, col2, col3 ) values ( val1, val2, val3 )语义:向table表中col1, col2, col3列replace数据val1,val2,val3
先讲讲组合(多列)索引是什么
组合索引的生效原则是 从前向后依次生效,如果中间某个索引没有使用, 那么断点前面的索引部分起作用,断点后面的索引没有起作用,即最左优先原则
例如创建多列索引(a,b,c)
where a=3 and b=45 and c=5...
这种三个索引顺序使用中间没有断点,全部发挥作用;
where a=3 and c=5...
这种情况下b就是断点,a发挥了效果,c没有效果;
where b=3 and c=4...
这种情况下a就是断点,在a后面的索引都没有发挥作用,这种写法联合索引没有发挥任何效果;
where b=45 and a=3 and c=5...
这个跟第一个一样,全部发挥作用,abc只要用上了就行,跟写的顺序无关;
如何创建组合索引
语法:CREATE UNIQUE INDEX index ON table( col1, col2, col3 )
实例:CREATE UNIQUE INDEX index_unique ON app(pkgName,version,device,osver)
很简单是不是,但是我还是遇到了两个坑,浪费了我一下午,第一个坑就是创建的索引长度是有限制的,不能超过767,发现字段的长度也用不了varchar(256)于是改成了varchar(100)。
你以为这样就OK了,NAIVE!第二个坑,“error: Duplicate etry ~”,英文不好真的很费劲,我翻译出来“重复实例”,却还是没能理解是什么意思,还一味的排除sql语句的错误。。。直到逛了外网论坛才发现是表的问题,因为在创建这个唯一索引的时候表中已经有很多重复数据了,所以很肯定创建不成功!
清空表数据,完美。。。