MySQL替换成多个键? [英] MySQL REPLACE INTO on multiple keys?

问题描述

我正在使用MySQL中的伪会话表.该表当前如下所示:

I'm working with a pseudo sessions table in MySQL. The table looks like this currently:

id   |   key   |   value   |   metadata

id是会话所属的用户，元数据是该用户的IP地址.其背后的想法是，每个用户可以从不同的IP地址多次登录.我想知道REPLACE INTO是否只能在id =用户ID，键=键和元数据= ip_address的地方替换值，所以理想情况下我们可以得到这样的结果:

The id is the user that the session belongs to and metadata is the IP Address of the user. The idea behind this is that each user can be logged in multiple times from different IP Addresses. I'm wondering if REPLACE INTO can Replace values only where id = userid, key = key, AND metadata = ip_address so ideally we can end up with something like this:

id   |   key   |   value   |   metadata
 1       test      avalue       127001
 1       test      bvalue       19216801
 1       test      cvalue       19215810

有可能吗?

推荐答案

如果在这三列中定义了UNIQUE索引或PRIMARY KEY，那么可以REPLACE INTO对其进行索引.如果还没有索引，请添加它:

If you have a UNIQUE index or PRIMARY KEY defined across those three columns, then yes you can REPLACE INTO it. Add the index if you do not already have it:

ALTER TABLE session_table ADD PRIMARY KEY (`id`, `key`, `metadata`); 

如果已经定义了PK，请在这些列上创建一个复合UNIQUE索引:

If you already have a PK defined, create a composite UNIQUE index across those columns:

CREATE INDEX `idx_id_key_metadata` ON session_table (`id`, `key`, `metadata`); 

有关MySQL CREATE INDEX语法的更多信息

More on the MySQL CREATE INDEX syntax

这是一个小示范.

这篇关于MySQL替换成多个键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！