①唯一约束
CREATE TABLE `login` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
当执行同样的name的数据时则会报如下错误
②insert ignore into
插入数据时,如果数据存在,则忽略此次插入
INSERT ignore INTO login(`name`,`password`) VALUES("ganhuojun","password")
③replace into
插入数据时,如果数据存在,则删除再插入
REPLACE INTO login(`name`,`password`) VALUES("ganhuojun","password")
执行后发现,2行生效,其中一行删除,1行新增
④insert if not exists
sql的语法为insert into … select … where not exist …,该语句先判断mysql数据库中是否存在这条数据,如果不存在,则正常插入,如果存在,则忽略
INSERT INTO login (`name`, `password`)
SELECT 'ganhuojun',
'password'
FROM login
WHERE NOT EXISTS(
SELECT `name`
FROM login
WHERE `name` = 'ganhuojun'
)
⑤on duplicate key update
插入数据时,如果数据存在,则执行更新操作
INSERT INTO login (`name`, `password`)
VALUES ("ganhuojun", "password")
ON DUPLICATE KEY UPDATE PASSWORD = 'passwd'