mysql中primary key重复时的处理办法_最火下载站
您的位置： >
> mysql中primary key重复时的处理办法
mysql中primary key重复时的处理办法
当insert进数据表, 发生唯一key(unique key与primary key)重复时, 会发生duplicate key错误. 这种情况有三种处理方法, 以下面的数据结构为例子 mysql& mysql& create table `user` (`userid` int(11) DEFAULT NULL, `username` varchar(255) NOT NULL DEFAULT ''); 给加上userid列primary key mysql& alter table `user` add primary key `userid` (`userid`); 插入数据 mysql& insert into `user` values (1, 'eric'), (2, 'jesus'); 现在我要插入或者编辑userid为1的记录, 但我不知道里面是否已经存在该记录. 1, 先删除再插入之 mysql& delete from user where userid = 1; mysql& insert into user values (1, 'xxxxx') ; 2, 使用replace into mysql& replace into user values (1, 'newvalue'); 这种情况下逻辑是这样的, mysql先判断记录是否存在, 若存在则先删除之, 再自行insert. 所以你能看到这条语句执行后affected rows是2条(当然前提是你的数据表里userid为1的数据只有1条) 3, 使用insert into ... on duplicate key update mysql& insert into user1 values (1, 'newvalueagain') on duplicate key update user1.username = VALUES(username); 这条语句的affected rows也是2. 当然还有另外的处理方式就是直接用php来实现, 先select出来, 发现没结果则insert, 否则update. 还可以先update, 发现affected rows是0, 则insert. 但明显这俩种办法都没有把工作直接交给mysql处理效率高
上一篇： 下一篇：mysql中key 、primary key 、unique key 与index区别 - ITeye问答
请详细说明下 mysql中key 、primary key 、unique key 与index区别
采纳的答案
参考下这个链接：/blog/1697043
已解决问题
未解决问题Bad Request (Invalid Hostname) 域名指向错误一、前言根据前一篇博客，知道了mysql中index和key在理论上的区别，但是总是很迷糊。现在不管理论里，在实践中看看吧。二、代码实践新建一张User表，包含字段id, name。（1）第一种情况：mysql& create table user(id int, name varchar(50), age int, primary key(id));Query OK, 0 rows affected (0.01 sec)mysql& sh+-------+------------------------------------------------------------------------------+| Table | Create Table& & & & & & & & & & & & & & & & & & & & & |+-------+------------------------------------------------------------------------------+| user &| CREATE TABLE `user` (& `id` int(11) NOT NULL default '0',& `name` varchar(50) default NULL,& `age` int(11) default NULL,& PRIMARY KEY &(`id`)) ENGINE=InnoDB DEFAULT CHARSET=gbk |mysql& +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null | Index_type | Comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| user &| & & & & &0 | PRIMARY &| & & & & & &1 | id & & & & &| A & & & & | & & & & & 0 | & & NULL | NULL & | & & &| BTREE & & &| & & & & |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+1 row in set (0.00 sec)mysql&+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null | Index_type | Comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| user &| & & & & &0 | PRIMARY &| & & & & & &1 | id & & & & &| A & & & & | & & & & & 0 | & & NULL | NULL & | & & &| BTREE & & &| & & & & |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+1 row in set (0.00 sec)（2）第二种情况：mysql& create table user(id int, name varchar(50), age int, key(id));Query OK, 0 rows affected (0.01 sec)mysql& sh+-------+------------------------------------------------------------+| Table | Create Table& & & & & & & & & & & & & & & |+-------+------------------------------------------------------------+| user &| CREATE TABLE `user` (& `id` int(11) default NULL,& `name` varchar(50) default NULL,& `age` int(11) default NULL,& KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=gbk |mysql& +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null | Index_type | Comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| user &| & & & & &1 | id & & & | & & & & & &1 | id & & & & &| A & & & & | & & & & & 0 | & & NULL | NULL & | YES &| BTREE & & &| & & & & |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+1 row in set (0.00 sec)mysql&+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null| Index_type | Comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| user &| & & & & &1 | id & & & | & & & & & &1 | id & & & & &| A & & & & | & & & & & 0 | & & NULL | NULL & | YES &| BTREE & & &| & & & & |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+1 row in set (0.00 sec)（3）第三种情况：mysql& create table user(id int, name varchar(50), age int, index(id));Query OK, 0 rows affected (0.01 sec)mysql& sh+-------+-----------------------------------------------------------+| Table | Create Table& & & & & & & & & & & & & & & |+-------+-----------------------------------------------------------+| user &| CREATE TABLE `user` (& `id` int(11) default NULL,& `name` varchar(50) default NULL,& `age` int(11) default NULL,& KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=gbk |mysql& +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |Null| Index_type | Comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| user &| & & & & &1 | id & & & | & & & & & &1 | id & & & & &| A & & & & | & & & & & 0 | & & NULL | NULL & |YES &| BTREE & & &| & & & & |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+1 row in set (0.00 sec)mysql&+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| user &| & & & & &1 | id & & & | & & & & & &1 | id & & & & &| A & & & & | & & & & & 0 | & & NULL | NULL & |YES &| BTREE & & &| & & & & |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+1 row in set (0.00 sec)三、总结（1）当建表时，建立primary key的键，同时默认建立对应的index（2）当建表时，指定某列为key时，那么同时为该键建立index，key和index对应的键允许null（3）当建表时，指定某列为index时，那么同时为该键建立key，index和key对应的键允许null。从建表语句中可以看出key ‘id’ (id)，等价于（2）中的情况。& 根据（1）（2）（3）说明在以上的使用情况中，index和key没有什么区别。
最新教程周点击榜
微信扫一扫mysql sql 语句中同时定义 PRIMARY KEY UNIQUE KEY 多余么？
mysql sql 语句中同时定义 PRIMARY KEY UNIQUE KEY 多余么？sql 如下 我总觉得 UNIQUE KEY `KEYTYPE` (`key`,`type`,`datatype`) USING BTREE 多余，默认情况下主键是不是已经有了聚集索引？
CREATE TABLE `data_{n}` (
`key` char(32) COLLATE utf8_bin NOT NULL,
`type` varchar(30) COLLATE utf8_bin NOT NULL,
`datatype` tinyint NOT NULL,
`headersdata` blob NOT NULL,
`bodydata` mediumblob NOT NULL,
`createddate` datetime NOT NULL,
`expiresabsolute` datetime NOT NULL,
`seconds` int(11)
NOT NULL DEFAULT (0),
PRIMARY KEY (`key`,`type`,`datatype`),
UNIQUE KEY `KEYTYPE` (`key`,`type`,`datatype`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC;
主键唯一性
从楼主举的例子里来说这个UNIQUE
KEY是多余的，PRIMARY
KEY本身就是UNIQUE
引用来自“一路有风”的评论从楼主举的例子里来说这个UNIQUE
KEY是多余的，PRIMARY
KEY本身就是UNIQUE
KEY。那主键是已经包含了索引么？
引用来自“一路有风”的评论从楼主举的例子里来说这个UNIQUE
KEY是多余的，PRIMARY
KEY本身就是UNIQUE
KEY。引用来自“foxidea”的评论那主键是已经包含了索引么？是的，主键是肯定包含了索引的