-1

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:

enter image description here

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

b) Data:

enter image description here

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: enter image description here

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

b) Data:

enter image description here

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?

Zakir_SZH
  • 466
  • 7
  • 21
  • Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Please use standard grammar & punctuation. Please don't insult us by suggesting that we would not treat your post reasonably. – philipxy Sep 26 '19 at 17:13
  • SQL tables have no order. Query result sets [sic] have (partial) order per any ORDER BY. This is a fundamental property of the fundamental data type of SQL. This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Sep 26 '19 at 17:20

1 Answers1

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.

ADyson
  • 57,178
  • 14
  • 51
  • 63
  • so, there is nothing wrong with my mysql structure? right? it is just how system works? and i need to rely on order by clause when need to get data from tables right? then how can i export in mysqldump? because i don't think that one support order by clause? oh! of course thanks for a explanation rather than blaming me for this post :) – Zakir_SZH Sep 26 '19 at 17:40
  • 2
    sql dump is a backup and means to transfer data between databases. It has nothing to do with order of data. I mean, if you copy some files to a jump drive, and then move them to another system, I would not be surprised if the listing of those files are different. And in a similar concept, if you want to display files by date, or name, then you can do so. However, as noted, the idea that some table has data in one order or another is not relevant. Punched cards, magnetic tapes etc. are a sequential type of data processing in which order matters. – Albert D. Kallal Sep 26 '19 at 19:03
  • 2
    SQL databases don't have the concept of order. This concept allows multi-user systems. If 10 people are adding data, deleting data, and modifying data, then the order of the data don't make sense VERY quick.Records 1 to 10 in the database fast change and have no order. 10 people adding new records via a web site means next record added will be out of order. And deleting records? So 1 to 10 records might not exist, been deleted, or have additions. Amazon purchases will mess up order super fast. If you need order, you have to tell/ask the data engine to return such data in the order you want. – Albert D. Kallal Sep 26 '19 at 19:14
  • @AlbertD.Kallal, thanks sir.. – Zakir_SZH Sep 26 '19 at 23:55