0

I have mysql table like following 

id,sort,enabled,font,store
1,1,1,Adlib,0
2,2,1,Arial,0
3,3,1,"Arial Bold",0
4,4,1,"Arial Black",0
----------
----------
40,40,1,Harlow,0
41,41,1,"Hobby Headline",0
42,42,1,"Hobby Horse",0
----------
----------
85,85,1,"Wild West",0
86,86,1,"Zap Chance",0
87,87,1,Helvetica,0

id column is primary key. If I add a new font entry it is stored in the last row of table

How can I reorder the font column values that they are in ascending order of their name?

NOTE: I do not want select statement, I want to reorder data in the table itself.

Update : If it is not possible in MySQL(I was just curious to know), I can solve my problem with alternate approach when displaying data. Thanks for help.

Mukesh
  • 7,630
  • 21
  • 105
  • 159
  • 3
    _I want to reorder data in the table itself..._ What is the benefit of that? I'd like to understand.. never had such n issue before – B001ᛦ Sep 06 '18 at 10:47
  • This was already asked here: https://stackoverflow.com/questions/1850594/mysql-how-to-reorder-a-table afaik MySQL does not provide such an option so you have to try the suggested option, other RMDBs do provide functions to do so, i,e, PostgreSQL offers the CLUSTER command (https://www.postgresql.org/docs/9.6/static/sql-cluster.html) to reorder table using an INDEX, so if this function is something critical i'd suggest changing MySQL for another RMDBs – L. Amigo Sep 06 '18 at 10:53
  • 2
    The primary key defines the order of the rows stored on the disk. If you don't want it stored by the id column but instead stored it by the font name you have do use your font column as the primary key. – Progman Sep 06 '18 at 10:59
  • 1
    Why would you even want to re-order your data, if a select can do this for you anyway. Personally, I dont see the purpose of re-ordering and fixing the primary key to your name. But you can switch primary key to name rather than the id. But still, it wont be sorted alphabeticlly, since you already entered these names. – Dorvalla Sep 06 '18 at 11:07
  • @Dorvalla you are right select can do it, but I wanted to know if it is possible for existing data in a table :) – Mukesh Sep 06 '18 at 11:10
  • @Dorvalla Can you verify this? I just tested it with an InnoDB table in `10.1.34-MariaDB` and "moving" the primary key will resort the rows. – Progman Sep 06 '18 at 11:13
  • @Progman interesting. I havent played arround with that myself (and have no dummy dataset availalble here) but i thought the fields in the entered db were somewhere also timestamped (not visible to user) and it would keep that as sort. I learn something everyday. I ll see if i can verify this later as well. – Dorvalla Sep 06 '18 at 11:18

3 Answers3

3

I want to reorder data in the table itself

The only actual physical order which would exist for your table would be whatever the clustered primary key is on disk. This would be the id column, and not the font name. But, in any case, in general you should proceed at the SQL level under the assumption that tables are modeled after unordered sets of records.

So, if you want the font column ordered a certain way, you should use ORDER BY:

SELECT *
FROM yourTable
ORDER BY font;

I should add that if you want a structure which can quickly search your table by the font name, then you may add an index on that column. Then, there would be a physical structure which is ordered by font, but it wouldn't be your actual table, it would be a B-tree.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

One thing I learned the hard way with MySQL is that anything that doesn't have a relatively straight-forward and well documented way to be done, doesn't have it for a reason

There's no such thing in MySQL. That said, you could spend a lot of resources and force this (keeping in mind it'll probably mess with your indexes and definitely with your primary key, so whatever app you have that depends on the table will behave unexpectedly) by having a process (stored procedure, script in any programming language, etc) that:

  1. Selects the data from the table and sorts it with an order by clause
  2. Dumps the data on a temp table
  3. Truncates the original table
  4. Dumps the temp table into the recently truncated original table with a regular insert

While this would work, it's definitely not advisable to do it. Plus, it's messy and expensive. There's no real reason for something like this ever to be done. It's better to index the table appropriately and sort at query-time

Javier Larroulet
  • 3,047
  • 3
  • 13
  • 30
  • This kind of order is needed when semantically near data is going to be pull together in big numbers, this way sequential scan is far better and faster than a random access following an index. It's not a superfluous thing and that's why other(more featureful) databases allow to. – L. Amigo Sep 06 '18 at 11:31
1

The order in which the rows are stored on the disk is determined by the primary key, at least for InnoDB:

InnoDB tables arrange your data on disk to optimize queries based on primary keys.

When you move the primary key from your Id column to your font name column, the rows gets sorted by the font name column:

CREATE TABLE foobar(Id INT PRIMARY KEY, Name VARCHAR(30));
INSERT INTO foobar(Id, Name) VALUES (1, 'middle');
INSERT INTO foobar(Id, Name) VALUES (3, 'east');
INSERT INTO foobar(Id, Name) VALUES (5, 'west');
INSERT INTO foobar(Id, Name) VALUES (7, 'up');
INSERT INTO foobar(Id, Name) VALUES (2, 'down');
INSERT INTO foobar(Id, Name) VALUES (4, 'left');
INSERT INTO foobar(Id, Name) VALUES (6, 'right');

SELECT * FROM foobar;
+----+--------+
| Id | Name   |
+----+--------+
|  1 | middle |
|  2 | down   |
|  3 | east   |
|  4 | left   |
|  5 | west   |
|  6 | right  |
|  7 | up     |
+----+--------+

ALTER TABLE foobar DROP PRIMARY KEY;
ALTER TABLE foobar ADD PRIMARY KEY(Name);

SELECT * FROM foobar;
+----+--------+
| Id | Name   |
+----+--------+
|  2 | down   |
|  3 | east   |
|  4 | left   |
|  1 | middle |
|  6 | right  |
|  7 | up     |
|  5 | west   |
+----+--------+
Progman
  • 16,827
  • 6
  • 33
  • 48