In my database I have some records where I am sorting by what happens to be the same value:
| col1 | timestamp |
| row1 | 2011-07-01 00:00:00 |
| row2 | 2011-07-01 00:00:00 |
| row3 | 2011-07-01 00:00:00 |
SELECT ... ORDER BY timestamp
It looks like the result is in random order.
Is the random order consistent? That means if I have these data in two mysql servers.Can I expect the same result?
解决方案
I'd advise against making that assumption. In standard SQL, anything not required by an explicit ORDER BY clause is implementation dependent.
I can't speak for MySQL, but on e.g. SQL Server, the output order for rows that are "equal" so far as the ORDER BY is concerned may vary every time the query is run - and could be influenced by practically anything (e.g. patch/service pack level of the server, workload, which pages are currently in the buffer pool, etc).
So if you need a specific order, the best thing you can do (both to guarantee it, and to document your query for future maintainers) is explicitly request the ordering you want.