Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


13.2.9 SELECT 構文

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT は、1 つ以上のテーブルから選択された行を取得するために使用され、UNION ステートメントとサブクエリーを含めることができます。セクション13.2.9.4「UNION 構文」およびセクション13.2.10「サブクエリー構文」を参照してください。

SELECT ステートメントのもっとも一般的に使用される句は次のとおりです。

  • select_expr は、取得するカラムを示します。少なくとも 1 つの select_expr が存在する必要があります。

  • table_references は、行を取得する 1 つまたは複数のテーブルを示します。その構文については、セクション13.2.9.2「JOIN 構文」で説明されています。

  • MySQL 5.6.2 から、SELECT は、table_reference 内のテーブル名のあとにパーティションまたはサブパーティション (またはその両方) のリストを含む PARTITION キーワードを使用した明示的なパーティション選択をサポートしています (セクション13.2.9.2「JOIN 構文」を参照してください)。この場合、行はリストされているパーティションからのみ選択され、テーブルのほかのパーティションはすべて無視されます。詳細および例については、セクション19.5「パーティション選択」を参照してください。

    MySQL 5.6.6 以降では、テーブルレベルのロック (つまり、パーティションロック) を実行する MyISAM などのストレージエンジンを使用しているテーブルからの SELECT ... PARTITION では、PARTITION オプションで指定されているパーティションまたはサブパーティションのみがロックされます。

    詳細は、セクション19.6.4「パーティショニングとロック」を参照してください。

  • WHERE 句 (指定されている場合) は、選択されるために行が満たす必要のある 1 つまたは複数の条件を示します。where_condition は、選択される各行に対して true に評価される式です。WHERE 句がない場合、このステートメントはすべての行を選択します。

    WHERE 式では、集約 (サマリー) 関数を除き、MySQL がサポートするすべての関数および演算子を使用できます。セクション9.5「式の構文」および第12章「関数と演算子を参照してください。

SELECT を使用して、どのテーブルも参照せずに計算された行を取得することもできます。

例:

mysql> SELECT 1 + 1;
        -> 2

テーブルが参照されない状況では、ダミーのテーブル名として DUAL を指定することが許可されます。

mysql> SELECT 1 + 1 FROM DUAL;
        -> 2

DUAL は純粋に、すべての SELECT ステートメントに FROM や、場合によってはその他の句が存在することを要求するユーザーの便宜のために用意されています。MySQL は、これらの句を無視する可能性があります。MySQL では、テーブルが参照されない場合でも FROM DUAL は必要ありません。

一般に、使用される句は、正確に構文の説明で示されている順序で指定する必要があります。たとえば、HAVING 句は、すべての GROUP BY 句のあとで、かつすべての ORDER BY 句の前にある必要があります。例外として、INTO 句は、構文の説明で示されている位置か、または select_expr リストの直後のどちらにも現れることができます。INTO の詳細は、セクション13.2.9.1「SELECT ... INTO 構文」を参照してください。

select_expr 項のリストは、どのカラムを取得するかを示す選択リストで構成されています。これらの項はカラムや式を指定するか、または * の短縮形を使用できます。

  • 1 つの修飾されていない * のみから成る選択リストは、すべてのテーブルのすべてのカラムを選択するための短縮形として使用できます。

    SELECT * FROM t1 INNER JOIN t2 ...
    
  • tbl_name.* は、指定されたテーブルのすべてのカラムを選択するための修飾された短縮形として使用できます。

    SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...
    
  • 修飾されていない * を選択リスト内のほかの項目とともに使用すると、解析エラーが生成される可能性があります。この問題を回避するには、修飾された tbl_name.* 参照を使用します。

    SELECT AVG(score), t1.* FROM t1 ...
    

次のリストは、その他の SELECT 句に関する追加情報を示しています。

  • AS alias_name を使用して、select_expr にエイリアスを指定できます。エイリアスは式のカラム名として使用され、GROUP BYORDER BY、または HAVING 句で使用できます。例:

    SELECT CONCAT(last_name,', ',first_name) AS full_name
      FROM mytable ORDER BY full_name;
    

    select_expr にエイリアスとして識別子を指定する場合、AS キーワードはオプションです。前の例は、次のように記述することもできました。

    SELECT CONCAT(last_name,', ',first_name) full_name
      FROM mytable ORDER BY full_name;
    

    ただし、AS はオプションであるため、2 つの select_expr 式の間のカンマを忘れると、軽微な問題が発生する可能性があります。MySQL は、2 番目の式をエイリアスとして解釈します。たとえば、次のステートメントでは、columnb はエイリアスとして処理されます。

    SELECT columna columnb FROM mytable;
    

    このため、カラムのエイリアスを指定するときは AS を明示的に使用するようにすることをお勧めします。

    WHERE 句が実行されるときはまだカラム値が決定されていない可能性があるため、WHERE 句内でカラムのエイリアスを参照することは許可されません。セクションB.5.5.4「カラムエイリアスに関する問題」を参照してください。

  • FROM table_references 句は、行を取得する 1 つまたは複数のテーブルを示します。複数のテーブルを指定すると、結合が実行されます。結合構文については、セクション13.2.9.2「JOIN 構文」を参照してください。指定されたテーブルごとに、オプションでエイリアスを指定できます。

    tbl_name [[AS] alias] [index_hint]
    

    インデックスヒントを使用すると、クエリー処理中にインデックスを選択する方法に関する情報がオプティマイザに提供されます。これらのヒントを指定するための構文については、セクション13.2.9.3「インデックスヒントの構文」を参照してください。

    代わりの方法として SET max_seeks_for_key=value を使用して、MySQL にテーブルスキャンの代わりにキースキャンを強制的に実行させることができます。セクション5.1.4「サーバーシステム変数」を参照してください。

  • データベースを明示的に指定するために、デフォルトデータベース内でテーブルを tbl_name または db_name.tbl_name として参照できます。カラムを col_nametbl_name.col_name または db_name.tbl_name.col_name として参照できます。参照があいまいにならないかぎり、カラム参照のために tbl_name または db_name.tbl_name プリフィクスを指定する必要はありません。より明示的なカラム参照形式を必要とするあいまいさの例については、セクション9.2.1「識別子の修飾子」を参照してください。

  • tbl_name AS alias_name または tbl_name alias_name を使用して、テーブル参照にエイリアスを指定できます。

    SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
      WHERE t1.name = t2.name;
    
    SELECT t1.name, t2.salary FROM employee t1, info t2
      WHERE t1.name = t2.name;
    
  • カラム名、カラムのエイリアス、またはカラム位置を使用して、出力のために選択されたカラムを ORDER BY および GROUP BY 句で参照できます。カラム位置は整数であり、1 から始まります。

    SELECT college, region, seed FROM tournament
      ORDER BY region, seed;
    
    SELECT college, region AS r, seed AS s FROM tournament
      ORDER BY r, s;
    
    SELECT college, region, seed FROM tournament
      ORDER BY 2, 3;
    

    逆の順序でソートするには、ソートに使用する ORDER BY 句内のカラムの名前に DESC (降順) キーワードを追加します。デフォルトは昇順です。これは、ASC キーワードを使用して明示的に指定できます。

    ORDER BY がサブクエリー内で発生し、外側のクエリー内でも適用される場合は、もっとも外側の ORDER BY が優先されます。たとえば、次のステートメントの結果は昇順ではなく、降順でソートされます。

    (SELECT ... ORDER BY a) ORDER BY a DESC;
    

    カラム位置の使用は、この構文が SQL 標準から削除されたため非推奨です。

  • GROUP BY を使用すると、出力行は、同じカラムに対して ORDER BY を指定したかのように GROUP BY カラムに従ってソートされます。GROUP BY によって生成されるソートのオーバーヘッドを回避するには、ORDER BY NULL を追加します。

    SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
    

    MySQL 5.6 における暗黙の GROUP BY ソートへの依存は、非推奨になっています。グループ化された結果の特定のソート順序を実現するには、明示的な ORDER BY 句を使用することをお勧めします。GROUP BY ソートは、たとえば、オプティマイザがもっとも効率的であると考えるどのような方法でも、グループ化を指示できるようにしたり、ソートオーバーヘッドを回避したりするためなどに、今後のリリースで変更される可能性のある MySQL 拡張機能です。

  • MySQL では GROUP BY 句が拡張され、この句で指定されているカラムのあとに ASCDESC も指定できるようになっています。

    SELECT a, COUNT(b) FROM test_table GROUP BY a DESC;
    
  • MySQL では、GROUP BY の使用が、GROUP BY 句で指定されていないフィールドの選択を許可するように拡張されています。クエリーから期待する結果が得られない場合は、セクション12.19「GROUP BY 句で使用される関数と修飾子」にある GROUP BY の説明を参照してください。

  • GROUP BY では、WITH ROLLUP 修飾子が許可されます。セクション12.19.2「GROUP BY 修飾子」を参照してください。

  • HAVING 句は、ほぼ最後 (項目がクライアントに送信される直前) に最適化なしで適用されます。(LIMITHAVING のあとに適用されます。)

    SQL 標準では、HAVINGGROUP BY 句内のカラムか、または集約関数で使用されるカラムしか参照できません。ただし、MySQL ではこの動作への拡張がサポートされており、HAVINGSELECT リスト内のカラムや外側サブクエリー内のカラムを参照することも許可されます。

    HAVING 句があいまいなカラムを参照している場合は、警告が発生します。次のステートメントにある col2 は、エイリアスとカラム名の両方として使用されているため、あいまいです。

    SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
    

    標準 SQL の動作の方が優先されるため、HAVING のカラム名が GROUP BY で使用されると同時に、出力カラムリスト内のエイリアスが指定されたカラムとしても使用されている場合は、GROUP BY カラム内のカラムが優先されます。

  • WHERE 句に含めるべき項目には HAVING を使用しないでください。たとえば、次のように記述しないでください。

    SELECT col_name FROM tbl_name HAVING col_name > 0;
    

    代わりに、次のように記述してください。

    SELECT col_name FROM tbl_name WHERE col_name > 0;
    
  • HAVING 句は、WHERE 句が参照できない集約関数を参照できます。

    SELECT user, MAX(salary) FROM users
      GROUP BY user HAVING MAX(salary) > 10;
    

    (これは、一部の古いバージョンの MySQL では機能しませんでした。)

  • MySQL では、重複したカラム名が許可されます。つまり、同じ名前を持つ複数の select_expr が存在できます。これは、標準 SQL の拡張です。MySQL では GROUP BYHAVINGselect_expr 値を参照することも許可されるため、これにより、あいまいさが発生する場合があります。

    SELECT 12 AS a, a FROM t GROUP BY a;
    

    このステートメントでは、どちらのカラムの名前も a です。グループ化のために正しいカラムが使用されるようにするために、select_expr ごとに異なる名前を使用してください。

  • MySQL は、ORDER BY 句内の修飾されていないカラムまたはエイリアス参照を、まず select_expr 値、次に FROM 句内のテーブルのカラム内を検索することによって解決します。GROUP BY または HAVING 句の場合は、select_expr 値内を検索する前に FROM 句を検索します。(GROUP BYHAVING について、これは、ORDER BY) の場合と同じルールを使用していた MySQL 5.0 より前の動作とは異なります。

  • LIMIT 句を使用すると、SELECT ステートメントによって返される行数を制約できます。LIMIT は 1 つまたは 2 つの数値引数を受け取ります。これは、どちらも負ではない整定数である必要があります。ただし、次の例外があります。

    • 準備済みステートメント内では、? プレースホルダマーカーを使用して LIMIT パラメータを指定できます。

    • ストアドプログラム内では、整数値のルーチンパラメータまたはローカル変数を使用して LIMIT パラメータを指定できます。

    引数が 2 つの場合、最初の引数は返す先頭行のオフセットを指定し、2 番目の引数は返す行の最大数を指定します。最初の行のオフセットは (1 ではなく) 0 です。

    SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15
    

    特定のオフセットから結果セットの最後までのすべての行を取得するために、2 番目のパラメータにある程度大きい数字を使用できます。次のステートメントは、96 行目から最後の行までのすべての行を取得します。

    SELECT * FROM tbl LIMIT 95,18446744073709551615;
    

    引数が 1 つの場合、この値は、結果セットの先頭から返す行数を指定します。

    SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows
    

    つまり、LIMIT row_countLIMIT 0, row_count と同等です。

    準備済みステートメントでは、プレースホルダを使用できます。次のステートメントは、tbl テーブルの 1 行を返します。

    SET @a=1;
    PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
    EXECUTE STMT USING @a;
    

    次のステートメントは、tbl テーブルの 2 行目から 6 行目までを返します。

    SET @skip=1; SET @numrows=5;
    PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
    EXECUTE STMT USING @skip, @numrows;
    

    PostgreSQL との互換性のために、MySQL は LIMIT row_count OFFSET offset 構文もサポートしています。

    LIMIT がサブクエリー内に現れ、また外部クエリーでも適用される場合は、もっとも外側の LIMIT が優先されます。たとえば、次のステートメントは、1 行ではなく 2 行を生成します。

    (SELECT ... LIMIT 1) LIMIT 2;
    
  • PROCEDURE 句は、結果セット内のデータを処理するプロシージャーを指定します。例については、セクション8.4.2.4「PROCEDURE ANALYSE の使用」を参照してください。ここでは、テーブルサイズの削減に役立つ可能性のある最適なカラムデータ型に関する提案を得るために使用できるプロシージャーである ANALYSE について説明しています。

  • SELECTSELECT ... INTO 形式を使用すると、クエリー結果をファイルに書き込んだり、変数に格納したりできます。詳細は、セクション13.2.9.1「SELECT ... INTO 構文」を参照してください。

  • ページまたは行ロックを使用するストレージエンジンで FOR UPDATE を使用した場合、クエリーによって検査される行は、現在のトランザクションの最後まで書き込みがロックされます。LOCK IN SHARE MODE を使用すると、その検査される行のほかのトランザクションによる読み取りは許可するが、その更新または削除を許可しない共有ロックが設定されます。セクション14.2.5「ロック読み取り (SELECT ... FOR UPDATE および SELECT ... LOCK IN SHARE MODE)」を参照してください。

    さらに、CREATE TABLE new_table SELECT ... FROM old_table ... などのステートメントで SELECT の一部として FOR UPDATE を使用することはできません。(それを行おうとすると、このステートメントはエラー Can't update table 'old_table' while 'new_table' is being created で拒否されます。)これは、CREATE TABLE ... SELECT ステートメントが作成されているテーブル以外のテーブルで変更を行うことを許可していた、MySQL 5.5 およびそれ以前からの動作の変更です。

SELECT キーワードのあとに、このステートメントの操作に影響を与えるいくつかのオプションを使用できます。HIGH_PRIORITYSTRAIGHT_JOIN、および SQL_ で始まるオプションは、標準 SQL への MySQL 拡張です。

  • ALL および DISTINCT オプションは、重複した行を返すかどうかを指定します。ALL (デフォルト) は、重複を含め、一致するすべての行を返すように指定します。DISTINCT は、重複した行の結果セットからの削除を指定します。両方のオプションを指定するとエラーになります。DISTINCTROWDISTINCT のシノニムです。

  • HIGH_PRIORITYSELECT に、テーブルを更新するステートメントより高い優先度を与えます。これは、非常に高速であり、かつただちに実行する必要のあるクエリーにのみ使用するようにしてください。テーブルが読み取りに対してロックされている間に発行された SELECT HIGH_PRIORITY クエリーは、そのテーブルが未使用になるのを待機している更新ステートメントが存在する場合でも実行されます。これは、テーブルレベルロックのみを使用するストレージエンジン (MyISAMMEMORY、および MERGE) にのみ影響を与えます。

    HIGH_PRIORITY を、UNION の一部である SELECT ステートメントで使用することはできません。

  • STRAIGHT_JOIN は、オプティマイザに、テーブルを FROM 句にリストされている順序で強制的に結合させます。オプティマイザがテーブルを最適でない順序で結合する場合は、これを使用してクエリーを高速化できます。STRAIGHT_JOIN はまた、table_references リストでも使用できます。セクション13.2.9.2「JOIN 構文」を参照してください。

    STRAIGHT_JOIN は、オプティマイザが const テーブルまたは system テーブルとして処理するテーブルには適用されません。このようなテーブルは単一行を生成し、クエリー実行の最適化フェーズ中に読み取られます。また、そのカラムへの参照は、クエリー実行が続行される前に適切なカラム値で置き換えられます。これらのテーブルは、EXPLAIN によって表示されるクエリー計画に最初に表示されます。「セクション8.8.1「EXPLAIN によるクエリーの最適化」」を参照してください。この例外は、外部結合の NULL で補完された側で使用されている const テーブルまたは system テーブル (つまり、LEFT JOIN の右側のテーブルまたは RIGHT JOIN の左側のテーブル) には適用されない可能性があります。

  • SQL_BIG_RESULT または SQL_SMALL_RESULT は、結果セットの行数がそれぞれ多いこと、または少ないことをオプティマイザに通知するために GROUP BY または DISTINCT とともに使用できます。SQL_BIG_RESULT の場合、MySQL は、必要に応じてディスクベースの一時テーブルを直接使用し、ソートでは GROUP BY 要素に関するキーで一時テーブルを使用することを優先します。SQL_SMALL_RESULT の場合、MySQL はソートを使用する代わりに、高速な一時テーブルを使用して結果のテーブルを格納します。これは、通常は必要ないはずです。

  • SQL_BUFFER_RESULT は、結果を強制的に一時テーブルに配置します。これは、MySQL がテーブルロックを早期に解放する場合や、結果セットをクライアントに送信するのに長い時間がかかる場合に役立ちます。このオプションは、サブクエリーまたは後続の UNION ではなく、トップレベルの SELECT ステートメントでのみ使用できます。

  • SQL_CALC_FOUND_ROWS は MySQL に、LIMIT 句をすべて無視して、結果セットに含まれる行数を計算するよう指示します。そのあと、行数は SELECT FOUND_ROWS() を使用して取得できます。セクション12.14「情報関数」を参照してください。

  • SQL_CACHE および SQL_NO_CACHE オプションは、クエリーキャッシュ内のクエリー結果のキャッシュに影響を与えます (セクション8.9.3「MySQL クエリーキャッシュ」を参照してください)。SQL_CACHE は MySQL に、結果がキャッシュ可能であり、query_cache_type システム変数の値が 2 または DEMAND である場合は、結果をクエリーキャッシュに格納するよう指示します。SQL_NO_CACHE を指定すると、サーバーはクエリーキャッシュを使用しません。それは、結果がすでにキャッシュされているかどうかを確認するためにクエリーキャッシュをチェックせず、クエリー結果もキャッシュしません。(パーサーの制限のため、スペース文字の前後に SQL_NO_CACHE キーワードを付ける必要があります。改行などのスペース以外では、結果がすでにキャッシュされているかどうかを確認するために、サーバーにクエリーキャッシュをチェックさせます。)

    ビューの場合、SQL_NO_CACHE は、クエリー内のいずれかの SELECT に現れた場合に適用されます。キャッシュ可能なクエリーでは、SQL_CACHE は、そのクエリーによって参照されるビューの最初の SELECT に現れた場合に適用されます。

    MySQL 5.6 では、これらの 2 つのオプションは相互に排他的であり、両方が指定された場合はエラーが発生します。また、これらのオプションは、サブクエリー (FROM 句内のサブクエリーを含む) や、最初の SELECT を除く和集合内の SELECT ステートメント内では許可されません。

MySQL 5.6.6 より前は、テーブルレベルのロックを採用した MyISAM などのストレージエンジンを使用しているパーティション化されたテーブルからの SELECT によって、そのテーブルのすべてのパーティションがロックされました。これは、SELECT ... PARTITION クエリーにも当てはまりました。(これは、行レベルロックを採用した InnoDB などのストレージエンジンでは発生しておらず、現在も発生しません。)MySQL 5.6.6 以降では、MySQL はパーティションロックプルーニングを使用します。これにより、SELECT ステートメントの WHERE 句に一致する行を含むパーティションだけが実際にロックされるようになります。詳細は、セクション19.6.4「パーティショニングとロック」を参照してください。


User Comments
  Posted by Colin Nelson on February 26, 2003
You can simulate a CROSSTAB by the following method:-

Use IF function to select the key value of the sub table as in:

SELECT
SUM(IF(beta_idx=1, beta_value,0)) as beta1_value,
SUM(IF(beta_idx=2, beta_value,0)) as beta2_value,
SUM(IF(beta_idx=3, beta_value,0)) as beta3_value
FROM alpha JOIN beta WHERE alpha_id = beta_alpha_id;

where alpha table has the form alpha_id, alpha_blah, alpha_blah_blah
and beta table has the form beta_alpha_id, beta_other stuff,
beta_idx, beta_value

This will create 3 columns with totals of beta values according to their idx field
  Posted by Corin Langosch on March 29, 2003
when selecting a single random row you have to use a query like this: SELECT ... FROM my_table ORDER BY RAND() LIMIT 1.
as explain shows, mysql optimizes this VERY badly (or may be better said, doens't optimize it at all): it uses an temporary table and an extra filesort.
couldn't this be optimized?!
if not, may be add a syntax like SELECT RANDOM_ROW .... FROM my_table ...
  Posted by David Phillips on April 2, 2003
This method of selecting a random row should be fast:

LOCK TABLES foo READ;
SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM foo;
SELECT * FROM foo LIMIT $rand_row, 1;
UNLOCK TABLES;

Unfortunately, variables cannot be used in the LIMIT clause, otherwise the entire thing could be done completely in SQL.
  Posted by on August 20, 2003
In reply to David Philips:

If your tables are not all that big, a simpler method is:
SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1;

If it's a big table, your method will almost certainly be faster.

  Posted by Count Henry De Havilland-Fortesque-Smedley on January 13, 2004
If you want to find duplicates on a field that hasn't been uniquely indexed, you can do this:

SELECT BookISBN, count(BookISBN) FROM Books GROUP BY BookISBN HAVING COUNT(BookISBN)>1;

  Posted by Count Henry De Havilland-Fortesque-Smedley on January 13, 2004
Sometimes you want to retrieve the records that DONT match a select statement.

Consider this select:
SELECT CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.CarIndex=BigCatalog.CarIndex

This finds all the CarIndex values in the Dealer's catalog that are in the bigger distributor catalog.

How do I then find the dealer CarIndex values that ARE NOT in the bigger catalog?

The answer is to use LEFT JOIN - anything that doesn't join is given a NULL value , so we look for that:

SELECT CarIndex FROM DealerCatalog LEFT JOIN BigCatalog ON DealerCatalog.CarIndex=BigCatalog.CarIndex WHERE BigCatalog.CarIndex IS NULL
  Posted by Johann Eckert on February 11, 2004
To find double entries in a table:

SELECT db1.*
FROM tbl_data db1, tbl_data k2
WHERE db1.id <> db2.id
AND db1.name = db2.name

db1.id must be the PK
db1.name must be the fields that should be verified as double entries.

(I'm not sure wether the code is correct but in my case it works)

Johann
  Posted by on March 2, 2004
In order to anti-match fields by wildcards, one has to check whether the value of the field is not NULL:

For example: The table 'runs' contains 34876 rows. 205 rows have an 'info' field containing the string 'wrong'.

To select those rows for which the 'info' column does *NOT* contain the word 'wrong' one has to do:

mysql> select count(*) FROM runs WHERE info is null or info not like '%wrong%';
+----------+
| count(*) |
+----------+
| 34671 |
+----------+

but not:
mysql> select count(*) FROM runs WHERE info not like %wrong%';
+----------+
| count(*) |
+----------+
| 5537 |
+----------+

which would lead to a much smaller number of selected rows.
  Posted by M M on March 4, 2004
I have managed to select random records using php and MySQL like the following:

$min=1;
$row=mysql_fetch_assoc(mysql_query("SHOW TABLE STATUS LIKE 'table';"));
$max=$row["Auto_increment"];

$random_id=rand($min,$max);
$row=mysql_fetch_assoc(mysql_query("SELECT * FROM table WHERE id='$random_id'");

Voila...

Cezar
http://RO-Escorts.com
  Posted by Geert van der Ploeg on March 9, 2004
Random records without PHP, only MySQL:

select * from mailinglists order by rand() limit 1

Regards,
Geert van der Ploeg
  Posted by Cody Caughlan on May 26, 2004
Sometimes it is nice to use the SELECT query options like SQL_CALC_FOUND_ROWS or SQL_CACHE, but to maintain compatibility across different databases or even older versions of MySQL which do not support those options, it is possible to enclose them in a comment block, e.g.:

SELECT /*! 40000 SQL_CALC_FOUND_ROWS */ foo,bar FROM some_table;

The /* construct will stop DBMS's other than MySQL from parsing the comment contents, while /*! will tell ALL MySQL versions to parse the "comment" (which is actually a non-comment to MySQL). The /*!40000 construct will tell MySQL servers starting from 4.0.0 (which is the first version to support SQL_CALC_FOUND_ROWS) to parse the comment, while earlier versions will ignore it.
  Posted by Boris Aranovich on June 9, 2004
I am using this way to select random row or rows:

SELECT * [or any needed fileds], idx*0+RAND() as rnd_id FROM tablename ORDER BY rnd_id LIMIT 1 [or the number of rows]

Meanwhile, I didn't stumble in any problems with this usage.
I picked this method in some forum, don't remember when, where or by who was it introduced :)
  Posted by Michal Nedoszytko on August 10, 2004
My method of retrieving duplicate entries

In a database with personal information (name, surname, etc..) with an auto_increment index I wanted to retrieve all the entries with same name and surname field (duplicate names), which by accident were inserted to the base.

I used this syntax

SELECT name,surname,COUNT(name) AS cnt_n, COUNT(surname) AS cnt_s FROM the_table GROUP BY name HAVING cnt_n>1 AND cnt_s>1;

I hope this might be of help to anyone that wants to do some extended maintenance on the database
  Posted by Dmitri Mikhailov on August 24, 2004
On the other hand, for this case it's simplier to engage an appropriate index if there is such:

CREATE INDEX ccr_news_insert_date_i ON ccr_news (insert_date DESC);

SELECT *
FROM ccr_news
WHERE insert_date > 0;

or, if for some reason MySQL still uses a full table scan:

SELECT *
FROM ccr_news FORCE INDEX (ccr_news_insert_date_i)
WHERE insert_date > 0;

  Posted by Adam Tylmad on August 25, 2004
If you want to ORDER BY [columnname] ASC
and have the NULL rows in the bottom
you can use ORDER BY -[columnname] DESC
  Posted by Edward Hermanson on October 6, 2004
Select Name,Category FROM authors ORDER BY Category,Name;

Will allow you to sort by categories listed in a seperate table
IF the category column in this primary table contains ID values
from your ID column in your second reference table.

So your first "authors" table looks like:

id name category
1 Henry Miller 2
3 June Day 1
3 Thomas Wolf 2

and your second reference table looks like:

id category
1 Modern
2 Classics

Now when the order of categories is changed in the second table
the order of categories will be reflected in the primary table.

Then just select the categories from the reference table and put
the list into a numbered array. Then in your script when you run
across a category number from the first recordset just reference
the value from the index in the second array to obtain the value.
In php in the above example it might look like:

foreach ($recordset as $key => $record) {
echo $record["id"] . ":" . $record["name"] . ":" . $ordered_cats[$record["category"]];
}

This may seem obvious to some but I was pulling my hair out
trying to figure out how to order a recordset based on a list
from a different table. Hope this helps someone.

Ed
  Posted by Greg Covey on December 2, 2004
The LIMIT clause can be used when you would use TOP in Access or MS SQL.
  Posted by Kenan Bektas on December 14, 2004
(LINUX) By default, if you don't specify absolute path for OUTFILE in
select ... into OUTFILE "..."

It creates the file in "/var/lib/mysql/<database_name>"

Make sure current user has (NOT) a write permission in that directory.
  Posted by Kumar S on January 4, 2005
If You want to find the rows which are having a column with identical values then,

SELECT managerId, count(company) FROM manager GROUP BY company HAVING COUNT(company)>=8 (say)

Regards,
Kumar.S
  Posted by Imran Chaudhry on February 17, 2005
I found a nifty way of influencing the ORDER of rows returned by a query that helps in displaying a list with frequently accessed items at the top.

An example is a name/address form where the country is a selectable list. If most of your users are from the UK and US you may want to do something like:

SELECT * FROM countries ORDER by iso_code IN ('UK', 'US') desc

Which returns something like:
+----------+----------------------------------------+
| iso_code | name |
+----------+----------------------------------------+
| UK | United Kingdom |
| US | United States |
| AF | Afghanistan |
| AL | Albania |
| DZ | Algeria |
| AS | American Samoa |

Hope this helps someone! megazoid@hotmail.com
  Posted by Fahed Bizzari on February 22, 2005
It seems there is no way to select * from table where a certain field is distinct. In 2 sqls it is easy:

$sql9 = "SELECT DISTINCT field AS distinctfield FROM table ORDER BY distinctfield ";
$res9= $db->execute($sql9);
for($ll=0;$ll<$res9->getNumTuples();$ll++)
{
$row = $res9->getTupleDirect($ll);
$distinctfield = $row[distinctfield];
$sql8="select * from table WHERE field='distinctfield' ORDER BY distinctfield LIMIT 1";
}

But not one!

Fahed
  Posted by M Berman on February 22, 2005
reply to Fahed Bizzari's post, based on Havilland-Fortesque-Smedley's comment (above) the equivalent of select * while doing DISTINCT is:

select *, count(FIELD) from TABLE group by FIELD having count(FIELD)=1 into outfile 'foobar.txt';

then you can check the output. note that there are twice as many rows as records, because each unique row is followed by its count (in this case count=1). so just toss the .txt file into something and sort on the field containing the count and throw out all the rows =1. this is the same result as a select * distinct FIELD (as far as I can tell).

anyway, works for me. aloha. Lex
  Posted by M Berman on February 22, 2005
oh, about the previous post, it's not correct because distinct should be

count(FIELD)=>1

which still doesn't solve the DISTINCT part

Lex
  Posted by Gregory Turner on March 10, 2005
In regards to:
_______________________________________________
******************************************
I found a nifty way of influencing the ORDER of rows returned by a query that helps in displaying a list with frequently accessed items at the top.

An example is a name/address form where the country is a selectable list. If most of your users are from the UK and US you may want to do something like:

SELECT * FROM countries ORDER by iso_code IN ('UK', 'US') desc

Which returns something like:

+----------+----------------------------------------+
| iso_code | name |
+----------+----------------------------------------+
| UK | United Kingdom |
| US | United States |
| AF | Afghanistan |
| AL | Albania |
| DZ | Algeria |
| AS | American Samoa |
_______________________________________________
******************************************
If found that if you also add in another 'iso_code' column in the order by statment after the first one containing the IN() statment, it will sort the remaining records:
SELECT * FROM countries ORDER by iso_code IN ('UK', 'US') desc, iso_code
  Posted by Heywood on March 11, 2005
When using the SELECT ... INTO OUTFILE syntax, use a UNION to add headers. Here's an example for CSV output:

SELECT 'Fiscal Year','Location','Sales'
UNION
SELECT FY, Loc, Sales INTO OUTFILE 'salesreport.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM SalesTable;

This will add the text headers Fiscal Year, Location and Sales to your fields. Only caveat is with an ORDER BY statement, if you don't want your headers sorted along with your data you need to enclose it in parenthesis:

SELECT 'Fiscal Year','Location','Sales'
UNION
{SELECT FY, Loc, Sales INTO OUTFILE 'salesreport.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM SalesTable
ORDER BY Sales DESC);

  Posted by on April 21, 2005
To correct Lex one more time, it should be count(FIELD)>=1.

So the whole query for retrieving a whole row with one field distinct is:

select *, count(FIELD) from TABLE group by FIELD having count(FIELD)>=1;

Thanks, Lex. You are a lifesaver.
  Posted by Jerry Nelson on May 6, 2005
As a newbie to MySQL and to dealing with BLOBs, I had a difficult time trying to determine how to extract a BLOB field from the database back to a file. It turns out to be quite simple by doing the following SQL:

select blobfield into dumpfile '/tmp/blobfile' from blobtable;
  Posted by joel boonstra on May 12, 2005
In response to Heywood's tip about adding column headers to OUTFILEs...

Make sure that the format of the columns that match up with your headers doesn't limit the display of the headers. For instance, I was using the UNION tip to add a header to a column defined as char(2) (for storing a two-letter state code). The resulting CSV file only displayed the first two letters of my column header. The fix is simple, just use CAST() on the column in the second SELECT to convert it to the appropriate type. In my case, doing something like this:

SELECT 'state header' FROM table UNION SELECT CAST(state AS char) FROM table INTO OUTFILE [...]

worked just dandy. Hope that saves someone a little time.
  Posted by Rene Liethof on June 8, 2005
Arbitrary Ordering

I came across this example at
http://www.shawnolson.net/a/722/
Neat way of using the CASE statement.

Example for ordering price information
price is orderd ascending but the 0.00
prices end up underneath

SELECT dienst.dienst, dienst.url, dienst.info, dienst_prijs.dienst_eenheid, dienst_prijs.prijs, dienst_prijs.inc_btw, dienst_prijs.dienst_optie,
CASE dienst_prijs.prijs
WHEN dienst_prijs.prijs = '0.00' THEN 1000
WHEN dienst_prijs.prijs > '0.00' THEN 10
ELSE NULL
END AS orderme
FROM dienst, dienst_prijs
WHERE dienst.taal = 'nl' &&
dienst.dienst_type = 'Internet toegang' &&
dienst.dienst != 'alle diensten' &&
dienst.publiceer != '' &&
dienst_prijs.dienst_eenheid IN ( 'maand', 'jaar' ) &&
dienst.dienst = dienst_prijs.dienst
ORDER BY orderme, dienst_prijs.prijs

  Posted by Callum Macdonald on June 27, 2005
If you want to use ORDER BY before GROUP BY, the only way I've found to achieve it is with a subquery.

For example, if you want to get a list of users from a table UserActions sorted according to the most recent action (based on a field called Time) the query would be:

SELECT * FROM (SELECT * FROM UserActions ORDER BY Time DESC) AS Actions GROUP BY UserID ORDER BY Time DESC;

Without the subquery, the group is performed first, and so the first record that appears in the database (which is not necessarily in the order you want) will be used to determine the sort order. This caused me huge problems as my data was in a jumbled order within the table.

--Edit--
This same result can be achieved with the use of MAX(Time), so the query would be:

SELECT *, MAX(Time) AS LatestAction GROUP BY UserID ORDER BY LatestAction DESC;

As far as I can see, the subquery model still holds up if you need more complex sorting before performing the GROUP.
  Posted by Paul Montgomery on August 13, 2005
I've seen it asked elsewhere about how to select all duplicates, not just one row for each dupe.

CREATE TEMPORARY TABLE dupes SELECT * FROM tablename GROUP BY colname HAVING COUNT(*)>1 ORDER BY colname;
SELECT t.* FROM tablename t, dupes d WHERE t.colname = d.colname ORDER BY t.colname;
  Posted by Wayne Smith on November 4, 2005
Be careful about the "SELECT...INTO OUTFILE" options. They are similar to, but not exactly the same as, the mysqldump options.

Two things:

1) The options in mysqldump can be in any order, because they are true command-line options (that is, they are conceptually used together, but syntactically separate on the mysqldump command line). The options in the SELECT...INTO OUTFILE need to be in the exact order as specified in the documentation above.

2) The options MUST have dashes between the words (e.g., fields-enclosed-by) when use as options with the mysqldump utility, but MUST NOT have dashes when used as options with the SELECT...INTO OUTFILE. This may not be clear in the documentation above.

Wayne

  Posted by Geoff on November 9, 2005
In reply to Fahed Bizzari et al...

If you want to select all fields from distinct rows why not use:
SELECT DISTINCT * FROM table GROUP BY field;

Don't forget the DISTINCT relates to the ORDER BY / GROUP BY and has nothing to do with the 'select_expr'

If you want the count as well then use:
SELECT DISTINCT *, count(*) AS count FROM table GROUP BY field;

  Posted by Kumar Mitra-Endres on November 22, 2005
Where is the pagination code as offered by the google search machine????

Kumar/Germany

  Posted by Flavio Ventura on December 9, 2005
If you have a binary string type field and you want a case insensitive sorting you can use CAST() as follow:

case sensitive example (DECODE return a binary string):
----------------------------------------------------------------------------
SELECT DECODE(EncodedField) AS DecodedField
FROM TableWithEncodedField
ORDER BY DecodedField;

case insensitive solution:
---------------------------------
SELECT CAST(DECODE(EncodedField) AS CHAR) AS DecodedField
FROM TableWithEncodedField
ORDER BY DecodedField;

I hope it may be usefull.
  Posted by mike gieson on January 10, 2006
To select specific rows from the table use the IN statement.

Example:

SELECT * FROM table WHERE myid IN (2, 16, 93,102);

This would return multiple rows based on specific criteria.
  Posted by Todd Farmer on January 22, 2006
For large tables with auto incremented primary key values, I have found the following to be most efficient in obtaining one random row:

SELECT * FROM my_table
WHERE pk_column >=
(SELECT FLOOR( MAX(pk_column) * RAND()) FROM my_table)
ORDER BY pk_column
LIMIT 1;
  Posted by Vlado Kocan on January 28, 2006
Reply to Edward Hermanson post (above):

I prefer this way of sorting table by column values listed in another table:

The accnumber column in primary table contains ID values from ID column in the secondary table.

Primary table "contacts":
id name accnumber
1 Cooke 3
2 Peterson 3
3 Stevens 1

Secondary table "accounts":
id accname
1 Company1
2 Company2
3 Company3

SELECT contacts.lname, accounts.accname
FROM contacts, accounts
WHERE contacts.accnumber = accounts.id ORDER BY accname;
  Posted by Lars-Erik Hoffsten on March 14, 2006
ORDER BY textfield in natural order!?
Lets say you want the following result:
File1
File2
File10

I havn't found a way to do it in SQL, here is a way to do it in PHP (just replace 'order_by' to the field you want to order by):

$result = mysql_query("SELECT order_by,... FROM table");
$rows = array();
if($result)
{
while(($row = mysql_fetch_array($result, MYSQL_ASSOC)))
$rows[] = $row;
usort($rows, create_function('$a, $b', 'return strnatcasecmp($a["order_by"], $b["order_by"]);'));
}

  Posted by Michal Carson on March 19, 2006
SELECT ... INTO OUTFILE requires the id to have the FILE privilege. That is,

GRANT SELECT, FILE ON * . * TO "[whomever]"@ "localhost";

As noted above, the output directory must be writable by the id under which the mysqld process is running. Use "grep user= /etc/my.cnf " to find it.

  Posted by Andrew Culver on March 20, 2006
Selecting a random row in SQL? Try:

set @a = (SELECT COUNT(*)-1 c FROM palette)*RAND() DIV 1;
PREPARE STMT FROM 'SELECT * FROM palette LIMIT ?,1';
EXECUTE STMT USING @a;
  Posted by on March 21, 2006
If you want to keep field names, consider using mysqldump instead of SELECT INTO OUTFILE.

I use this method to transfer small amounts of data from our live database to our test database, for example when investigating a reported problem in our program code. (We cannot guarantee the field order across all our databases.)

rem Edit order number before running
rem Give password when prompted
rem Result files will be in current working directory
\mysql\bin\mysqldump livedb -uliveuser -p --no-create-info --tables orderpayment --where=orderid=2712>resultp.txt
\mysql\bin\mysqldump livedb -uliveuser -p --no-create-info --tables orderitem --where=orderid=2712>resulti.txt
\mysql\bin\mysqldump livedb -uliveuser -p --no-create-info --tables orderheader --where=id=2712>resulth.txt

  Posted by Zhao Xinyou on March 29, 2006
when you meet more conditions, you may use the following code:
select * from yourdatabase where fieldone='value1' and fieldtwo='value2';
  Posted by John Bachir on April 3, 2006
Fahed Bizzari, that is not 2 queries, that is $res9->getNumTuples() + 1 queries!
  Posted by Michael Heyman on April 28, 2006
To select the identifiers with the greatest value in each class (where each identifier falls into one class):

SELECT id_class,id FROM tbl,(SELECT MAX(val) AS val FROM tbl GROUP BY id_class) AS _tbl WHERE tbl.val = _tbl.val;

We had a table logging state changes for a series of objects and wanted to find the most recent state for each object. The "val" in our case was an auto-increment field.

This seems to be the simplest solution that runs in a reasonable amount of time.
  Posted by Rich Altmaier on May 4, 2006
In a student signup list, use sql to find classes which are
not full. Involves combined use of RIGHT JOIN, COUNT, WHERE, GROUP BY, HAVING, and ORDER BY.

CREATE TABLE `classdescription` (
`ClassID` mediumint(9) NOT NULL auto_increment,
`ClassType` varchar(10) NOT NULL default '',
`ClassName` varchar(50) NOT NULL default '',
`ClassDate` datetime NOT NULL default '0000-00-00 00:00:00',
`ClassMax` mediumint(9) default NULL,
PRIMARY KEY (`ClassID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE `class_signups` (
`s_PersonID` mediumint(9) NOT NULL default '0',
`s_ClassID` mediumint(9) NOT NULL default '0',
`s_Status` varchar(5) default NULL,
KEY `s_ClassID` (`s_ClassID`),
KEY `s_PersonID` (`s_PersonID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `classdescription` VALUES (2, 'firstaid', '', '2005-01-02 11:00:00', 2);
INSERT INTO `classdescription` VALUES (3, 'advanced-med', '', '2005-01-02 13:00:00', 1);

INSERT INTO `class_signups` VALUES (11, 2, '');
INSERT INTO `class_signups` VALUES (12, 2, '');

Now use RIGHT JOIN to list all class descriptions along with signups if any,
SELECT cs.s_ClassID, cs.s_PersonID, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMax
from class_signups cs RIGHT JOIN
classdescription cd on (cs.s_ClassID = cd.ClassID )
in itself, not too useful, but you can see classes
having no one signed up as a NULL.

To count the number of signups for each class:
SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMax
from class_signups cs RIGHT JOIN
classdescription cd on (cs.s_ClassID = cd.ClassID )
GROUP BY cd.ClassID
The COUNT/GROUP BY options show a row per unique ClassID, and the COUNT is adding up
non-null occurances of field s_ClassID. If we had used COUNT(*) then the class with
no signups would have counted 1 record, rather than the desired 0/NULL for no
signups.

Now we show only classes where the count of signups is less than ClassMax, meaning the
class has openings!
SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMax
from class_signups cs RIGHT JOIN
classdescription cd on (cs.s_ClassID = cd.ClassID )
GROUP BY cd.ClassID
HAVING ClassTotal < cd.ClassMax
The HAVING clause limits the after-JOIN output rows to ones matching its criteria, discarding others!

We may want to look only at the firstaid ClassType, so add a WHERE clause to
the JOIN,
SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMax
from class_signups cs RIGHT JOIN
classdescription cd on (cs.s_ClassID = cd.ClassID ) WHERE cd.ClassType='firstaid'
GROUP BY cd.ClassID
HAVING ClassTotal < cd.ClassMax
Now there are no outputs as firstaid is full, but
suppose we are looking in this list with respect
to a certain student PersonID==12. That is, we want to see classes this person can signup
for, including the ones they are already in!
In the case we need to disregard signups by PersonID==12 for e.g.,

SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMax
from class_signups cs RIGHT JOIN
classdescription cd on (cs.s_ClassID = cd.ClassID AND cs.s_PersonID <> 12) WHERE cd.ClassType='firstaid'
GROUP BY cd.ClassID
HAVING ClassTotal < cd.ClassMax
In the join we drop out signups of PersonID 12, so they don't get counted.

Finally we probably want to show the available classes in date order:

SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal , cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMax
from class_signups cs RIGHT JOIN
classdescription cd on (cs.s_ClassID = cd.ClassID AND cs.s_PersonID <> 12)
WHERE cd.ClassType='firstaid'
GROUP BY cd.ClassID
HAVING ClassTotal < cd.ClassMax ORDER BY ClassDate

I had fun figuring this out, I hope it works for you.
(sorry it was so long).
Rich

  Posted by YI ZHANG on May 10, 2006
If you cancel a long-time running query by Ctrl-C, you might find the CPU load of mysqld remains at 99%. That's because the query is still running on mysqld, and Ctrl-C only closes the client.
Now, you can enter mysql again and use command SHOW PROCESSLIST to check the thread of the query, and kill the query by command KILL thread_id.
I'm using mysql 5.0.21.
  Posted by Wade Bowmer on May 14, 2006
Be aware that SQL_CALC_FOUND_ROWS disables ORDER BY ... LIMIT optimizations (see bugs http://bugs.mysql.com/bug.php?id=18454 and http://bugs.mysql.com/bug.php?id=19553). Until it's fixed, you should run your own benchmarks with and without it.

  Posted by Marc Grue on June 24, 2006
Since the LIMIT clause of a SELECT statement doesn't allow user variables you can use a prepared statement as in the example above in the manual. An alternative is to load all record ids of yourTable into a temporary table as shown below. This also has the benefit of getting all data necessary for pagination of your result set:

CREATE PROCEDURE `listData`(IN _limitstart INT, IN _limit INT)
BEGIN
-- make a 'row container'
DROP TEMPORARY TABLE IF EXISTS AllRows;
CREATE TEMPORARY TABLE AllRows (rownum INT, id INT, label VARCHAR(50)) ENGINE=MEMORY;

-- insert all ids (and optional labels (for use in a page selector))
SET @a=-1;
INSERT INTO AllRows SELECT @a:=@a+1 AS rownum, id, CONCAT(first_name, ' ', last_name) AS label FROM yourTable;

## Output 1: total number of rows
SELECT @a+1 AS total_rows;

## Output 2: id/labels for pagination [see table 'NumberSeq' below]
SELECT * FROM AllRows
INNER JOIN NumberSeq ON AllRows.rownum = NumberSeq.n*_limit
WHERE (n*_limit) < @a+1;

## Output 3: data for list
SELECT yourTable.* FROM yourTable
INNER JOIN AllRows ON yourTable.id = AllRows.id
WHERE rownum >= _limitstart AND rownum < (_limitstart+_limit);

DROP TEMPORARY TABLE AllRows;
END

The NumberSeq table just contains the numbers 0, 1, 2, 3, ... 500 (or whatever limit you want to set on number of pages..):

CREATE PROCEDURE `createNumberSeq `()
BEGIN
DECLARE _n int default -1;
DROP TABLE IF EXISTS NumberSeq;
CREATE TABLE NumberSeq (n INT);
loop1: LOOP
SET _n = _n + 1;
INSERT INTO NumberSeq (n) VALUES _n;
IF _n >= 500 THEN
LEAVE loop1;
END IF
END LOOP loop1;
END

With smaller record sets the second approach is faster than the prepared statement approach. Haven't checked speed with bigger record sets, but suspect the first approach will win then...

Hope this helps to get around the limitations of the LIMIT clause. To the MySQL team: any plans to allow user variables in the LIMIT clause? (pleeeze!)
  Posted by Rene Lopez on June 23, 2006
If you want to get the record in an specific order you can do it like this

SELECT * FROM table ORDER BY FIELD( id, 23, 234, 543, 23 )

+----------+---------------------------+
| id | name |
+----------+---------------------------+
| 23 | rene |
| 234 | miguel |
| 543 | ana |
| 23 | tlaxcala |

or if the table as a name

SELECT * FROM table ORDER BY FIELD( name, 'miguel', 'rene', 'ana', 'tlaxcala' )

+----------+---------------------------+
| id | name |
+----------+---------------------------+
| 234 | miguel |
| 23 | rene |
| 543 | ana |
| 23 | tlaxcala |
  Posted by Marc Grue on June 24, 2006
Example of using dynamic column_name parameters in the ORDER BY clause of a SELECT statement in stored procedures:
http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html
(go to posting by Marc Grue on June 24 2006)
  Posted by Dennis Lindkvist on June 28, 2006
Or you could use this.

select [column], rand() as rnd from [table] order by rnd

Althoug there is still overhead compared to "order by null" its not as bad as "order by rand()".
  Posted by Chris Whitten on July 14, 2006
I was trying to figure out how to sort a varchar field which contained both number string and alphanumeric string. I wanted to sort it so that the numbers would be in order and then the alphanumeric entries would be in order. Here is the query that helped me accomplish that:

SELECT partnum, comments , if( partnum >0, cast( partnum AS SIGNED ) , 0 ) AS numpart,
if( partnum >0, 0, partnum ) AS stringpart
FROM `part`
ORDER BY `numpart` , `stringpart`
  Posted by Frank Flynn on October 6, 2006
If you wish to use OUTFILE or DUMPFILE with a variable for the file name you cannot simply put it in place - MySQL will not resolve the name.

But you can put the whole command into a variable and use "prepare" and "execute" for example:

SELECT @myCommand := concat("SELECT * into OUTFILE '/home/mysql/archive/daemonLog-", DATE_FORMAT(now(),'%Y%m%d-%H%i%s'), "' FROM daemonLog");
PREPARE stmt FROM @myCommand;
EXECUTE stmt;

This will work, Good luck.

  Posted by Michael Ekoka on November 8, 2006
Just my little contribution when it comes to random row selection used with mysql & php. Based on the solution that consists of returning the count(*) of a table, then using that value to select a random row.

SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM foo;
SELECT * FROM foo LIMIT {$rand_row}, 1;
or
SELECT COUNT(*) AS rows FROM foo;
SELECT * FROM foo LIMIT {rand(0,$rows-1)}, 1;

The problem with that solution from the MySQL standpoint is that there still remains the possibility of duplicate selections when we want more than one row, especially if the table is not that large (e.g. what are the chances of getting at least 2 duplicate rows while selecting 5 randomly, 1 at a time, out of a set of 10).

My approach is to rather generate unique random numbers from php, then fetch the corresponding table rows:

1- Use the appropriate php methods to fetch the table count from MySQL as done before:
SELECT COUNT(*) FROM foo;

2- Use php to generate some unique random numbers based on the count.

This is the php function that i use. It takes 3 arguments: the minimum and maximum range values, and the amount of unique random numbers to be returned. It returns these numbers as an array.


<?php
/*Array of Unique Random Numbers*/

function uniq_rand($min,$max,$size){
 
$randoms=array(); //this is our array
    
 /*if ($min > $max) swap their value*/
 
if($min>$max){
  
$min=$min^$max;$max=$min^$max;$min=$min^$max;
 }
    
 
/*if requested size of array is larger than possible 
 or if requested size of array is negative return*/
 
if ( (($max-$min)+1)<$size || $size<=){return false;}
 
 
/*while array has not reached the requested size
 keep generating random numbers and insert them 
 if they're not yet present in the array */
 
while (count($randoms)<$size){
  
$newval rand($min,$max);
  if(!
in_array($newval,$randoms)){$randoms[] = $newval;}
 }

 return 
$randoms;
}
?>


3- Once you receive your set of randoms from the above function, perform a query for each random:

<?php
foreach($randoms as $random_row){
$query="SELECT * FROM foo LIMIT $random_row, 1;"
//perform query, retrieve values and move on to the next random row
...
}
?>


That's it
-----

On a side note regarding the php random number generation function that I have here, I'm sure it's not the best solution all the time. For example, the closer the amount of random numbers gets to the range of numbers available the less efficient the function gets, i.e. if you have a range of 300 numbers and you want 280 of them unique and random, the function could spend quite some time trying to get the last 10 numbers into the array. Some probabilities get involved here, but I suspect that it would be faster to insert the 300 numbers directly into an array, shuffle that array, then finally select the 280 first entries and return them.

Also, as pointed earlier in the thread, keep in mind that if your table isn't that large, just performing the following works very well (e.g. selecting 5 random rows on a moderately large table):
SELECT * FROM foo ORDER BY RAND() LIMIT 5;
  Posted by Rustam Valiev on November 17, 2006
If you want use multilanguage queryies you cat use this:
Table 1
--------------
langid langname
--------------
1 rus
2 eng
3 den
---------------
Table 2 (catalog)
-----------------------
catid url
-----------------------
1 www.google.com
2 www.yandex.ru
3 www.mysql.com
------------------------
table 3 (titles of sites from Table 3)
-------------------------------------
langid catid title
-------------------------------------
1 1 Poiskovaya sistema
2 1 Search system
1 2 Portal
2 2 Portal
3 2 Portal
1 3 Sayt razrabotchikov MySQL
2 3 Site of MySQL's team
3 3 Bla bla bla
------------------------------------
And you need select sites from table2 on any language (for example Denmark), but site google.com have not title by Denmark. Ok if you can't select title by current language, you should select title by default language (here russian). You can make in one query
SELECT *, (
SELECT title
FROM table3
WHERE table3.catid = table2.catid AND langid = 3
UNION
SELECT title
FROM table3
WHERE table3.catid = table2.catid AND langid = 1
LIMIT 1
) as title
FROM table2

It very easy, but i think it query very big for MySQL if table2 contain around 1000-5000 rows, and site have 5000-6000 people per second.

You can make it another:
SELECT *, (SELECT title FROM table3 ORDER BY IF(langid='1',0,1) ASC LIMIT 1) as title FROM `table2`
i couldn't compare this queries, if anybody can compary spped of this method please write r.valiev@uzinfocom.uz (by russian (:^) .

Now my task more complexed, i need select any site from table2 :
1 - On current language
2 - If site have not title, Select title by default language
3 - If site have not title on default, Select title by any language.
I think if will make it by thats method - it will very big for MySQL.
  Posted by Ray Perea on December 8, 2006
In regards to:
Posted by Count Henry De Havilland-Fortesque-Smedley on January 13 2004 5:59am
--------------START QUOTE---------------------
Sometimes you want to retrieve the records that DONT match a select statement.

Consider this select:
SELECT CarIndex FROM DealerCatalog, BigCatalog WHERE
DealerCatalog.CarIndex=BigCatalog.CarIndex

This finds all the CarIndex values in the Dealer's catalog that are in the bigger distributor catalog.

How do I then find the dealer CarIndex values that ARE NOT in the bigger catalog?

The answer is to use LEFT JOIN - anything that doesn't join is given a NULL value , so we look for that:

SELECT CarIndex FROM DealerCatalog LEFT JOIN BigCatalog ON DealerCatalog.CarIndex=BigCatalog.CarIndex WHERE BigCatalog.CarIndex IS NULL
------------------END QUOTE--------------------------

I have found that the Left Join is quite expensive when doing this type of SQL Query. It is great if you have less than 1000 records in each table that you want to compare. But the real hardship is realized when you have 100,000 records in each table. Trying to do this type of join takes forever because each and every record in 1 table has to be compared to each and every record in the other table. In the case of 100,000 records, MySQL will do 10 BILLION comparisons (from what I have read, I may be mistaken).

So I tried the sql query above to see which rows in 1 table do not have a corresponding value in the other table. (Note that each table had close to 100,000 rows) I waited for 10 minutes and the Query was still going. I have since came up with a better way that works for me and I hope it will work for someone else. Here goes....

1: You must create another field in your base table. Let's call the new field `linked` (For the example above, we would perform this query ---ONLY ONCE--- to create the linked field in the DealerCatalog table.)

ALTER TABLE `DealerCatalog` ADD `linked` TINYINT NOT NULL ;

2: Now to get your results, simply execute the following queries instead of the left join query stated above

UPDATE `DealerCatalog` SET `linked` = 0;
UPDATE `DealerCatalog`, `BigCatalog` SET `linked` = 1 WHERE `DealerCatalog`.`CarIndex` = `BigCatalog`.`CarIndex`;
SELECT `CarIndex` FROM `DealerCatalog` WHERE `linked` = 0;

I know it is 3 queries instead of 1 but I am able to achieve the same result with 100K rows in each table in about 3 seconds instead of 10 minutes (That is just how long I waited until I gave up. Who knows how long it actually takes) using the LEFT JOIN method.

I would like to see if anyone else has a better way of dealing with this type of situation. I have been looking for a better solution for a few years now. I haven't tried MySQL 5 yet to see if there is a way to maybe create a view to deal with this situation but I suspect MySQL developers know about the expensive LEFT JOIN....IS NULL situation on large tables and are doing something about it.

Until then, you have my contribution
Ray Perea

  Posted by Frederic Theriault on December 8, 2006
Make sure you don't use stored functions in your WHERE clause if it is not necessary.

For our search feature, we needed to get an id using a stored function. Since it was in the WHERE clause, it reprocesses the function for every row! This could turn out to be pretty heavy.

If you can, do it in the FROM clause. Ex:
SELECT
...
FROM
...,
(select getSpecialID() as specialID) as tmp
WHERE
...

In our case we went from 6.5 sec query to 0.48 sec. We have over 2 million rows in our tables.
  Posted by Héctor Hugo Huergo on April 19, 2007
Hi! I'm using SELECT ... INTO OUTFILE. First I´ve permissions problems. Add to the user the FILE privileges... AND RESTART THE DAEMON :). Bye
  Posted by Will Jaspers on May 4, 2007
For anyone utilizing two or more tables to create select boxes, I've finally (and painstakingly) found a way to check if the item is selected.

-- Sample table 1, we'll call this 'STATES'
CREATE TABLE states
(
state_id int auto_increment not null,
state_code char(2) not null,
state_name varchar(100) not null,
UNIQUE(state_code),
PRIMARY KEY(state_id)
);

CREATE TABLE drivers
(
driver id int not null auto_increment,
driver_name varchar(255) not null,
PRIMARY KEY(driver_id)
);

CREATE TABLE drove_to_states
(
state_id int not null,
driver_id int not null,
arrived datetime not null,
departed datetime not null,
notes text
);

-- Query
SELECT
s.`state_code`,
s.`state_name`,
IF(state_id IN
(SELECT d2s.state_id
FROM drove_to_states d2s
WHERE driver_id = '%u'
), 1, null)
`selected`
FROM `states` s,
ORDER BY `state_name` ASC;

Using PHP's sprintf command, we can create a select field using this query:

<?php
[...]
$driver_id 1;
define("QUERY", (SEE ABOVE) );
define("OPTION",'<option value="%s"%s>%s</option>');
$query mysql_query(sprintf(QUERY$driver_id), $connect);
echo 
'<select>';
while(list(
$code,$state,$selected) = mysql_fetch_row($query))
{
 
$selected is_null($selected) ? null ' selected';
 echo 
sprintf(OPTION$code$selected$state);
}
echo 
'</select>';
[...]
?>


Hope this helps anyone.

If anyone has a better way of writing this, please post.
  Posted by Martin Sarfy on August 14, 2007
SELECT INTO OUTFILE creates world-writable files. To avoid this security risk, you can create new subdirectory with +x rights for mysql and your user only (e.g. using chown me:mysql restricted_dir, chmod 770 restricted_dir), and then save the file into this directory. This way only you and mysql process can modify the file.
  Posted by Dan Bogdan on October 27, 2007
If you want to copy a file from the server in other location you can use
select load_file('source_file') into OUTFILE 'target_file'
Security issue on windows ... you can copy any file from any folder even if you don't have access to that file
to an convenient folder where you have access !!

  Posted by Drazen Djurisic on November 13, 2007
If you need names from second table for more then 1 columns in first table.
Select
table1.id,
table1.konto,
table2.name as name1,
table1.konto1,
table2_2.name as name2,
table1.konto3,
table2_3.naziv as name3,
from table1
left join table2 on (table1.konto=table2.id)
left join table2 as table2_2 on (table1.konto2=table2_2.id)
left join table2 as table2_3 on (table1.konto3=table2_3.id)

  Posted by Rich Altmaier on February 16, 2008
As a variant on the random row selection question, I had the goal of reading out a limited set of rows, always in the same order, but starting at a different point in the sequence each time (like Facebook short list of members):
e.g. given records a, b, c, d, e, f
I want random selections of triplets such as:
b, c, d
c, d, e
f, a, b --> note I want wraparound!

The prior postings on random rows selections have shown:
SELECT * FROM foo ORDER BY count*RAND() LIMIT 5;
This will yield the 5 random rows, but not in the same record ordering.

To preserve order, including a wraparound,
we must UNION a pair of queries.
For e.g. to get 3 rows from a table of $counted rows,
where we have selected $start, which happens to be within
3 of the end, we wrap as:

(SELECT * FROM `Ordering` ORDER BY something LIMIT $start, 1) UNION
(SELECT * FROM `Ordering` ORDER BY something LIMIT 0, 2)

suppose the table has 6 rows, and we decide
randomly to start with row 6, then concretely:
(SELECT * FROM `Ordering` ORDER BY something LIMIT 5, 1) UNION
(SELECT * FROM `Ordering` ORDER BY something LIMIT 0, 2)

  Posted by engin karahan on February 17, 2008
As a variant on the random row selection question, I had the goal of reading out a limited set of rows, always in the same order, but starting at a different point in the sequence each time (like Facebook short list of members):
e.g. given records a, b, c, d, e, f
I want random selections of triplets such as:
b, c, d
c, d, e
f, a, b --> note I want wraparound!

The prior postings on random rows selections have shown:
SELECT * FROM foo ORDER BY count*RAND() LIMIT 5;
This will yield the 5 random rows, but not in the same record ordering.

To preserve order, including a wraparound,
we must UNION a pair of queries.
For e.g. to get 3 rows from a table of $counted rows,
where we have selected $start, which happens to be within
3 of the end, we wrap as:

(SELECT * FROM `Ordering` ORDER BY something LIMIT $start, 1) UNION
(SELECT * FROM `Ordering` ORDER BY something LIMIT 0, 2)

suppose the table has 6 rows, and we decide
randomly to start with row 6, then concretely:
(SELECT * FROM `Ordering` ORDER BY something LIMIT 5, 1) UNION
(SELECT * FROM `Ordering` ORDER BY something LIMIT 0, 2)

  Posted by Michael Yakobi on March 6, 2008
Rotating rows to columns - crosstab pivoted queries
Having the following tables where Attributes.objId refers to Objects.id:
| Objects                 Attributes  
+----+------+------+ +-------+-----+-------+
| id | type | name | | objId | key | value |
+====+======+======+ +=======+=====+=======+
| 1 | T1 | O1 | | 1 | K1 | V1 |
| 2 | T2 | O2 | | 1 | K2 | V2 |
| 3 | T1 | O3 | | 2 | K3 | V3 |
| 4 | T2 | O4 | | 2 | K4 | V4 |
| | 2 | K5 | V5 |
| | 3 | K1 | V6 |
| | 3 | K2 | V7 |
The common approach for selecting the attributes of each object into a single result-row per object is to join Objects with Attributes multiple times. However, not only such SELECT can grow very big and ugly, with large tables it becomes very slow.
This could be dealt with using group-by functions, so to select all the objects of type T1, use the following SQL:
| SELECT
| o.id,
| o.name,
| MAX(IF(a.key='K1', a.value, null)) as K1,
| MAX(IF(a.key='K2', a.value, null)) as K2
| FROM
| Objects o,
| Attributes a
| WHERE
| o.id = a.objid and
| o.type = 'T1'
| GROUP BY
| a.id
The result will be:
+----+------+----+----+
| id | name | K1 | K2 |
+====+======+====+====+
| 1 | O1 | V1 | V2 |
| 3 | O3 | V6 | V7 |
  Posted by Wiebe Cazemier on March 25, 2008
For those who don't fully understand the concept of joins, I wrote an article which might help.

http://www.halfgaar.net/sql-joins-are-easy
  Posted by Sam on April 8, 2008
I just spent a few hours figuring this one out and there doesn't seem to be much info online about it so I thought I'd share.

You should use the following syntax to create a CSV file in the format expected by Microsoft Excel:

... INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

However fields with carriage returns may break the CSV as MySQL will automatically close a field when the \r\n line break is found. To work around this, replace all \r\n breaks with \n. The field does not close on \n breaks and it will be read into a single cell in Excel. You can do this in the same SQL statement, for example:

SELECT REPLACE(field_with_line_breaks, '\r\n', '\n') FROM table INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

I also found that null values could break the CSV. These can be handled in a similar way:

SELECT IFNULL(possible_null_field, "") FROM table INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Note: this replaces NULL values with an empty string which is technically not the same thing but it will give you an empty cell in Excel instead of breaking the CSV structure and shifting the following cells to the left.
  Posted by Paris Alex on October 16, 2009
To create a SELECT statement that categorize and label its result set according to special rules, try this...

SELECT 'cheap' AS priceCat, productName productCount FROM MyProducts WHERE price < 1000
UNION
SELECT 'moderate' AS priceCat, productName FROM MyProducts WHERE price >= 1000 AND price <2000
UNION
SELECT 'expensive' AS priceCat, productName FROM MyProducts WHERE price >= 2000

It essentially returns a two column result set. The first column contains the word 'cheap', 'moderate' or 'expensive' depending on the price of the product. The second column is the product name. This query can easily be modified to return a count of number of products categorized by the price range:

SELECT 'cheap' AS priceCat, COUNT(*) productCount FROM MyProducts WHERE price < 1000
UNION
SELECT 'moderate' AS priceCat, COUNT(*) FROM MyProducts WHERE price >= 1000 AND price <2000
UNION
SELECT 'expensive' AS priceCat, COUNT(*) FROM MyProducts WHERE price >= 2000

It may sound like an obvious thing the an experienced SQL guy, but I think this tip will be useful to a beginner. Hope this tip helps a SQL developer soon! ;-)


  Posted by Joseph Triplett on October 16, 2009
to pass the output of a SELECT into a variable array for further processing (PHP), do the following:

$array = ("SELECT ID, post_title, FROM Posts", ARRAY_A);

Notice the "ARRAY_A" declaration at the end of the variable assignment.

I have had issues passing SQL statements into arrays, when the "ARRAY_A" declaration is not made.
  Posted by Yury Ramanouski on November 13, 2009
# To select a random row of the table `tbl`
# I call the following procedure:

CREATE PROCEDURE select_rnd()
BEGIN
START TRANSACTION;
SELECT FLOOR(RAND() * COUNT(*)) INTO @rnd FROM tbl;
PREPARE stmt FROM 'SELECT * FROM tbl LIMIT ?, 1';
EXECUTE stmt USING @rnd;
COMMIT;
END;
  Posted by Peter Colclough on November 26, 2009
A really good speedup for those using 'Group By'. This is reported to MySql who are looking atr it, but can halve the speed of your query.

If you have a query that looks like:

Select col1, col2, col3
From tab1, tab2 ...
Group by col1, col2

You can add the following:
Group By col1, col2 WITH ROLLUP
Having (col1 IS NOT NULL) and (Col2 is not NUll)

This totals the 'groupings' but then removes those rows from the query. At the moment it is believed that an optimisation was performed for the 'WITH ROLLUP' that didn't make it into the main optimisation...

HTH

  Posted by Jon Webb on December 7, 2009
Ordering the Order By...
SELECT country,population FROM places ORDER BY country='UK' DESC, country ASC
This gives:
UK
Agola
Belgium
etc
  Posted by Michael Ash on December 17, 2009
Several users asked about including headers, i.e., column names or variable names, in the "INTO OUTFILE" syntax.

One approach is to use the "--column-names" option in the mysql invocation:

mysql --column-names -e 'SELECT * FROM mysql.user' > test.dat

(This creates a tab-delimited file test.dat with column names in the first row followed by the query results.)

  Posted by Elliot Greene on June 4, 2010
I discovered a well placed parentheses can make a difference in output. This Query search at least three columns for data like the $query variable.

Example 1: (This doesn't work)

$query = "Whatever text";

$sql2 = "SELECT * FROM $tbl_name WHERE CompanyName LIKE '%". $query ."%' OR description LIKE '%". $query ."%' OR KeywordTags LIKE '%". $query ."%' AND Active='yes' AND State=Florida ";

Example 2: (Works for Me)
Notice the parentheses enclosing the "WHERE" section of the query separating it from the final "AND" Section.

$sql2 = "SELECT * FROM $tbl_name WHERE (CompanyName LIKE '%". $query ."%' OR description LIKE '%". $query ."%' OR KeywordTags LIKE '%". $query ."%' AND Active='yes') AND State=Florida ";

Regards,
Elliot
http://www.sioure.com
  Posted by pablo uribe on January 10, 2011
You can use this example to look for uniques rows using distinct over a mix of fields. use concat .

para hacer un distinct de varias columnas se puede usar concat

SELECT distinct(concat(id_cliente,rut,fecha_cesion)), id_cliente,rut,fecha_cesion FROM `tmp_consolidado` WHERE 1

www.puribe.cl
  Posted by Sagi Rozen on January 25, 2011
In order to select random rows from a table don't use the ORDER BY RAND() clause.
You can get a much better performing query if you use the RAND() function at the WHERE clause only. This query will not result in file sort and will stop as soon as it get to the limit.
See http://www.rndblog.com/how-to-select-random-rows-in-mysql/

  Posted by erick ringot on March 31, 2011
RECURSION / RECURSIVE SELECT :

1) Let `task` be a MySql table containing at least 2 columns: id (primary key), pid (parent id - may be NULL) so that the rows form a classic tree structure.

2) Suppose you want to extract the tree relative to a particular actual id (constituted by itself and all its spawns) so that you need a recursive select which is unfortunately not implemented in MySql.

You can proceed by writing a recursive function as below.

CREATE PROCEDURE `TASKTREE`(tid INT UNSIGNED, flag BOOLEAN)
BEGIN
--
-- TASKTREE(id,flag)
-- recursive function, must be called with flag=FALSE
-- tid is the task (row) id
-- creation of a temporary table `tasktree`
-- containing the tree relative to tid
--
declare xid,xpid INT UNSIGNED;
declare tend BOOLEAN DEFAULT FALSE;
declare tcur CURSOR FOR SELECT id,pid FROM `task`
WHERE ((tid>0 AND pid=tid) OR (tid=0 AND pid IS NULL));
declare CONTINUE HANDLER FOR NOT FOUND SET tend=TRUE;
--
-- external call : flag MUST be FALSE
-- -> creation of the temporary table `tasktree`
--
IF (NOT flag) THEN
DROP TEMPORARY TABLE IF EXISTS `tasktree`;
CREATE TEMPORARY TABLE `tasktree` (
`id` int(10) unsigned NOT NULL,
`pid` int(10) unsigned,
PRIMARY KEY (`id`));
SELECT pid FROM `task` WHERE id=tid INTO xpid;
INSERT `tasktree` SET id=tid,pid=xpid;
END IF;
--
-- recursive (internal) call: flag MUST be TRUE
--
OPEN tcur;
tscan:LOOP
FETCH tcur INTO xid,xpid;
IF tend THEN LEAVE tscan; END IF;
INSERT `tasktree` SET id=xid,pid=xpid;
CALL TASKTREE(xid,TRUE);
END LOOP;
CLOSE tcur;
END

-----------------
Note: Don't omit to set the global variable max_sp_recursion_depth to an adequate positive value (for instance in the file 'my.ini').
  Posted by Charles Peterson on February 22, 2012
Reply to Post by Heywood on March 11 2005 2:04pm
QUOTE
When using the SELECT ... INTO OUTFILE syntax, use a UNION to add headers. Here's an example for CSV output:

SELECT 'Fiscal Year','Location','Sales'
UNION
SELECT FY, Loc, Sales INTO OUTFILE 'salesreport.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM SalesTable;

This will add the text headers Fiscal Year, Location and Sales to your fields. Only caveat is with an ORDER BY statement, if you don't want your headers sorted along with your data you need to enclose it in parenthesis:

SELECT 'Fiscal Year','Location','Sales'
UNION
{SELECT FY, Loc, Sales INTO OUTFILE 'salesreport.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM SalesTable
ORDER BY Sales DESC);

END QUOTE...

Here is a more dynamic option for adding column_names to the top of output...

SELECT group_concat( column_name
SEPARATOR "," )
FROM information_schema.columns
WHERE table_name = 'SalesTable'
GROUP BY table_name
UNION
{SELECT * INTO OUTFILE 'salesreport.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM SalesTable
ORDER BY Sales DESC);

  Posted by Roland Giesler on June 23, 2012
In an effort to automate the exporting of data for a larger number of tables to .csv file by using SELECT ... INTO OUTFILE, I have created a stored procedure to facilitate this. It's somewhat bulky, but please refer to http://lifeboysays.wordpress.com/2012/06/23/mysql-how-to-export-data-to-csv-with-column-headers/ if you have need for this.
Sign Up Login You must be logged in to post a comment.