表(注意唯一约束)
-- ----------------------------
-- Table structure for star_platform
-- ----------------------------
DROP TABLE IF EXISTS `star_platform`;
CREATE TABLE `star_platform` (
`platform_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`platform_source` int(5) NOT NULL DEFAULT '0' COMMENT '平台编号',
`platform_source_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '平台名',
`note` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
PRIMARY KEY (`platform_id`),
UNIQUE KEY `unique_source` (`platform_source`,`platform_source_name`) USING BTREE COMMENT '用于约束平台唯一性'
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='平台(任务源)';
SET FOREIGN_KEY_CHECKS = 1;
-- 插入一条初始数据 (Affected rows: 0)
insert into star_platform (platform_source,platform_source_name) values (1,"平台名");
-- insert ignore into ... 忽略:忽略主键或唯一索引重复的数据,注意执行结果中的受影响行数 (Affected rows: 0)
insert IGNORE into star_platform (platform_source,platform_source_name) values (1,"平台名");
-- replace into ... 有重复数据则替换(删除再插入,注意看主键id值) 受影响行数(Affected rows: 2)
replace into star_platform (platform_source,platform_source_name) values (1,"平台名");
-- insert ... on duplicate key update ... 有重复数据则更新update后的数值 受影响行数(Affected rows: 2)
INSERT INTO star_platform (platform_source, platform_source_name, note)
VALUES ( 1, "平台名", "备注")
ON DUPLICATE KEY UPDATE platform_source_name = "平台名2",note = "备注2";
1、插入或忽略
insert ignore into ... :主键或唯一索引不重复,执行插入;重复,则不插入,但是会占用一个自增主键值(如:上一条数据主键id值为1,则 ignore 语句执行并忽略一条重复数据后,则再次insert时,id值会是3)
insert IGNORE into star_platform (platform_source,platform_source_name) values (1,"平台名")
2、插入或替换(删除并新增数据)
replace into ... :主键或唯一索引不重复,执行插入;重复,则先删除重复数据,然后重新插入新数据(主键值会改变)
replace into star_platform (platform_source,platform_source_name) values (1,"平台名")
3、插入或更新
insert ... on duplicate key update ... :主键或唯一索引不重复,则插入;重复,则修改UPDATE后的数据(主键值不变)
INSERT INTO star_platform (platform_source, platform_source_name, note)
VALUES ( 1, "平台名", "备注")
ON DUPLICATE KEY UPDATE platform_source_name = "平台名2",note = "备注2";
参考: