MySQLを使用する際のいくつかの興味深いトリックと機能

特定のDBMSを研究する過程で、各自が問題を解決するために自転車を発明したことがあると思います。特定の関数またはテクニックの存在を知らず、クエリの実行を高速化し、コードの量を減らすことができます。 この記事では、非常に「親切」かつ「レスポンシブ」なMySQLでの私の経験を共有したいと思います。これにより、プログラマは他のDBMSが消化できないことを行うことができます。 この資料は、クエリのすばらしい世界を掘り下げようと決心した人にとってはむしろ有用ですが、おそらく経験豊富なプログラマーはここで何か面白いものを見つけるでしょう。

重複削除

非常に多くの場合、さまざまな専門リソースで、テーブル内の重複エントリを迅速かつ最適に削除する方法についての質問に遭遇しました。 すぐに、たとえばINSERT IGNOREを使用して、このテーブルと同じ別のテーブルを作成し、そのテーブルに一意のキーを作成して、元のテーブルからデータをコピーする必要があることに気付きます。 しかし、もっと簡単な方法があります。 次のクエリを使用して、テーブルに一意のキーを作成するだけです。

ALTER IGNORE TABLE table1 ADD UNIQUE (field1, field2);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

キーを追加した後、すべての重複は自動的に削除されます。

文字列を数値に変換

ある番号の家の住所の住所テーブルを検索するタスクがあるとします。 さらに、家番号は、タイプ「1」、「1a」、「1 / b」、「2y」、「3ytsuken」などの値を含むnumテキストフィールドに格納されます。 そして、1と他のいくつかのキャラクターで構成される数字を持つ家を選択したいと思います。 LIKEまたはREGEXPを使用したソリューションを探すために、多くの人がすぐに急ぐと思います。 ただし、次のMySQL機能を使用する方が簡単です。

 SELECT * FROM address WHERE num + 0 = 1;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

算術演算を満たしていると、MySQLはすべての引数を自動的に数値型にキャストします。 文字列の場合、すべての文字は数値以外の最初の文字から単純に切り捨てられます。

エラーなしで静かに実行されたこのようなリクエストもここにあります：

 SELECT '1qwe3s' + '2regt3g';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

その結果、答えが得られます：3。

クエリで変数を使用する

ここで、変数が私たちの生活を楽にする問題を解決する例をすぐに示します。

次の表table1があります。

id	合計
1	35
2	25
3	10
4	55
5	12

これらすべてのフィールドを表示し、それらにさらに2つ、onStartおよびtotalを追加する必要があります。

合計= summ-onStart。

onStartは、最初のレコードonStart = 0の場合、前のレコードの合計値に等しくなります。

つまり 最終的に、次の結果が得られます。

id	合計	onStart	合計
1	35	0	35
2	25	35	-10
3	10	-10	20
4	55	20	35
5	12	35	-23

変数を使用すると、この問題を解決するときに余分なJOINとサブクエリを取り除くことができます。

 SELECT t1.id, t1.summ, @i AS onStart, @i := t1.summ - @i AS total FROM table1 t1 JOIN (SELECT @i := 0) var;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

テーブル内の異なるレコードの数を数える

別の一般的なタスク。 そして、ここですぐに例を挙げます。

テーブルtable1（id、f1、f2）を指定します。 次の結果を返すクエリを作成する必要があります。

エントリの総数	f1 = 1のレコード数	f1 = 2のf2の値の合計

もちろん、次のような結果を得ることができます。

 SELECT COUNT(1), (SELECT COUNT(1) FROM table1 WHERE f1 = 1), (SELECT SUM(f2) FROM table1 WHERE f1 = 2) FROM table1;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

しかし、これが最適なソリューションとはほど遠いことは明らかです。 各レコードには2つの追加のサブクエリがあります。 そして、それを別の方法で行います。

 SELECT COUNT(1), SUM(f1 = 1), SUM(IF(f1 = 2, f2, 0)) FROM table1;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

もう一つ。 必要なものはすべて、テーブルの1つのパスでカウントしました。

グループステートメントの列 'id'があいまいです

記事のこの部分では、MySQLの興味深い機能の1つに注意を向けたいと思います。

次のリクエストがあります。

 SELECT t1.id, t2.id FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id_t1 GROUP BY id;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

GROUP BYブロックでidフィールドのエイリアスを指定するのを忘れたため、リクエストを実行しようとすると、「グループステートメントの列 'id'があいまいです」というエラーを受け取りました。 すべてが真実であるように思われます。 次に、このクエリを変更します。

 SELECT t1.id, t2.f1 FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id_t1 GROUP BY id;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

表示されたフィールドのリストからt2.idを削除しました。見よ、要求が機能し、データはt1.idによってグループ化されました。 MS SQLやPostgreSQLなどの他のDBMSでは、2番目のケースでエラーが発生しますが、MySQLの場合、2番目のクエリは完全に正しいです。

そのため、フィールドの前で常に注意してエイリアスを使用することをお勧めします。そうしないと、リクエストにわずかな変更を加えると、エラーが発生する可能性があります。

最終日付データを検索する

最後に、複雑ではなく、よく遭遇する問題の1つを解決する別の例を示します。 何らかの理由で、多くの人にとってしばしば困難をもたらします。

支払いテーブルには、支払い（id INT、uid INT、pay_date DATETIME、金額DECIMAL（15、2））が与えられます。

id-主キー

uid-ユーザー識別子

pay_date-支払い日

金額-支払い金額

各ユーザーに最後の支払いの日付と金額を表示するリクエストを作成する必要があります。

UPD。 ユーザーは1秒間に複数回の支払いはできないと考えています。 （この条件がなければ、問題の記述は正しくありません）。 Pay_dateタイプがDATEからDATETIMEに変更されました。

次の標準ソリューションを提供します。

 SELECT p.uid, p.amount FROM payments p JOIN (SELECT uid, MAX(pay_date) AS max_dt FROM payments GROUP BY uid) sel ON p.uid = sel.uid AND p.pay_date = sel.max_dt;