MySQL - Many to Many - Weighting/Order the relation table rows - Rebuilding order when relations are unlinked

I need advice on the best way to accomplish the following design schema.

I have a two tables with many-to-many relationship. And one table that links them all together.

tableName: playlist
  columns: 
     id
     name
track
  columns: 
     id
     name
playlist_track
   playlist_id { onDelete: CASCADE }
   track_id { onDelete: CASCADE }
   order_no

Sample data for the relation mapper table:

Entry1:
   playlist_id: 1
   track_id: 1
   order_no: 1
Entry2:
   playlist_id: 1
   track_id: 2
   order_no: 2
Entry3:
   playlist_id: 1
   track_id: 3
   order_no: 3
Entry4:
   playlist_id: 1
   track_id: 4
   order_no: 4

When Entry3 is deleted, the item order for playlist_id: 1 would be [1,2,4], which would result in unprettiness.

What would be the best way to "rebuild" the order when an item is removed?

The alternatives 开发者_如何转开发in mind would be between the two:

1. PHP code that fetches all tracks in a playlist and rebuilds them to [1,2,3]
2. MySQL trigger of some sort that does this task automagically(?)

Anyone with some concrete advice ?

---

UPDATE playlist_track
SET order_no = order_no -1
WHERE playlist_id = $whatever_id_you_deleted
AND order_no > $whatever_order_no_was_deleted

On the other hand, you can think of the order_no as some kind of weight (heavier items sink to the bottom). Then an update of the order_no is not necessary. Depends on your other queries if this is appropriate. If you frequently have queries like "give me the next item in the playlist" or "give me item n in the playlist", then an update might be worth it. But if you usually fetch a complete playlist, I wouldn't bother updating.

If you do update the order, you will need some kind of automation (like triggers), because items can be deleted from the playlist by deleting the track that they reference.

---

UPDATE playlist_track SET order_no = order_no - 1 WHERE playlist_id = 3 AND order_no > 3