mysql - 为什么SQL使用limit显示出来的前五行是乱序的

Question

数据本来是这样的
然后使用
SELECT company_id,company_name,tel FROM table LIMIT 5;

显示如下

然后问题就来了，使用
SELECT comapny_id,company_name FROM table LIMIT 5;

显示如下

为什么少使用了一列就会导致数据乱序输出呢？

Answer 1

Tested it, when the table has the specified field after PRIMARY KEY 的时候根据PRIMARY KEY 来排序，如果没有，则根据TABLE_INDEX 来排序,楼主方便贴一下SHOW INDEX FROM table 结果吗？不过建议使用 ORDER BY xx LIMIT xx 根据ORDER BY sorting, it is clear at a glance, concise and easy to understand.

Answer 2

Explain these two SQL statements and see if the type and key values ​​are different. The guess is that different sorting methods are used according to the query columns (maybe the optimal sorting method judged by mysql itself). So it's best to add order by. To avoid finding duplicate data, the questioner can try your two SQL statement query fields and add one less query. The sorting results may be different. For the paging situation of the title, it is better to add order by. Generally, order by is added to paging.

Answer 3

You didn’t specify the sorting column. The order by field is necessary to make sense. Otherwise, of course, you will just give a few randomly.

Answer 4

explain SELECT comapny_id,company_name FROM table LIMIT 5; 

Look at the index used
Maybe it is sorted according to the index

Answer 5

If there is no primary key and no sorting is specified, it will be chaotic; if there is a primary key, the primary key sorting will be defaulted. Without a primary key, if you want the data to be stable, you must specify the sorting field