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


MySQL 5.6 リファレンスマニュアル  /  ...  /  比較関数と演算子

12.3.2 比較関数と演算子

表 12.3 比較演算子

名前 説明
BETWEEN ... AND ... 値が値の範囲内に含まれているかどうかを確認します
COALESCE() NULL 以外の最初の引数を返します
= 等価 (等しい) 演算子
<=> NULL 安全等価演算子
> 右不等 (より多い) 演算子
>= 以上 (より多いか等しい) 演算子
GREATEST() 最大の引数を返します
IN() ある値が値セット内に含まれているかどうかを確認します
INTERVAL() 第 1 引数より小さい引数のインデックスを返します
IS ブーリアンに対して値をテストします
IS NOT ブーリアンに対して値をテストします
IS NOT NULL NOT NULL 値テスト
IS NULL NULL 値テスト
ISNULL() 引数が NULL かどうかをテストします
LEAST() 最小の引数を返します
< 左不等 (より少ない) 演算子
<= 以下 (より少ないか等しい) 演算子
LIKE 単純なパターン一致
NOT BETWEEN ... AND ... 値が値の範囲内に含まれていないかどうかを確認します
!=, <> 不等価 (等しくない) 演算子
NOT IN() 値が値セット内に含まれていないかどうかを確認します
NOT LIKE 単純なパターン一致の否定
STRCMP() 2 つの文字列を比較します

比較演算の結果は、1 (TRUE)、0 (FALSE)、または NULL の値になります。これらの演算は、数字と文字列の両方で機能します。必要に応じて、文字列は数字に、数字は文字列に自動的に変換されます。

次の関係比較演算子を使用すれば、スカラーオペランドだけでなく行オペランドも比較できます。

=  >  <  >=  <=  <>  !=

行比較の例については、セクション13.2.10.5「行サブクエリー」を参照してください。

このセクションで示す関数の一部では、1 (TRUE)、0 (FALSE)、または NULL 以外の値が返されます。たとえば、LEAST()GREATEST() です。ただし、返される値は、セクション12.2「式評価での型変換」で説明したルールに従って実行された比較演算に基づきます。

CAST() 関数を使用すると、比較目的で値を特定の型に変換できます。CONVERT() を使用すると、文字列値を別の文字セットに変換できます。セクション12.10「キャスト関数と演算子」を参照してください。

デフォルトでは、文字列の比較では大文字と小文字が区別されず、現在の文字セットが使用されます。デフォルトは latin1 (cp1252 西ヨーロッパ言語) であり、英語でも正常に機能します。

  • =

    等しい:

    mysql> SELECT 1 = 0;
            -> 0
    mysql> SELECT '0' = 0;
            -> 1
    mysql> SELECT '0.0' = 0;
            -> 1
    mysql> SELECT '0.01' = 0;
            -> 0
    mysql> SELECT '.01' = 0.01;
            -> 1
    
  • <=>

    NULL - 安全等価。この演算子では、= 演算子のように等価比較が実行されますが、両方のオペランドが NULL であれば、NULL でなく 1 が返され、一方のオペランドが NULL の場合は、NULL でなく 0 が返されます。

    mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
            -> 1, 1, 0
    mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
            -> 1, NULL, NULL
    
  • <>, !=

    等しくない:

    mysql> SELECT '.01' <> '0.01';
            -> 1
    mysql> SELECT .01 <> '0.01';
            -> 0
    mysql> SELECT 'zapp' <> 'zappp';
            -> 1
    
  • <=

    より少ないか等しい:

    mysql> SELECT 0.1 <= 2;
            -> 1
    
  • <

    より少ない:

    mysql> SELECT 2 < 2;
            -> 0
    
  • >=

    より多いか等しい:

    mysql> SELECT 2 >= 2;
            -> 1
    
  • >

    より多い:

    mysql> SELECT 2 > 2;
            -> 0
    
  • IS boolean_value

    boolean_valueTRUEFALSE、または UNKNOWN にすることができるブール値に対して値をテストします。

    mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
            -> 1, 1, 1
    
  • IS NOT boolean_value

    boolean_valueTRUEFALSE、または UNKNOWN にすることができるブール値に対して値をテストします。

    mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
            -> 1, 1, 0
    
  • IS NULL

    値が NULL かどうかをテストします。

    mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
            -> 0, 0, 1
    

    ODBC プログラムとの連携が正しく機能するように、MySQL では IS NULL の使用時に次の追加機能がサポートされます。

    • sql_auto_is_null 変数が 1 に設定されている場合は、自動的に生成された AUTO_INCREMENT 値を正常に挿入するステートメントのあとに、次の形式のステートメントを発行すれば、その値を検索できます。

      SELECT * FROM tbl_name WHERE auto_col IS NULL
      

      ステートメントが行を返す場合、返される値は LAST_INSERT_ID() 関数を呼び出した場合と同じです。複数行の挿入後の戻り値などについての詳細は、セクション12.14「情報関数」を参照してください。AUTO_INCREMENT 値を正常に挿入できなかった場合、SELECT ステートメントは行を返しません。

      IS NULL の比較を使用して AUTO_INCREMENT 値を取得する動作は、sql_auto_is_null = 0 を設定すると無効にできます。セクション5.1.4「サーバーシステム変数」を参照してください。

      MySQL 5.6 では sql_auto_is_null のデフォルト値は 0 です。

    • NOT NULL として宣言された DATE および DATETIME カラムでは、次のようなステートメントを使用することで、特殊な日付 '0000-00-00' を検索できます。

      SELECT * FROM tbl_name WHERE date_column IS NULL
      

      ODBC では '0000-00-00' 日付値がサポートされていないため、一部の ODBC アプリケーションを取得する際に、これが必要になります。

      Obtaining Auto-Increment Values、およびConnector/ODBC Connection ParametersFLAG_AUTO_IS_NULL オプションについての説明を参照してください。

  • IS NOT NULL

    値が NULL でないかどうかをテストします。

    mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
            -> 1, 1, 0
    
  • expr BETWEEN min AND max

    exprmin より多いか等しく、exprmax より少ないか等しい場合、BETWEEN1 を返し、それ以外では 0 を返します。すべての引数の型が同じであれば、これは式 (min <= expr AND expr <= max) と同等です。それ以外の場合は、セクション12.2「式評価での型変換」に記載したルールに従って型変換が実行されますが、3 つのすべての引数に適用されます。

    mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
            -> 1, 0
    mysql> SELECT 1 BETWEEN 2 AND 3;
            -> 0
    mysql> SELECT 'b' BETWEEN 'a' AND 'c';
            -> 1
    mysql> SELECT 2 BETWEEN 2 AND '3';
            -> 1
    mysql> SELECT 2 BETWEEN 2 AND 'x-3';
            -> 0
    

    日付または時間の値とともに BETWEEN を使用したときの結果を最適にするには、CAST() を使用して明示的に値を目的のデータ型に変換します。例 : DATETIME を 2 つの DATE 値と比較する場合は、DATE 値を DATETIME 値に変換します。DATE との比較で '2001-1-1' などの文字列定数を使用する場合は、文字列を DATE にキャストします。

  • expr NOT BETWEEN min AND max

    これは、NOT (expr BETWEEN min AND max) と同じです。

  • COALESCE(value,...)

    リストの最初の非 NULL 値を返します。非 NULL 値がない場合は、NULL を返します。

    mysql> SELECT COALESCE(NULL,1);
            -> 1
    mysql> SELECT COALESCE(NULL,NULL,NULL);
            -> NULL
    
  • GREATEST(value1,value2,...)

    2 つ以上の引数がある場合は、最大の (最大値の) 引数を返します。引数は、LEAST() のルールと同じルールを使用して比較されます。

    mysql> SELECT GREATEST(2,0);
            -> 2
    mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
            -> 767.0
    mysql> SELECT GREATEST('B','A','C');
            -> 'C'
    

    引数のいずれかが NULL である場合、GREATEST()NULL を返します。

  • expr IN (value,...)

    exprIN リストのいずれかの値と等しい場合は 1 を返し、それ以外の場合は 0 を返します。すべての値が定数の場合は、expr の型に従って評価され、ソートされます。その際の項目の検索は、バイナリ検索を使って行われます。つまり、IN 値のリストがすべて定数で構成されている場合、IN は非常に高速です。それ以外の場合は、セクション12.2「式評価での型変換」で説明したルールに従って型変換が実行されますが、すべての引数に適用されます。

    mysql> SELECT 2 IN (0,3,5,7);
            -> 0
    mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
            -> 1
    

    引用符で囲まれた値 (文字列など) と囲まれていない値 (数字など) の比較ルールは異なるため、IN リストの引用符で囲まれた値と囲まれていない値を決して混同しないでください。したがって、型を混同すると、整合性のない結果になる可能性があります。たとえば、IN 式を次のように記述しないでください。

    SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');
    

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

    SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');
    

    IN リストの値の数は、max_allowed_packet 値によってのみ制限されます。

    SQL の標準に準拠するために、左側の式が NULL である場合だけでなく、リストに一致が見つからない場合やリストの式のいずれかが NULL である場合にも、INNULL を返します。

    IN() 構文は、特定のタイプのサブクエリーを作成する際にも使用できます。セクション13.2.10.3「ANY、IN、または SOME を使用したサブクエリー」を参照してください。

  • expr NOT IN (value,...)

    これは、NOT (expr IN (value,...)) と同じです。

  • ISNULL(expr)

    exprNULL の場合、ISNULL()1 を返し、それ以外の場合は 0 を返します。

    mysql> SELECT ISNULL(1+1);
            -> 0
    mysql> SELECT ISNULL(1/0);
            -> 1
    

    = の代わりに ISNULL() を使用すると、値が NULL であるかどうかをテストできます。(= を使用して値を NULL と比較すると、常に false が発生します。)

    ISNULL() 関数は IS NULL 比較演算子と、いくつかの特殊な動作を共有します。IS NULL の説明を参照してください。

  • INTERVAL(N,N1,N2,N3,...)

    N < N1 の場合は 0 を返し、N < N2 などの場合は 1 を返し、NNULL の場合は -1 を返します。すべての引数は整数として処理されます。この関数が正しく機能するには、N1 < N2 < N3 < ... < Nn とする必要があります。これは、バイナリ検索が使用されていることが理由です (非常に高速)。

    mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
            -> 3
    mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
            -> 2
    mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
            -> 0
    
  • LEAST(value1,value2,...)

    2 つ以上の引数がある場合は、最小の (最小値の) 引数を返します。引数は、次のルールを使用して比較されます。

    • 引数が NULL である場合、結果は NULL になります。比較は必要ありません。

    • 戻り値が INTEGER コンテキストで使用されている場合、またはすべての引数が整数値である場合は、整数として比較されます。

    • 戻り値が REAL コンテキストで使用されている場合、またはすべての引数が実数値である場合は、実数として比較されます。

    • 引数が数字と文字列が混在して構成されている場合は、数字として比較されます。

    • 引数が非バイナリ (文字) 文字列の場合は、非バイナリ文字列として比較されます。

    • ほかのすべてのケースでは、引数はバイナリ文字列として比較されます。

    mysql> SELECT LEAST(2,0);
            -> 0
    mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
            -> 3.0
    mysql> SELECT LEAST('B','A','C');
            -> 'A'
    

    一部のボーダーラインケースでは、前述の変換ルールで異常な結果が生成される可能性があります。

    mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);
            -> -9223372036854775808
    

    これは、MySQL が 9223372036854775808.0 を整数のコンテキストで読み取ることが原因で発生します。整数表記では値を保持するのに十分でないため、符号付き整数にラップします。


User Comments
  Posted by Peter on May 17, 2002
ASP users: if you're getting empty recordset
returned when using COALESCE, add "OPTION=16384"
to your connectionstring, or check "Change Bigint
to Int" in the DSN manager!
  Posted by on May 17, 2002
If you are looking for something like:
SELECT id,name,perm_list FROM users WHERE 'write'
IN perm_list
where 'perm_list' contains a comma separated list
of privileges, you would try to use:
SELECT id,name FROM users WHERE FIND_IN_SET
('write',perm_list)>0;
  Posted by Per Persson on January 28, 2005
The IN operator also works with tuples, at least in version 4.1:

mysql> select (3,4) in ((2,3),(3,4));
+------------------------+
| (3,4) in ((2,3),(3,4)) |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.15 sec)

mysql> select (3,5) in ((2,3),(3,4));
+------------------------+
| (3,5) in ((2,3),(3,4)) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)

  Posted by DRB on April 4, 2006
If you want to do a case sensitive string comparision (for ex. username/password) then simply add BINARY to your statement.

SELECT * FROM sometable WHERE BINARY somecolumn='somestring';

For more information see Section 12.8, “Cast Functions and Operators” as mentioned above.
  Posted by James Alday on January 5, 2007
I use PHP with MySQL and was expecting similar behaviour out of its operators, which led me to mess up a program when they didn't behave similarly... I post this as a warning to others who may experience the same problem (as there is no mention in the code examples above).

In our DB there is a column that was added after creation and defaults to null. All of the old records are thus marked null . Some newer records are marked with a source of where the record came from, so during a check for duplicates I put a clause like this in my query:

SOURCE != 'external'

!= (or <>) doesn't see NULL as something that can be compared to and thus doesn't compare itself to those records that have the field marked null! Thus hundreds of duplicate records were added because the comparison was failing.

Changing it to this fixed the problem:

SOURCE != 'external' || SOURCE IS NULL

Hope that helps someone!
  Posted by Bill Wilkinson on November 29, 2007
James Alday could have solved his problem a couple of other ways, at least one of which is almost surely faster.

He ended up doing
. . . SOURCE != 'external' || SOURCE IS NULL
so that the comparison operator (that is, the !=) would be effectively ignored if the field SOURCE is null.

Other ways:

. . . WHERE IFNULL( SOURCE, '' ) != 'external'
or
. . . WHERE ( SOURCE <=> 'external' ) = 0
or
. . . WHERE NOT ( SOURCE <=> 'external' )

Almost surely the last of those will perform better.

  Posted by Axel Axel on December 14, 2007
If you want to compare an empty string to a numeric value or an integer field, you'll have to CAST the integer field or value to a string, due to the fact that for mysql, a zero (the integer one) equals to an empty string

Example :
SELECT 0 = '';
==> 1

SELECT '0' = '';
==> 0

SELECT CAST(0 AS CHAR) = '';
==> 0

This is common when you want to check user input : if a user inputs a "0" for a field, the check without cast will fail because mysql thinks this is an empty string.

  Posted by Thaylor Harmor on December 21, 2007
If you're playing with GREATEST and Dates you should not use NULL when comparing.

Example (MYSQL 5.0.44):
SELECT GREATEST('2007-12-31 23:59:59', '');
==> 2007-12-31 23:59:59

SELECT GREATEST('2007-12-31 23:59:59', '2037-01-01 00:00:00');
==> 2037-01-01 00:00:00

SELECT GREATEST('2007-12-31 23:59:59', NULL);
==> NULL

Work around is to test the data for NULL using ISNULL.

Here is an example with <columnB> that may or may not be NULL:

SELECT IF( ISNULL( columnB ), columnA, GREATEST( columnA, columnB) );

If columnB is NULL then the output will be columnA, otherwise its the result of GREATEST( columnA, columnB ).
  Posted by Michal Borychowski on February 16, 2009
COALESCE() function is very useful if you need to calculate the average value of items stored in a row (not in a column where you would use AVG())

Exemplary usage when we have three items per row would be:

SELECT *,
(COALESCE(V.rank_0, 0)
+ COALESCE(V.rank_1, 0)
+ COALESCE(V.rank_2, 0))
/
(3 -
(COALESCE(V.rank_0 - V.rank_0, 1)
+ COALESCE(V.rank_1 - V.rank_1, 1)
+ COALESCE(V.rank_2 - V.rank_2, 1))
) AS row_avg FROM voting V

I talk about it in more detail on my blog post at:
http://tech-blog.borychowski.com/index.php/2009/02/mysql/average-value-in-a-row/

  Posted by Landon Springer on June 13, 2010
When working with the "SET" datatype, it's useful to use the "COALESCE" function to add items to the set:

COALESCE(CONCAT(my_set, ",new_value"), "new_value")

This helped me to stop triggering data truncated errors. Cheers!
  Posted by Ilan Hazan on October 17, 2010
I found a way to optimize the MySQL IN() Comparison Operation in the case the field which the IN clause refers to, is part of the index used to execute the query. In this case, using the IN() operator will perform badly, as it first evaluate a full and unlimited sub query for each value in the IN clause, then, at completion, will merge the results and limit the returned result set.
My solution is to use a union of limited sub-queries for each value at the IN() clause.
Please read more about it: http://www.mysqldiary.com/optimizing-the-mysql-in-comparison-operations-which-include-the-indexed-field/
  Posted by Dmitrii Tisnek on May 2, 2012
Mixing data types in the in operator will make your queries slow, e.g.:

select * from account where account_id in ('','aaa'); 0 rows, 0.00 sec
select * from account where account_id in (123,124); 0 rows, 0.00 sec
select * from account where account_id in ('',123); 0 rows, 1 min 10.87 sec

table in questions has 4M rows, account_id is numeric, primary key
  Posted by Mike Jonsson on February 28, 2014
Well this works for me on my old 5.1 setup of community server.

SELECT COUNT(id) OR 0 FROM tblName
WHERE fldName = 1;

Mitigating all NULL errors in return by defaulting to a returned ZERO on NULL.

Perhaps not a standard SQL or ANSII format but we are all using MySQL and not JohnnyDoe's Home Brew SQL.

  Posted by Michael Muryn on March 5, 2014
When building a query dynamically, if you have to verify if a string value is in a given list of string and use the "IN" statement like this:

SELECT
....r.id AS recipe_id,
....GROUP_CONCAT(
..........DISTINCT concat('"', ra.code, '"')
..........ORDER BY ra.code
..........SEPARATOR ', '
.....) AS recipe_attribute_list
FROM recipe r
LEFT JOIN recipe_attribute ra ON
....ra.recipe_id = r.id
....AND ra.code IN ('ALCOHOL_FREE', 'NUTS_FREE', 'VEGETARIAN');

And then, if your list is empty, you still want your recipes, it just won't have any attributes. If you have the brilliant idea to replace the IN content with false like this

...
AND ra.code IN (false);
...

You'll get a major surprise as potentially all records will be sent.
Why? Because "code" is a string value and false is an alias to 0, which is an int. And when comparing string to int, the string is converted to an int, and unless the string begin by a number (e.g., "3abc"), then it will be converted to 0. So 0 IN (0), will of course evaluate to true.

So what to do? For my example:

a) you can use null instead of false, i.e.:
IN (null)

b) you can replace the whole condition with false, i.e.:
AND ra.code IN ('ALCOHOL_FREE', 'NUTS_FREE', 'VEGETARIAN');
will become:
AND false
this is clean because it give the result of the evaluation if SQL was supporting empty list.

c) you can of course remove the whole JOIN if it is logical, but it is a less straightforward approach and you won't be able to always do this.

I prefer 'b' here, but solutions might differ depending on the need of the query, or your style.
Sign Up Login You must be logged in to post a comment.