INSERT INTO table (value1, value2) VALUES ('1', '2')
ON DUPLICATE KEY UPDATE value1 = value1;
Category: MySQL
MySQL, loading fixed width file
LOAD DATA INFILE '/tmp/test.txt'
INTO TABLE table(@var) SET
name=Trim(SUBSTR(@var,1,24)),
something=Trim(SUBSTR(@var,25,4)),
else=Trim(SUBSTR(@var,29,3));
The first number is the starting row, the second number is the length of the field. @var in this example is a randomly named variable. Name, something & else describe the database column(s).
MySQL INSERT INTO UNIQUE index
Inserting into MySQL UNIQUE indexes can lead to errors (e.g. Duplicate entry ‘3’ for key 2). Either use INSERT IGNORE or the ON DUPLICATE KEY UPDATE Syntax.
13.2.5.3 INSERT … ON DUPLICATE KEY UPDATE Syntax If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:
Source: MySQL :: MySQL 5.7 Reference Manual :: 13.2.5.3 INSERT … ON DUPLICATE KEY UPDATE Syntax
MYSQL DELETE entries older than 24 hours
DELETE FROM tablename WHERE timestampfield < DATE_SUB(NOW(), INTERVAL 24 HOUR)
reusing ID on an AUTO_INCREMENT column
It’s a costly operation to find gaps so you can re-use primary key values.
It also creates a risk of false references. I.e. dependent data looking for id value ‘1234’ which is one that you deleted and then subsequently re-assigned. But the entity who now has that value is different from the one that was deleted. With nothing but the primary key to identify it, this creates a false reference. This can be a serious problem depending on the nature of the data. For example tax records, medical records, criminal records, etc.
It’s also a needless operation to try to make the primary key values contiguous because you’re worrid about running out. If you use an ordinary datatype like UNSIGNED INT for your AUTO_INCREMENT column, you have 4294967295 values. You can insert 1 row per second, 24 hours per day, for 136 years before you run out of values.
If that’s a problem, then use UNSIGNED BIGINT. That should handle 1000 new unique records per second, 24 hours per day, for 584 million years.
If that’s _still_ a problem, then make the primary key a two-column key.