MySQL REPLACE INTO on multiple keys?

I am working with a pseudo segment table in MySQL. The table looks like this:

id   |   key   |   value   |   metadata

      

The identifier is the user to which the session belongs, and the metadata is the user's IP address. The idea is that each user can register multiple times from different IP addresses. I'm wondering if it can REPLACE INTO

only replace values ​​where id = userid, key = key, AND metadata = ip_address, so we can end up with something like this:

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

      

Is something like this possible?

+3


source to share


1 answer


If you have an index UNIQUE

or PRIMARY KEY

defined on those three columns, then yes, you can REPLACE INTO

. Add an index if you don't already have one:

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

      

If you already have PK installed, create a composite index on these columns UNIQUE

:



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

      

More about MySQL syntax CREATE INDEX

Here's a quick demo .

+5


source







All Articles