即使在MySQL中截断表,也不应删除缺省值

bogh5gae  于 2022-09-18  发布在  Java
关注(0)|答案(1)|浏览(156)

我有一个registration表,其中还存储了super adminadmin以及其他用户的详细信息。我的问题是,即使我在该表上执行截断操作,也不应该删除super adminadmin条目。它必须始终作为默认条目存在。

这是我的登记表。

所以,我想用ID和密码冻结super adminadmin的条目,它不应该被更改。即使我当时正在创建一个新的数据库,我也不想为同样的内容做手工录入。

Stored procedure I tried

f0brbegy

f0brbegy1#

If you really want to truncate and still retain certain line, use a temporary table to keep the rows intended to stay. Then insert them back to the registration table. This can be done in a procedure. My apologies in advance as my page has trouble displaying the image so the base table name and all its column names are purely based on assumptions.

create table registration (id int primary key auto_increment,user varchar(20));
insert registration (user) values ('admin'),('root'),('super admin');
delimiter //
drop procedure if exists truncate_reg//
create procedure truncate_reg()
begin
drop temporary table if exists temp_registration;
create temporary table temp_registration select * from registration where user='admin' or user='super admin';
truncate registration ;
insert registration select * from temp_registration;

end//
delimiter ;
call truncate_reg;
select * from registration;
-- result:
1, admin
3, super admin

相关问题