mysql创建表、添加字段时,可重复执行sql 说明
1、创建表1.1、表存在,原表数据作废:如果表存在,删除表结构,添加新数据,sql如下:-- 如果表存在,则删除DROP TABLE IF EXISTS person;-- 创建表CREATE TABLE `person` (`USER_ID` varchar(60) NOT NULL,`USER_NAME` varchar(60) DEFAULT NULL,`GENDER` char(1) DE
1、创建表
1.1、表存在,原表数据作废:
如果表存在,删除表结构,添加新数据,sql如下:
-- 如果表存在,则删除
DROP TABLE IF EXISTS person;
-- 创建表
CREATE TABLE `person` (
`USER_ID` varchar(60) NOT NULL,
`USER_NAME` varchar(60) DEFAULT NULL,
`GENDER` char(1) DEFAULT NULL,
`BIRTH_DATE` datetime DEFAULT NULL,
`EDUCATION_DEGREE` varchar(60) DEFAULT NULL,
PRIMARY KEY (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 添加数据
INSERT INTO `person`(`USER_ID`, `USER_NAME`, `GENDER`, `BIRTH_DATE`, `EDUCATION_DEGREE`) VALUES ('100001', '用户100001', '1', '1986-05-12 00:00:00', '20');
INSERT INTO `person`(`USER_ID`, `USER_NAME`, `GENDER`, `BIRTH_DATE`, `EDUCATION_DEGREE`) VALUES ('100002', '用户100002', '1', '1986-05-12 00:00:00', '20');
INSERT INTO `person`(`USER_ID`, `USER_NAME`, `GENDER`, `BIRTH_DATE`, `EDUCATION_DEGREE`) VALUES ('100003', '用户100003', '1', '1986-05-12 00:00:00', '20');
INSERT INTO `person`(`USER_ID`, `USER_NAME`, `GENDER`, `BIRTH_DATE`, `EDUCATION_DEGREE`) VALUES ('100004', '用户100004', '1', '1986-05-12 00:00:00', '20');
1.2、表存在,原表数据不做处理
如果表存在,原数据不动,添加新数据,sql如下:
-- 表不存在时,进行创建
CREATE TABLE IF NOT EXISTS `person` (
`USER_ID` varchar(60) NOT NULL,
`USER_NAME` varchar(60) DEFAULT NULL,
`GENDER` char(1) DEFAULT NULL,
`BIRTH_DATE` datetime DEFAULT NULL,
`EDUCATION_DEGREE` varchar(60) DEFAULT NULL,
PRIMARY KEY (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
添加新数据时,存在的问处理如下:
1.2.1、避免重复插入
关键字/句:insert ignore into,如果插入的数据会导致UNIQUE索引或PRIMARY KEY发生冲突/重复,则忽略此次操作/不插入数据。示例如下:
INSERT IGNORE INTO `person`(`USER_ID`, `USER_NAME`, `GENDER`, `BIRTH_DATE`, `EDUCATION_DEGREE`) VALUES ('100001', '用户100001', '1', '1986-05-12 00:00:00', '20');
1.2.2、重复插入以新插入数据为准:
关键字/句:REPLACE INTO
如果插入的数据会导致UNIQUE 索引
或PRIMARY KEY
发生冲突/重复,则先删除旧数据再插入最新的数据,示例如下:
REPLACE语句会返回一个数,来指示受影响的行的数目。该数是被删除和被插入的行数的和。
如果对于一个单行REPLACE该数为1,则一行被插入,同时没有行被删除。
如果该数大于1,则在新行被插入前,有一个或多个旧行被删除。如果表包含多个唯一索引,并且新行复制了在不同的唯一索引中的不同旧行的值,则有可能是一个单行替换了多个旧行。
受影响的行数可以容易地确定是否REPLACE只添加了一行,或者是否REPLACE也替换了其它行:检查该数是否为1(添加)或更大(替换)。
REPLACE INTO `person`(`USER_ID`, `USER_NAME`, `GENDER`, `BIRTH_DATE`, `EDUCATION_DEGREE`) VALUES ('100001', '用户100001', '1', '1986-05-12 00:00:00', '30');
-- 相当于:
if not exists (select 1 from person where USER_ID= '100001') insert into person(USER_ID, USER_NAME, GENDER, BIRTH_DATE, EDUCATION_DEGREE) VALUES ('100001', '用户100001', '1', '1986-05-12 00:00:00', '30')
else update person set USER_NAME= '用户100001',GENDER='1',BIRTH_DATE = '1986-05-12 00:00:00',EDUCATION_DEGREE='30' where USER_ID = '100001';
执行第一条记录时:
这里受影响的行数是2
,因为数据库中存在user_id='100001'
的数据,并且education_degree的值会变成30
,因为它是先删除旧数据,然后再插入数据。
执行第二条记录是:
这里受影响的行数是1
,因为数据库中存在user_id='100001'
的数据,其它中没有变动,因为它是先删除旧数据,然后再插入数据。
1.2.3、不存在则插入,存在则更新
on duplicate key update 为Mysql特有语法,当insert已经存在的记录时,执行Update。
如果插入的数据会导致UNIQUE 索引
或PRIMARY KEY
发生冲突/重复,则执行UPDATE
语句。例:
insert into person(USER_ID, USER_NAME, GENDER, BIRTH_DATE, EDUCATION_DEGREE) VALUES ('100001', '用户100001', '1', '1986-05-12 00:00:00', '30') on duplicate key update EDUCATION_DEGREE = '30'
如果行作为新记录被插入,则受影响的行为1;
如果原有记录被更新,则受影响行为2;
如果原有记录已存在,但是更新的值和原有值相同,则受影响行为0。
更多推荐
所有评论(0)