前言:最近在维护脚本了,老是写数据的更新脚本或者是字段的更新脚本,并且脚本在同步给同事的时候要求能反复执行不报错,通过学习了一小段时间后,来做个总结,方便我后续回来复制粘贴
准备数据
-- 创建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语句