Mysql脚本-存在数据/字段则更新/删除，不存在则新增字段/数据

前言：最近在维护脚本了，老是写数据的更新脚本或者是字段的更新脚本，并且脚本在同步给同事的时候要求能反复执行不报错，通过学习了一小段时间后，来做个总结，方便我后续回来复制粘贴

准备数据

-- 创建student表
create table student
(
    id   int auto_increment
        primary key,
    name varchar(32) null
);

INSERT INTO student (id, name) VALUES (1, '张三');
INSERT INTO student (id, name) VALUES (2, '李四');
INSERT INTO student (id, name) VALUES (3, '王五');
INSERT INTO student (id, name) VALUES (4, '赵六');

数据更新

数据更新一般以主键，索引或者唯一条件判断是否存在，假设现在我想更新赵六这条数据，如果存在则更新name变成赵六1

1.如果存在数据则更新，不存在则新增

insert into 表名(字段1,字段2) VALUES(值1,值2)
ON DUPLICATE KEY UPDATE 字段=更新的值

• 样例

insert into student(id,name) VALUES(4, '赵六')
ON DUPLICATE KEY UPDATE name='赵六1'

如上语句可以理解为如果存在主键为4的数据，则更新name为’赵六1’，如果不存在则新增{id=4, name=‘赵六’}的数据

2.如果存在数据则替换(先删除后添加)，不存在则新增

REPLACE INTO 表名(字段1,字段2)
VALUES(值A,值B);

• 样例

REPLACE INTO student(id,name)
VALUES(4,'赵六1');

如上语句可以理解为如果存在主键为4的数据，则删除然后新增一条{id=4, name=‘赵六’}的数据，不存在则新增

3.如果存在则忽略语句(什么都不做)，不存在则新增

INSERT IGNORE INTO 表名(字段1,字段2)
VALUES(值A,值B);

• 样例

INSERT IGNORE INTO student(id,name)
VALUES(4,'赵六');

如上语句可以理解为如果存在主键为4的数据，则什么都不做，如果不存在则新增一条{id=4, name=‘赵六’}的数据

虽然控制台会提示一条信息**Duplicate entry ‘4’ for key ‘student.PRIMARY’**但是这不妨碍继续执行语句（不会报错中断）

字段更新

字段更新可以利用存储过程，然后用if判断查出来的数量即可

DROP PROCEDURE IF EXISTS proc_tempPro;
CREATE PROCEDURE proc_tempPro()
BEGIN
    SELECT count(*) into @count FROM INFORMATION_SCHEMA.Columns 
    WHERE table_schema= DATABASE() AND table_name='表名' AND column_name='旧列名';
    if(@count>0) THEN
     -- 你的字段更新语句放在这里
    end if;
end;
call proc_tempPro;
DROP PROCEDURE IF EXISTS proc_tempPro;

比如这里就是根据@count的大小判断是否存在，大于0即存在旧字段，小于等于0则不存在旧字段

也可以多写几条更新

DROP PROCEDURE IF EXISTS update_fields_to_student;
CREATE PROCEDURE update_fields_to_student()
BEGIN
    SELECT count(*) into @count1 FROM INFORMATION_SCHEMA.Columns
    WHERE table_schema=DATABASE() AND table_name='student' AND column_name='name';
    if(@count1 > 0) THEN
     -- 如果存在字段name则更新name字段变成name1字段
        alter table student change name name1 varchar(32) null;
    end if;
    SELECT count(*) into @count2 FROM INFORMATION_SCHEMA.Columns
    WHERE table_schema=DATABASE() AND table_name='student' AND column_name='name1';
    if(@count2 > 0) THEN
     -- 、如果存在name1字段则删除
        alter table student drop column name1;
    end if;
    SELECT count(*) into @count3 FROM INFORMATION_SCHEMA.Columns
    WHERE table_schema=DATABASE() AND table_name='student' AND column_name='name';
    if(@count3 <= 0) THEN
     -- 、如果不存在name字段则新增
        alter table student add name varchar(64) null;
    end if;
end;
call update_fields_to_student;
DROP PROCEDURE IF EXISTS update_fields_to_student;

根据具体情况编写alter语句

参考

mysql 判断字段否存在，如果存在就修改字段-CSDN博客

MySql数据库实现数据存在则更新，不存在则插入_mysql存在就更新不存在就添加-CSDN博客