Best Way To Change Order Of Rows In Mysql Table?

I have a MySQL table with rows that need to be sorted in a particular order decided by the user. In other words, need the user to be able to insert a new row at a random point in t

Solution 1:

One of the most fundamental points of relational databases in general is that the order of the data (as stored) is utterly irrelevant.

If you want retrieved data in a particular order, select the data, and specify the required order in an order-by clause.

If you want to specify that order all the time, you might want to create a view, and specify an ordering in the definition of the view (this can still be overridden if you do a select on the view that specifies its own order by clause).

If you really retrieve all the data from that table in a particular order all (or nearly all) the time, you may want to create a clustered index on that order. This can/will (typically) help in retrieving data in that order.

Solution 2:

What @Jerry said is true, the sequence of data in a table is not important. Associated or related data is.

For example, if you are trying to record the times of certain events, you need to include a column for that time:

item    time----    ----one01:00
two     02:00
four    04:00

If you were noting this on paper or a whiteboard or something, a medium that also encompasses presentation, you would erase the last row if you wanted to include item "three" at 03:00. But in a database, you can simply insert (insert is sort of a misnomer) new data to the end of a table:

item    time----    ----one01:00
two     02:00
four    04:00
three   03:00

The storage of data need not be sequential. When you present the data (for human readability or a stockholder report), you then order it according to what suits the presentation best.

What if instead of numbered "items" you had names:

person    time----      ----
Betty      01:00
Annie      02:00
Charlie    03:00

Here the rows are "in order" by time, but not alphabetically by name. What if a report required you to order data by name?

SELECT name, timeFROM mytable
ORDERBY name ASC;

Output:

Annie      02:00
Betty      01:00
Charlie    03:00

If you need random order:

SELECT name, timeFROM mytable
ORDERBY RAND();

Hopefully this additional info helps, as I think storage and presentation of data are two concepts that you were perhaps tying together.

(A spreadsheet like Excel, for example, in many cases ties together both storage and presentation.)