I Cant Solve The Issue With Primary Key And Unique Key On Mysql DB

Question

please before vote down, read the full questions.

OK.. now i am trying to import data from access to mysql db.

on my access file data look likes:

a) Structure:

you can see there ID as the primary Key, and MGenre is the Unique key

b) Data:

as you see when i open the table (not by query and no sorting was used), it shows that data completely fine (ordered by primary key)

now the issue arise when i use same structure on MySQL database, data is not showing correctly. here is the screenshots of my MySQL data

a) Structure:

as you can see here just like ms access db, this also has ID as primary key, and MGenre as Unique key

b) Data:

but as you can see, here when i browse the data by phpmyadmin, data is not showing correctly (not ordered by primary key), it seems data is getting ordered by unique key column. And also for your information, i have imported the data as same order (using insert command) as the data shows on access table. even i tried to import using third party software, but same results.

and also, when i try to export the db using mysqldump, it export as the same order as showing on phpmyadmin.

but i need data to be stored and export same order as access table.

(Note: oh! even i change the MGenre Index Key From unique to non unique, still the same issue :()

any suggestion?

Answer 1

There's nothing incorrect about what you're seeing. SQL tables have no inherent ordering rules for how rows are stored in a table. it's not in the spec. It's up to each DBMS implementation to decide what to do. You cannot assume that the data will be stored or retrieved in any particular order.

If you need to be able to view or process the data in a certain order for some purpose then you need to access that data with a query using an ORDER BY clause.

Consider also if you need a specific "Sort order" column in your table, if the desired order is different to the order of the primary key field.