bokehman — 2012-03-10T17:04:33-05:00 — #1
I haven't started this yet so all suggestions are welcome.
col_1 = integer between -180 and +180
col_2 = integer between 3000 and 12750
col_3 = integer between 1000 and 45000
col_4 = integer between 0 and 3
col_5 = timestamp(now)
I'm receiving data from a central server that I don't control and it must put it into a local MySQL database, but without any duplicates. A duplicate is when columns 1, 2, 3, and 4, are all identical to the incoming data. In that case I just want to update the timestamp, not insert a new line. If only 3 or less of the first 4 columns are matches I want to insert a line. What is the best way to go about this?
scallioxtx — 2012-03-10T17:11:16-05:00 — #2
Make col_1 through col_4 the primary key and use INSERT ... ON DUPLICATE KEY UPDATE
bokehman — 2012-03-10T17:35:40-05:00 — #3
How can you make 4 columns a primary key? I thought a primary key was on one column, not a combination of columns.
scallioxtx — 2012-03-10T17:50:50-05:00 — #4
No, you can use multiple columns if you like.
CREATE TABLE mytable (
PRIMARY KEY (col_1, col_2, col_3, col_4)
guido2004 — 2012-03-10T17:50:59-05:00 — #5
From the manual:
A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, ...) clause.
What he says ^^
bokehman — 2012-03-10T18:00:36-05:00 — #6