Randomizing A Table In MySQL
All Posts, Computers No Comments »I’m working on a project right now where I want to collect a Network ID (for prize-drawing purposes) from a survey participant but I don’t want it to be able to be tied to their response. Even if there’s no relational connection through keys or anything, just the fact that the order of the network IDs in the table matches the order of survey responses is enough for correlation.
I tried alter table netIDs order by rand(); but that gave me an error. I used the Google and found a decent solution which I modified for my purposes:
set autocommit=0;
begin work;
insert into netIDs values ('$netID');
create table netIDs_temp like netIDs;
insert into netIDs_temp (select * from netIDs order by rand());
truncate netIDs;
insert into netIDs (select * from netIDs_temp);
drop table netIDs_temp;
commit;
I wanted to wrap the whole operation in a transaction for atomicity – losing network IDs while keeping responses would be a nightmare, not to mention a violation of research ethics.
Recent Comments