今天将一个oracle的数据库生成到了mySQL,因为代码比较原始，是JDBC访问数据库的，所以，对数据库的分页查询一下子就查不出来了。小忧伤( ⊙ o ⊙ )啊！
先看下之前查询的code:
public PageModel
findUserList(int pageNo,int pageSize) {
StringBuffer sbSql=new StringBuffer();
sbSql.append("Select user_id,user_name,password,contact_tel,email,create_date ")
.append("From")
.append("(")
.append("Select rownum rn,user_id,user_name,password,contact_tel,email,create_date ")
.append("From")
.append("(")
.append("Select user_id,user_name,password,contact_tel,email,create_date from t_user where user_id <> 'root' ")
.append(" order by user_id")
.append(" )where rownum <=")
.append(") where rn>");
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
PageModel
pageModel=null; try{ conn=DbUtil.getConnnection(); pstmt=conn.prepareStatement(sbSql.toString()); pstmt.setInt(1, pageNo*pageSize); pstmt.setInt(2, (pageNo-1)*pageSize); rs=pstmt.executeQuery(); List
userList=new ArrayList
(); while(rs.next()){ User user=new User(); user.setUserId(rs.getString("user_id")); user.setUserName(rs.getString("user_name")); user.setPassword(rs.getString("password")); user.setContactTel(rs.getString("contact_tel")); user.setEmail(rs.getString("email")); user.setCreateDate(rs.getTimestamp("create_date")); userList.add(user); } pageModel=new PageModel
(); pageModel.setList(userList); pageModel.setTotalRecords(getTotalRecords(conn)); pageModel.setPageNo(pageNo); pageModel.setPageSize(pageSize); }catch(SQLException e){ DbUtil.close(rs); DbUtil.close(pstmt); DbUtil.close(conn); } return pageModel; }
基本上跟以前sql server数据库的rownum方式差不多。但是mysql这样子就不行了，要使用limit来进行分页。
先来看下我的表结构：
MD5b2DbTUXkG231sgu7zyor/da7qDtrtx+lm33p2Nhz1iQEwAAQvrnBG23DrIs38lLcjnYatfnylzAId/kuXbpvtls8lzPCeqdhZ4n0Iw6T1CPvi7unEAb56vUoC7fFsV2WxTb4ihvJ1jPIdQ/W3stbz7Ye9cWZZ4AANBm4DyB58FC10MA9p0C9VGDeoXmqULreQJ9dy3fO1j8PIG+8rGobzeo6oXquwyeeQLzGcOYhICcAACgGpwTuK+V3TmBcU2uPkRQr1AmA3pOoP7PsUvX4L+ceYLjXr/fb+QE1Wa2++PeMU9QrrrdhuYJ7O8iajsgJwAARJh3nkDNCeqxWn3GoFrpZrfZ7A7aFf7s8wTt5MW7/GagfnO/fJyguusvvyBwcuUEjnsH9QMISpLBPAEAYKiJ5gkOO/05AP88gT6aN+8ccrlZ9x7tUd7zRQd3dY2KqZ/aGZyI7x0chesKvVlR/9WF2/1Jv+9QLli/LqccCmMP3t9ZxDwBAKCr0X+PoRyH84N9fd58leDGOwFg5ATqmN13nqBODZqExJNWnMHfO7C/d9ANOQEAoDbq9w7kTQBzaG5WVB8iMK7V60v66mlEx0MJ+r5i6iNXd+cKh9x+a/E5wVDkBACA2vn+vYMpkBMAAC4HOUEIOQEA4HKQE4SQEwAALgc5QQg5AQDgcpAThJATAAAuR3tO8PSyTTlCT2/u6AIAFqQ9J5hyiAIAAHMhJwAAAEKQEwAAAImcAAAACEFOAAAApPac4E8jEELE/BcAAExmnpyAhAAAgKVhngAAAAjBPAEAAJBGzAk+/tFbb7311vd+/EmPhKB3WvD8ffFyLl7OxdX7/TYwyGm/LY4dlv/wUVnbl3Nx//Fo1TpPdXDu3KuKHjfh0so/EVdqeS4+FEIoncFcxbcdD2M7d3Lx7idNef3y3Xvi5VzceeTdzrHIpO3+dNpv6xfy3bogK4piuxdCNMvU5ML1hvQtBBhrZLKbmjvo1HkBrNBYOcEnP/7eWz/6+E8f/8iZE4yUENSev58gJ3j3Xjm0RDkej3VOcNoX+1P7Kp+Iq7bRaAwd85Z5fPioGVyfv98M22rLfvhIaeXHymD8WEsLmmWUgHu34/H8fSVpe6ylKe46eByLrdI1jkU1nJ/22/qNY9EM82q5fOFcfhvT34yt1SvX3eFYkBYAF27keweenEB0mSeQl1/yeu7KdWloX+c5cgL/8ur16Lv3xLufmNedURfx2oVY+ylanSFQqyQ/7NX7zTVonZc0cZCFn4irXNy5J+7Lksfm8g7WhWd4BJCLy2Xk51M/WLOt7bYeq7RdKNevslS5Pg7u2cqWPnxUNoE5xqt5gDIe14vVP9zXN+jdjoeZEyjLv3uvfOt+OPhCiLicQFWVN0s6lo+enmrJCfzVAHAh5skJ4hMC43pOHTvlhK3QryPttcLLm9ejebNYt3kCORpu9x1Oqr55gsfaLLccb969p1+P3qu2IPOV6spYG7o8Os0TaMONMqD5rmuNoUqb1q7SCvlzoBKOlO6xOcYLZTCWC2jX6NXLeto/0EO07firZN+bqN3PxVXERoQ/JxBajtUU2rcY6vJ6Q/tt7NV9a05gvgRwYc5gnuC+fS62ruON07Q5qPiXv++fve+aE5SjpzLB28KfEzgmOfQL2Q8fVfMZVepQD4HT5AT6xEi1iOMGuLIvfTwMCOcEdSNqy/hzAt88gXs7/ipp+YeSOzr27hfICWpqLhWYJ+gwK6WsRU4AIGDp8wQa5T5uYCwXrkHFt3zCnKCz884JFMeiGsPcg1zL6raYeweP9USw7d6BwbsdDyOwZpz75QTe9LEZmdvuKXR7CIB7BwDClj5PoM75q2fe8HNh9kjgW94ov688OtDsOm7Y6Cw+JzDiUKcyPXOC6rQfMZw0g8Rpv1UuSbWxQxlXQs+7xecEcc8YPn9ff65QGZXvO58xVPI873Y8jMDe7ztPcFIm+pWf9bApiZgxSNdvqOWn6JsHbTkBswTApRsrJ5BfRGx878dd0wKpfrDOvkHgfOu+/56Cb1NquXHx57t/PJy6U/ULbEb9m/rotz/uP25K7jwqq1o/mdiSFjSz/hEDgHpDe1/UM9X6TQJtO6633F+FCwt8F1F9rOTOPe8zoY7vIsqm9G3Hw96O9nxip2dR1Vg0kwTWlwubNMx1L0b5UmMT8+Adq7jvIpIRAJeO32MIAACE4PcYAgAAiXkCAAAgBPMEcN615uby+aEdAQzGPAEAABCCeQIAACAxTwAAAIRgngAAAEjMEwAAACGYJwAAABLzBAAAQAjmCQAAgMQ8AQAAEIJ5AgAAIDFPAAAAhGCeAAAASMwTAAAAIZgnAAAAEvMEAABACOYJAACAxDwBAAAQgnkCAAAgMU8AAACEYJ4AAABIzBMAAAAhmCcAAAAS8wQAAEAI5gkAAIDEPAEAABCCeQIAACAxTwAAAIRgngAAAEjMEwAAACGYJwAAABLzBAAAQAjmCQAAgMQ8AQAAEIJ5AgAAIDFPAAAAhGCeAAAASMwTAAAAIZgnAAAAEvMEAABACOYJAACAxDwBAAAQIiYnAAAAl4CcAAAAvHhBTgAAAKT2nCD+PsQ0ni7Y3LEBAKC/s8wJ5q6C22IrBgBAjNicYO4rcM1Uwelm7qgs1NzNAgCI1SEnmOZmRqvFDjOLCtHcVSgttrEAADZygmQWFaK5q1BabGMBAGzkBMksKkRzV6G02MYCANhWlhMci6w4Kq9P+632WojTfpt5bPenIaFcVIjmrkKJnAAAzsiEOcEh3+xuhm4kOMyc9ltjXHfmBPUyvp+FEEIci45ZwqJG4rmrUCInAIAzMjgnOORZfmh+tof9Q+67Lq/XSzTM1JMEnrmA7f4UNU8gFymKYnBOIINzs9tUnzhXgrHZ3TTBa5bJtCX6RCiYE9zsNk3ctRcOg9M4cgIAOCNDc4JDvslzx3DWDGlq0qCtmDgnqKYEtAv+AfME4pgwJ9jsbpRPrAy1VRpVLmOEdpKc4Ga38TSFo1LdaxIfQADAvIblBDGDxlTzBIVyte+cDAjOIajzBKWBOUHzuTebep7gcMjzgz7ay0F6/JygHPqd8wR1WuBvrH5NRk4AAGdkSE5ws9vUF7n+4WOqeQIhxGm/P/recy9vzg2ohs8TNMNwNU+w223MnKBedPR5gkOebXaH6HsHN7vNJs/zQRMF5AQAcEb65wQyEWgftqaaJ1Cu/4ujYzagOAYnCZSlSoNzgjJVyndqTpDvblwj8STPE8hRfpPvXDvT91bPHAy7f0BOAABnpH9OcMjzQ3kpu5R5gob5HIH7uYKi2G6LovAM/ENzgptdvtlk+UFJirRnDLWnC+TQazx/MPrzBM6HQm92m0xOKJRvlgV96kJOAABnZNjzBDHD1nTfO2jmAYp9ICeQC5WPHWz3p/JrBtZdh4E5wc0u3+3UMV55R50nUGMofx78aJ8/Jzjkatytwf6Qa/MFakVip4WsmsQHEAAwrzQ5gWvc7zfi9x9mikx5StBz7+CoLaQ9T3As6qVEXTLoGcOdft1fj/2HPMvyQ/OdBO2yPT8keNw/8nmCQ57lh8AjBb2ncrSaxAcQADCvVDmBMYjVw4n8ob7+VS41+458ie4dlKUDf3ehKvxdxF0uE6VDrj5oqAauDoj6Q69B2dlYTcJRt4zry6JVolC/12QyPZqLnAAAzsi4OUE13Jjvl2NOr7RgscOMNyeoJ+y1rwMe8s1ulyvX7Ha2lDQnKNVffcy0+Yn6gULXTEXTXF0nDhbbWAAA2zT3DuyHEPtPSi92mPHmBNYLGS71MQI9IE04ez9y4X2v+jKEsWGjCmaDDnjace5mAQDEGvveQXqLHWYW9VcG5q5CabGNBQCwrezvIs5pUSGauwqlxTYWAMBGTpDMokI0dxVKi20sAICNnCCZRYVo7iqUFttYAAAbOUEyiwrR3FUoLbaxAAC29pzg6fJMGaB4c0cFAIBBYucJAADAupETAAAAIcgJAACARE4AAACEICcAAABSgpzg2bNnTz/94nL+PXv2LGkTXAriNhwxHANRXQEaMRVyAnKCiRC34YjhGIjqCtCIqZATkBNMhLgNRwzHQFRXgEZMhZyAnGAixG04YjgGoroCNGIq5ATkBBMhbsMRwzEQ1RWgEVMhJyAnmAhxG44YjoGorgCNmEranOD6lWzz+k+VEfSn3/nKS9/5oHm39Mp7suTj11/KFHffsQub1c1/77yafeXNj/3bmSAnOBbZ7YcnJRanh7ebgmNRVag41m+r9ZTFeqG+PX3T1tvm/k8Pbzc7865V160sdtYqPSVu2h71/dkfQbgi6Sg8Ftrbp4e31c+tvdT2rwbH1xYThaiVdeJrgqBVquUDunuRsi17m852cVQguBF7HXUZ79Exdrh9PVM/QsMHkbK6Xl3lGJ3is1ysiEOjx9FtNmiXrmtoXSbhvgaZLCe4fkVLBZrhvyqs/2mF77yaZa9ex+QEzXbeu5tl9mYnzgnUEer08HbgBKcVGgObuVih79DY/+nh7du3b1t9+vbt28b5rOxWSk4wwalKP/Oq50l96DY/gjOSrkItGuZ5vHnzWGRmsqAlba620EN0LEY5FiNoJz6jGsci9gOGe5E4Fs5OaraLvwK+jZiMZdqPjpEYOYGzDm0HUVVkRYmcYBoRh0bc0a1ydvty7daua28reA5Jua9BpsoJtAkD9/DvLnzvbuecQO5utNmCqJxAmzBQ324763nPpXIxfQjV9y/3qW/g9PB2Vjx8aCUKtx8+LBaRE+gfwvERnJH0FTZbOhZZURTqWCjfciVdTZmvLawQzXR61/ueswYxHzDUi5xd0NW1/OmrZyNtyyw6J2g7iOoiI0rkBBOJODSij25lBbtBjZXDW/Du3SpIuq9BZpkniM8JPn79pXrg75ITuLc81zyB+na40Lhqdi2mXvaZV8bVFLB+uVwc9TL54rjAnMD5EZyRbAvvsSiHPmOst1pMr4KvLewQzXN+b2Lo/iDRH9Dbi4RrOHe1i68C3o20LrPsnKDlIBKe3ktOMJH2Q6PD0V0v42jQah+DcwKzPgn3NciEzxO8dzfLMv1mv/4cQDkfoBbqWzunnKC+HWTdnQrd5QqcZfUBzh7htMlx7c55cXRdCus5wZj3p0rt9w7cH8EZSXehOvSXI189O1DfcwjN3/ja4txzAu0DhnpRuQkr1Ha7jJMTOI+OiXMCbx1CB5G395ITTCMyJ/Ad3Y4ToO90VJa0dl3H3r3nkMT7GmTKZwy/ePrpFx+8uTGG/8A8QeBhgoicIJRPTJETVAVtJ7imMDgbaya5xpWL5xJOGwxuPzw17808T+A4OMNXoXokPYVl/M1bOKEjTlnN3xbO4zk0JI5kQE7gngixrn+FPZxHDHzOeqxtniB4EHl7LznBNDrNE7Qd3eZmrE2mmidwHEwp9jVI2pzAGpvfu+v64kB9H6H13oHzjkNETuDeb/KcwGoe78yteZWmUAsDWYF19ylTHxY7Ws+oGpPmZd0eOs7pM987qPg+grlM8D5Cdc9AOVNrrzs8T6AuGdnQowvlo0155Ae0e1G9tLoBX7uEQ7DKnCBwEHl7LznBNCIOjeiju1rAfzpKkRNEdJ6++xok8e8n+ODNjXJ34PqVrBq2f/qdrzRX/PV0Qvszhh+8ufGN7sHvHYw1SaDnBNptWdm0zTjbtJvzdrW2DfVCN+JmWL3rrLlesc7jVhYie57yalE5ge8jOCPpDm/5oigK/2sZN/O18+ygtIUeIqWdJ2b1Pe0OS9PJ4j6gMHpRvSF1EW/X8lbAsRG3M8wJfAeRP0rkBNOIODTiju5qFV+DVi+H5QTqOST9vgZJ/zuL3nm1yXbUxwPLuwZZlnl/P4EzUfj49ZfcdxC8v59gtBkCOycQeo7nu0mlzdaaixttrKcZQit3TJxXD6YY7ykjqn4YOO6WOWuVXjgn8H8EZyTdhdVq5phmxlP7xIHnBuq20EM0Tz4ghP0lbLXzOYb61g8o7POncQ4KtEuoAsOfJ1COjrEj7//9BI4j1HkQBaJk5ARL6EWrFHFoxB3d1d2hULeP6roGb+uPsK9B+D2GQ3MCRCJuwxHDMRDVFaARUyEnICeYCHEbjhiOgaiuAI2YyrnkBM3vRZbG+zWFy8gJzKdOVnAbkoN2uPOM4dI783lGFZqFNeLS+3zAueQEC/q3sM53NojbcMRwDER1BWjEVMgJyAkmQtyGI4ZjIKorQCOmQk5ATjAR4jYcMRwDUV0BGjGV9pzgGQAAuADtOQHXzYaYuRPYiNtwxHAMRHUFaMRUyAk6o/P1Q9yGI4ZjIKorQCOmQk7QGZ2vH+I2HDEcA1FdARoxFXKCzuh8/RC34YjhGIjqCtCIqZATdEbn64e4DUcMx0BUV4BGTKVDTpBlGTmBoPP1RdyGI4ZjIKorQCOm0i0ncKYFVk5wLLJsu9f/0FpJLa5Lz+jXPgphdL7qQzQfrF/JBbAO2gH9pHOPsvY1YO+n/db/O0vtHaWUMoaoENUVcJ6WPe1lHKBmcwcP8NCK/kLj/ahqzKVzTmCnBUpOIINZFIX62Y5F/UL52+v7bRls+y9FLlzT+bQPsd2f+pZcBuWgHdhPOvcoub9qpaG9NNBs+o7SSxdDNIjqCiiN6Guvqk31Qqu5Y87LzhXdW9N569a24nT65ARGWmDfO/DH1PXOuY2Mrkmq037rGIa6l6yZHbcU/SRiydN+u93vreV67934e+YNz44SGieGl46oroDn3oFyJdYcteaJ92jlBJGnZWfLR3cHc8GF9KOeOYGaFnTICVwZ9UICEc/R+UgJInQ488b3k/Ylyyjbq/fduzqzqC7m3VFCo8Tw4hHVFXDnBHV76eO8nQRYl/tRd4UG5QRWX1pIR+qfE9RpQWxOcNpvXXdlz25UNDufMik1qGTtYs+88f0kYsl6F7E5QZdeqrZhYEcJpY8hiOoqeC7VlNn52JxA30DwYO6fExh9KX7F8U00T3Dab323WZfyZEU0vfMxRRAr5swb30+iltQn/Ftzgu69tNpGcEcJpY0hJKK6AkYjmu3VYZ5Apab6jgcDI3ICx4q+vnTeOUGn5wnansFZSChi6Z0vZqzpMG+9Yq1n3vh+Erek9viwfUSn6KVRO0ooYQxRI6oroDaio720Jmp5nsC3WuTb4XUCfWkh3Sjt9w5KwfhXhVXZ2U2i80XYflrOvPH9pFePasnLOmzzdDo1W5j4gbNkMYSCqK6A9nUwx6irlFqHqPE8QfgAV3XOCYITxOeaE8T8ziLts9VzJ/pjG8q11ZlNolvzBHYH61Gyfi1n3vh+0qtHteQEHbaplLmacM6cYHXH2jSI6go0jehpL6XB2p9KDhzg/hVDhS11a11xQvwew86YJ+iHuA1HDMdAVFeARkyFv3fQGZ2vH+I2HDEcA1FdARoxleypBzmBD52vH+I2HDEcA1FdARoxFXKCzuh8/RC34YjhGIjqCtCIqZATdEbn64e4DUcMx0BUV4BGTIWcoDM6Xz/EbThiOAaiugI0YirtOcGzCC8AAMCZa88JWtMK5gkQg7gNRwzHQFRXgEZMhZygMzpfP8RtOGI4BqK6AjRiKuQEndH5+iFuwxHDMRDVFaARUyEn6IzO1w9xG44YjoGorgCNmAo5QWd0vn6I23DEcAxEdQVoxFTICTqj8/VD3IYjhmMgqitAI6YyRk5g/dG/5o9BmX+U3PNXxbQtaH+dfgF/gYzO1w9xG86K4fBjDUR1DbRGbGkv+y+kakXRI47vj6UH/7yhu24RK04lbU4gg1kU2t98PBb1i2Oh/KHR5o9ZG3GXW1FXWkKkaoxt/RC34ZQYpjnWIIjqKiiN6Guvqk31Qqu5Y0Yc54rurem8dWtbcTqj3Dvwx9T1jlF22m+3+32h5QSLOuIY2/ohbsPZMRx0rEEIQVRXwXN6qdrmtN82w4j2QlgNGD/iOFs+ujuYCy6kH02bE7gy6qOVahVHtVCdyFlEblDFREk81RdKfZXqeieMLgdxG67D6NV+rKFEVFfAPVTV7aWP83YSYF3uR404g3ICqy8tpCNNmBOc9ls1BlXknfmacwvaYDKfJiZNLevE87Qv7KmhpdwomhdxGy529Io41lAjqivgGKqU9jJmBoI5gb6B4Amof05g9KX4Fcc3UU5w2m99p/fmxK+s5onOIoKm3bhyTExZV7yLqPX8iNtwMaNX1LEGBVFdAaMRzfbqME+gUq9SHfOVETmBY0VfX1rICW+KnKDtGRy5uPawp2e+eBFBU2NSV1257rWugRdR6/kRt+FaR6+4Y22cyp0toroCxukleA3e8jyBb7XIt8PrBPrSQrpR5nsjWU5gxr8qrMqc8zPq8wSnU1O4hJhpMTntt9uiqIY26zmIZg68+rjH/fyfYB7EbbiW0avXsQaiugJNIzrbSy21xl7jeYL4EadzTuCuW8SKExo/J2ieEtMe23A/UubYgrLgEgJmnkE89xizbFsUTfM3xZd785G4DdcyevU61kBUV0B7XMnVXoGTifcZw7YRp3NO4Ktb64oTGiUnWDe+U9cPcRuOGI6BqK4AjZgKOUFndL5+iNtwxHAMRHUFaMRUyAk6o/P1Q9yGI4ZjIKorQCOmQk7QGZ2vH+I2HDEcA1FdARoxFXKCzuh8/RC34YjhGIjqCtCIqZATdEbn64e4DUcMx0BUV4BGTKU9J3gW4QUAADhzaeYJvrgkL168mLsKZ4m4DUcMx0BUV4BGTIWcoDM6Xz/EbThiOAaiugI0YirkBJ3R+fohbsMRwzEQ1RWgEVMhJ+iMztcPcRuOGI6BqK4AjZgKOUFndL5+iNtwxHAMRHUFaMRUyAk6Uzrf9VV2dV3+cOvBk/nqdA44aIcjhmMgqitAI6ZCTtAZOUE/Yx6011eZ3gDXV9UfHlOL61LZaudHi6HzM7Z88LLwyYNbxt9nKyPSYZtfGGH3bVMrrwLvLJzLRURV39D6zlbtjWi1l1quF0WeKOwVfYWubbdXYx6LygmePLg1+/mhHTlBP/asqOMAAASqSURBVOlyArWfyFPd1dWVfrqtX1xfKWdR5RS9/H7movU9x2f0ffAqOM5P3oSz2zatsDu36T42FnXArD6qTx7cMmp4fbWAXCyptkZ0tpcz4DEnCt+KweYL1q1txemQE3RGTtDPODlByd8ArnfOtrk8MQwOEFq03KFzHXRRg44vkOo2ndv37HQeK49qvbnyCvXq6iq7uj6Ts220lkYMtpf3fNB2ohiW78a2+8TS5gQy1tdXzbxIM1GSma1glCslSwiMnzcneGBPOHnniS6QErf0/SR0VFtnvoUcez24T3zOy5m6UB8oOow/gW2G19ZK1Tlr7aTsmeOewbqjWtZUnbdocgQ1x1A0O/KVa7XX33Qey9dX2a0H12UFr66bpVIdiS2NGGyvQHOFqzcoJ4hs98mlzwn0fmD0rua07io/j8zVkxPYc1RLuT+0EEZOkLafuA8nY870iXJKOk+OE589L6wXGsHSA+U54tq26dlacJva7G1L4cRWHdUmJaiWqufG6x+00Uk56nzlak0dd+J9x7I2XZH6lB9uxGB7WQGPPlH0zwli230GY8wTVK9ckS4zRGf5eecE6lNBtx48WUwLL4Y1T1C9StFP7GA/eXDLl5Kdb7JmnPicn9EsDFwhuQ64qG06t+bfZmDxJRwnq45qkxNU66tnreoa2jlCtl5Nt5fU9Vbf8P08SEsj9p0nsCYy2+eD9ELHih3afQ4T5wTOwbJOYckJ1qxLTtC5n9gHebAnnWvbqCe+uHno0HjlPKVHbdO5bU9J25vzt8Wqo1oeNUpCfXXrlj6dPm5OYG9r9JygU3sFK9FSvR4dulO7z2LMnMCeR1JyLlf5unICrfWvHyygseflzQlS9JPWCzX1QmkJ89X9NDH0fUbHAeR7Wt06BXXYpm8TjvmaJ+pEctWCrsLZrDuq1a7KCfFbD540PymLqweH696BUq7fHtVPcYFjeaqcoFt7WQVdThSdc4Ju7T6PUXOCL5SZk9Znx6r1Ez52Mo7onEB96Gf5qc7o/DnBF8P7iXla1jX3FM+8OZoYOj+j54O7+2G4EVq3Wa3hnirWi9yzp2bhbFYf1fbEq6nPrQcP9CHbLleHSkcQnMfshDlBp/ZyVSL+RNE5J+jU7jNZ1HcRzwO/MKsf4jYcMRzDRUTVGI3Cs9u+u+tLGLI8LqIRJ0FO0Bmdrx/iNhwxHANR1ZATXDZygs7ofP0Qt+GI4RiIqoac4LKRE3RG5+uHuA1HDMdAVFeARkyFnKAzOl8/xG04YjgGoroCNGIq5ASd0fn6IW7DEcMxENUVoBFTac8JnkV4AQAAzlyCeQIAALAC5AQAAEAIcgIAACCREwAAACHICQAAgEROAAAAhCAnAAAAEjkBAAAQgpwAAABI5AQAAEAIcgIAACCREwAAACHICQAAgEROAAAAhCAnAAAAEjkBAAAQgpwAAABI5AQAAEAIcgIAACCREwAAACHICQAAgEROAAAAhCAnAAAAEjkBAAAQgpwAAABI5AQAAEAIcgIAACCREwAAACHICQAAgEROAAAAhCAnAAAAEjkBAAAQgpwAAABI5AQAAEAIcgIAACCREwAAACHICQAAgPT/teH5Ycy9JqwAAAAASUVORK5CYII=" width="619" />
PS：我在user_id上面加了个索引。
然后，使用没有经过优化的limit进行查询：
#create INDEX rowindex on t_user(user_id)
SELECT * from t_user ORDER BY USER_ID DESC limit 0,2
然后我们对此进行优化查询：
1，使用子查询方式进行优化查询
SELECT
*
FROM
t_user
WHERE
USER_ID < =(
SELECT
USER_ID
FROM
t_user
ORDER BY
USER_ID DESC
LIMIT ($page-1)*$pagesize.", 1),
1
)
ORDER BY
USER_ID DESC
LIMIT $pagesize
例如：
SELECT
*
FROM
t_user
WHERE
USER_ID < =(
SELECT
USER_ID
FROM
t_user
ORDER BY
USER_ID DESC
LIMIT 3,
1
)
ORDER BY
USER_ID DESC
LIMIT 3
二，使用join方式进行优化
SELECT
*
FROM
t_user AS u1
JOIN (
SELECT
user_id
FROM
t_user
ORDER BY
USER_ID DESC
LIMIT ($page-1)*$pagesize.", 1),
1
) AS u2
示例：
SELECT
*
FROM
t_user AS u1
JOIN (
SELECT
user_id
FROM
t_user
ORDER BY
USER_ID DESC
LIMIT 0,
1
) AS u2
三，对返回的数据总条数查询的优化
通常在代码里面，我要分页的话，需要返回的结果集中，包含数据总条数，这样我才能够根据当前的pageSize来在页面上显示数据一共有多少页。
而对这个数据总条数的查询，我们通常使用count(*) 或者count(0)，然而在mysql里面，提供了内置的函数，来对这一查询进行优化：
SELECT SQL_CALC_FOUND_ROWS * from t_user where USER_ID<'root' limit 1;
SELECT FOUND_ROWS(); #返回的第二个结果集为如果没有limit限制返回的条数