Alright, here's what I've come up with so far:
SET @id=(SELECT IFNULL(MAX(id) + 1, 0) FROM leads); UPDATE leads SET id=@id WHERE id IN ( SELECT id FROM (SELECT * FROM leads) AS something WHERE hash IN ("hash1", "hash2") ); INSERT IGNORE INTO leads (id, hash) VALUES (@id, "hash1"), (@id, "hash2");
Basically, I find the max
id and add one, then update all indices if a hash is repeated. Finally, I insert the new hashes and ignore any duplicates.
Is this good? I have no idea. It works, but that's about as much as I know.
I've come up with a (hopefully) better way to save unique company data, which now consists of unique hashes for each entity. Basically, there are multiple hashes associated with a company:
hash1: ABC Corp hash2: ABC Corp ... hash6: XYZ Corp hash7: XYZ Corp
Consequently, I'm trying to create a
hashes table that looks something like this:
hash (unique primary key), company_id (auto increments with the group)
For example, if I inserted the above example information, it should be reflected in the
hashes table like so:
hash | company_id -------|------------ hash1 | 1 hash2 | 1 hash6 | 2 hash7 | 2 hash8 | 2 hash13 | 3 hash14 | 3
From there, things get a bit more tricky:
Say I want to add some new hashes, all to the same company but I don't know if the company is in the tables or not. To decide that:
- If none of the new hashes exist in the
hashestable, I want to create a new
company_idfor that group and use that id for these hashes and in subsequent insert statements.
- If, however, any of the new hashes already exist in the
hashestable, I want to merge all of the
company_id's into a new value in the
hashestable and any other table that has rows referencing that id. Finally, I want to add any additional data using the new
Say (case 2) I have these hashes to add:
hash1 | ? hash6 | ? hash17 | ? hash18 | ?
The result, after inserting them, should be:
hash | company_id -------|------------ hash13 | 3 hash14 | 3 hash2 | 4 -- company_id modified 1->4, hash2 appeared in the new hashes hash6 | 4 -- company_id modified 2->4, hash6 appeared in the new hashes hash1 | 4 -- company_id modified, (due to hash2) hash7 | 4 -- company_id modified, (due to hash6) hash8 | 4 -- company_id modified, (due to hash6) hash17 | 4 -- added, new hash hash18 | 4 -- added, new hash
I've done some research, it seems like a grouped auto-incrementing column is possible. Similarly, it seems like I can use the result of a previous statement in a subsequent query. I should also maybe look at transactions?
As of now, my workflow looks something like this:
- Select all
company_ids where the
hashequals any of the existing hashes
- Select the largest
- If no
company_id's exist from step 1, insert appropriate rows with an
max(company_id) + 1
- If one or more
company_idexists, change all rows with those
company_ids to an
max(company_id) + 1
- Perform steps 3 and 4 on any other tables that reference the
company_id, writing most of the logic in PHP
As you can see, my method neglects MySQL almost entirely. I have a feeling I can use
ON DUPLICATE KEY effectively here, and I'm wondering if there's a way to skip step 5. But regardless of if it's possible, does this database design make any sense? Part of me thinks I'm going to have to do a portion of this with PHP but I am trying to make this as efficient as possible (I will be handling a large amount of data with whatever method is chosen).