{"id":444,"date":"2018-03-22T09:00:00","date_gmt":"2018-03-22T00:00:00","guid":{"rendered":"https:\/\/p-corporate-blog-cms.mmmcorp.co.jp\/blog\/2018\/03\/22\/sql_order_by"},"modified":"2018-03-22T09:00:00","modified_gmt":"2018-03-22T00:00:00","slug":"sql_order_by","status":"publish","type":"post","link":"https:\/\/p-corporate-blog-cms.mmmcorp.co.jp\/blog\/2018\/03\/22\/sql_order_by\/","title":{"rendered":"ORDER BY\u3068LIMIT, OFFSET\u306e\u7d44\u307f\u5408\u308f\u305b\u306b\u306f\u6ce8\u610f\u3057\u3088\u3046"},"content":{"rendered":"<p>\u3053\u3093\u306b\u3061\u306f\u3001\u4e0b\u689d\u3067\u3059\u3002\u4eca\u65e5\u306fSQL\u306e\u8efd\u3044 (\u3057\u304b\u3057\u91cd\u8981\u306a) \u8a71\u984c\u306b\u3064\u3044\u3066\u66f8\u3044\u3066\u307f\u3088\u3046\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n<p>\u307e\u305a\u306f\u4ee5\u4e0b\u306e\u901a\u308a\u30e6\u30cb\u30fc\u30af\u3067\u306a\u3044 <code>col<\/code> \u30ab\u30e9\u30e0\u3092\u542b\u3080 <code>test<\/code> \u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u3001\u30c7\u30fc\u30bf\u3092\u6295\u5165\u3059\u308bSQL\u3092\u3054\u89a7\u304f\u3060\u3055\u3044\u3002 (MySQL\u3067\u306e\u4f8b\u3067\u3059\u3002)<\/p>\n<pre><code>create table test(id INT, col INT);\n\ninsert into test values(1,1);\ninsert into test values(2,1);\ninsert into test values(10,1);\ninsert into test values(3,1);<\/code><\/pre>\n<p>\u305d\u3057\u3066\u3001\u4ee5\u4e0b\u306e2\u3064\u306eSQL\u3092\u5b9f\u884c\u3057\u305f\u5834\u5408\u3001\u7d50\u679c\u306f\u3069\u3046\u306a\u308b\u3067\u3057\u3087\u3046\u304b\uff1f\u3053\u3053\u3067\u4f55\u304c\u8a00\u3044\u305f\u3044\u304b\u304c\u5206\u304b\u308b\u65b9\u306f\u3053\u306e\u5148\u306f\u8aad\u307e\u306a\u304f\u3066\u304b\u307e\u3044\u307e\u305b\u3093\u3002<\/p>\n<pre><code>select * from test order by col limit 2 offset 0;\nselect * from test order by col limit 2 offset 2;<\/code><\/pre>\n<p>\u4e0a\u8a18\u306f\u307b\u307c\u307b\u307c\u3000<a href=\"https:\/\/bugs.mysql.com\/bug.php?id=69732\">https:\/\/bugs.mysql.com\/bug.php?id=69732<\/a>\u3000\u3088\u308a\u6301\u3063\u3066\u304d\u305fSQL\u306a\u306e\u3067\u3059\u304c\u3001\u5b9f\u6a5f\u691c\u8a3c\u3057\u305f\u3068\u3053\u308d\u91cd\u8907\u3057\u305f\u30ec\u30b3\u30fc\u30c9\u304c\u53d6\u5f97\u3055\u308c\u308b\u7d50\u679c\u3068\u306a\u308a\u307e\u3057\u305f\u3002<\/p>\n<pre><code>mysql&gt; select * from test order by col limit 2 offset 0;\n+------+------+\n| id   | col  |\n+------+------+\n|    3 |    1 |\n|    2 |    1 |\n+------+------+\n2 rows in set (0.00 sec)\n\nmysql&gt; select * from test order by col limit 2 offset 2;\n+------+------+\n| id   | col  |\n+------+------+\n|   10 |    1 |\n|    3 |    1 |\n+------+------+\n2 rows in set (0.00 sec)<\/code><\/pre>\n<p>\u3053\u308c\u306f\u4eba\u306b\u3068\u3063\u3066\u306f\u610f\u56f3\u3057\u306a\u3044\u7d50\u679c\u3068\u601d\u308f\u308c\u308b\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u3002\u5b9f\u969b\u3001\u5148\u307b\u3069\u306e\u30d0\u30b0\u30ec\u30dd\u30fc\u30c8\u3067\u3082\u3053\u308c\u306f\u30d0\u30b0\u3067\u306f\u306a\u3044\u304b\u3068\u30b3\u30e1\u30f3\u30c8\u3057\u3066\u3044\u308b\u65b9\u3082\u3044\u3089\u3063\u3057\u3083\u3044\u307e\u3059\u3002<\/p>\n<p>\u3057\u304b\u3057\u3001\u30e6\u30cb\u30fc\u30af\u3067\u306f\u306a\u3044\u30ab\u30e9\u30e0\u306e\u30aa\u30fc\u30c0\u30fc\u9806\u5e8f\u306f\u4e0d\u5b9a\u3068\u306a\u308a\u307e\u3059\u3002\u5148\u307b\u3069\u306e\u30d0\u30b0\u30ec\u30dd\u30fc\u30c8\u306e\u4ee5\u4e0b\u306e\u30b3\u30e1\u30f3\u30c8\u3067\u3042\u308b\u901a\u308a\u3001<\/p>\n<blockquote>\n<p>[12 Jul 2013 15:50] Hartmut Holzgraefe<br \/>\nWithout a distinct ORDER BY the result order is undefined. Period.<\/p>\n<p>Even if the same query, executed twice, results results in different order there is nothing wrong with that. You may be used to what looks like deterministic results but that is actually an illusion. It may be what you see in the usual case but there is <em>nothing<\/em> that guarantees this. Result order can change as statistics get updated, as index trees are reshuffled, as data is partitioned across different physical machines so that result order depends on network latencies ...<\/p>\n<p>So if you want pagination then make sure you ORDER BY on something guaranteed to be UNIQUE, or live with the fact that the sort order of identical values in a non-unique sequence is not deterministic and can change at any time without prior notice.<\/p>\n<p>Your example is equivalent to<\/p>\n<p> SELECT * FROM table ORDER BY &#39;x&#39;;<\/p>\n<p>( not the same as &quot;ORDER BY 1&quot; as that would be &#39;order by the first result column )<\/p>\n<p>and almost equivalent to<\/p>\n<p>  SELECT * FROM table ORDER BY RAND();<\/p>\n<p>and you wouldn&#39;t expect that to return the same row order every time either, would you?<\/p>\n<\/blockquote>\n<p>\u30e6\u30cb\u30fc\u30af\u3067\u306a\u3044\u30ab\u30e9\u30e0\u306eORDER BY\u3067\u306f\u3001\u7d50\u679c\u306e\u9806\u5e8f\u306f\u4e0d\u5b9a\u3068\u3044\u3046\u306e\u304cMySQL\u306e\u4ed5\u69d8\u3067\u3059\u3002<br \/>\n\u5f15\u7528\u4f8b\u306b\u66f8\u3044\u3066\u3042\u308b\u3088\u3046\u306b\u3001\u30e6\u30cb\u30fc\u30af\u3067\u306a\u3044\u30ab\u30e9\u30e0 <code>x<\/code> \u306b\u5bfe\u3057\u3066<\/p>\n<pre><code>SELECT * FROM table ORDER BY &#39;x&#39;;<\/code><\/pre>\n<p>\u3068\u3044\u3046SQL\u3092\u767a\u884c\u3059\u308b\u306e\u306f\u307b\u307c<\/p>\n<pre><code>SELECT * FROM table ORDER BY RAND();<\/code><\/pre>\n<p>\u3068\u540c\u69d8\u3067\u3059\u3002\u3053\u306e\u4f8b\u3067\u3042\u308c\u3070\u30da\u30fc\u30b8\u30cd\u30fc\u30b7\u30e7\u30f3\u3092\u3057\u3066\u3082\u6bce\u56de\u9055\u3046\u30c7\u30fc\u30bf\u304c\u8fd4\u308b\u3053\u3068\u304c\u611f\u899a\u7684\u306b\u5206\u304b\u308a\u3084\u3059\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n<p>\u3057\u305f\u304c\u3063\u3066\u3001\u57fa\u672c\u7684\u306b\u306f\u30da\u30fc\u30b8\u30cd\u30fc\u30b7\u30e7\u30f3\u3068ORDER BY\u3092\u7d44\u307f\u5408\u308f\u305b\u308b\u969b\u306b\u306f\u30e6\u30cb\u30fc\u30af\u306a\u30ab\u30e9\u30e0\u3067\u30bd\u30fc\u30c8\u3059\u308b\u3053\u3068\u304c\u5fc5\u8981\u3067\u3059\u3002\u4eca\u56de\u306fMySQL\u306e\u4f8b\u3092\u3054\u7d39\u4ecb\u3057\u307e\u3057\u305f\u304c\u3001Oracle\u3067\u3082PostgreSQL\u3067\u3082\u91cd\u8907\u304c\u8fd4\u308b\u53ef\u80fd\u6027\u306f\u3042\u308a\u307e\u3059\u3002<\/p>\n<p>\u3053\u308c\u306f\u57fa\u672c\u7684\u306a\u8a71\u306a\u306e\u3067\u3059\u304c\u3001\u3082\u3057\u4f5c\u308a\u8fbc\u3093\u3067\u3057\u307e\u3046\u3068\u4e00\u898b\u52d5\u3044\u3066\u3044\u308b\u3088\u3046\u306b\u898b\u3048\u308b\u5206\u304b\u308a\u3065\u3089\u3044\u30d0\u30b0\u3068\u306a\u308b\u3053\u3068\u304c\u3042\u308b\u306e\u3067\u3001ORDER BY, LIMIT, OFFSET\u3092\u7d44\u307f\u5408\u308f\u305b\u308b\u969b\u306b\u306fORDER BY\u304c\u30e6\u30cb\u30fc\u30af\u30ab\u30e9\u30e0\u306b\u52b9\u3044\u3066\u3044\u308b\u304b\u304d\u3061\u3093\u3068\u30c1\u30a7\u30c3\u30af\u3059\u308b\u3088\u3046\u306b\u3057\u307e\u3057\u3087\u3046\u3002<\/p>\n<p>\u3068\u30a8\u30e9\u305d\u3046\u306b\u66f8\u304d\u307e\u3057\u305f\u304c\u3001\u5148\u65e5\u601d\u308f\u306c\u3068\u3053\u308d\u3067\u3053\u306e\u30d0\u30b0\u3092\u4f5c\u308a\u8fbc\u3093\u3067\u3044\u305f\u305f\u3081\u3001\u81ea\u6212\u3092\u8fbc\u3081\u305f\u8a18\u4e8b\u3067\u3054\u3056\u3044\u307e\u3057\u305f\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u3053\u3093\u306b\u3061\u306f\u3001\u4e0b\u689d\u3067\u3059\u3002\u4eca\u65e5\u306fSQL\u306e\u8efd\u3044 (\u3057\u304b\u3057\u91cd\u8981\u306a) \u8a71\u984c\u306b\u3064\u3044\u3066\u66f8\u3044\u3066\u307f\u3088\u3046\u3068\u601d\u3044\u307e\u3059\u3002 \u307e\u305a\u306f\u4ee5\u4e0b\u306e\u901a\u308a\u30e6\u30cb\u30fc\u30af\u3067\u306a\u3044 col \u30ab\u30e9\u30e0\u3092\u542b\u3080 test \u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u3001\u30c7\u30fc\u30bf\u3092\u6295\u5165\u3059\u308bSQL\u3092\u3054\u89a7\u304f\u3060\u3055\u3044\u3002 (MySQL\u3067\u306e\u4f8b\u3067\u3059\u3002) create table test(id INT, col INT); insert into test values(1,1); insert int [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":826,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/p-corporate-blog-cms.mmmcorp.co.jp\/wp-json\/wp\/v2\/posts\/444"}],"collection":[{"href":"https:\/\/p-corporate-blog-cms.mmmcorp.co.jp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/p-corporate-blog-cms.mmmcorp.co.jp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/p-corporate-blog-cms.mmmcorp.co.jp\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/p-corporate-blog-cms.mmmcorp.co.jp\/wp-json\/wp\/v2\/comments?post=444"}],"version-history":[{"count":0,"href":"https:\/\/p-corporate-blog-cms.mmmcorp.co.jp\/wp-json\/wp\/v2\/posts\/444\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/p-corporate-blog-cms.mmmcorp.co.jp\/wp-json\/wp\/v2\/media\/826"}],"wp:attachment":[{"href":"https:\/\/p-corporate-blog-cms.mmmcorp.co.jp\/wp-json\/wp\/v2\/media?parent=444"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/p-corporate-blog-cms.mmmcorp.co.jp\/wp-json\/wp\/v2\/categories?post=444"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/p-corporate-blog-cms.mmmcorp.co.jp\/wp-json\/wp\/v2\/tags?post=444"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}