mysql插入重复的FIELD而不是KEY
------------------------------------- | user_id | user_name | user_visits | ------------------------------------- | 1 | foo | 5 | ------------------------------------- | 2 | bar | 12 | -------------------------------------
user_id：自动递增，user_visits：默认1
INSERT INTO table (user_name) VALUES ('baz'), ('bar'), ('qux');
上面的语句当然会插入 3 条新记录，结果是：
------------------------------------- | user_id | user_name | user_visits | ------------------------------------- | 1 | foo | 5 | ------------------------------------- | 2 | bar | 12 | ------------------------------------- | 3 | baz | 1 | ------------------------------------- | 4 | bar | 1 | ------------------------------------- | 5 | qux | 1 | -------------------------------------
但我想要实现的是：
------------------------------------- | user_id | user_name | user_visits | ------------------------------------- | 1 | foo | 5 | ------------------------------------- | 2 | bar | 13 | ------------------------------------- | 3 | baz | 1 | ------------------------------------- | 4 | qux | 1 | -------------------------------------
从字面上看，
如果字段 user_name 存在，则更新 user_visits，否则插入一条新记录。
是否可以通过单个插入语句来实现这一点？
------------------------------------- | user_id | user_name | user_visits | ------------------------------------- | 1 | foo | 5 | ------------------------------------- | 2 | bar | 12 | -------------------------------------
user_id: auto increament, user_visits: default 1
INSERT INTO table (user_name) VALUES ('baz'), ('bar'), ('qux');
the above statement will of course insert 3 new records, as the result:
------------------------------------- | user_id | user_name | user_visits | ------------------------------------- | 1 | foo | 5 | ------------------------------------- | 2 | bar | 12 | ------------------------------------- | 3 | baz | 1 | ------------------------------------- | 4 | bar | 1 | ------------------------------------- | 5 | qux | 1 | -------------------------------------
but what I'm trying to achieve is:
------------------------------------- | user_id | user_name | user_visits | ------------------------------------- | 1 | foo | 5 | ------------------------------------- | 2 | bar | 13 | ------------------------------------- | 3 | baz | 1 | ------------------------------------- | 4 | qux | 1 | -------------------------------------
so literally,
if field user_name exists, update user_visits, else insert a new record.
is it possible to achieve this with a single insert statement?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
当然有，但它与您的
insert语句无关。您需要在user_name列上添加唯一索引：然后在跟踪计数的代码中必须决定是进行插入还是更新。
Sure there is but it has nothing to do with your
insertstatement. You need to add a unique index on theuser_namecolumn:Then afterward in your code that tracks the count will have to decide whether to do an insert or an update.
您必须为用户名字段创建一个键，然后使用
INSERT ON DUPLICATE查询来更新列值。例如，您的查询必须是，
如需进一步参考，请访问 http: //dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
You have to create a key for your username field and then use
INSERT ON DUPLICATEquery to update the columns values.For example your query must be,
For further reference visit http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
您可以按原样保留 INSERT 语句并实现一个处理特殊操作的触发器。
请参阅：触发器简介
You could leave your INSERT statement as is and implement a trigger that handles your special actions.
See: An Introduction To Triggers