创建表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(32) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`userName` varchar(32) NOT NULL COMMENT '用户名',
`passWord` varchar(50) NOT NULL COMMENT '密码',
`realName` varchar(32) DEFAULT NULL COMMENT '真实名',
`is_settlement` tinyint(1) DEFAULT '0' COMMENT '结算状态',
`uniq1` varchar(255) NOT NULL,
`uniq2` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq` (`uniq1`,`uniq2`) USING BTREE COMMENT '联合唯一索引'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
表插入数据
INSERT INTO `user` (`userName`, `passWord`, `realName`, `is_settlement`, `uniq1`, `uniq2`) VALUES ('1', '1', '1', '0', '1', '1');
INSERT INTO `user` (`userName`, `passWord`, `realName`, `is_settlement`, `uniq1`, `uniq2`) VALUES ('2', '2', '3', '0', '2', '2');
INSERT INTO `user` (`userName`, `passWord`, `realName`, `is_settlement`, `uniq1`, `uniq2`) VALUES ('3', '3', '3', '0', '3', '3');
使用ON DUPLICATE KEY UPDATE
利用联合唯一索引"uniq",当唯一索引存在时,更新记录中其他字段值;不存在时添加一条记录
INSERT INTO `user` (
`userName`,
`passWord`,
`realName`,
`is_settlement`,
`uniq1`,
`uniq2`
)
VALUES
(
'用户名',
'密码',
'真实名',
'0',
'1',
'1'
) ON DUPLICATE KEY UPDATE
`userName` = VALUES(`userName`),
`passWord` = VALUES(`passWord`),
`realName` = VALUES(`realName`);