Fastest way to change a table with decimal keys in MySQL?
I am dealing with a MySQL table here which is related in several unfortunate ways. Rather than using an auto-increment table as a key, it uses a decimal place column to preserve order (which is probably why it is not that hard to insert new rows while preserving the primary key and order).
Before I go and redo this table to something more sane, I need to figure out how to reverse it without breaking everything.
What I would like to do is that it takes a list of doubles (current keys) and outputs a list of integers (which can be reset to double to be pressed again).
For example, entering {1.00, 2.00, 2.50, 2.60, 3.00} will result in {1, 2, 3, 4, 5).
Since this is a database, I also need to be able to update rows nicely:
UPDATE table SET `key`='3.00' WHERE `key`='2.50';
Anyone can think of a fast algorithm? My current thought is to read all twins into a vector, take the size of the vector, and output a new vector with values from 1 => doubleVector.size
. This seems to be quite slow as you wouldn't want to read every value in the vector if, for example, only the last elements n/100
needed to be changed.
I think there is something I can do in-place since only the values after the first non-integer double need to be changed, but I can't for the life of me figure out something that would allow me to update in-place as well. For example, setting 2.60-3.00 the first time you look at 2.50 in the original keylist, an error occurs because the key value 3.00 is already in use for the table.
Edit: I think this is really abstract:
I need a way to convert an ordered map with doubles to an ordered map with integers where there are by no means two values for the same key (which is a map violation anyway).
a source to share
I am assuming that you will be able to grab the database at some point to do this conversion.
Note. I am NOT a MySQL user. My DB of choice is PostgreSQL, so there MAY BE SYNTAX BUGS here between MySQL doing this and Pg doing it. But that should give you a good idea.
First, create a keyboard map table that maps old keys to new ones:
create table keymap (
oldkey decimal,
newkey integer autoincrement
)
Make sure you include the key card, as we'll be looking a lot on it.
create unique index keymap_oldkey on keymap(oldkey);
Then fill it in with the old keys and let MySQL create new ones:
insert into keymap
select distinct `key` from fribbles order by `key`
You will now have a keyboard shortcut with all the old keys, and since you did not specify a new key, you will have auto-increment on the column newkey
and your table will look like.
oldkey newkey
----------------
1.5 1
1.6 2
1.93 3
3.1 4
Now add the newkey column to the tables it needs
alter table fribbles add column newkey integer
Don't auto-increment it because otherwise it will be populated in alter
time and we don't need it.
Now, finally, update the table fribbles
:
update fribbles f
set newkey = ( select newkey from keymap m where m.oldkey = f.`key` )
Finally, now that you have a new key, you can discard the old one.
alter table fribbles drop column `key`;
alter table fribbles alter column newkey rename to `key`;
Hopefully this gives you a decent attack plan.
a source to share
I would just add an int column (which allows nulls) to the table, then do a cursor or code-based run where I sort by the original deleted PK column and then iterate over the records that write the incremented value to the new int column. Then update the table by changing the PK to the new int column and deleting the old PK.
“Here,” as they say in France.
a source to share