insert into
"user"(
display_name,
telephone,
address,
username,
"password")
select
'王凯歌',
null,
null,
'wangkaige',
'$2a$10$i2Wwn3AFp8t'
where
not exists(
select
id
from
"user"
where
username ='wangkaige'
and delete_flag = false );
该句分两个段落进行解析:
第一个段落为:
select
'王凯歌',
null,
null,
'wangkaige',
'$2a$10$i2Wwn3AFp8t'
where
not exists(
select
id
from
"user"
where
username ='wangkaige'
and delete_flag = false )
where
子句后跟的是一段判断表达式,如当我们执行
select * from "user" where true
-- 或 select * from "user" where 1=1
时,将恒成立地查询所有的数据。
not exists(
select
id
from
"user"
where
username ='wangkaige'
and delete_flag = false )
这段代码判断的是是否存在一个username='wangkaige'
并且未被删除的记录。当不存在时,将返回空白记录,因此not exists
判断成功。这个时候sql片段
select
'王凯歌',
null,
null,
'wangkaige',
'$2a$10$i2Wwn3AFp8t'
where
not exists(
select
id
from
"user"
where
username ='wangkaige'
and delete_flag = false )
将返回一行记录
?column? | ?column? | ?column? | ?column? | ?column? |
---|---|---|---|---|
王凯歌 | null | null | wangkaige | $2a$10$i2Wwn3AFp8t |
这行记录的值将作为insert
语句values
添加到库中去。这里的表头不用关心,没关系的。
当原来查询的记录存在时,自行脑补一下。
第二个段落
第二个段落就是insert
语句段落了,再粘一遍
insert into
"user"(
display_name,
telephone,
address,
username,
"password")
select
'王凯歌',
null,
null,
'wangkaige',
'$2a$10$i2Wwn3AFp8t'
where
not exists(
select
id
from
"user"
where
username ='wangkaige'
and delete_flag = false );