MySQL のソートとページング (order by と limit) と既存の落とし穴

並べ替えクエリ (order by)

電子商取引の場合: 今日完了したすべての注文を表示し、取引金額に応じて高いものから低いものの順に並べ替えます。この時点で、データベースの並べ替え機能を使用して完了できます。

ソート構文:

テーブル名からフィールド名を選択し、フィールド1 [asc|desc]、フィールド2 [asc|desc] で順序付けします。

並べ替える必要があるフィールドは、次の順序に従います。
asc|desc はソート規則を示します。asc: 昇順、desc: 降順、デフォルトは asc です。
複数のフィールドによる並べ替えをサポートし、複数のフィールドはカンマで区切られます。

単一フィールドの並べ替え

mysql> テーブル test2(a int,b varchar(10)) を作成します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
mysql> test2 に値 (10,'jack'),(8,'tom'),(5,'ready'),(100,'javacode') を挿入します。
クエリは正常、4 行が影響を受けました (0.00 秒)
記録: 4 重複: 0 警告: 0
mysql> test2 から * を選択します。
+------+----------+
| ア | ロ |
+------+----------+
| 10 | ジャック |
| 8 | トム |
| 5 | 準備完了 |
| 100 | ジャバコード |
+------+----------+
セット内の 4 行 (0.00 秒)
mysql> test2 から * を選択して、 asc で並べ替えます。
+------+----------+
| ア | ロ |
+------+----------+
| 5 | 準備完了 |
| 8 | トム |
| 10 | ジャック |
| 100 | ジャバコード |
+------+----------+
セット内の 4 行 (0.00 秒)
mysql> test2 から * を選択し、desc で並べ替えます。
+------+----------+
| ア | ロ |
+------+----------+
| 100 | ジャバコード |
| 10 | ジャック |
| 8 | トム |
| 5 | 準備完了 |
+------+----------+
セット内の 4 行 (0.00 秒)
mysql> test2 から * を選択して、順序を a で指定します。
+------+----------+
| ア | ロ |
+------+----------+
| 5 | 準備完了 |
| 8 | トム |
| 10 | ジャック |
| 100 | ジャバコード |
+------+----------+
セット内の 4 行 (0.00 秒)

複数フィールドの並べ替え

たとえば、学生テーブルでは、まず学生の年齢で降順で並べ替え、次に年齢が同じ場合は学生 ID で昇順で並べ替えます。

mysql> テーブル stu を作成します (id int not null コメント '学生番号' 主キー、age tinyint not null コメント '年齢'、name varchar(16) コメント '名前');
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
mysql> insert into stu (id,age,name) values (1001,18,'路人甲Java'),(1005,20,'刘德华'),(1003,18,'張薛友'),(1004,20,'張國荣'),(1010,19,'梁朝伟');
クエリは正常、5 行が影響を受けました (0.00 秒)
記録: 5 重複: 0 警告: 0
mysql> stu から * を選択します。
+------+-----+---------------+
| ID | 年齢 | 名前 |
+------+-----+---------------+
| 1001 | 18 | 通行人ジャワ |
| 1003 | 18 | ジャッキー・チュン |
| 1004 | 20 | レスリー・チャン |
| 1005 | 20 | アンディ・ラウ |
| 1010 | 19 | トニー・レオン・チウワイ |
+------+-----+---------------+
セット内の行数は 5 です (0.00 秒)
mysql> select * from stu order by age desc,id asc;
+------+-----+---------------+
| ID | 年齢 | 名前 |
+------+-----+---------------+
| 1004 | 20 | レスリー・チャン |
| 1005 | 20 | アンディ・ラウ |
| 1010 | 19 | トニー・レオン・チウワイ |
| 1001 | 18 | 通行人ジャワ |
| 1003 | 18 | ジャッキー・チュン |
+------+-----+---------------+
セット内の行数は 5 です (0.00 秒)

別名で並べ替え

mysql> stu から * を選択します。
+------+-----+---------------+
| ID | 年齢 | 名前 |
+------+-----+---------------+
| 1001 | 18 | 通行人ジャワ |
| 1003 | 18 | ジャッキー・チュン |
| 1004 | 20 | レスリー・チャン |
| 1005 | 20 | アンディ・ラウ |
| 1010 | 19 | トニー・レオン・チウワイ |
+------+-----+---------------+
セット内の行数は 5 です (0.00 秒)
mysql> select age 'age',id as 'student number' from stu order by ageasc,student numberdesc;
+--------+--------+
| 年齢| 学生証|
+--------+--------+
| 18 | 1003 |
| 18 | 1001 |
| 19 | 1010 |
| 20 | 1005 |
| 20 | 1004 |
+--------+--------+

機能別に並べ替え

次のような学生テーブル（id: 番号、birth: 生年月日、name: 名前）があります。

mysql> 存在する場合は Student テーブルを削除します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
mysql> 学生テーブルを作成 (
  -> id int(11) NOT NULL COMMENT '学生番号',
  -> 生年月日 NOT NULL COMMENT '生年月日',
  -> name varchar(16) デフォルト NULL コメント '名前',
  -> 主キー (id)
  -> );
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
mysql> 学生 (ID、生年月日、名前) に値を挿入します (1001,'1990-10-10'、'Passerby Java')、(1005,'1960-03-01'、'Andy Lau')、(1003,'1960-08-16'、'Jacky Cheung')、(1004,'1968-07-01'、'Leslie Cheung')、(1010,'1962-05-16'、'Tony Leung');
クエリは正常、5 行が影響を受けました (0.00 秒)
記録: 5 重複: 0 警告: 0
マイSQL>
mysql> SELECT * FROM 学生;
+------+------------+---------------+
| ID | 生年月日 | 名前 |
+------+------------+---------------+
| 1001 | 1990-10-10 | 通りすがりのJava |
| 1003 | 1960-08-16 | ジャッキー・チュン |
| 1004 | 1968-07-01 | レスリー・チャン |
| 1005 | 1960-03-01 | アンディ・ラウ |
| 1010 | 1962-05-16 | トニー・レオン・チウワイ |
+------+------------+---------------+
セット内の行数は 5 です (0.00 秒)

要件: 生年月日、生年月日、名前を生年月日と番号の昇順で照会します。次の 2 つの書き方があります。

mysql> SELECT id number,birth date of birth,year(birth) year of birth,name name from student ORDER BY year(birth) asc,id asc;
+--------+--------------+--------------+---------------+
| 番号| 生年月日| 生年月日| 名前|
+--------+--------------+--------------+---------------+
| 1003 | 1960-08-16 | 1960 | ジャッキー・チュン |
| 1005 | 1960-03-01 | 1960 | アンディ・ラウ |
| 1010 | 1962-05-16 | 1962 | トニー・レオン・チウワイ |
| 1004 | 1968-07-01 | 1968 | レスリー・チャン |
| 1001 | 1990-10-10 | 1990 | 通行人 Java |
+--------+--------------+--------------+---------------+
セット内の行数は 5 です (0.00 秒)
mysql> SELECT id number,birth date of birth,year(birth) year of birth,name name from student ORDER BY year of birth asc,id asc;
+--------+--------------+--------------+---------------+
| 番号| 生年月日| 生年月日| 名前|
+--------+--------------+--------------+---------------+
| 1003 | 1960-08-16 | 1960 | ジャッキー・チュン |
| 1005 | 1960-03-01 | 1960 | アンディ・ラウ |
| 1010 | 1962-05-16 | 1962 | トニー・レオン・チウワイ |
| 1004 | 1968-07-01 | 1968 | レスリー・チャン |
| 1001 | 1990-10-10 | 1990 | 通行人 Java |
+--------+--------------+--------------+---------------+
セット内の行数は 5 です (0.00 秒)

例:
year関数: 対応する日付の年を取得できる日付関数です。
上記のソートには 2 つの方法があります。1 つ目は order by 関数を使用する方法で、2 つ目はエイリアスソートを使用する方法です。

どこを基準に並べ替えるか

注文データは次のとおりです。

mysql> t_order が存在する場合はテーブルを削除します。
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.00 秒)
mysql> テーブル t_order( を作成します。
  -> id int not null auto_increment comment '注文番号',
  -> 価格 小数点(10,2) null ではない デフォルト 0 コメント '注文金額',
  -> 主キー(ID)
  -> )comment '注文表';
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
mysql> t_order (価格) に値 (88.95),(100.68),(500),(300),(20.88),(200.5) を挿入します。
クエリは正常、6 行が影響を受けました (0.00 秒)
記録: 6 重複: 0 警告: 0
mysql> t_order から * を選択します。
+----+--------+
| ID | 価格 |
+----+--------+
| 1 | 88.95 |
| 2 | 100.68 |
| 3 | 500.00 |
| 4 | 300.00 |
| 5 | 20.88 |
| 6 | 200.50 |
+----+--------+
セット内の 6 行 (0.00 秒)

要件: 注文金額 >= 100 を照会し、注文金額の降順で並べ替え、次のように 2 列のデータ (列ヘッダー: 注文番号、注文金額) を表示します。

mysql> select a.id order number, a.price order amount from t_order a where a.price>=100 order by a.price desc;
+--------------+--------------+
| 注文番号| 注文金額|
+--------------+--------------+
| 3 | 500.00 |
| 4 | 300.00 |
| 6 | 200.50 |
| 2 | 100.68 |
+--------------+--------------+
セット内の 4 行 (0.00 秒)

制限の紹介

Limit は、選択クエリによって返される行数を制限するために使用され、ページングなどの操作によく使用されます。

文法：

テーブルから列を選択 limit [offset,] count;

例:

offset: オフセットを表します。簡単に言えば、スキップする行数です。 offset は省略可能で、デフォルトは 0 です。つまり、0 行スキップすることを意味します。範囲: [0, +∞)。
count: オフセット行をスキップしてデータの取得を開始し、count 行のレコードを取得します。範囲: [0, +∞)。
limit 内の offset と count の値には式を使用できません。

以下に、理解を深めるためによく使用される例をいくつか示します。

最初のn行のレコードを取得する

テーブル制限 0,n から列を選択します。
または、テーブル制限 n から列を選択します。

たとえば、注文の最初の 2 つのレコードを取得するには、次のようにします。

mysql> テーブル t_order( を作成します。
  -> id int not null auto_increment comment '注文番号',
  -> 価格 小数点(10,2) null ではない デフォルト 0 コメント '注文金額',
  -> 主キー(ID)
  -> )comment '注文表';
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
mysql> t_order (価格) に値 (88.95),(100.68),(500),(300),(20.88),(200.5) を挿入します。
クエリは正常、6 行が影響を受けました (0.01 秒)
記録: 6 重複: 0 警告: 0
mysql> t_order から * を選択します。
+----+--------+
| ID | 価格 |
+----+--------+
| 1 | 88.95 |
| 2 | 100.68 |
| 3 | 500.00 |
| 4 | 300.00 |
| 5 | 20.88 |
| 6 | 200.50 |
+----+--------+
セット内の 6 行 (0.00 秒)
mysql> select a.id 注文番号、a.price 注文金額 from t_order a limit 2;
+--------------+--------------+
| 注文番号| 注文金額|
+--------------+--------------+
| 1 | 88.95 |
| 2 | 100.68 |
+--------------+--------------+
セット内の 2 行 (0.00 秒)
mysql> select a.id order number, a.price order amount from t_order a limit 0,2;
+--------------+--------------+
| 注文番号| 注文金額|
+--------------+--------------+
| 1 | 88.95 |
| 2 | 100.68 |
+--------------+--------------+
セット内の 2 行 (0.00 秒)

最大の記録を達成する

注文金額が最も大きいレコードを取得する必要があります。これは、次のように金額の降順で並べ替え、最初のレコードを取得することで実行できます。

mysql> select a.id order number, a.price order amount from t_order a order by a.price desc;
+--------------+--------------+
| 注文番号| 注文金額|
+--------------+--------------+
| 3 | 500.00 |
| 4 | 300.00 |
| 6 | 200.50 |
| 2 | 100.68 |
| 1 | 88.95 |
| 5 | 20.88 |
+--------------+--------------+
セット内の 6 行 (0.00 秒)
mysql> select a.id order number, a.price order amount from t_order a order by a.price desc limit 1;
+--------------+--------------+
| 注文番号| 注文金額|
+--------------+--------------+
| 3 | 500.00 |
+--------------+--------------+
セット内の 1 行 (0.00 秒)
mysql> select a.id order number, a.price order amount from t_order a order by a.price desc limit 0,1;
+--------------+--------------+
| 注文番号| 注文金額|
+--------------+--------------+
| 3 | 500.00 |
+--------------+--------------+
セット内の 1 行 (0.00 秒)

n から m までのランク付けされたレコードを取得します

次のように、最初に n-1 個のレコードをスキップし、次に m-n+1 個のレコードを取得する必要があります。

テーブルから列を選択、制限 n-1、m-n+1;

たとえば、注文金額が最も高い上位 3 ～ 5 件のレコードを取得する場合は、次のように 2 つのレコードをスキップして 3 つのレコードを取得する必要があります。

mysql> select a.id order number, a.price order amount from t_order a order by a.price desc;
+--------------+--------------+
| 注文番号| 注文金額|
+--------------+--------------+
| 3 | 500.00 |
| 4 | 300.00 |
| 6 | 200.50 |
| 2 | 100.68 |
| 1 | 88.95 |
| 5 | 20.88 |
+--------------+--------------+
セット内の 6 行 (0.00 秒)
mysql> select a.id order number, a.price order amount from t_order a order by a.price desc limit 2,3;
+--------------+--------------+
| 注文番号| 注文金額|
+--------------+--------------+
| 6 | 200.50 |
| 2 | 100.68 |
| 1 | 88.95 |
+--------------+--------------+
セット内の 3 行 (0.00 秒)

ページネーションクエリ

開発プロセスでは、ページングを頻繁に使用しますが、これには通常、次の 2 つのパラメーターがあります。
ページ: ページ番号を示します。1から始まり、[1, +∞)の範囲です。
pageSize: 1ページあたりに表示されるレコード数、範囲[1, +∞)
たとえば、page = 2、pageSize = 10 は、ページ 2 に 10 個のデータ項目を取得することを意味します。
ページングを実装するには limit を使用します。構文は次のとおりです。

テーブル名制限 (ページ - 1) * pageSize、pageSize から列を選択します。

要件: 注文金額の降順でページごとに 2 つのアイテムを表示し、すべての注文データ (ページ 1、ページ 2、ページ 3 のデータを順番に取得) を次のように取得します。

mysql> select a.id order number, a.price order amount from t_order a order by a.price desc;
+--------------+--------------+
| 注文番号| 注文金額|
+--------------+--------------+
| 3 | 500.00 |
| 4 | 300.00 |
| 6 | 200.50 |
| 2 | 100.68 |
| 1 | 88.95 |
| 5 | 20.88 |
+--------------+--------------+
セット内の 6 行 (0.00 秒)
mysql> select a.id order number, a.price order amount from t_order a order by a.price desc limit 0,2;
+--------------+--------------+
| 注文番号| 注文金額|
+--------------+--------------+
| 3 | 500.00 |
| 4 | 300.00 |
+--------------+--------------+
セット内の 2 行 (0.00 秒)
mysql> select a.id order number, a.price order amount from t_order a order by a.price desc limit 2,2;
+--------------+--------------+
| 注文番号| 注文金額|
+--------------+--------------+
| 6 | 200.50 |
| 2 | 100.68 |
+--------------+--------------+
セット内の 2 行 (0.00 秒)
mysql> select a.id order number, a.price order amount from t_order a order by a.price desc limit 4,2;
+--------------+--------------+
| 注文番号| 注文金額|
+--------------+--------------+
| 1 | 88.95 |
| 5 | 20.88 |
+--------------+--------------+
セット内の 2 行 (0.00 秒)

落とし穴を避ける

表現は制限内では使用できません

mysql> t_order から * を選択します。ここで、制限は 1,4+1 です。
エラー 1064 (42000): SQL 構文にエラーがあります。MySQL サーバーのバージョンに対応するマニュアルで、1 行目の 'limit 1,4+1' 付近で使用する正しい構文を確認してください。
mysql> t_order から * を選択します。ここで、制限は 1+0 です。
エラー 1064 (42000): SQL 構文にエラーがあります。MySQL サーバーのバージョンに対応するマニュアルで、1 行目の 'limit 1+0' 付近で使用する正しい構文を確認してください。
マイSQL>

結論: 制限の後には特定の数値のみが続きます。

制限値の後の 2 つの数値は負の数にできません。

mysql> t_order から * を選択します。ここで、制限は -1 です。
エラー 1064 (42000): SQL 構文にエラーがあります。MySQL サーバーのバージョンに対応するマニュアルで、1 行目の 'limit -1' 付近で使用する正しい構文を確認してください。
mysql> t_order から * を選択します。ここで、制限は 0,-1 です。
エラー 1064 (42000): SQL 構文にエラーがあります。MySQL サーバーのバージョンに対応するマニュアルで、1 行目の 'limit 0,-1' 付近で使用する正しい構文を確認してください。
mysql> t_order から * を選択します。ここで、制限は -1,-1 です。
エラー 1064 (42000): SQL 構文にエラーがあります。MySQL サーバーのバージョンに対応するマニュアルで、1 行目の 'limit -1,-1' 付近で使用する正しい構文を確認してください。

ソートページングの落とし穴

データを準備します:

mysql> test1 (b) に値 (1),(2),(3),(4),(2),(2),(2),(2); を挿入します。
クエリは正常、8 行が影響を受けました (0.01 秒)
記録: 8 重複: 0 警告: 0
mysql> test1 から * を選択します。
+---+---+
| ア | ロ |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
| 8 | 2 |
+---+---+
セット内の行数は 8 です (0.00 秒)
mysql> test1 から * を選択し、b で asc で並べ替えます。
+---+---+
| ア | ロ |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
| 8 | 2 |
| 3 | 3 |
| 4 | 4 |
+---+---+
セット内の行数は 8 です (0.00 秒)

次に、ページごとに 2 つのレコードで、b の昇順でデータを取得します。

次の SQL は、ページ 1、ページ 2、ページ 3、ページ 4、およびページ 5 のデータです。

mysql> test1 から * を選択し、b で並べ替え、asc で制限を 0,2 にします。
+---+---+
| ア | ロ |
+---+---+
| 1 | 1 |
| 2 | 2 |
+---+---+
セット内の 2 行 (0.00 秒)
mysql> test1 から * を選択し、b で並べ替え、asc で制限を 2,2 にします。
+---+---+
| ア | ロ |
+---+---+
| 8 | 2 |
| 6 | 2 |
+---+---+
セット内の 2 行 (0.00 秒)
mysql> test1 から * を選択し、b で並べ替え、asc で制限を 4,2 にします。
+---+---+
| ア | ロ |
+---+---+
| 6 | 2 |
| 7 | 2 |
+---+---+
セット内の 2 行 (0.00 秒)
mysql> test1 から * を選択し、b で並べ替え、asc で制限を 6,2 にします。
+---+---+
| ア | ロ |
+---+---+
| 3 | 3 |
| 4 | 4 |
+---+---+
セット内の 2 行 (0.00 秒)
mysql> test1 から * を選択し、b で並べ替え、asc で制限を 7,2 にします。
+---+---+
| ア | ロ |
+---+---+
| 4 | 4 |
+---+---+
セット内の 1 行 (0.00 秒)

上記には2つの質問があります:

質問 1: 2 番目と 3 番目の SQL を見てください。それぞれページ 2 とページ 3 のデータです。結果には同じデータが表示されます。混乱していませんか?

質問 2: テーブル全体には 8 つのレコードしかないのに、なぜ 5 ページ目のデータが表示されるのでしょうか。また混乱しています。

上記の理由を分析してみましょう。主な理由は、b フィールドの値が同じであることです。ソート処理中に同じ値が存在し、他のソート規則がない場合、MySQL は混乱し、ソート方法がわかりません。

学校で並ぶとき、身長順に並びますが、同じ身長の場合はどのように並び替えるのでしょうか?同じ高さのものはランダムに配置されます。

提案: ソートに同一の値がある場合は、別のソートルールを指定する必要があります。このソートルールには曖昧さはありません。たとえば、次のように降順を上記に追加できます。

mysql> test1 から * を選択し、b asc、a desc で順序付けします。
+---+---+
| ア | ロ |
+---+---+
| 1 | 1 |
| 8 | 2 |
| 7 | 2 |
| 6 | 2 |
| 5 | 2 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+---+---+
セット内の行数は 8 です (0.00 秒)
mysql> test1 から * を選択します。order by b asc,a desc limit 0,2;
+---+---+
| ア | ロ |
+---+---+
| 1 | 1 |
| 8 | 2 |
+---+---+
セット内の 2 行 (0.00 秒)
mysql> test1 から * を選択し、b asc、a desc で順序付けし、制限を 2,2 にします。
+---+---+
| ア | ロ |
+---+---+
| 7 | 2 |
| 6 | 2 |
+---+---+
セット内の 2 行 (0.00 秒)
mysql> select * from test1 order by b asc,a desc limit 4,2;
+---+---+
| ア | ロ |
+---+---+
| 5 | 2 |
| 2 | 2 |
+---+---+
セット内の 2 行 (0.00 秒)
mysql> select * from test1 order by b asc,a desc limit 6,2;
+---+---+
| ア | ロ |
+---+---+
| 3 | 3 |
| 4 | 4 |
+---+---+
セット内の 2 行 (0.00 秒)
mysql> select * from test1 order by b asc,a desc limit 8,2;
空のセット (0.00 秒)

上記の結果を見ると、ページングデータは正常であり、5ページ目にはデータがありません。

要約する

order by ... [asc|desc] はクエリ結果を並べ替えるために使用されます。asc: 昇順、desc: 降順、asc|desc は省略可能、デフォルトは asc です。
limit は、クエリ結果によって返される行数を制限するために使用されます。2 つのパラメーター (offset、count) があります。offset: はスキップする行数を示し、count: は offset 行をスキップして count 行を取得することを示します。
制限内のオフセットは省略可能で、デフォルト値は0です。
制限内のオフセットとカウントは両方とも 0 以上である必要があります
limit 内の offset と count の値は式として表現できません。
ページごとに並べ替える場合、並べ替えがあいまいであってはなりません。あいまいな場合、ページング結果の順序が乱れる可能性があります。最後に主キーによる並べ替えを追加できます。

これで、MySQL のソートとページング (order by と limit) および既存の落とし穴に関するこの記事は終了です。MySQL のソートとページングに関するより関連性の高いコンテンツについては、123WORDPRESS.COM で以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: