MySql WHERE condition based on unique index-CodePudding

I have the following table groupToScore:

CREATE TABLE `groupToScore` (
  `groupId` int NOT NULL,
  `scoreId` varchar(255) NOT NULL,
  `scoreName` varchar(255) DEFAULT NULL,
  UNIQUE KEY `gToS` (`groupId`,`scoreId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The pair (groupId, scoreId) form a unique key gTos for this table.

My question is how do I perform UPDATE using gTos in the WHERE clause? It would look something like, UPDATE groupToScore SET scoreName = #{scoreName} WHERE gToS is equal to (groupId, scoreId).

CodePudding user response：

UPDATE groupToScore SET scoreName = #{scoreName} WHERE groupId = 1 and scoreId = 1;

I believe mysql server will choose a proper index for you and you can get the one sql indexing information by put a explain at the top of the sql

explain UPDATE groupToScore SET scoreName = #{scoreName} WHERE groupId = 1 and scoreId = 1;

CodePudding user response：

You can't use indices directly. The SQL engine uses the index automatically if it is applicable. Therefore, you simply query for WHERE groupId = ? AND scoreId = ?.

CodePudding user response：

You cannot use the key name directly but you can use its expression. Try this:

UPDATE groupToScore SET scoreName = #{scoreName} WHERE (`groupId`,`scoreId`) = (groupId, scoreId)
--e.g for a single match
insert groupToScore values(1,3,'dd'),(1,2,'xx');
UPDATE groupToScore SET scoreName = 'aa' WHERE (`groupId`,`scoreId`) = (1, 2);

--e.g for multiple matches
UPDATE groupToScore SET scoreName = 'kk' WHERE (`groupId`,`scoreId`) in (
(1,2),
(1,3)
);