Right now, I have my database setup so that I insert a new row. But if the row I'm trying to insert is a duplicate (based off of my primary key id), then I increment the count field by 1.
现在,我有我的数据库设置,以便我插入一个新行。但是,如果我尝试插入的行是重复的(基于我的主键ID),那么我将计数字段增加1。
Right now I want to know when I was able to create a new row instead of having it increment count. How would I do this in an efficient/proper manner?
现在我想知道什么时候我能够创建一个新行而不是增加计数。我如何以有效/适当的方式做到这一点?
The current method I'm thinking of doing this, is by querying the id first and checking if it exists. But I feel like there's a faster/better way. I've also read about triggers but I've heard that they're bad/risky to use.
我正在考虑这样做的当前方法是首先查询id并检查它是否存在。但我觉得有更快/更好的方式。我也读过有关触发器但我听说它们使用起来很糟糕/有风险。
4 个解决方案
#1
2
Use INSERT ... ON DUPLICATE KEY UPDATE... Then query for affected_rows (as @Tarek Fadel suggested). MySQL will return 1 if the row was inserted, or 2 if existing row were updated.
使用INSERT ... ON DUPLICATE KEY UPDATE ...然后查询affected_rows(如@Tarek Fadel建议的那样)。如果插入行,MySQL将返回1,如果更新现有行,则返回2。
#2
0
Use your database AUTO INCREMENT option for your primary ID field. Only propper solution.
对主ID字段使用数据库AUTO INCREMENT选项。只有适当的解决方案。
Here you have mysql reference, but that exist in just every database engine: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
这里有mysql引用,但只存在于每个数据库引擎中:http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
#3
0
How about an auto_increment on the id column?
id列上的auto_increment怎么样?
Otherwise you might use SELECT MAX(id) FROM TABLE to retreive the highest id and add one to it, but that isn't thread-safe since another user might execute the same insert at the same time. Auto_increment fixes that for you.
否则,您可以使用SELECT MAX(id)FROM TABLE来检索最高ID并向其添加一个,但这不是线程安全的,因为另一个用户可能同时执行相同的插入。 Auto_increment为您修复了这个问题。
#1
2
Use INSERT ... ON DUPLICATE KEY UPDATE... Then query for affected_rows (as @Tarek Fadel suggested). MySQL will return 1 if the row was inserted, or 2 if existing row were updated.
使用INSERT ... ON DUPLICATE KEY UPDATE ...然后查询affected_rows(如@Tarek Fadel建议的那样)。如果插入行,MySQL将返回1,如果更新现有行,则返回2。
#2
0
Use your database AUTO INCREMENT option for your primary ID field. Only propper solution.
对主ID字段使用数据库AUTO INCREMENT选项。只有适当的解决方案。
Here you have mysql reference, but that exist in just every database engine: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
这里有mysql引用,但只存在于每个数据库引擎中:http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
#3
0
How about an auto_increment on the id column?
id列上的auto_increment怎么样?
Otherwise you might use SELECT MAX(id) FROM TABLE to retreive the highest id and add one to it, but that isn't thread-safe since another user might execute the same insert at the same time. Auto_increment fixes that for you.
否则,您可以使用SELECT MAX(id)FROM TABLE来检索最高ID并向其添加一个,但这不是线程安全的,因为另一个用户可能同时执行相同的插入。 Auto_increment为您修复了这个问题。
#4
0
PHP's mysql_affected_rows()