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?
source to share
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`);
source to share