0

there is a table in mysql database with AUTO_INCREMENT column as primary key. i want to add another AUTO_INCREMENT column for sorting the table by that. do you have better solution?

Shayan Ghosh
  • 882
  • 6
  • 14
alireza
  • 11
  • 2
  • 3
    What is wrong with just auto_increment primary key for sorting ? – Abhik Chakraborty Jun 09 '15 at 11:16
  • maybe i have to change the number of column for sorting in cms. i can not to change primary key – alireza Jun 09 '15 at 11:38
  • this the order column – alireza Jun 09 '15 at 11:53
  • There is no straight-forward way to have TWO auto-increment values in the same table (there are work-arounds with Triggers, though). And the "better" way to do it depends on your application. Will the order change often? How is the order set? – Paul Jun 09 '15 at 12:01
  • Inside a transaction, ask mysql what the next auto incremented value will be and insert it into both id and sort index columns. See http://stackoverflow.com/a/12500309/1220835 Alternatively you could also insert, then update set sortIndex = id. This update could be handled via trigger, if you have access to those. – Basti Jun 09 '15 at 12:14
  • If you prefer a dense sort index, auto increment values are not what you want. Use `MAX(sortIndex) + 1` instead. – Basti Jun 09 '15 at 12:16

0 Answers0