2019-06-14 18:41:21 0 Comments MySQL Boy.Lee

MySQL Reverse PK value

Say we have movie table with 1000 rows, id from 1 to 1000, now we want reverse the PK order set 1000 to 1, 1 to 1000, we can do it like this.


{ foucs on movie table }

in movie we will use 2 fields, m_id the PK, m_imdbId, the unique value, it's important to have a unique value in each row, you can use m_id(the PK too), but I suggest you duplicate m_id to m_id_d if u want use it.



{ Create New Table }

as you see we have 3 fields in new table movieT, we will keep mt_id as auto increment(from 1 - 1000 in this case), mt_v1 will store movie.m_imdbId the unique value, mt_v2 will store movie.m_id(just for cache)

  `mt_id` int(11) NOT NULL AUTO_INCREMENT,
  `mt_v1` varchar(45) DEFAULT NULL,
  `mt_v2` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`mt_id`)


{ Let's Do It }

//open FK, only after this open the FK value can auto update.
set foreign_key_checks=1;

//fill data to movieT, with reverse order
insert into yiilib.movieT(mt_v1, mt_v2) (select m_imdbId, m_id from yiilib.movie where m_id <= 1000 order by m_id Desc);

//update movie, change the m_id out 0-1000
update yiilib.movie set m_id  = m_id + 10000 where m_id <= 1000;
//after this change the movie.m_id is 10001 to 11000

//update m_id
update yiilib.movie set m_id  = (select mt_id from yiilib.movieT where mt_v1 = m_id_douban) where m_type_id = 2; //you can add any condition in where part as you want, but do not use m_id <=1000, because we are changing it with the update statement, so just be careful.


{ Wating }

This kind update will cost some time, just just wait, in my test 16000 rows used 1 min 38 sec, so just wait.


{ Summary }

The main idea is use new table movieT's auto increment PK to generate the really PK we want with insert, then do the update with the unique key. Good Luck for your MySQL work.