mysql创建表、添加字段时，可重复执行sql 说明

爱被打了一巴掌 2023-10-05 14:49 12阅读 0赞

## 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';

![20201201112424549.png][]

执行第一条记录时：

这里受影响的行数是`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。

[20201201112424549.png]: https://img-blog.csdnimg.cn/20201201112424549.png