所以我有下表:
mysql> show create table user_api_skills \G
*************************** 1. row ***************************
Table: user_api_skills
Create Table: CREATE TABLE `user_api_skills` (
`characterID` int(11) NOT NULL,
`typeID` int(11) NOT NULL,
`level` enum('0','1','2','3','4','5') NOT NULL DEFAULT '0',
`skillpoints` int(11) NOT NULL,
`currentTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`characterID`,`typeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
然后在该表中尝试插入/更新的行:
mysql> SELECT * FROM `user_api_skills` WHERE `characterID` =93192782 AND `typeID` =3359;
+-------------+--------+-------+-------------+---------------------+
| characterID | typeID | level | skillpoints | currentTime |
+-------------+--------+-------+-------------+---------------------+
| 93192782 | 3359 | 3 | 135765 | 2013-09-30 16:58:35 |
+-------------+--------+-------+-------------+---------------------+
1 row in set (0.00 sec)
我相信我的查询格式正确,执行时不会引发任何错误或警告:
mysql> INSERT INTO user_api_skills (characterID,typeID,level,skillpoints)
VALUES (93192782,3359,4,135765) ON DUPLICATE KEY UPDATE level=4,
skillpoints=135765,currentTime=NOW();
Query OK, 2 rows affected (0.22 sec)
我得到2行更新(正如我希望在dup更新中插入)
mysql> SELECT * FROM `user_api_skills` WHERE `characterID` =93192782 AND `typeID` =3359;
+-------------+--------+-------+-------------+---------------------+
| characterID | typeID | level | skillpoints | currentTime |
+-------------+--------+-------+-------------+---------------------+
| 93192782 | 3359 | 3 | 135765 | 2013-09-30 16:59:13 |
+-------------+--------+-------+-------------+---------------------+
1 row in set (0.00 sec)
mysql>
但该行本身仅更改一个值(currentTime)。谁能解释为什么其他两个字段没有更新?
抱歉,我自己解决了这个问题。级别字段是ENUM,查询将新值指定为数字。将查询更新为以下内容可获得预期结果。
mysql> INSERT INTO user_api_skills (characterID,typeID,level,skillpoints) VALUES
(93192782,3359,4,135765) ON DUPLICATE KEY UPDATE level='4', skillpoints=135765,
currentTime=NOW();
通过为更新提供一个整数,它会将更新更新为枚举的基于一个数字的选择,因此在这种情况下,第四个选择为“ 3”。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句