使用 INSERT INTO ... ON DUPLICATE KEY UPDATE 时的 Mysql 主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4343821/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql primary key when using INSERT INTO ... ON DUPLICATE KEY UPDATE
提问by webdev_007
My table structure is:
我的表结构是：
CREATE TABLE IF NOT EXISTS `users_settings_temp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(10) unsigned DEFAULT NULL,
`type` enum('type1','type2')
`data` text,
`date_created` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
What I am trying to do is:
我想做的是：
Let say I want to insert a new entry, but I dont want it to be duplicate, after google around, i found this format:
假设我想插入一个新条目，但我不希望它重复，在谷歌搜索之后，我发现了这种格式：
INSERT INTO users_settings_temp(...)
ON DUPLICATE KEY UPDATE data = '{$data}'
I guess the problem is in my table, the primary key => id. How do I alter the table, so that I could use the:
我猜问题出在我的表中，主键 => id。如何更改表格，以便我可以使用：
INSERT INTO ... ON DUPLICATE KEY UPDATE
Can I use user_id + type as primary key? If yes, could you please show me how to do it?
我可以使用 user_id + type 作为主键吗？如果是的话，你能告诉我怎么做吗？
回答by Unreason
CREATE TABLE IF NOT EXISTS `users_settings_temp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(10) unsigned DEFAULT NULL,
`type` enum('type1','type2'),
`data` text,
`date_created` int(11) DEFAULT NULL,
PRIMARY KEY (`id`, `type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
When you do it like this then
当你这样做时
a) specifying id works
a) 指定 id 作品
mysql> INSERT INTO users_settings_temp VALUES (1, 2, 'type1', 'keks', 5);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO users_settings_temp VALUES (1, 2, 'type2', 'keks', 5);
Query OK, 1 row affected (0.00 sec)
b) of course primary key is guaranteed to be unique
b) 当然主键保证是唯一的
mysql> INSERT INTO users_settings_temp VALUES (1, 2, 'type2', 'keks', 5);
ERROR 1062 (23000): Duplicate entry '1-type2' for key 'PRIMARY'
c) letting database pull a new id works
c) 让数据库拉取一个新的 id 有效
mysql> INSERT INTO users_settings_temp VALUES (NULL, 2, 'type2', 'keks', 5);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO users_settings_temp VALUES (NULL, 2, 'type1', 'keks', 5);
Query OK, 1 row affected (0.00 sec)
but will increase them always
但总是会增加它们
mysql> SELECT * FROM users_settings_temp;
+----+--------+-------+------+--------------+
| id | userid | type | data | date_created |
+----+--------+-------+------+--------------+
| 1 | 2 | type1 | keks | 5 |
| 1 | 2 | type2 | keks | 5 |
| 2 | 2 | type2 | keks | 5 |
| 3 | 2 | type1 | keks | 5 |
+----+--------+-------+------+--------------+
4 rows in set (0.00 sec)
NOTES:
笔记：
You should think if your idshould still be autoincrement or not.
Also, can not think of a reason why date_createdshould be int(11)instead of datetime
您应该考虑是否id仍然应该自动增量。还有，想不出为什么date_created要int(11)代替datetime
