mysql insert的几种方式

测试的库表

CREATE TABLE `user_info` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) DEFAULT NULL COMMENT '用户id',
  `business_id` varchar(50) DEFAULT NULL COMMENT ' 业务id',
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间',
  `update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8mb4 COMMENT='用户';

存在不插入,不存在再插入

# 如果 id=1 AND user_id=2 AND business_id=2这样的数据不存在，则插入数据
INSERT INTO user_info (id,user_id,business_id)
SELECT 
1,2,2
FROM DUAL 
WHERE NOT EXISTS(
    SELECT id FROM user_info WHERE id=1 AND user_id=2 AND business_id=2
    );

插入时，存在则更新

# id如果存在的话，就更新user_id和business_id的值为999和666

INSERT INTO user_info(id,user_id,business_id)
VALUES(1,999,666)
ON DUPLICATE KEY UPDATE user_id=VALUES(user_id),business_id=VALUES(business_id);

存在即更新，不存在即插入

replace into user_info (id,user_id, business_id) values(111,991,661);

查询插入

# 测试表以及数据
CREATE TABLE `test` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) DEFAULT NULL COMMENT '用户id',
  `business_id` VARCHAR(50) DEFAULT NULL COMMENT ' 业务id',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8mb4 COMMENT='用户';
INSERT INTO test(id,user_id,business_id) VALUES
(10,10,10),
(11,11,11),
(12,12,12);

# 查询插入
INSERT INTO user_info(id,user_id, business_id)
SELECT id,user_id,business_id FROM test LIMIT 100;

# 查询插入，并且存在就更新
INSERT INTO user_info(id,user_id, business_id)
SELECT id,user_id,business_id FROM test LIMIT 100
ON DUPLICATE KEY UPDATE user_id=VALUES(user_id),business_id=VALUES(business_id);